---
jupyter:
  jupytext:
    text_representation:
      extension: .md
      format_name: markdown
      format_version: '1.3'
      jupytext_version: 1.14.7
  kernelspec:
    display_name: worker_env
    language: python
    name: worker_env
---

<div style="width: 100%;text-align:right;display: flex; align-items: top;">
    <div style="float: left;width: 80%;text-align:left">
        <h1>Wikidata: Events SPARQL Query<a class="tocSkip">&#182;</a></h1>
        <p><em><a href="mailto:alexander.dunkel@tu-dresden.de">Alexander Dunkel</a>, Institute of Cartography, TU Dresden</em></p></div>
    <div style="width:256px;text-align:right;margin-top:0px;margin-right:10px"><a href="https://gitlab.hrz.tu-chemnitz.de/ad/ephemeral_events"><img src="https://kartographie.geo.tu-dresden.de/ad/wip/ephemeral_events/version.svg"></a></div>
</div>

```python tags=["hide_code"] jupyter={"source_hidden": true} editable=true slideshow={"slide_type": ""}
from IPython.display import Markdown as md
from datetime import date

today = date.today()
with open('/.version', 'r') as file: app_version = file.read().split("'")[1]
md(f"Last updated: {today.strftime('%b-%d-%Y')}, [Carto-Lab Docker](https://gitlab.vgiscience.de/lbsn/tools/jupyterlab) Version {app_version}")
```

Visualization of events (for Nevada example) queried from Wikidata using SPARQL.

# Preparations


Create environment

```python
!python -m venv /envs/wikidata_venv
```

