{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1bff0133",
   "metadata": {},
   "source": [
    "# Schools Count Exercise in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0aa1a72",
   "metadata": {},
   "source": [
    "### Count Schools in Berlin using Overpass API through Python\n",
    "This analysis uses the [OSMPythonTools](https://github.com/mocnik-science/osm-python-tools) to query the Overpass API. In addition [Nominatim](https://nominatim.org/) is queried first to define the area of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ec53e00f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[nominatim] downloading data: search\n",
      "[overpass] downloading data: [timeout:25][out:json];area(3600062422)->.searchArea;(node[\"amenity\"=\"school\"](area.searchArea);way[\"amenity\"=\"school\"](area.searchArea);relation[\"amenity\"=\"school\"](area.searchArea);); out body;\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nodes: 131\n",
      "Ways: 932\n",
      "Relations: 21\n"
     ]
    }
   ],
   "source": [
    "from OSMPythonTools.overpass import Overpass\n",
    "from OSMPythonTools.overpass import overpassQueryBuilder\n",
    "from OSMPythonTools.nominatim import Nominatim\n",
    "\n",
    "nominatim = Nominatim()\n",
    "overpass = Overpass()\n",
    "\n",
    "query = overpassQueryBuilder(\n",
    "    area=nominatim.query('Berlin').areaId(),\n",
    "    elementType=['node', 'way', 'relation'],\n",
    "    selector=['\"amenity\"=\"school\"']\n",
    ")\n",
    "\n",
    "result = overpass.query(query)\n",
    "\n",
    "print(f\"Nodes: {len(result.nodes())}\")\n",
    "print(f\"Ways: {len(result.ways())}\")\n",
    "print(f\"Relations: {len(result.relations())}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49708877",
   "metadata": {},
   "source": [
    "### Count Schools per District using ohsome-py package\n",
    "Here we use the [ohsome-py](https://github.com/GIScience/ohsome-py) package to query the ohsome API. We use the `elements/count/groupBy/boundary` endpoint. \n",
    "\n",
    "For this analysis we assume that you already downloaded the district boundaries for Berlin. If not do this via [OSM Boundaries](https://osm-boundaries.com/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5302441a",
   "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></th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>boundary</th>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>feature1</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature10</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature11</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature12</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature2</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature3</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature4</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature5</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature6</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>87.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature7</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature8</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature9</th>\n",
       "      <th>2022-11-02 09:00:00+00:00</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     value\n",
       "boundary  timestamp                       \n",
       "feature1  2022-11-02 09:00:00+00:00   74.0\n",
       "feature10 2022-11-02 09:00:00+00:00  107.0\n",
       "feature11 2022-11-02 09:00:00+00:00   78.0\n",
       "feature12 2022-11-02 09:00:00+00:00   84.0\n",
       "feature2  2022-11-02 09:00:00+00:00  105.0\n",
       "feature3  2022-11-02 09:00:00+00:00  133.0\n",
       "feature4  2022-11-02 09:00:00+00:00   76.0\n",
       "feature5  2022-11-02 09:00:00+00:00   80.0\n",
       "feature6  2022-11-02 09:00:00+00:00   87.0\n",
       "feature7  2022-11-02 09:00:00+00:00   92.0\n",
       "feature8  2022-11-02 09:00:00+00:00   75.0\n",
       "feature9  2022-11-02 09:00:00+00:00  100.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from ohsome import OhsomeClient\n",
    "client = OhsomeClient()\n",
    "\n",
    "infile = \"../../data/berlin_districts.geojson\"\n",
    "with open(infile) as f:\n",
    "    boundaries = json.load(f)\n",
    "    \n",
    "filter_schools = \"amenity=school\"\n",
    "response = client.elements.count.groupByBoundary.post(\n",
    "    bpolys=json.dumps(boundaries), \n",
    "    filter=filter_schools\n",
    ")\n",
    "\n",
    "schools_df = response.as_dataframe()\n",
    "display(schools_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a2a5969",
   "metadata": {},
   "source": [
    "### Count Schools in Berlin per district using ohsome API through Python\n",
    "This example directly queries the ohsome API using Python's `request` module. As above we use this endpoint `elements/count/groupBy/boundary`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f2b8b069",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Response:\n",
      "{\n",
      "    \"attribution\": {\n",
      "        \"url\": \"https://ohsome.org/copyrights\",\n",
      "        \"text\": \"\\u00a9 OpenStreetMap contributors\"\n",
      "    },\n",
      "    \"apiVersion\": \"1.7.0\",\n",
      "    \"groupByResult\": [\n",
      "        {\n",
      "            \"groupByObject\": \"feature1\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 72.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature2\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 113.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature3\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 141.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature4\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 76.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature5\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 80.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature6\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 91.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature7\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 92.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature8\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 75.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature9\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 102.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature10\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 117.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature11\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 80.0\n",
      "                }\n",
      "            ]\n",
      "        },\n",
      "        {\n",
      "            \"groupByObject\": \"feature12\",\n",
      "            \"result\": [\n",
      "                {\n",
      "                    \"timestamp\": \"2022-01-01T00:00:00Z\",\n",
      "                    \"value\": 87.0\n",
      "                }\n",
      "            ]\n",
      "        }\n",
      "    ]\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "from datetime import datetime\n",
    "import requests\n",
    "\n",
    "base_url = \"https://api.ohsome.org/v1\"\n",
    "endpoint = \"/elements/count/groupBy/boundary\"\n",
    "url = base_url + endpoint\n",
    "\n",
    "# open geojson file\n",
    "with open(\"../../data/berlin_districts.geojson\", \"r\") as file:\n",
    "    bpolys = json.load(file)\n",
    "\n",
    "parameters = {\n",
    "    \"bpolys\": json.dumps(bpolys),  # pass GeoJSON as string.\n",
    "    \"filter\": \"amenity=school\",\n",
    "    \"format\": \"json\",\n",
    "    \"time\": \"2022-01-01\",\n",
    "}\n",
    "\n",
    "response = requests.post(url, data=parameters)\n",
    "response.raise_for_status()  # Raise an Exception if HTTP Status Code is not 200\n",
    "\n",
    "print(\"Response:\")\n",
    "print(json.dumps(response.json(), indent=4))  # Pretty print response\n",
    "\n",
    "result = response.json()[\"groupByResult\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cfdbb88",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}