geosparql/geosparql-tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "523f2ce6-f5e8-40fd-9a0f-75c38427277a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from franz.openrdf.connect import ag_connect\n",
    "from franz.openrdf.rio.rdfformat import RDFFormat\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import ipyleaflet\n",
    "\n",
    "pd.options.display.max_colwidth = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf01a48b-f9cb-4081-841e-999c39c5e139",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Using GeoSPARQL in AllegroGraph\n",
    "\n",
    "GeoSPARQL is a standard for representation and querying of geospatial linked data for the Semantic Web from the Open Geospatial Consortium (OGC). It provides:\n",
    "\n",
    "* a vocabulary for representing geospatial data in RDF \n",
    "* an extension to the SPARQL query language for processing geospatial data\n",
    "\n",
    "In this tutorial, we will demostrate how to use GeoSPARQL in AllegroGraph and our Python APIs.\n",
    "\n",
    "## 1. Introduction\n",
    "\n",
    "GeoSPARQL provides many classes and properties to model and annotate geospatial data. The following is a simplified illustration of GeoSPARQL's ontology, including [Feature](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_class_geofeature), [Geometry](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_class_geogeometry) and their properties. \n",
    "\n",
    "![](img/geosparql-ontology.png)\n",
    "\n",
    "### 1.1 Feature and Geometry Class\n",
    "\n",
    "Features and geometries are connected by using [geo:hasGeometry](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_property_geohasgeometry), [geo:hasDefaultGeometry](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_property_geohasdefaultgeometry) and etc. For example,\n",
    "\n",
    "```turtle\n",
    "@prefix : <http://example.org/ApplicationSchema#> .\n",
    "@prefix geo: <http://www.opengis.net/ont/geosparql#> .\n",
    "\n",
    ":Franz a geo:Feature.\n",
    ":FranzGeom a geo:Geometry .\n",
    ":Franz geo:hasGeometry :FranzGeom .\n",
    "```\n",
    "\n",
    "### 1.2 Serialization\n",
    "\n",
    "Serializations annotate geomeries by using [geo:asWKT](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_property_geoaswkt), [geo:asGeoJSON](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_property_geoasgeojson) and etc. For example,\n",
    "\n",
    "```turtle\n",
    "@prefix : <http://example.org/ApplicationSchema#> .\n",
    "@prefix geo: <http://www.opengis.net/ont/geosparql#> .\n",
    "\n",
    ":FranzGeom geo:asWKT \"POINT(-122.12956214302625 37.88963389303175)\"^^geo:wktLiteral .\n",
    "```\n",
    "\n",
    "or in the equivalent GeoJSON format:\n",
    "\n",
    "```turtle\n",
    "@prefix : <http://example.org/ApplicationSchema#> .\n",
    "@prefix geo: <http://www.opengis.net/ont/geosparql#> .\n",
    "\n",
    ":FranzGeom geo:asGeoJSON '''{\n",
    "  \"type\": \"Point\",\n",
    "  \"coordinates\": [\n",
    "    -122.12956214302625,\n",
    "    37.88963389303175\n",
    "  ]\n",
    "}'''^^geo:geoJSONLiteral .\n",
    "```\n",
    "\n",
    "In the example above, both serializations represent [latitude: 37.88963389303175, longitude: -122.12956214302625](https://goo.gl/maps/ejrUhFSrJc8zRuadA). By standard, as shown below, a spatial reference identifier may be added at the beginning of a WKT literal, and `<http://www.opengis.net/def/crs/OGC/1.3/CRS84>` is used by default.\n",
    "\n",
    "```turtle\n",
    ":someGeom geo:asWKT \"\"\"<http://www.opengis.net/def/crs/OGC/1.3/CRS84>\n",
    "                 Point(-83.4 34.3)\"\"\"^^geo:wktLiteral .\n",
    "\n",
    ":someOtherGeom geo:asWKT \"\"\"<http://www.opengis.net/def/crs/EPSG/0/6366> \n",
    "                 Point(538447.8454476658 3602285.563945497)\"\"\"^^geo:wktLiteral .\n",
    "```\n",
    "\n",
    "Note that literals **must** be typed with [geo:wktLiteral](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_rdfs_datatype_geowktliteral) or [geo:geoJSONLiteral](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_rdfs_datatype_geogeojsonliteral).\n",
    "\n",
    "To know more details of the WKT and GeoJSON format, please see:\n",
    "\n",
    "* Well-known text representation of geometry, https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry\n",
    "* GeoJSON - RFC 7946, https://tools.ietf.org/html/rfc7946\n",
    "\n",
    "## 2. Basics\n",
    "\n",
    "GeoSPARQL standard has [provided](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_c_2_1_example_data) example data: [geosparql-example.ttl](data/geosparql-example.ttl), where these features and geometries are visualized in the figure below:\n",
    "\n",
    "![](img/geosparql-example.png)\n",
    "\n",
    "In this section, we'll use this data and demostrate some basics of using GeoSPARQL in AllegroGraph.\n",
    "\n",
    "Let's set up the server connection's parameters first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d764da8a-754d-4ca3-8984-1c03a7dea1d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'host': 'demo2.franz.com', 'port': '10079', 'user': 'test', 'password': 'xyzzy'}\n"
     ]
    }
   ],
   "source": [
    "agraph_host = os.environ.setdefault(\"AGRAPH_HOST\", \"localhost\")\n",
    "agraph_port = os.environ.setdefault(\"AGRAPH_PORT\", \"10035\")\n",
    "agraph_user = os.environ.setdefault(\"AGRAPH_USER\", \"test\")\n",
    "agraph_password = os.environ.setdefault(\"AGRAPH_PASSWORD\", \"xyzzy\")\n",
    "\n",
    "print({\n",
    "    \"host\": agraph_host,\n",
    "    \"port\": agraph_port,\n",
    "    \"user\": agraph_user,\n",
    "    \"password\": agraph_password\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "707b7d8a-c462-4f46-90ca-da42a558d2fa",
   "metadata": {},
   "source": [
    "Now we can create a repository and load triples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8334c9dd-47c2-48bd-93e6-33d7945bccb0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "43 triples has been loaded.\n"
     ]
    }
   ],
   "source": [
    "conn = ag_connect('geosparql-example', clear=True)\n",
    "conn.addFile(\"data/geosparql-example.ttl\", None, format=RDFFormat.TURTLE)\n",
    "print(\"%d triples has been loaded.\" % conn.size())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7989c6f3-b64d-4736-90f6-7e38b2bb05cb",
   "metadata": {},
   "source": [
    "In the example dataset, it defines two subproperties, `my:hasExactGeometry` and `my:hasPointGeometry`:\n",
    "\n",
    "```turtle\n",
    "@prefix my: <http://example.org/ApplicationSchema#> .\n",
    "@prefix geo: <http://www.opengis.net/ont/geosparql#> .\n",
    "\n",
    "my:hasExactGeometry a rdf:Property ;\n",
    "    rdfs:subPropertyOf geo:hasDefaultGeometry, geo:hasGeometry .\n",
    "\n",
    "my:hasPointGeometry a rdf:Property ;\n",
    "    rdfs:subPropertyOf geo:hasGeometry .\n",
    "```\n",
    "\n",
    "The query below will show you the \"exact\" geometry and \"point\" geometry of feature **my:A**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c42c9c2b-b86d-4ddf-b194-f9dff6e64add",
   "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>aExactGeom</th>\n",
       "      <th>aPointGeom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#AExactGeom&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#APointGeom&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          aExactGeom  \\\n",
       "0  <http://example.org/ApplicationSchema#AExactGeom>   \n",
       "\n",
       "                                          aPointGeom  \n",
       "0  <http://example.org/ApplicationSchema#APointGeom>  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "\n",
    "SELECT ?aExactGeom ?aPointGeom \n",
    "WHERE {\n",
    "  my:A my:hasExactGeometry ?aExactGeom ;\n",
    "       my:hasPointGeometry ?aPointGeom .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a685a217-06a3-4e1f-855c-1c6cb532dd0d",
   "metadata": {},
   "source": [
    "You can also look up the WKT serialization respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "fe69097a-b7a2-4a6c-97cc-4673be3a4278",
   "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>aExactWKT</th>\n",
       "      <th>aPointWKT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://www.opengis.net/def/crs/OGC/1.3/CRS84&gt;\\n                 Polygon((-83.6 34.1, -83.2 34.1, -83.2 34.5,\\n                 -83.6 34.5, -83.6 34.1))</td>\n",
       "      <td>&lt;http://www.opengis.net/def/crs/OGC/1.3/CRS84&gt;\\n                 Point(-83.4 34.3)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                                                  aExactWKT  \\\n",
       "0  <http://www.opengis.net/def/crs/OGC/1.3/CRS84>\\n                 Polygon((-83.6 34.1, -83.2 34.1, -83.2 34.5,\\n                 -83.6 34.5, -83.6 34.1))   \n",
       "\n",
       "                                                                            aPointWKT  \n",
       "0  <http://www.opengis.net/def/crs/OGC/1.3/CRS84>\\n                 Point(-83.4 34.3)  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "\n",
    "SELECT ?aExactWKT ?aPointWKT \n",
    "WHERE {\n",
    "  my:A my:hasExactGeometry [ geo:asWKT ?aExactWKT ] ;\n",
    "       my:hasPointGeometry [ geo:asWKT ?aPointWKT ] .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55aa2421-ea35-4728-86ea-03c3cc6ca3fc",
   "metadata": {},
   "source": [
    "### 2.1 Simple Features Relation Family\n",
    "\n",
    "As defined by Simple Features [[OGCSFACA]](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#OGCSFACA) [[ISO19125-1]](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#ISO19125-1), AllegroGraph supports `geof:sfEquals`, `geof:sfDisjoint`, `geof:sfIntersects`, `geof:sfTouches`, `geof:sfCrosses`, `geof:sfWithin`, `geof:sfContains` and `geof:sfOverlaps` as SPARQL extension functions, consistent with their corresponding DE-9IM intersection patterns, where `geof:` namespace has uri `http://www.opengis.net/def/function/geosparql/`.\n",
    "\n",
    "All these functions take two arbitrary geometry serializations as arguments and return a boolean result. You may see [here](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_simple_features_relation_family_2) for more details.\n",
    "\n",
    "#### 2.1.1 geof:sfContains\n",
    "\n",
    "By using `geof:sfContains`, we can find all features that feature **my:A** contains, \n",
    "where spatial calculations are based on **my:hasExactGeometry**.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a6f5e2be-0ab0-4b0a-b625-a476a97fcb2f",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#F&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#B&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#F>\n",
       "1  <http://example.org/ApplicationSchema#B>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "    my:A my:hasExactGeometry ?aGeom .\n",
    "    ?aGeom geo:asWKT ?aWKT .\n",
    "    ?f my:hasExactGeometry ?fGeom .\n",
    "    ?fGeom geo:asWKT ?fWKT .\n",
    "\n",
    "    FILTER (\n",
    "        geof:sfContains(?aWKT, ?fWKT) &&\n",
    "            !sameTerm(?aGeom, ?fGeom)\n",
    "        )\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60283e5a-af13-4f84-9874-06d9be5d37b5",
   "metadata": {},
   "source": [
    "#### 2.1.2 geof:sfWithin\n",
    "\n",
    "Find all features that are within a transient bounding box geometry, where spatial calculations are based on **my:hasPointGeometry**.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c2b3a316-812b-4518-9706-d175cb10d61e",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#D&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#D>"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "    ?f my:hasPointGeometry ?fGeom .\n",
    "    ?fGeom geo:asWKT ?fWKT .\n",
    "    FILTER (\n",
    "        geof:sfWithin(\n",
    "            ?fWKT,\n",
    "            '''<http://www.opengis.net/def/crs/OGC/1.3/CRS84>\n",
    "            Polygon ((-83.4 34.0, -83.1 34.0,\n",
    "                        -83.1 34.2, -83.4 34.2,\n",
    "                        -83.4 34.0))'''^^geo:wktLiteral\n",
    "        )\n",
    "    )\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddff8800-9782-4695-9830-11dd9edcbd57",
   "metadata": {},
   "source": [
    "### 2.2 Non-topological Query Functions\n",
    "\n",
    "These functions include:\n",
    "\n",
    "* geof:isEmpty\n",
    "* geof:convexHull\n",
    "* geof:intersection\n",
    "* geof:union\n",
    "* geof:difference\n",
    "* geof:symDifference\n",
    "* geof:envelope\n",
    "* geof:centroid\n",
    "\n",
    "#### 2.2.1 geof:union\n",
    "\n",
    "[geof:union](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_function_geofunion) returns a geometric object that represents all Points in the union of the given two geometric objects.\n",
    "\n",
    "The example below finds all features that touch the union of feature **my:A** and feature **my:D**, where computations are based on **my:hasExactGeometry**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4e80827b-79cd-4280-8ef1-64e23bf45a25",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#C&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#C>"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "    ?f my:hasExactGeometry ?fGeom .\n",
    "    ?fGeom geo:asWKT ?fWKT .\n",
    "    my:A my:hasExactGeometry ?aGeom .\n",
    "    ?aGeom geo:asWKT ?aWKT .\n",
    "    my:D my:hasExactGeometry ?dGeom .\n",
    "    ?dGeom geo:asWKT ?dWKT .\n",
    "    FILTER (\n",
    "        geof:sfTouches(\n",
    "            ?fWKT,\n",
    "            geof:union(?aWKT, ?dWKT)\n",
    "        )\n",
    "    )\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04b9eb06-a8a6-464c-b86f-754747d097db",
   "metadata": {},
   "source": [
    "#### 2.2.2 geof:distance\n",
    "\n",
    "[geof:distance](https://opengeospatial.github.io/ogc-geosparql/geosparql11/spec.html#_function_geofdistance) returns the shortest distance in the given unit between any two Points in the two geometric objects. We use [QUDT ontology](https://qudt.org/) for representing units of measurements. Supported units are:\n",
    "\n",
    "| Resource   | Unit of Measurement |\n",
    "|------------|---------------------|\n",
    "| qudt:M     | Meter               |\n",
    "| qudt:KiloM | KiloMeter           |\n",
    "| qudt:MI    | International Mile  |\n",
    "| qudt:MI_US | Mile US Statute     |\n",
    "| qudt:YD    | Yard                |\n",
    "| qudt:FT    | Foot                |\n",
    "| qudt:FT_US | US Survey Foot      |\n",
    "\n",
    "The query below finds the 3 closest features to feature **my:C**, where computations are based on **my:hasExactGeometry**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f6cc6e50-d851-4e9f-9f0f-d9e22f9d6cf4",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#A&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#E&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#D&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#A>\n",
       "1  <http://example.org/ApplicationSchema#E>\n",
       "2  <http://example.org/ApplicationSchema#D>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX qudt: <http://qudt.org/vocab/unit/>\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "    my:C my:hasExactGeometry ?cGeom .\n",
    "    ?cGeom geo:asWKT ?cWKT .\n",
    "    ?f my:hasExactGeometry ?fGeom .\n",
    "    ?fGeom geo:asWKT ?fWKT .\n",
    "    FILTER (?fGeom != ?cGeom)\n",
    "}\n",
    "ORDER BY ASC (geof:distance(?cWKT, ?fWKT, qudt:M))\n",
    "LIMIT 3\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fef9e581-b319-48cd-b425-6cade11853f4",
   "metadata": {},
   "source": [
    "## 3. GeoSPARQL Extensions\n",
    "\n",
    "On top of the standard GeoSPARQL functions, AllegroGraph adds a few useful extensions. In this section, the prefix `geoext:` stands for the namespace `http://franz.com/ns/allegrograph/3.0/geosparql/ext#`.\n",
    "\n",
    "### 3.1 Geohash Indexing\n",
    "\n",
    "[Geohash](https://en.wikipedia.org/wiki/Geohash) is an algorithm that encodes a geographic location into a short string of letters and digits. For example, [latitude: 37.88963389303175, longitude: -122.12956214302625](https://goo.gl/maps/ejrUhFSrJc8zRuadA) may be encoded as `\"9q9psceun6\"`. To provide better spatial query performance, we have implemented Geohash indexing, which is a critical component required by a few other extensions.\n",
    "\n",
    "To index your spatial geometries, use either `geoext:buildGeohashIndex` or `geoext:rebuildGeohashIndex` [SPARQL magic property](https://franz.com/agraph/support/documentation/current/magic-properties.html).\n",
    "\n",
    "#### 3.1.1 geoext:buildGeohashIndex\n",
    "\n",
    "`geoext:buildGeohashIndex` **incrementally** builds Geohash index.\n",
    "It only builds for triples that do NOT have geohash index.\n",
    "\n",
    "For example:\n",
    "\n",
    "```sparql\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:buildGeohashIndex () .\n",
    "}\n",
    "```\n",
    "\n",
    "If the building process finishes successfully, it will return `\\\"true\\\"^^<http://www.w3.org/2001/XMLSchema#boolean>`; otherwise, it will return (bound to `?success`) `\\\"false\\\"^^<http://www.w3.org/2001/XMLSchema#boolean>`.\n",
    "\n",
    "#### 3.1.2 geoext:rebuildGeohashIndex\n",
    "\n",
    "`geoext:buildGeohashIndex` **forcefully** rebuilds Geohash index.\n",
    "It will delete all the current Geohash index and then rebuild.\n",
    "\n",
    "For example:\n",
    "\n",
    "```sparql\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:rebuildGeohashIndex () .\n",
    "}\n",
    "```\n",
    "\n",
    "If the rebuilding process finishes successfully, it will return `\\\"true\\\"^^<http://www.w3.org/2001/XMLSchema#boolean>`; otherwise, it will return (bound to `?success`) `\\\"false\\\"^^<http://www.w3.org/2001/XMLSchema#boolean>`.\n",
    "\n",
    "#### 3.1.3 Example\n",
    "\n",
    "We will use a different dataset `franz.ttl` to demostrate its usage. \n",
    "This dataset contains locations of Franz Inc. headquarter and a few nearby restaurants.\n",
    "\n",
    "```turtle\n",
    "@prefix : <http://example.org/ApplicationSchema#> .\n",
    "@prefix geo: <http://www.opengis.net/ont/geosparql#> .\n",
    "\n",
    ":Franz geo:hasGeometry :FranzGeom .\n",
    ":FranzGeom geo:asWKT \"POINT(-122.12956214302625 37.88963389303175)\"^^geo:wktLiteral .\n",
    "\n",
    ":OyamaSushi a :Restaurant ;\n",
    "            geo:hasGeometry :OyamaSushiGeom .\n",
    ":OyamaSushiGeom geo:asWKT \"POINT(-122.1275488659957 37.89076272510303)\"^^geo:wktLiteral .\n",
    "\n",
    ":PostinoRestaurant a :Restaurant ;\n",
    "            geo:hasGeometry :PostinoRestaurantGeom .\n",
    ":PostinoRestaurantGeom geo:asWKT \"POINT(-122.12107937769098 37.891406215514536)\"^^geo:wktLiteral .\n",
    "\n",
    ":TheCooperageAmericanGrille a :Restaurant ;\n",
    "            geo:hasGeometry :TheCooperageAmericanGrilleGeom .\n",
    ":TheCooperageAmericanGrilleGeom geo:asWKT \"POINT(-122.12016742659232 37.89114373984338)\"^^geo:wktLiteral .\n",
    "\n",
    ":TheHideoutKitchenAndCafe a :Restaurant ;\n",
    "            geo:hasGeometry :TheHideoutKitchenAndCafeGeom .\n",
    ":TheHideoutKitchenAndCafeGeom geo:asWKT \"POINT(-122.10956550861351 37.89419432704863)\"^^geo:wktLiteral .\n",
    "\n",
    ":TheParkBistroAndBar a :Restaurant ;\n",
    "            geo:hasGeometry :TheParkBistroAndBarGeom .\n",
    ":TheParkBistroAndBarGeom geo:asWKT \"POINT(-122.1009540100645 37.89637380250877)\"^^geo:wktLiteral .\n",
    "```\n",
    "\n",
    "Let's create an empty repository and load `franz.ttl` first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3a13723b-f3de-4da8-936e-db9ab89ff538",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "17 triples has been loaded.\n"
     ]
    }
   ],
   "source": [
    "conn = ag_connect('geosparql-example', clear=True)\n",
    "conn.addFile(\"data/franz.ttl\", None, format=RDFFormat.TURTLE)\n",
    "print(\"%d triples has been loaded.\" % conn.size())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85c7a1bc-1060-4445-9cce-1ad41c15472b",
   "metadata": {},
   "source": [
    "Let's build the Geohash index. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0a715844-6d15-48f8-a334-63e156a1e372",
   "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>success</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success\n",
       "0  True   "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:buildGeohashIndex () .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d11b033-8427-4010-9bed-9047944de767",
   "metadata": {},
   "source": [
    "After indexing, each geometry will be linked to its geohash by predicate `geoext:geohash`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "1c427b0a-6066-45e3-aae8-4ed84527aae4",
   "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>place</th>\n",
       "      <th>geometry</th>\n",
       "      <th>geohash</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#OyamaSushi&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#OyamaSushiGeom&gt;</td>\n",
       "      <td>9q9pscv3ty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheParkBistroAndBar&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheParkBistroAndBarGeom&gt;</td>\n",
       "      <td>9q9ptdbf3s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheCooperageAmericanGrille&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheCooperageAmericanGrilleGeom&gt;</td>\n",
       "      <td>9q9pt1fgcv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#Franz&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#FranzGeom&gt;</td>\n",
       "      <td>9q9pscegng</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheHideoutKitchenAndCafe&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheHideoutKitchenAndCafeGeom&gt;</td>\n",
       "      <td>9q9pt66t8v</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#PostinoRestaurant&gt;</td>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#PostinoRestaurantGeom&gt;</td>\n",
       "      <td>9q9pt1fjkw</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                               place  \\\n",
       "0  <http://example.org/ApplicationSchema#OyamaSushi>                   \n",
       "1  <http://example.org/ApplicationSchema#TheParkBistroAndBar>          \n",
       "2  <http://example.org/ApplicationSchema#TheCooperageAmericanGrille>   \n",
       "3  <http://example.org/ApplicationSchema#Franz>                        \n",
       "4  <http://example.org/ApplicationSchema#TheHideoutKitchenAndCafe>     \n",
       "5  <http://example.org/ApplicationSchema#PostinoRestaurant>            \n",
       "\n",
       "                                                                geometry  \\\n",
       "0  <http://example.org/ApplicationSchema#OyamaSushiGeom>                   \n",
       "1  <http://example.org/ApplicationSchema#TheParkBistroAndBarGeom>          \n",
       "2  <http://example.org/ApplicationSchema#TheCooperageAmericanGrilleGeom>   \n",
       "3  <http://example.org/ApplicationSchema#FranzGeom>                        \n",
       "4  <http://example.org/ApplicationSchema#TheHideoutKitchenAndCafeGeom>     \n",
       "5  <http://example.org/ApplicationSchema#PostinoRestaurantGeom>            \n",
       "\n",
       "      geohash  \n",
       "0  9q9pscv3ty  \n",
       "1  9q9ptdbf3s  \n",
       "2  9q9pt1fgcv  \n",
       "3  9q9pscegng  \n",
       "4  9q9pt66t8v  \n",
       "5  9q9pt1fjkw  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?place ?geometry ?geohash WHERE {\n",
    "    ?place geo:hasGeometry ?geometry .\n",
    "    ?geometry geoext:geohash ?geohash . # new triples added after indexing\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf957451-a585-45bc-9e16-f25fba5865c7",
   "metadata": {},
   "source": [
    "### 3.2 Simple Features Relation Query by Magic Properties\n",
    "\n",
    "In section 2.1, we have shown examples of doing relation query by using SPARQL extension functions. \n",
    "However, they are not using any Geohash indices hence their performance may be concerning if the dataset is fairly large.\n",
    "\n",
    "In the meanwhile, GeoSPARQL provides their corresponding semantics in the form of RDF predicates, and we have implemented them as SPARQL magic properties. For example, `geof:sfWithin` corresponds to the predicate form `geo:sfWithin`. \n",
    "\n",
    "Note that these magic properties depends on Geohash indexing. Users may use these magic properties instead of the SPARQL extension functions when the performance is concerning.\n",
    "\n",
    "#### 3.2.1 Example\n",
    "\n",
    "Let's create an empty repository and load `geosparql-example.ttl`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f7aff836-a2f7-446c-aba3-5cd4c1a387c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "43 triples has been loaded.\n"
     ]
    }
   ],
   "source": [
    "conn = ag_connect('geosparql-example', clear=True)\n",
    "conn.addFile(\"data/geosparql-example.ttl\", None, format=RDFFormat.TURTLE)\n",
    "print(\"%d triples has been loaded.\" % conn.size())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45242362-7377-4b8b-8501-7f8f95cde0f6",
   "metadata": {},
   "source": [
    "Let's build the Geohash index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b9a70e3c-99ff-4b0b-9780-6cdded177104",
   "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>success</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success\n",
       "0  True   "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:buildGeohashIndex () .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4c2bf20-7d5b-4364-8abe-2d34f8dfced8",
   "metadata": {},
   "source": [
    "Find all features that feature **my:A** contains, \n",
    "where spatial calculations are based on **my:hasExactGeometry**.\n",
    "\n",
    "_It's just like section 2.1.1, but uses `geo:sfContains`._\n",
    "\n",
    "**FIXME!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f21bc316-cb5a-46a0-a38f-388185916ae7",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#F&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#B&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#F>\n",
       "1  <http://example.org/ApplicationSchema#B>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "    my:A my:hasExactGeometry ?aGeom .\n",
    "    ?f my:hasExactGeometry ?fGeom .\n",
    "    ?aGeom geo:sfContains ?fGeom .\n",
    "    FILTER (!sameTerm(?aGeom, ?fGeom))\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f91e8c4-70e7-4bed-9467-d462f3bb9a0d",
   "metadata": {},
   "source": [
    "Find all features that are within a transient bounding box geometry, where spatial calculations are based on **my:hasPointGeometry**.\n",
    "\n",
    "_It's just like section 2.1.2, but uses `geo:sfWithin`._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ef63472b-5208-4fa2-9c57-a2ac4c9b9424",
   "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>f</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#D&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          f\n",
       "0  <http://example.org/ApplicationSchema#D>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX my: <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "\n",
    "SELECT ?f\n",
    "WHERE {\n",
    "   ?f my:hasPointGeometry ?fGeom .\n",
    "   ?fGeom geo:sfWithin '''\n",
    "        <http://www.opengis.net/def/crs/OGC/1.3/CRS84>\n",
    "            Polygon ((-83.4 34.0, -83.1 34.0,\n",
    "                      -83.1 34.2, -83.4 34.2,\n",
    "                      -83.4 34.0))\n",
    "        '''^^geo:wktLiteral\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aab82051-9154-4378-8443-9e50e7917d26",
   "metadata": {},
   "source": [
    "### 3.3 geoext:haversineDistance\n",
    "\n",
    "`geoext:haversineDistance` is a SPARQL extension function to compute [haversine distance](https://en.wikipedia.org/wiki/Haversine_formula) from two geometric objects.\n",
    "\n",
    "The example below shows the haversine distance (unit is Meter by default) between New York and London."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ad301ee9-4f35-41b8-ae26-31b633a840cd",
   "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>haversineDistance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.570230e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   haversineDistance\n",
       "0  5.570230e+06     "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?haversineDistance\n",
    "WHERE {\n",
    "  BIND (geoext:haversineDistance(\"POINT(-74.006 40.7128)\"^^geo:wktLiteral, # New York\n",
    "                                 \"POINT(-0.1278 51.5074)\"^^geo:wktLiteral) # London\n",
    "        AS ?haversineDistance)\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "694a2e87-c362-433c-b790-6b4ab7a32e0d",
   "metadata": {},
   "source": [
    "And just like `geof:distance`, you may specify the unit of measurements. For example, you can use `qudt:FT` to get the distance in Feet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "1680b6f4-3d3c-4faf-9b33-0fd390ef15db",
   "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>haversineDistanceFeet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.827503e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   haversineDistanceFeet\n",
       "0  1.827503e+07         "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "PREFIX qudt: <http://qudt.org/vocab/unit/>\n",
    "\n",
    "SELECT ?haversineDistanceFeet\n",
    "WHERE {\n",
    "  BIND (geoext:haversineDistance(\"POINT(-74.006 40.7128)\"^^geo:wktLiteral, # New York\n",
    "                                 \"POINT(-0.1278 51.5074)\"^^geo:wktLiteral, # London\n",
    "                                qudt:FT)\n",
    "        AS ?haversineDistanceFeet)\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "caeee59f-2ce0-4e6c-81e0-f4241437a08f",
   "metadata": {},
   "source": [
    "### 3.4 geoext:nearby\n",
    "\n",
    "`geoext:nearby` is a SPARQL magic property that returns all geometric objects that are within a specified radius of a given _Point_ serialization. **It depends on Geohash indexing.**\n",
    "\n",
    "Let's load `franz.ttl` again and build Geohash index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "2b4f403d-bcf0-4c05-9959-2f17ed45d426",
   "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>success</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success\n",
       "0  True   "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = ag_connect('franz', clear=True)\n",
    "conn.addFile(\"data/franz.ttl\", None, format=RDFFormat.TURTLE)\n",
    "\n",
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:buildGeohashIndex () .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba4ae4bd-4414-4507-9c21-34d6c5864000",
   "metadata": {},
   "source": [
    "The query below lists all the restaurants' distances (km) to Franz office in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "fbb33b38-ecc0-4d3d-83d9-cf9a14dbb0ad",
   "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>restaurant</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#OyamaSushi&gt;</td>\n",
       "      <td>0.216936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#PostinoRestaurant&gt;</td>\n",
       "      <td>0.771664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheCooperageAmericanGrille&gt;</td>\n",
       "      <td>0.843209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheHideoutKitchenAndCafe&gt;</td>\n",
       "      <td>1.830314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheParkBistroAndBar&gt;</td>\n",
       "      <td>2.625215</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          restaurant  distance\n",
       "0  <http://example.org/ApplicationSchema#OyamaSushi>                  0.216936\n",
       "1  <http://example.org/ApplicationSchema#PostinoRestaurant>           0.771664\n",
       "2  <http://example.org/ApplicationSchema#TheCooperageAmericanGrille>  0.843209\n",
       "3  <http://example.org/ApplicationSchema#TheHideoutKitchenAndCafe>    1.830314\n",
       "4  <http://example.org/ApplicationSchema#TheParkBistroAndBar>         2.625215"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX : <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "PREFIX qudt: <http://qudt.org/vocab/unit/>\n",
    "\n",
    "SELECT ?restaurant ?distance\n",
    "WHERE {\n",
    "  :FranzGeom geo:asWKT ?franzLiteral .\n",
    "  \n",
    "  ?restaurant a :Restaurant;\n",
    "         geo:hasGeometry ?restaurantGeom .\n",
    "  ?restaurantGeom geo:asWKT ?restaurantLiteral . \n",
    "  \n",
    "  BIND (geof:distance(?franzLiteral, ?restaurantLiteral, qudt:KiloM) AS ?distance)\n",
    "\n",
    "}\n",
    "ORDER BY ASC(?distance)\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dcf5205-db94-4c96-9135-61501d695812",
   "metadata": {},
   "source": [
    "Search for restaurants that are within radius of 1 km. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "08ec6245-a466-4af7-b807-b43c4c34d32a",
   "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>restaurant</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#OyamaSushi&gt;</td>\n",
       "      <td>0.216936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#PostinoRestaurant&gt;</td>\n",
       "      <td>0.771664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;http://example.org/ApplicationSchema#TheCooperageAmericanGrille&gt;</td>\n",
       "      <td>0.843209</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          restaurant  distance\n",
       "0  <http://example.org/ApplicationSchema#OyamaSushi>                  0.216936\n",
       "1  <http://example.org/ApplicationSchema#PostinoRestaurant>           0.771664\n",
       "2  <http://example.org/ApplicationSchema#TheCooperageAmericanGrille>  0.843209"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX : <http://example.org/ApplicationSchema#>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "PREFIX qudt: <http://qudt.org/vocab/unit/>\n",
    "\n",
    "SELECT ?restaurant ?distance\n",
    "WHERE {\n",
    "  :FranzGeom geo:asWKT ?franzWKT .\n",
    "  \n",
    "  ?restaurant a :Restaurant;\n",
    "         geo:hasGeometry ?restaurantGeom .\n",
    "  ?restaurantGeom geo:asWKT ?restaurantLiteral . \n",
    "  \n",
    "  BIND (geof:distance(?franzWKT, ?restaurantLiteral, qudt:KiloM) AS ?distance)\n",
    "  \n",
    "  ?restaurantGeom geoext:nearby (?franzWKT 1 qudt:KiloM) .\n",
    "}\n",
    "ORDER BY ASC(?distance)\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f33f4692-fc80-4260-8e50-5ab0bc150a92",
   "metadata": {
    "tags": []
   },
   "source": [
    "## 4. An Interactive Example\n",
    "\n",
    "In this section, we will create an interactive map by using [Mobile Food Facility Permit](https://catalog.data.gov/dataset/mobile-food-facility-permit) dataset which includes name of vendor, location, type of food sold and status of permit. \n",
    "From the map, users can draw polygons and a callback will execute GeoSPARQL queries to find points that are within the created polygons.\n",
    "\n",
    "Let's read `Mobile_Food_Facility_Permit.csv` by using Pandas and [GeoPandas](https://geopandas.org/en/stable/index.html) first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8ea37abf-3ec5-436f-988a-661923ffa3ce",
   "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>locationid</th>\n",
       "      <th>Applicant</th>\n",
       "      <th>FacilityType</th>\n",
       "      <th>cnn</th>\n",
       "      <th>LocationDescription</th>\n",
       "      <th>Address</th>\n",
       "      <th>blocklot</th>\n",
       "      <th>block</th>\n",
       "      <th>lot</th>\n",
       "      <th>permit</th>\n",
       "      <th>...</th>\n",
       "      <th>Received</th>\n",
       "      <th>PriorPermit</th>\n",
       "      <th>ExpirationDate</th>\n",
       "      <th>Location</th>\n",
       "      <th>Fire Prevention Districts</th>\n",
       "      <th>Police Districts</th>\n",
       "      <th>Supervisor Districts</th>\n",
       "      <th>Zip Codes</th>\n",
       "      <th>Neighborhoods (old)</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1571753</td>\n",
       "      <td>The Geez Freeze</td>\n",
       "      <td>Truck</td>\n",
       "      <td>887000</td>\n",
       "      <td>18TH ST: DOLORES ST to CHURCH ST (3700 - 3799)</td>\n",
       "      <td>3750 18TH ST</td>\n",
       "      <td>3579006</td>\n",
       "      <td>3579</td>\n",
       "      <td>006</td>\n",
       "      <td>21MFF-00015</td>\n",
       "      <td>...</td>\n",
       "      <td>20210315</td>\n",
       "      <td>0</td>\n",
       "      <td>11/15/2022 12:00:00 AM</td>\n",
       "      <td>(37.76201920035647, -122.42730642251331)</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28862.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>POINT (-122.42731 37.76202)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1565571</td>\n",
       "      <td>MOMO INNOVATION LLC</td>\n",
       "      <td>Truck</td>\n",
       "      <td>3525000</td>\n",
       "      <td>CALIFORNIA ST: DAVIS ST to FRONT ST (100 - 199)</td>\n",
       "      <td>101 CALIFORNIA ST</td>\n",
       "      <td>0263011</td>\n",
       "      <td>0263</td>\n",
       "      <td>011</td>\n",
       "      <td>21MFF-00089</td>\n",
       "      <td>...</td>\n",
       "      <td>20211022</td>\n",
       "      <td>0</td>\n",
       "      <td>11/15/2022 12:00:00 AM</td>\n",
       "      <td>(37.792948952834664, -122.39809861316652)</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>28860.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>POINT (-122.39810 37.79295)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1565954</td>\n",
       "      <td>Treats by the Bay LLC</td>\n",
       "      <td>Truck</td>\n",
       "      <td>10624001</td>\n",
       "      <td>POST ST: MONTGOMERY ST to LICK PL (1 - 40)</td>\n",
       "      <td>1 MONTGOMERY ST</td>\n",
       "      <td>0292002</td>\n",
       "      <td>0292</td>\n",
       "      <td>002</td>\n",
       "      <td>21MFF-00094</td>\n",
       "      <td>...</td>\n",
       "      <td>20211022</td>\n",
       "      <td>0</td>\n",
       "      <td>11/15/2022 12:00:00 AM</td>\n",
       "      <td>(37.78924953407508, -122.40241859729358)</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>28854.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>POINT (-122.40242 37.78925)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1565594</td>\n",
       "      <td>MOMO INNOVATION LLC</td>\n",
       "      <td>Truck</td>\n",
       "      <td>9094000</td>\n",
       "      <td>MISSION ST: ANNIE ST to 03RD ST (663 - 699)</td>\n",
       "      <td>667 MISSION ST</td>\n",
       "      <td>3722067</td>\n",
       "      <td>3722</td>\n",
       "      <td>067</td>\n",
       "      <td>21MFF-00090</td>\n",
       "      <td>...</td>\n",
       "      <td>20211022</td>\n",
       "      <td>0</td>\n",
       "      <td>11/15/2022 12:00:00 AM</td>\n",
       "      <td>(37.7865580501799, -122.40103337534973)</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>28855.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>POINT (-122.40103 37.78656)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1565628</td>\n",
       "      <td>Treats by the Bay LLC</td>\n",
       "      <td>Truck</td>\n",
       "      <td>5170000</td>\n",
       "      <td>ELLIS ST: POWELL ST to CYRIL MAGNIN ST (100 - 148)</td>\n",
       "      <td>120 ELLIS ST</td>\n",
       "      <td>0326005</td>\n",
       "      <td>0326</td>\n",
       "      <td>005</td>\n",
       "      <td>21MFF-00093</td>\n",
       "      <td>...</td>\n",
       "      <td>20211022</td>\n",
       "      <td>0</td>\n",
       "      <td>11/15/2022 12:00:00 AM</td>\n",
       "      <td>(37.78561008636915, -122.40815476434545)</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>28852.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>POINT (-122.40815 37.78561)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  locationid              Applicant FacilityType       cnn  \\\n",
       "0  1571753    The Geez Freeze        Truck        887000     \n",
       "1  1565571    MOMO INNOVATION LLC    Truck        3525000    \n",
       "2  1565954    Treats by the Bay LLC  Truck        10624001   \n",
       "3  1565594    MOMO INNOVATION LLC    Truck        9094000    \n",
       "5  1565628    Treats by the Bay LLC  Truck        5170000    \n",
       "\n",
       "                                  LocationDescription            Address  \\\n",
       "0  18TH ST: DOLORES ST to CHURCH ST (3700 - 3799)      3750 18TH ST        \n",
       "1  CALIFORNIA ST: DAVIS ST to FRONT ST (100 - 199)     101 CALIFORNIA ST   \n",
       "2  POST ST: MONTGOMERY ST to LICK PL (1 - 40)          1 MONTGOMERY ST     \n",
       "3  MISSION ST: ANNIE ST to 03RD ST (663 - 699)         667 MISSION ST      \n",
       "5  ELLIS ST: POWELL ST to CYRIL MAGNIN ST (100 - 148)  120 ELLIS ST        \n",
       "\n",
       "  blocklot block  lot       permit  ...  Received PriorPermit  \\\n",
       "0  3579006  3579  006  21MFF-00015  ...  20210315  0            \n",
       "1  0263011  0263  011  21MFF-00089  ...  20211022  0            \n",
       "2  0292002  0292  002  21MFF-00094  ...  20211022  0            \n",
       "3  3722067  3722  067  21MFF-00090  ...  20211022  0            \n",
       "5  0326005  0326  005  21MFF-00093  ...  20211022  0            \n",
       "\n",
       "           ExpirationDate                                   Location  \\\n",
       "0  11/15/2022 12:00:00 AM  (37.76201920035647, -122.42730642251331)    \n",
       "1  11/15/2022 12:00:00 AM  (37.792948952834664, -122.39809861316652)   \n",
       "2  11/15/2022 12:00:00 AM  (37.78924953407508, -122.40241859729358)    \n",
       "3  11/15/2022 12:00:00 AM  (37.7865580501799, -122.40103337534973)     \n",
       "5  11/15/2022 12:00:00 AM  (37.78561008636915, -122.40815476434545)    \n",
       "\n",
       "   Fire Prevention Districts  Police Districts Supervisor Districts Zip Codes  \\\n",
       "0  8.0                        4.0               5.0                  28862.0    \n",
       "1  4.0                        1.0               10.0                 28860.0    \n",
       "2  4.0                        1.0               10.0                 28854.0    \n",
       "3  12.0                       2.0               9.0                  28855.0    \n",
       "5  14.0                       10.0              10.0                 28852.0    \n",
       "\n",
       "   Neighborhoods (old)                     geometry  \n",
       "0  3.0                  POINT (-122.42731 37.76202)  \n",
       "1  6.0                  POINT (-122.39810 37.79295)  \n",
       "2  6.0                  POINT (-122.40242 37.78925)  \n",
       "3  6.0                  POINT (-122.40103 37.78656)  \n",
       "5  36.0                 POINT (-122.40815 37.78561)  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv into a DataFrame\n",
    "df = pd.read_csv(\n",
    "    \"data/Mobile_Food_Facility_Permit.csv\",\n",
    "    dtype={\"locationid\": str, \"Longitude\": float, \"Latitude\": float},    \n",
    ")\n",
    "\n",
    "# Filter \"APPROVED\" records\n",
    "df = df[df.Status == \"APPROVED\"]\n",
    "\n",
    "# Construct a GeoDataFrame from df\n",
    "# \"geometry\" column is built from \"Logitude\" and \"Latitude\" columns\n",
    "gdf = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df.Longitude, df.Latitude))\n",
    "\n",
    "gdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac526ff5-eb49-4a14-a3d3-3f9157bdedb9",
   "metadata": {},
   "source": [
    "Now we can start importing triples and building Geohash index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "cfc88bac-cce6-4e37-8444-07a99f3d8e7e",
   "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>success</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   success\n",
       "0  True   "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = ag_connect('Mobile_Food_Facility_Permit', clear=True)\n",
    "\n",
    "# Namespaces\n",
    "ns = 'ex://food/'\n",
    "ns_geo = \"http://www.opengis.net/ont/geosparql#\"\n",
    "ns_geof = \"http://www.opengis.net/def/function/geosparql/\"\n",
    "ns_geoext = \"http://franz.com/ns/allegrograph/3.0/geosparql/ext#\"\n",
    "ns_qudt = \"http://qudt.org/vocab/unit/\"\n",
    "\n",
    "# RDF Properties\n",
    "name = conn.createURI(ns, 'name')\n",
    "location = conn.createURI(ns, 'location')\n",
    "geo_hasGeometry = conn.createURI(ns_geo, \"hasGeometry\")\n",
    "geo_asWKT = conn.createURI(ns_geo, \"asWKT\")\n",
    "\n",
    "# RDF Datatype\n",
    "geo_wktLiteral = conn.createURI(ns_geo, \"wktLiteral\")\n",
    "\n",
    "for _, row in gdf.iterrows():\n",
    "    if row.Longitude != 0 and row.Latitude != 0:\n",
    "        subject = conn.createURI(ns, row.locationid)\n",
    "        geometry = conn.createURI(ns, row.locationid+\"Geom\")\n",
    "        wkt_literal = conn.createLiteral(row.geometry.wkt, datatype=geo_wktLiteral)\n",
    "        conn.addTriple(subject, name, conn.createLiteral(row.Applicant))\n",
    "        conn.addTriple(subject, geo_hasGeometry, geometry)\n",
    "        conn.addTriple(geometry, geo_asWKT, wkt_literal)\n",
    "    \n",
    "conn.prepareTupleQuery(query=\"\"\"\n",
    "PREFIX geoext: <http://franz.com/ns/allegrograph/3.0/geosparql/ext#>\n",
    "\n",
    "SELECT ?success WHERE {\n",
    "    ?success geoext:buildGeohashIndex () .\n",
    "}\n",
    "\"\"\").evaluate().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "769143ca-d411-4e03-9561-7f284ceed9a3",
   "metadata": {},
   "source": [
    "By using [ipyleaflet](https://ipyleaflet.readthedocs.io/en/latest/index.html), we create an interactive map where all locations are marked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d911da27-b742-455e-adb5-e1fc34d31500",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "80ba5d3cdfb043419e8df659d1592a8b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Map(center=[37.7601167, -122.4580325], controls=(ZoomControl(options=['position', 'zoom_in_text', 'zoom_in_tit…"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize the map\n",
    "m = ipyleaflet.Map(center=(37.7601167,-122.4580325), zoom=12)\n",
    "\n",
    "# Add markers\n",
    "for _, row in gdf.iterrows():\n",
    "    m += ipyleaflet.Marker(location=(row.Latitude, row.Longitude), draggable=False)\n",
    "\n",
    "# Draw polygon callback \n",
    "def handle_draw(self, action, geo_json):\n",
    "    if action == 'created':\n",
    "        polygon_geojson = json.dumps(geo_json[\"geometry\"])\n",
    "        query = f'''PREFIX : <ex://food/>\n",
    "PREFIX geo: <http://www.opengis.net/ont/geosparql#>\n",
    "PREFIX geof: <http://www.opengis.net/def/function/geosparql/>\n",
    "\n",
    "SELECT DISTINCT ?s ?name WHERE\n",
    "{{\n",
    "    ?s :name ?name ;\n",
    "        geo:hasGeometry ?geom .\n",
    "    ?geom geo:sfWithin \"\"\"{polygon_geojson}\"\"\"^^geo:geoJSONLiteral .\n",
    "}}'''\n",
    "        with conn.prepareTupleQuery(query=query).evaluate() as res:\n",
    "            for bindings in res:\n",
    "                print(bindings.getValue('s'), bindings.getValue(\"name\"))\n",
    "\n",
    "dc = ipyleaflet.DrawControl(polyline={}, circle={}, marker={}, edit=False)\n",
    "dc.on_draw(handle_draw)\n",
    "m.add_control(dc)\n",
    "    \n",
    "# Show the map    \n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9f382e9-e7e8-4245-892a-ba8d0d6a5726",
   "metadata": {},
   "source": [
    "From the topleft column, we can click the \"polygon\" button and draw a polygon.\n",
    "\n",
    "![](img/screenshot-1.jpg)\n",
    "\n",
    "After drawing a polygon, `handle_draw` callback will print the contained points to log.\n",
    "\n",
    "![](img/screenshot-2.jpg)\n",
    "![](img/screenshot-3.jpg)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:geo-example]",
   "language": "python",
   "name": "conda-env-geo-example-py"
  },
  "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}