Install [qwikidata](https://github.com/kensho-technologies/qwikidata) in a `venv` and link the Python Kernel to Jupyter Lab.

```bash
if [ ! -d "/envs/wikidata_venv/lib/python3.10/site-packages/qwikidata" ]; then
    /envs/wikidata_venv/bin/python -m pip install qwikidata ipykernel pandas > /dev/null 2>&1
else
  echo "Already installed."
fi
# link
if [ ! -d "/root/.local/share/jupyter/kernels/qwikidata" ]; then
    echo "Linking environment to jupyter"
    /envs/wikidata_venv/bin/python -m ipykernel install --user --name=qwikidata
else
  echo "Already linked."
fi
```

Hit <kbd>F5</kbd> and select the `qwikidata` Kernel on the top-right corner of Jupyter Lab.


See the package versions used below.

```python editable=true slideshow={"slide_type": ""} tags=["hide_code"] jupyter={"source_hidden": true}
import sys
from pathlib import Path
module_path = str(Path.cwd().parents[0] / "py")
if module_path not in sys.path:
    sys.path.append(module_path)
from modules.base import tools
root_packages = [
        'qwikidata', 'ipykernel', 'pandas']
tools.package_report(root_packages)
```

# Query wikidata using SPARQL


import dependencies

```python
import csv
import pandas as pd
from qwikidata.sparql import return_sparql_query_results
```

Define query:
- use distance query to Nevada (centroid)
- filter based on country geometry is done later in Geopandas
- see SPARQL examples [here](https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/queries/examples) and [here](https://addshore.com/2016/05/geospatial-search-for-wikidata-query-service/)


## Parameters

There are two parameters that needs modification, the entity name that is used to get the centroid (location), for filtering based on geodistance (the second parameter).

```python
## Example 1:
loc_name = "Nevada"
entity = "Q1227"
geodistance = 400

## Example 2:
# loc_name = "Leipzig"
# geodistance = 80
# entity = "Q2079" # Leipzig, Germany
```

------

```python
sparql_query = f"""
#title: All events in {loc_name}, based on distance query ({geodistance})
SELECT ?event ?eventLabel ?date ?location ?eventDescription
WITH {{
  SELECT DISTINCT ?event ?date ?location
  WHERE {{
    # find events
    wd:{entity} wdt:P625 ?loc_ref. 
    ?event wdt:P31/wdt:P279* wd:Q1190554.
           # wdt:P17 wd:Q30;
    # with a point in time or start date
    OPTIONAL {{ ?event wdt:P585 ?date. }}
    OPTIONAL {{ ?event wdt:P580 ?date. }}
    ?event wdt:P625 ?location.
    FILTER(geof:distance(?location, ?loc_ref) < {geodistance}).
  }}
  LIMIT 5000
}} AS %i
WHERE {{
  INCLUDE %i
  SERVICE wikibase:label {{ bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de" .}}
}}
"""
```

```python
%%time
result = return_sparql_query_results(sparql_query)
```

Format and convert to pandas DataFrame

```python
import dateutil.parser

event_list = []
for event in result["results"]["bindings"]:
    date_val = event.get('date')
    if date_val:
        date_val = date_val.get('value')
        date_val = pd.to_datetime(dateutil.parser.parse(date_val), errors = 'coerce')
    event_desc = event.get('eventDescription')
    if event_desc:
        event_desc = event['eventDescription']['value']
    event_tuple = (
        event['event']['value'],
        event['eventLabel']['value'],
        date_val,
        event['location']['value'],
        event_desc)
    event_list.append(event_tuple)
```

```python
df = pd.DataFrame(event_list, columns=result['head']['vars'])
```

```python
df.head()
```

```python
print(len(df))
```

Store to disk

```python
from pathlib import Path
OUTPUT = Path.cwd().parents[0] / "out" 
df.to_pickle(OUTPUT / f"wikidata_events_{loc_name.lower()}.pkl") 
```

# Visualize on a map

<!-- #region editable=true slideshow={"slide_type": ""} -->
Select `worker_env` as the visualization environment.
<!-- #endregion -->

```python
%load_ext autoreload
%autoreload 2
```

Load dependencies

```python editable=true slideshow={"slide_type": ""}
import sys
import pandas as pd
import geopandas as gp
from pathlib import Path
from shapely.geometry import Point
from shapely import wkt
module_path = str(Path.cwd().parents[0] / "py")
if module_path not in sys.path:
    sys.path.append(module_path)
from modules.base import tools
```

```python editable=true slideshow={"slide_type": ""} tags=["hide_code"] jupyter={"source_hidden": true}
import sys
from pathlib import Path
module_path = str(Path.cwd().parents[0] / "py")
if module_path not in sys.path:
    sys.path.append(module_path)
from modules.base import tools
root_packages = [
        'shapely', 'geopandas', 'pandas']
tools.package_report(root_packages)
```

```python
OUTPUT = Path.cwd().parents[0] / "out" 
df = pd.read_pickle(OUTPUT / f"wikidata_events_{loc_name.lower()}.pkl") 
```

```python
CRS_WGS = "epsg:4326"

df['geometry'] = df.location.apply(wkt.loads)
gdf = gp.GeoDataFrame(df, crs=CRS_WGS)
```

Get Shapefile for US States/ Germany

```python
if loc_name == "Nevada":
    source_zip = "https://www2.census.gov/geo/tiger/GENZ2018/shp/"
    filename = "cb_2018_us_state_5m.zip"
    shapes_name = "cb_2018_us_state_5m.shp"
elif loc_name == "Leipzig":
    source_zip = "https://daten.gdz.bkg.bund.de/produkte/vg/vg2500/aktuell/"
    filename = "vg2500_12-31.utm32s.shape.zip"
    shapes_name = "vg2500_12-31.utm32s.shape/vg2500/VG2500_LAN.shp"
```

```python
SHAPE_DIR = (OUTPUT / "shapes")
SHAPE_DIR.mkdir(exist_ok=True)

if not (SHAPE_DIR / shapes_name).exists():
    tools.get_zip_extract(uri=source_zip, filename=filename, output_path=SHAPE_DIR)
else:
    print("Already exists")
```

```python
shapes = gp.read_file(SHAPE_DIR / shapes_name)
shapes = shapes.to_crs("EPSG:4326")
```

```python
ax = shapes.plot(color='none', edgecolor='black', linewidth=0.5)
ax = gdf.plot(ax=ax)
ax.set_axis_off()
buffer = 0.5
minx, miny, maxx, maxy = gdf.total_bounds
ax.set_xlim(minx-buffer, maxx+buffer)
ax.set_ylim(miny-buffer, maxy+buffer)
```

## Highlight/Select all in Region

We want to filter those events whose location falls within the state boundary (Nevada, Saxony)

```python
if loc_name == "Nevada":
    state_name = "Nevada"
    col_name = "NAME"
elif loc_name == "Leipzig":
    state_name = "Sachsen"
    col_name = "GEN"
```

```python
sel_geom = shapes[shapes[col_name]==state_name].copy()
```

```python
tools.drop_cols_except(df=sel_geom, columns_keep=["geometry", col_name])
sel_geom.rename(columns={col_name: "country"}, inplace=True)
```

```python
gdf_overlay = gp.overlay(
    gdf, sel_geom,
    how='intersection')
```

```python
ax = shapes.plot(color='none', edgecolor='black', linewidth=0.5)
ax = gdf.plot(ax=ax)
ax = gdf_overlay.plot(ax=ax, color='red')
ax.set_axis_off()
buffer = 1
minx, miny, maxx, maxy = gdf.total_bounds
ax.set_xlim(minx-buffer, maxx+buffer)
ax.set_ylim(miny-buffer, maxy+buffer)
```

```python
print(f'{len(gdf_overlay)} events queried from wikidata that are located in {loc_name}')
```

```python
gdf_overlay.head(20)
```

Store results as CSV

```python
gdf_overlay.to_csv(OUTPUT / f"wikidata_events_{loc_name.lower()}.csv")
```

# Create notebook HTML

```python
!jupyter nbconvert --to html_toc \
    --output-dir=../resources/html/ ./03_wikidata_event_query.ipynb \
    --output 03_wikidata_event_query_{loc_name.lower()} \
    --template=../nbconvert.tpl \
    --ExtractOutputPreprocessor.enabled=False >&- 2>&-
```

```python

```
