zurich_fountains_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Automatic import of Zurich fountain data to Wikidata\n",
    "The following script downloads fountain data from Open Data Zurich, compares it to existing fountains in Wikidata for the same region, and creates Wikidata Quickstatement commands to complete the entries in Wikidata. New entities are created if no matching fountains are found."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initialize environment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import io\n",
    "import numpy as np\n",
    "from urllib.request import urlopen\n",
    "import json\n",
    "from math import *\n",
    "from gastrodon import RemoteEndpoint,QName,ttl,URIRef,inline\n",
    "from matplotlib import pyplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#@prefix wikibase: <wikibase: <http://wikiba.se/ontology#> .\n",
    "prefixes=inline(\"\"\"\n",
    "   @prefix wd: <http://www.wikidata.org/entity/> .\n",
    "   @prefix wdt: <http://www.wikidata.org/prop/direct/> .\n",
    "   @prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .\n",
    "   @prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .\n",
    "\"\"\").graph\n",
    "endpoint=RemoteEndpoint(\n",
    "   \"http://query.wikidata.org/sparql\"\n",
    "   ,prefixes=prefixes\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_url = 'https://data.stadt-zuerich.ch/dataset/brunnen/resource/d741cf9c-63be-495f-8c3e-9418168fcdbf/download/brunnen.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data_js = json.loads(urlopen(data_url).read())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename columns to make them easier to work with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# convert to dataframe\n",
    "df = pd.io.json.json_normalize(data_js['features'])\n",
    "# extract coords\n",
    "df['X'] = df['geometry.coordinates'].apply(lambda crds:crds[0])\n",
    "df['Y'] = df['geometry.coordinates'].apply(lambda crds:crds[1])\n",
    "# remove not needed columns\n",
    "df = df.drop(columns=['geometry.coordinates', 'geometry.type', 'properties.objectid', 'type'])\n",
    "# rename columns\n",
    "df = df.rename(index=str, columns=\n",
    "               {\"properties.bezeichnung\": \"label_de\", \n",
    "                \"properties.brunnenart_txt\": \"fountain_type\", \n",
    "                \"properties.wasserart_txt\": \"water_type\", \n",
    "                \"properties.nummer\":\"operator_id\",\n",
    "                \"properties.historisches_baujahr\": \"date\"\n",
    "               })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify already existing fountains\n",
    "### Query fountains from Wikidata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the geographic extent of the data\n",
    "\n",
    "buffer = 0.0003  # in degrees, corresponds to about 20-30 meters)\n",
    "bounds = {\n",
    "    'minX': df['X'].min() - buffer,\n",
    "    'minY': df['Y'].min() - buffer,\n",
    "    'maxX': df['X'].max() + buffer,\n",
    "    'maxY': df['Y'].max() + buffer\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query fountains (both water wells and fountains) from Wikidata within bounding box found above\n",
    "\n",
    "query_string = \"\"\" SELECT ?place ?placeLabel ?location ?date ?catalog_code ?catalogLabel ?operator\n",
    "WHERE\n",
    "{{\n",
    "  # Enter coordinates\n",
    "  SERVICE wikibase:box {{\n",
    "    ?place wdt:P625 ?location .\n",
    "    bd:serviceParam wikibase:cornerWest \"Point({minX} {minY})\"^^geo:wktLiteral.\n",
    "    bd:serviceParam wikibase:cornerEast \"Point({maxX} {maxY})\"^^geo:wktLiteral.\n",
    "  }} .\n",
    "  # Is a water well or fountain or subclass of fountain\n",
    "  FILTER (EXISTS {{ ?place wdt:P31/wdt:P279* wd:Q43483 }} || EXISTS {{ ?place wdt:P31/wdt:P279* wd:Q483453 }}).\n",
    "  SERVICE wikibase:label {{\n",
    "    bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],de\" .\n",
    "  }} \n",
    "  OPTIONAL {{ ?place p:P528 ?catalog_code.\n",
    "            ?catalog_code pq:P972 ?catalog.}}\n",
    "  OPTIONAL {{ ?place wdt:P571 ?date.}}\n",
    "  OPTIONAL {{ ?place wdt:P137 ?operator.}}\n",
    "}}\n",
    "  \"\"\".format(**bounds)\n",
    "\n",
    "# Perform query\n",
    "query_result = endpoint.select(query_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tidy up data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract coordinates from Wikidata results\n",
    "\n",
    "query_result['X'] = query_result['location'].apply(lambda l:float(l.split('(')[1].split(' ')[0]))\n",
    "query_result['Y'] = query_result['location'].apply(lambda l:float(l.split(' ')[1].split(')')[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# remove duplicate entries\n",
    "\n",
    "# duplicate entries are caused when e.g. a fountain has catalog codes from two catalogs\n",
    "query_result = query_result.drop_duplicates('place')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compute distances between fountains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# helper function to compute distances on the globe, returns distances in meters\n",
    "def spherical_dist(pos1, pos2, r=6371000):\n",
    "    pos1 = pos1 * np.pi / 180\n",
    "    pos2 = pos2 * np.pi / 180\n",
    "    cos_lat1 = np.cos(pos1[..., 0])\n",
    "    cos_lat2 = np.cos(pos2[..., 0])\n",
    "    cos_lat_d = np.cos(pos1[..., 0] - pos2[..., 0])\n",
    "    cos_lon_d = np.cos(pos1[..., 1] - pos2[..., 1])\n",
    "    return r * np.arccos(cos_lat_d - cos_lat1 * cos_lat2 * (1 - cos_lon_d))\n",
    "\n",
    "\n",
    "# compute distances from each ODZ fountain to each Wikidata fountain\n",
    "distances = spherical_dist(df[['X','Y']].values[:, None], query_result[['X','Y']].values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identify nearest and second nearest matches for each ODZ fountain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label_de</th>\n",
       "      <th>fountain_type</th>\n",
       "      <th>date</th>\n",
       "      <th>operator_id</th>\n",
       "      <th>water_type</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>nearest_qid</th>\n",
       "      <th>nearest_distance</th>\n",
       "      <th>2nd_nearest_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aussichtsturm</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>510</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.599255</td>\n",
       "      <td>47.369752</td>\n",
       "      <td>Q55165903</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.913025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>349</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.590811</td>\n",
       "      <td>47.369293</td>\n",
       "      <td>Q55165905</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>541.765153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Biberlinterrasse</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>365</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.575754</td>\n",
       "      <td>47.366130</td>\n",
       "      <td>Q55165919</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>360.590819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>338</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564845</td>\n",
       "      <td>47.370993</td>\n",
       "      <td>Q27230192</td>\n",
       "      <td>1.980033</td>\n",
       "      <td>190.081520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>6069</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564390</td>\n",
       "      <td>47.369327</td>\n",
       "      <td>Q55165936</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>188.767471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1903.0</td>\n",
       "      <td>317</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.560110</td>\n",
       "      <td>47.365197</td>\n",
       "      <td>Q55165939</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>179.860503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1907.0</td>\n",
       "      <td>329</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.562215</td>\n",
       "      <td>47.365124</td>\n",
       "      <td>Q55165953</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.801415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1906.0</td>\n",
       "      <td>327</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.560303</td>\n",
       "      <td>47.366987</td>\n",
       "      <td>Q55166016</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>156.849604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1903.0</td>\n",
       "      <td>315</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.556960</td>\n",
       "      <td>47.368494</td>\n",
       "      <td>Q55166017</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>133.190434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1892.0</td>\n",
       "      <td>313</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.555891</td>\n",
       "      <td>47.369544</td>\n",
       "      <td>Q55166032</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.021392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>6021</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.558166</td>\n",
       "      <td>47.371336</td>\n",
       "      <td>Q55166036</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>60.256399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1948.0</td>\n",
       "      <td>360</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.560288</td>\n",
       "      <td>47.368405</td>\n",
       "      <td>Q27229969</td>\n",
       "      <td>3.424257</td>\n",
       "      <td>41.245317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>None</td>\n",
       "      <td>privater Brunnen</td>\n",
       "      <td>1905.0</td>\n",
       "      <td>736</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.561562</td>\n",
       "      <td>47.370205</td>\n",
       "      <td>Q55166038</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>202.082153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>343</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.565773</td>\n",
       "      <td>47.367253</td>\n",
       "      <td>Q55166041</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>196.964274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1880.0</td>\n",
       "      <td>308</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564053</td>\n",
       "      <td>47.366365</td>\n",
       "      <td>Q55166043</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>121.417124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>6060</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564745</td>\n",
       "      <td>47.365512</td>\n",
       "      <td>Q55166045</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>68.186150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Klusplatz</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>319</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.566123</td>\n",
       "      <td>47.364812</td>\n",
       "      <td>Q55166047</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>94.345818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1905.0</td>\n",
       "      <td>322</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.553845</td>\n",
       "      <td>47.370534</td>\n",
       "      <td>Q55166050</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>152.043615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1892.0</td>\n",
       "      <td>311</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.554405</td>\n",
       "      <td>47.369267</td>\n",
       "      <td>Q27230160</td>\n",
       "      <td>0.657731</td>\n",
       "      <td>152.580518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>bei der Turnhalle</td>\n",
       "      <td>privater Brunnen</td>\n",
       "      <td>1905.0</td>\n",
       "      <td>783</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.549223</td>\n",
       "      <td>47.370946</td>\n",
       "      <td>Q55166053</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>106.662670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1905.0</td>\n",
       "      <td>40</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.550185</td>\n",
       "      <td>47.368984</td>\n",
       "      <td>Q55166055</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>59.721924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Merkurplatz</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1934.0</td>\n",
       "      <td>350</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.555080</td>\n",
       "      <td>47.367398</td>\n",
       "      <td>Q27229939</td>\n",
       "      <td>1.545435</td>\n",
       "      <td>164.199456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1941.0</td>\n",
       "      <td>359</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.558569</td>\n",
       "      <td>47.366817</td>\n",
       "      <td>Q55166058</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>193.634242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1939.0</td>\n",
       "      <td>358</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.566850</td>\n",
       "      <td>47.368677</td>\n",
       "      <td>Q27229863</td>\n",
       "      <td>2.511752</td>\n",
       "      <td>197.205615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>None</td>\n",
       "      <td>Brunnen in städtischer Liegenschaft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>746</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.552027</td>\n",
       "      <td>47.365913</td>\n",
       "      <td>Q55166059</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>109.114693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1913.0</td>\n",
       "      <td>339</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.569773</td>\n",
       "      <td>47.364485</td>\n",
       "      <td>Q55166062</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>174.981954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Pumpwerk Sonnenberg</td>\n",
       "      <td>Brunnen in städtischer Liegenschaft</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>1054</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.570770</td>\n",
       "      <td>47.369013</td>\n",
       "      <td>Q27229879</td>\n",
       "      <td>10.660266</td>\n",
       "      <td>240.890831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>bei Hütte</td>\n",
       "      <td>Brunnen des Verschönerungsvereins</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>712</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.580996</td>\n",
       "      <td>47.366000</td>\n",
       "      <td>Q55166064</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>292.587878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>bei Schifflände</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1939.0</td>\n",
       "      <td>61</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.545250</td>\n",
       "      <td>47.365585</td>\n",
       "      <td>Q27230145</td>\n",
       "      <td>0.328866</td>\n",
       "      <td>61.961557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>grosser Brunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1938.0</td>\n",
       "      <td>59</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.545497</td>\n",
       "      <td>47.366748</td>\n",
       "      <td>Q27229960</td>\n",
       "      <td>0.355215</td>\n",
       "      <td>64.161654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>bei Kollerwiese</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>6030</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.519050</td>\n",
       "      <td>47.368630</td>\n",
       "      <td>Q55166132</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>95.023073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Spielplatz</td>\n",
       "      <td>Brunnen in städtischer Liegenschaft</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>987</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.522759</td>\n",
       "      <td>47.368546</td>\n",
       "      <td>Q55166134</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>94.511987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1873.0</td>\n",
       "      <td>172</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.520373</td>\n",
       "      <td>47.371232</td>\n",
       "      <td>Q55166136</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>135.060735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Schmiede Wiedikon</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1167</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.520545</td>\n",
       "      <td>47.370016</td>\n",
       "      <td>Q55166137</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>131.104547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1873.0</td>\n",
       "      <td>171</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.517801</td>\n",
       "      <td>47.370597</td>\n",
       "      <td>Q55166138</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>140.948963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>178</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.517578</td>\n",
       "      <td>47.369336</td>\n",
       "      <td>Q55166142</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>116.189752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1953.0</td>\n",
       "      <td>183</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.517197</td>\n",
       "      <td>47.368352</td>\n",
       "      <td>Q55166144</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>85.961907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1943.0</td>\n",
       "      <td>206</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.510676</td>\n",
       "      <td>47.367974</td>\n",
       "      <td>Q55166145</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>140.695297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>6024</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.515929</td>\n",
       "      <td>47.366066</td>\n",
       "      <td>Q55166148</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>81.342157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Kindergartengebäude</td>\n",
       "      <td>Brunnen in städtischer Liegenschaft</td>\n",
       "      <td>1938.0</td>\n",
       "      <td>556</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.516894</td>\n",
       "      <td>47.367543</td>\n",
       "      <td>Q55166151</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>95.091575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>6025</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.511915</td>\n",
       "      <td>47.370173</td>\n",
       "      <td>Q55166153</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>181.004945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Goldbrunnenplatz</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>211</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.513541</td>\n",
       "      <td>47.370097</td>\n",
       "      <td>Q55166155</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>181.005816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Gutenbergstrasse</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.530084</td>\n",
       "      <td>47.365820</td>\n",
       "      <td>Q55166157</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>236.512066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Anlage</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>209</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.523384</td>\n",
       "      <td>47.369129</td>\n",
       "      <td>Q27229965</td>\n",
       "      <td>0.445286</td>\n",
       "      <td>94.492055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>240</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.530099</td>\n",
       "      <td>47.371299</td>\n",
       "      <td>Q55166160</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>153.810565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Trinkbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1987.0</td>\n",
       "      <td>214.1</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.528068</td>\n",
       "      <td>47.371230</td>\n",
       "      <td>Q55166163</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>128.978433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Tramhaltestelle</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>506</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.495697</td>\n",
       "      <td>47.367998</td>\n",
       "      <td>Q27230049</td>\n",
       "      <td>1.884412</td>\n",
       "      <td>53.062463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1941.0</td>\n",
       "      <td>426</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.495452</td>\n",
       "      <td>47.367583</td>\n",
       "      <td>Q55166164</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>52.869245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>neben der Augustinerkirche</td>\n",
       "      <td>privater Brunnen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>760</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.539590</td>\n",
       "      <td>47.371650</td>\n",
       "      <td>Q55166166</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>46.618741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Zentralhof</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1877.0</td>\n",
       "      <td>35</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.540088</td>\n",
       "      <td>47.369314</td>\n",
       "      <td>Q55166168</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.084138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>151</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.534311</td>\n",
       "      <td>47.368696</td>\n",
       "      <td>Q55166171</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>142.482923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>None</td>\n",
       "      <td>privater Brunnen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>780</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.532733</td>\n",
       "      <td>47.368185</td>\n",
       "      <td>Q55166172</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>145.560643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1859.0</td>\n",
       "      <td>21</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.532065</td>\n",
       "      <td>47.369557</td>\n",
       "      <td>Q55166174</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>113.903369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Fraumünsterdurchgang</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1742.0</td>\n",
       "      <td>1090</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.541548</td>\n",
       "      <td>47.369598</td>\n",
       "      <td>Q55166175</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>56.046893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>6006</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.541404</td>\n",
       "      <td>47.370167</td>\n",
       "      <td>Q55166177</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>64.561308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Junobrunnen beim Paradeplatz</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1872.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.539411</td>\n",
       "      <td>47.370075</td>\n",
       "      <td>Q27229661</td>\n",
       "      <td>2.177312</td>\n",
       "      <td>44.065525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>beim Kiosk (Anlagage Sihlhölzli)</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>198</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.523652</td>\n",
       "      <td>47.367071</td>\n",
       "      <td>Q55166180</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>130.772779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>199.2</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.526157</td>\n",
       "      <td>47.367916</td>\n",
       "      <td>Q55166182</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>130.015303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1931.0</td>\n",
       "      <td>199.3</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.525117</td>\n",
       "      <td>47.367237</td>\n",
       "      <td>Q55166184</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>125.329084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Mosaikbrunnen</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1909.0</td>\n",
       "      <td>125</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.537833</td>\n",
       "      <td>47.364619</td>\n",
       "      <td>Q27229889</td>\n",
       "      <td>0.545362</td>\n",
       "      <td>120.155050</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            label_de                        fountain_type  \\\n",
       "0                      Aussichtsturm                 öffentlicher Brunnen   \n",
       "1                               None                 öffentlicher Brunnen   \n",
       "2                   Biberlinterrasse                 öffentlicher Brunnen   \n",
       "3                               None                 öffentlicher Brunnen   \n",
       "4                               None                     Notwasserbrunnen   \n",
       "5                               None                 öffentlicher Brunnen   \n",
       "6                               None                 öffentlicher Brunnen   \n",
       "7                               None                 öffentlicher Brunnen   \n",
       "8                               None                 öffentlicher Brunnen   \n",
       "9                               None                 öffentlicher Brunnen   \n",
       "10                              None                     Notwasserbrunnen   \n",
       "11                              None                 öffentlicher Brunnen   \n",
       "12                              None                     privater Brunnen   \n",
       "13                              None                 öffentlicher Brunnen   \n",
       "14                              None                 öffentlicher Brunnen   \n",
       "15                              None                     Notwasserbrunnen   \n",
       "16                         Klusplatz                 öffentlicher Brunnen   \n",
       "17                              None                 öffentlicher Brunnen   \n",
       "18                              None                 öffentlicher Brunnen   \n",
       "19                 bei der Turnhalle                     privater Brunnen   \n",
       "20                              None                 öffentlicher Brunnen   \n",
       "21                       Merkurplatz                 öffentlicher Brunnen   \n",
       "22                              None                 öffentlicher Brunnen   \n",
       "23                              None                 öffentlicher Brunnen   \n",
       "24                              None  Brunnen in städtischer Liegenschaft   \n",
       "25                              None                 öffentlicher Brunnen   \n",
       "26               Pumpwerk Sonnenberg  Brunnen in städtischer Liegenschaft   \n",
       "27                         bei Hütte    Brunnen des Verschönerungsvereins   \n",
       "28                   bei Schifflände                 öffentlicher Brunnen   \n",
       "29                   grosser Brunnen                 öffentlicher Brunnen   \n",
       "..                               ...                                  ...   \n",
       "70                   bei Kollerwiese                     Notwasserbrunnen   \n",
       "71                        Spielplatz  Brunnen in städtischer Liegenschaft   \n",
       "72                              None                 öffentlicher Brunnen   \n",
       "73                 Schmiede Wiedikon                 öffentlicher Brunnen   \n",
       "74                              None                 öffentlicher Brunnen   \n",
       "75                              None                 öffentlicher Brunnen   \n",
       "76                              None                 öffentlicher Brunnen   \n",
       "77                              None                 öffentlicher Brunnen   \n",
       "78                              None                     Notwasserbrunnen   \n",
       "79               Kindergartengebäude  Brunnen in städtischer Liegenschaft   \n",
       "80                              None                     Notwasserbrunnen   \n",
       "81                  Goldbrunnenplatz                 öffentlicher Brunnen   \n",
       "82                  Gutenbergstrasse                 öffentlicher Brunnen   \n",
       "83                            Anlage                 öffentlicher Brunnen   \n",
       "84                              None                 öffentlicher Brunnen   \n",
       "85                      Trinkbrunnen                 öffentlicher Brunnen   \n",
       "86                   Tramhaltestelle                 öffentlicher Brunnen   \n",
       "87                              None                 öffentlicher Brunnen   \n",
       "88        neben der Augustinerkirche                     privater Brunnen   \n",
       "89                        Zentralhof                 öffentlicher Brunnen   \n",
       "90                              None                 öffentlicher Brunnen   \n",
       "91                              None                     privater Brunnen   \n",
       "92                              None                 öffentlicher Brunnen   \n",
       "93              Fraumünsterdurchgang                 öffentlicher Brunnen   \n",
       "94                              None                     Notwasserbrunnen   \n",
       "95      Junobrunnen beim Paradeplatz                 öffentlicher Brunnen   \n",
       "96  beim Kiosk (Anlagage Sihlhölzli)                 öffentlicher Brunnen   \n",
       "97                              None                 öffentlicher Brunnen   \n",
       "98                              None                 öffentlicher Brunnen   \n",
       "99                     Mosaikbrunnen                 öffentlicher Brunnen   \n",
       "\n",
       "      date operator_id   water_type         X          Y nearest_qid  \\\n",
       "0   1970.0         510  Verteilnetz  8.599255  47.369752   Q55165903   \n",
       "1   1933.0         349  Verteilnetz  8.590811  47.369293   Q55165905   \n",
       "2   1965.0         365  Quellwasser  8.575754  47.366130   Q55165919   \n",
       "3   1910.0         338  Quellwasser  8.564845  47.370993   Q27230192   \n",
       "4   1988.0        6069  Quellwasser  8.564390  47.369327   Q55165936   \n",
       "5   1903.0         317  Quellwasser  8.560110  47.365197   Q55165939   \n",
       "6   1907.0         329  Quellwasser  8.562215  47.365124   Q55165953   \n",
       "7   1906.0         327  Quellwasser  8.560303  47.366987   Q55166016   \n",
       "8   1903.0         315  Quellwasser  8.556960  47.368494   Q55166017   \n",
       "9   1892.0         313  Quellwasser  8.555891  47.369544   Q55166032   \n",
       "10  1978.0        6021  Quellwasser  8.558166  47.371336   Q55166036   \n",
       "11  1948.0         360  Quellwasser  8.560288  47.368405   Q27229969   \n",
       "12  1905.0         736  Quellwasser  8.561562  47.370205   Q55166038   \n",
       "13  1928.0         343  Verteilnetz  8.565773  47.367253   Q55166041   \n",
       "14  1880.0         308  Quellwasser  8.564053  47.366365   Q55166043   \n",
       "15  1986.0        6060  Quellwasser  8.564745  47.365512   Q55166045   \n",
       "16  1951.0         319  Quellwasser  8.566123  47.364812   Q55166047   \n",
       "17  1905.0         322  Quellwasser  8.553845  47.370534   Q55166050   \n",
       "18  1892.0         311  Quellwasser  8.554405  47.369267   Q27230160   \n",
       "19  1905.0         783  Quellwasser  8.549223  47.370946   Q55166053   \n",
       "20  1905.0          40  Quellwasser  8.550185  47.368984   Q55166055   \n",
       "21  1934.0         350  Quellwasser  8.555080  47.367398   Q27229939   \n",
       "22  1941.0         359  Quellwasser  8.558569  47.366817   Q55166058   \n",
       "23  1939.0         358  Verteilnetz  8.566850  47.368677   Q27229863   \n",
       "24     NaN         746  Verteilnetz  8.552027  47.365913   Q55166059   \n",
       "25  1913.0         339  Verteilnetz  8.569773  47.364485   Q55166062   \n",
       "26  1981.0        1054  Verteilnetz  8.570770  47.369013   Q27229879   \n",
       "27  1964.0         712  Quellwasser  8.580996  47.366000   Q55166064   \n",
       "28  1939.0          61  Quellwasser  8.545250  47.365585   Q27230145   \n",
       "29  1938.0          59  Quellwasser  8.545497  47.366748   Q27229960   \n",
       "..     ...         ...          ...       ...        ...         ...   \n",
       "70  1979.0        6030  Quellwasser  8.519050  47.368630   Q55166132   \n",
       "71  1975.0         987  Verteilnetz  8.522759  47.368546   Q55166134   \n",
       "72  1873.0         172  Quellwasser  8.520373  47.371232   Q55166136   \n",
       "73  1993.0        1167  Quellwasser  8.520545  47.370016   Q55166137   \n",
       "74  1873.0         171  Verteilnetz  8.517801  47.370597   Q55166138   \n",
       "75  1937.0         178  Verteilnetz  8.517578  47.369336   Q55166142   \n",
       "76  1953.0         183  Verteilnetz  8.517197  47.368352   Q55166144   \n",
       "77  1943.0         206  Verteilnetz  8.510676  47.367974   Q55166145   \n",
       "78  1979.0        6024  Quellwasser  8.515929  47.366066   Q55166148   \n",
       "79  1938.0         556  Verteilnetz  8.516894  47.367543   Q55166151   \n",
       "80  1979.0        6025  Quellwasser  8.511915  47.370173   Q55166153   \n",
       "81  1959.0         211  Quellwasser  8.513541  47.370097   Q55166155   \n",
       "82     NaN         110  Quellwasser  8.530084  47.365820   Q55166157   \n",
       "83  1946.0         209  Verteilnetz  8.523384  47.369129   Q27229965   \n",
       "84  1950.0         240  Verteilnetz  8.530099  47.371299   Q55166160   \n",
       "85  1987.0       214.1  Quellwasser  8.528068  47.371230   Q55166163   \n",
       "86  1970.0         506  Verteilnetz  8.495697  47.367998   Q27230049   \n",
       "87  1941.0         426  Quellwasser  8.495452  47.367583   Q55166164   \n",
       "88     NaN         760  Quellwasser  8.539590  47.371650   Q55166166   \n",
       "89  1877.0          35  Verteilnetz  8.540088  47.369314   Q55166168   \n",
       "90  1940.0         151  Verteilnetz  8.534311  47.368696   Q55166171   \n",
       "91     NaN         780  Verteilnetz  8.532733  47.368185   Q55166172   \n",
       "92  1859.0          21  Verteilnetz  8.532065  47.369557   Q55166174   \n",
       "93  1742.0        1090  Quellwasser  8.541548  47.369598   Q55166175   \n",
       "94  1977.0        6006  Quellwasser  8.541404  47.370167   Q55166177   \n",
       "95  1872.0           3  Quellwasser  8.539411  47.370075   Q27229661   \n",
       "96  1931.0         198  Verteilnetz  8.523652  47.367071   Q55166180   \n",
       "97  1931.0       199.2  Verteilnetz  8.526157  47.367916   Q55166182   \n",
       "98  1931.0       199.3  Verteilnetz  8.525117  47.367237   Q55166184   \n",
       "99  1909.0         125  Verteilnetz  8.537833  47.364619   Q27229889   \n",
       "\n",
       "    nearest_distance  2nd_nearest_distance  \n",
       "0           0.000000            342.913025  \n",
       "1           0.000000            541.765153  \n",
       "2           0.000000            360.590819  \n",
       "3           1.980033            190.081520  \n",
       "4           0.000000            188.767471  \n",
       "5           0.000000            179.860503  \n",
       "6           0.000000            213.801415  \n",
       "7           0.000000            156.849604  \n",
       "8           0.000000            133.190434  \n",
       "9           0.000000             15.021392  \n",
       "10          0.000000             60.256399  \n",
       "11          3.424257             41.245317  \n",
       "12          0.000000            202.082153  \n",
       "13          0.000000            196.964274  \n",
       "14          0.000000            121.417124  \n",
       "15          0.000000             68.186150  \n",
       "16          0.000000             94.345818  \n",
       "17          0.000000            152.043615  \n",
       "18          0.657731            152.580518  \n",
       "19          0.000000            106.662670  \n",
       "20          0.000000             59.721924  \n",
       "21          1.545435            164.199456  \n",
       "22          0.000000            193.634242  \n",
       "23          2.511752            197.205615  \n",
       "24          0.000000            109.114693  \n",
       "25          0.000000            174.981954  \n",
       "26         10.660266            240.890831  \n",
       "27          0.000000            292.587878  \n",
       "28          0.328866             61.961557  \n",
       "29          0.355215             64.161654  \n",
       "..               ...                   ...  \n",
       "70          0.000000             95.023073  \n",
       "71          0.000000             94.511987  \n",
       "72          0.000000            135.060735  \n",
       "73          0.000000            131.104547  \n",
       "74          0.000000            140.948963  \n",
       "75          0.000000            116.189752  \n",
       "76          0.000000             85.961907  \n",
       "77          0.000000            140.695297  \n",
       "78          0.000000             81.342157  \n",
       "79          0.000000             95.091575  \n",
       "80          0.000000            181.004945  \n",
       "81          0.000000            181.005816  \n",
       "82          0.000000            236.512066  \n",
       "83          0.445286             94.492055  \n",
       "84          0.000000            153.810565  \n",
       "85          0.000000            128.978433  \n",
       "86          1.884412             53.062463  \n",
       "87          0.000000             52.869245  \n",
       "88          0.000000             46.618741  \n",
       "89          0.000000              5.084138  \n",
       "90          0.000000            142.482923  \n",
       "91          0.000000            145.560643  \n",
       "92          0.000000            113.903369  \n",
       "93          0.000000             56.046893  \n",
       "94          0.000000             64.561308  \n",
       "95          2.177312             44.065525  \n",
       "96          0.000000            130.772779  \n",
       "97          0.000000            130.015303  \n",
       "98          0.000000            125.329084  \n",
       "99          0.545362            120.155050  \n",
       "\n",
       "[100 rows x 10 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# indexes of nearest fountains\n",
    "nearest_idx = np.argmin(distances, axis=1).tolist()\n",
    "\n",
    "# QID of nearest fountains\n",
    "df['nearest_qid'] = query_result.iloc[nearest_idx]['place'].apply(lambda id:id[3:]).tolist()\n",
    "\n",
    "# distance to nearest fountain\n",
    "df['nearest_distance'] = np.min(distances, axis=1).tolist()\n",
    "\n",
    "\n",
    "# then remove nearest\n",
    "i_line=0\n",
    "for i_col in nearest_idx:\n",
    "    distances[i_line, i_col] = 100000\n",
    "    i_line += 1\n",
    "# find distance to second nearest\n",
    "df['2nd_nearest_distance'] = np.min(distances, axis=1).tolist()\n",
    "\n",
    "df.head(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find out what information already exists for the nearest fountains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# does nearest have label in german?\n",
    "df['nearest_has_label_de'] = (query_result.iloc[nearest_idx]['place'].apply(lambda p:p[3:]) != query_result.iloc[nearest_idx]['placeLabel']).tolist()\n",
    "\n",
    "# does nearest have date?\n",
    "df['nearest_has_date'] = query_result.iloc[nearest_idx]['date'].apply(lambda d:d is not None).tolist()\n",
    "\n",
    "# does nearest have operator?\n",
    "df['nearest_has_operator'] = query_result.iloc[nearest_idx]['operator'].apply(lambda id:id is not None).tolist()\n",
    "\n",
    "# does nearest have catalog code?\n",
    "df['nearest_has_code'] = query_result.iloc[nearest_idx]['catalog_code'].apply(lambda id:id is not None).tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decide on whether nearest fountain should be considered a match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The nearest fountain is a match if: \n",
    "# - no further than x m away\n",
    "# - 2nd nearest fountain at nearest least ratio_min further away than the nearest fountain\n",
    "def validate_proposal(qid, d1, d2, dmax=10, ratio_min=0.5):\n",
    "    \n",
    "    if d1 == 0 or (d1<=dmax and d2/d1-1 >= ratio_min):\n",
    "        return 'match'\n",
    "    elif d1<=dmax and d2/d1-1 < ratio_min:\n",
    "        return 'unclear'\n",
    "    else:\n",
    "        return 'no match'\n",
    "    \n",
    "for index, row in df.iterrows():\n",
    "    df.loc[index, 'match_found'] = validate_proposal(\n",
    "        row['nearest_qid'], \n",
    "        row['nearest_distance'], \n",
    "        row['2nd_nearest_distance'],\n",
    "        dmax=15\n",
    "    )\n",
    "dffinal = df.drop(columns=['nearest_distance', '2nd_nearest_distance'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label_de</th>\n",
       "      <th>fountain_type</th>\n",
       "      <th>date</th>\n",
       "      <th>operator_id</th>\n",
       "      <th>water_type</th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>nearest_qid</th>\n",
       "      <th>nearest_has_label_de</th>\n",
       "      <th>nearest_has_date</th>\n",
       "      <th>nearest_has_operator</th>\n",
       "      <th>nearest_has_code</th>\n",
       "      <th>match_found</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aussichtsturm</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>510</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.599255</td>\n",
       "      <td>47.369752</td>\n",
       "      <td>Q55165903</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>349</td>\n",
       "      <td>Verteilnetz</td>\n",
       "      <td>8.590811</td>\n",
       "      <td>47.369293</td>\n",
       "      <td>Q55165905</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Biberlinterrasse</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1965.0</td>\n",
       "      <td>365</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.575754</td>\n",
       "      <td>47.366130</td>\n",
       "      <td>Q55165919</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>öffentlicher Brunnen</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>338</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564845</td>\n",
       "      <td>47.370993</td>\n",
       "      <td>Q27230192</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>Notwasserbrunnen</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>6069</td>\n",
       "      <td>Quellwasser</td>\n",
       "      <td>8.564390</td>\n",
       "      <td>47.369327</td>\n",
       "      <td>Q55165936</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           label_de         fountain_type    date operator_id   water_type  \\\n",
       "0     Aussichtsturm  öffentlicher Brunnen  1970.0         510  Verteilnetz   \n",
       "1              None  öffentlicher Brunnen  1933.0         349  Verteilnetz   \n",
       "2  Biberlinterrasse  öffentlicher Brunnen  1965.0         365  Quellwasser   \n",
       "3              None  öffentlicher Brunnen  1910.0         338  Quellwasser   \n",
       "4              None      Notwasserbrunnen  1988.0        6069  Quellwasser   \n",
       "\n",
       "          X          Y nearest_qid  nearest_has_label_de  nearest_has_date  \\\n",
       "0  8.599255  47.369752   Q55165903                  True              True   \n",
       "1  8.590811  47.369293   Q55165905                 False              True   \n",
       "2  8.575754  47.366130   Q55165919                  True              True   \n",
       "3  8.564845  47.370993   Q27230192                  True              True   \n",
       "4  8.564390  47.369327   Q55165936                 False              True   \n",
       "\n",
       "   nearest_has_operator  nearest_has_code match_found  \n",
       "0                  True              True       match  \n",
       "1                  True              True       match  \n",
       "2                  True              True       match  \n",
       "3                  True              True       match  \n",
       "4                  True              True       match  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dffinal.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create Quickstatement commands from data\n",
    "### Helper functions to format content according to Quickstatements v1 syntax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_coordinates(x, y):\n",
    "    # format geographic coordinates\n",
    "    return '@{1:1.8f}/{0:1.8f}'.format(x,y)\n",
    "\n",
    "\n",
    "def process_year(date):\n",
    "    # format date\n",
    "    if np.isnan(date):\n",
    "        return ''\n",
    "    else:\n",
    "        return '+{0:4d}-00-00T00:00:00Z/9'.format(int(date))\n",
    "\n",
    "    \n",
    "fountain_type_map = {\n",
    "    'öffentlicher Brunnen': 'Q53628296',\n",
    "    'Notwasserbrunnen': 'Q53628522',\n",
    "    'privater Brunnen': 'Q53629707',\n",
    "    'Brunnen in städtischer Liegenschaft': 'Q53628618',\n",
    "    'Brunnen des Verschönerungsvereins': 'Q53628761',\n",
    "    'Brunnen mit eigener Versorgung': 'Q53630002'\n",
    "}\n",
    "\n",
    "def process_fountain_type(type):\n",
    "    # translate fountain types to wikidata values\n",
    "    return fountain_type_map[type]\n",
    "\n",
    "\n",
    "def process_label_de(text):\n",
    "    # process German language labels\n",
    "    if text is None:\n",
    "        return ''\n",
    "    elif 'brunnen' in text.lower():\n",
    "        return '\"{}\"'.format(text)\n",
    "    else:\n",
    "        return '\"Brunnen ({})\"'.format(text)\n",
    "    \n",
    "\n",
    "def createline(lines, item, prop, value, qualifiers=[]):\n",
    "    # general function to create Quickstatement v1 commands\n",
    "    if value != '' and value != '\"\"':\n",
    "        statement = '{}\\t{}\\t{}'.format(item, prop, value)\n",
    "        if len(qualifiers):\n",
    "            # append qualifiers if applicable\n",
    "            for q in qualifiers:\n",
    "                statement += '\\t{}\\t{}'.format(q['prop'], q['value'])\n",
    "        statement += '\\n'\n",
    "        lines.append(statement)\n",
    "    return lines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create statements, taking care not to overwrite existing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initialize command storage list\n",
    "lines = []\n",
    "\n",
    "for index, row in dffinal.iterrows():\n",
    "    \n",
    "    # either create new or edit existing entity\n",
    "    if row['match_found'] == 'no match':\n",
    "        # create a new fountain\n",
    "        lines.append('CREATE\\n')\n",
    "        item = 'LAST'\n",
    "    elif row['match_found'] == 'unclear':\n",
    "        print('unclear match')\n",
    "        print(row)\n",
    "        continue\n",
    "    elif row['match_found'] == 'match':\n",
    "        # update existing fountain\n",
    "        item = row['nearest_qid']\n",
    "        \n",
    "        \n",
    "    # Add this basic information only if creating a new entity\n",
    "    if item == 'LAST':\n",
    "        # instance of drinking fountain\n",
    "        lines = createline(lines, item, 'P31', 'Q1630622')\n",
    "\n",
    "        # instance of specific water fountain type\n",
    "        lines = createline(lines, item, 'P31', process_fountain_type(row['fountain_type']))\n",
    "\n",
    "        # coordinates\n",
    "        lines = createline(lines, item, 'P625', process_coordinates(row['X'], row['Y']))\n",
    "        \n",
    "        \n",
    "    # For other properties, add information if the entity is new or if property does not yet exist\n",
    "    \n",
    "    # label in german\n",
    "    if item == 'LAST' or not row['nearest_has_label_de']:\n",
    "        lines = createline(lines, item, 'Lde', process_label_de(row['label_de']))\n",
    "    \n",
    "    # creation date\n",
    "    if item == 'LAST' or not row['nearest_has_date']:\n",
    "        lines = createline(lines, item, 'P571', process_year(row['date']))\n",
    "\n",
    "    # operated by WVZ    \n",
    "    if item == 'LAST' or not row['nearest_has_operator']:\n",
    "        lines = createline(lines, item, 'P137', 'Q27229237')\n",
    "    \n",
    "    \n",
    "    # catalog number can always be added (it is hard to check for)\n",
    "    lines = createline(lines, item, 'P528', '\"{}\"'.format(row['operator_id']), [{\n",
    "        'prop': 'P972',\n",
    "        'value': 'Q53629101'\n",
    "    }])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write commands to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "with io.open(\"quickstatement_commands.txt\", \"w\", encoding='utf8') as f:\n",
    "    f.writelines(lines)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}