Python för Geodata

Ovning_1.ipynb

Ovning_1_Losningar.ipynb

Ovning_2.ipynb

Ovning_2_Losningar.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Avsnitt 2.1: Hitta paket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.1.1: Gå till den [officiella dokumentationen](https://docs.python.org/3/library/index.html) och leta reda på ett paket för att göra en enkel nedladdning (av t.ex. en webbsida)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from urllib.request import urlopen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.1.2: Använd Google för att hitta ett paket för att arbeta mot en PostgreSQL-databas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# http://initd.org/psycopg/docs/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Avsnitt 2.2: Använda paket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.1: Använd de inbyggda paketen för nedladdning (som du redan importerat) och [`xml.etree.ElementTree`](https://docs.python.org/3/library/xml.etree.elementtree.html) för att skapa en lista av alla datamängders namn (_title_) i [Nedladdningstjänsten Byggnad](http://download.lantmateriet.se/atom/inspire/bu.xml)\n",
    "\n",
    "Länken är `http://download.lantmateriet.se/atom/inspire/bu.xml`\n",
    "\n",
    "Tips: Användbara funktioner i ElementTree: `fromstring()`, `findall()`, `find()`, `text`\n",
    "\n",
    "OBS: XML-dokumentet använder sig av namespaces. Det innebär att du måste ange t.ex. `{http://www.w3.org/2005/Atom}entry` istället för bara `entry` när du anropar `find()` eller `findall()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from xml.etree import ElementTree\n",
    "\n",
    "res = urlopen('http://download.lantmateriet.se/atom/inspire/bu.xml')\n",
    "tree = ElementTree.fromstring(res.read())\n",
    "titles = [e.find('{http://www.w3.org/2005/Atom}title').text for e in tree.findall('{http://www.w3.org/2005/Atom}entry')]\n",
    "titles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.2: Upprepa 2.2.1 men använd nu istället [`requests`](https://2.python-requests.org/en/master/) för nedladdningen\n",
    "\n",
    "Tips: Du kan ladda ned med pip direkt i Jupyter. Skriv bara `%pip install <namn>` som rad i samma cell som din Python-kod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q requests\n",
    "\n",
    "from xml.etree import ElementTree\n",
    "import requests\n",
    "\n",
    "res = requests.get('http://download.lantmateriet.se/atom/inspire/bu.xml')\n",
    "tree = ElementTree.fromstring(res.content)\n",
    "titles = [e.find('{http://www.w3.org/2005/Atom}title').text for e in tree.findall('{http://www.w3.org/2005/Atom}entry')]\n",
    "titles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.3: Använd [`fiona`](https://fiona.readthedocs.io/en/stable/) och [`shapely`](https://shapely.readthedocs.io/en/latest/) för att:\n",
    "\n",
    "1. Läsa in filen `idrottsplatser.geojson`\n",
    "2. Buffra alla _tennisplatser_ med 10 meter\n",
    "3. Skriv de buffrade tennisplatserna till en ny Shapefil\n",
    "\n",
    "Du har fått en början att utgå ifrån nedan:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q shapely fiona\n",
    "\n",
    "from shapely.geometry import shape, mapping\n",
    "import fiona\n",
    "\n",
    "with fiona.open('idrottsplatser.geojson', 'r') as reader:\n",
    "    # reader.meta innehåller koordinatsystem, geometrityp och tabelldefinition, och genom att använda\n",
    "    # `**reader.meta` får den nya filen exakt samma struktur (men den är ännu tom)\n",
    "    with fiona.open('idrottsplatser_buffrade.geojson', 'w', **reader.meta) as writer:\n",
    "        # du kan använda reader som en lista som ger dig `dict`s med nycklarna _geometry_ och _properties_\n",
    "        # tennisplatser har ANDAMAL 7\n",
    "        # du kan skapa ett shapely objekt från en fiona-geometri med `shape(rad['geometry'])`\n",
    "        # leta själv upp hur du buffrar med shapely i dess dokumentation\n",
    "        # du kan konvertera tillbaka till fiona-geometry med `mapping(...din shapely-geometri...)`\n",
    "        # och sist kan du skriva ett fiona objekt (som du fått av `reader`) med `writer.write(...)`\n",
    "        from tqdm.autonotebook import tqdm\n",
    "        for rad in tqdm(reader):\n",
    "            if rad['properties']['ANDAMAL'] != 7:\n",
    "                continue\n",
    "            s = shape(rad['geometry'])\n",
    "            s = s.buffer(10)\n",
    "            rad['geometry'] = mapping(s)\n",
    "            writer.write(rad)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tips: Tog körningen tid? Importera `from tqdm.autonotebook import tqdm` och byt sedan ut din for-loop mot `for ... in tqdm(reader):`. Vad händer?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.4: Använd [`GeoPandas`](http://geopandas.org/) för att läsa in filen med idrottsplatser och undersök resultatet\n",
    "\n",
    "* Använd metoden `plot`, inklusive dess argument `column` satt till `'ANDAMAL'` (exempel här: http://geopandas.org/mapping.html#choropleth-maps)\n",
    "* Använd metoden `head`\n",
    "\n",
    "OBS: Utöver att installera geopandas så behöver du även installera `descartes` vilket GeoPandas använder för att göra kartor\n",
    "OBS: Descartes i sin tur använder matplotlib, så du vill först av allt ha med raden `%matplotlib inline`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%pip install -q geopandas descartes\n",
    "\n",
    "import geopandas\n",
    "df = geopandas.read_file('idrottsplatser.geojson')\n",
    "df.plot(column='ANDAMAL')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.5: Mer plottar\n",
    "\n",
    "Var det svårt att se resultatet av `plot`? GeoPandas/Pandas ger dig några olika \"indexerare\", bl.a. `cx` som gör det möjligt att välja objekt baserade på dess koordinater. Du kan använda `cx` såhär för att få en ny GeoDataFrame över endast Gävle:\n",
    "\n",
    "```python\n",
    "gavle_df = df.cx[610000:625000, 6720000:6735000]\n",
    "```\n",
    "\n",
    "Där siffrorna såklart är koordinater. Prova att göra en ny plot som endast innehåller idrottsplatser över Gävleområdet!\n",
    "\n",
    "Prova sedan att lägga till argumenten `legend=True` och `categorical=True` till `plot`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gavle_df = df.cx[610000:625000, 6720000:6735000]\n",
    "gavle_df.plot(column='ANDAMAL', legend=True, categorical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.6: Arbeta med Pandas\n",
    "\n",
    "Lägg märke till att teckenförklaringen i förgående plot bara innehöll siffror. Inte speciellt användbart! Vi kan ändra på det genom de funktioner som Pandas (som GeoPandas baserar på) erbjuder.\n",
    "\n",
    "* Byt ut alla `{}` så att koden blir korrekt\n",
    "* Skapa en ny plot med legend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ny_df = gavle_df.replace(dict(ANDAMAL={\n",
    "    1: 'bollplan', 2: 'fotbollsplan', 3: 'galoppbana', 4: 'bandyplan', 5: 'löparbana', 6: 'motorsportbana', 7: 'tennisbana', 8: 'travbana'}))\n",
    "ny_df.plot(column='ANDAMAL', legend=True, categorical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Skapa en plot med endast fotbollsplaner i Gävle\n",
    "\n",
    "Tips:\n",
    "* Du kan anropa `df[...]` med en lista av radindex (oftast radnummer)\n",
    "* `df.ANDAMAL == 'fotbollsplan'` ger dig en lista med radindex där _ANDAMAL_ är lika med _fotbollsplan_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ny_df[ny_df.ANDAMAL == 'fotbollsplan'].plot(column='ANDAMAL')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgift 2.2.7: Skriva rapporter i Word och Excel med [`python-docx`](https://python-docx.readthedocs.io/) och [`openpyxl`](https://openpyxl.readthedocs.io/)\n",
    "\n",
    "Skapa ett Word-dokument och ett Excel-dokument med en kort rapport om idrottsplatserna i `idrottsplatser.geojson`, t.ex. antal platser av respektive typ. Välj själv om du använder `fiona` eller GeoPandas för att läsa filen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q python-docx\n",
    "\n",
    "from docx import Document\n",
    "\n",
    "df = geopandas.read_file('idrottsplatser.geojson')\n",
    "\n",
    "replaced_df = df.replace(dict(ANDAMAL={\n",
    "    1: 'bollplan', 2: 'fotbollsplan', 3: 'galoppbana', 4: 'bandyplan', 5: 'löparbana', 6: 'motorsportbana', 7: 'tennisbana', 8: 'travbana'\n",
    "}))\n",
    "count = replaced_df.ANDAMAL.value_counts()\n",
    "print(count)\n",
    "\n",
    "doc = Document()\n",
    "doc.add_heading('Idrottsplatser', 0)\n",
    "doc.add_paragraph('Det finns {} idrottsplatser där man kan hitta hästar.'.format(count['travbana'] + count['galoppbana']))\n",
    "doc.add_paragraph('På {} idrottsplaner blir dock fotbollsspelarna gnälliga om du dyker upp med din häst.'.format(count['fotbollsplan']))\n",
    "doc.save('idrottsplatser.docx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q openpyxl\n",
    "\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import Font\n",
    "import fiona\n",
    "\n",
    "count = dict()\n",
    "replacements = {\n",
    "    0: 'ospec', 1: 'bollplan', 2: 'fotbollsplan', 3: 'galoppbana', 4: 'bandyplan', 5: 'löparbana', 6: 'motorsportbana', 7: 'tennisbana', 8: 'travbana'\n",
    "}\n",
    "with fiona.open('idrottsplatser.geojson', 'r') as reader:\n",
    "    for feature in reader:\n",
    "        raw = feature['properties']['ANDAMAL']\n",
    "        name = replacements[raw]\n",
    "        if name not in count:\n",
    "            count[name] = 0\n",
    "        # eller count.setdefault(name, 0)\n",
    "        count[name] += 1\n",
    "print(count)\n",
    "\n",
    "wb = Workbook()\n",
    "sheet = wb.active\n",
    "sheet.append(('Namn', 'Antal'))\n",
    "sheet['A1'].font = Font(bold=True)\n",
    "sheet['B1'].font = Font(bold=True)\n",
    "for name, amount in count.items():\n",
    "    sheet.append((name, amount))\n",
    "wb.save('idrottsplatser.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Avsnitt 2.3: Avslut\n",
    "\n",
    "Nu ska du få prova använda flera av de saker du lärt dig vid dessa övningar.\n",
    "\n",
    "* Slutresultatet måste innehålla anrop till samtliga funktioner/metoder du skapat\n",
    "\n",
    "Gör __en__ av följande uppgifter:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgiftsalternativ 2.3.1: GDS-BAL\n",
    "\n",
    "**OBS:** Denna uppgift måste genomföras på egen dator uppkopplad på intranätet!\n",
    "\n",
    "**OBS:** Kräver inloggningsuppgifter till _oe11gdlr_ databasen (GDSBAL verifikation)\n",
    "\n",
    "* Använd paketet [`cx_Oracle`](https://cx-oracle.readthedocs.io/en/latest/) och SQL för att hitta antalet gällande byggnader i databasen\n",
    "    * Använd `'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oe11gdlr.lmv.lm.se)(PORT=1521))(CONNECT_DATA=(SID=oe11gdlr)))'` som serveradress och `encoding='utf-8'`\n",
    "    * Byggnader finns i tabellen `gds_bui.building` och de är gällande om `OBJECTSTATUS` är lika med `1`\n",
    "* Skapa en enkel webbserver med `Flask` som visar antalet byggnader\n",
    "    * Börja med första kodstycket [här](https://flask.palletsprojects.com/en/1.1.x/quickstart/) och avsluta med raden `app.run()`\n",
    "    * När du kör koden kommer den inte att bli klar, utan starta den, vänta på meddelandet att servern är redo och besök sedan den addressen som visas i en ny flik i din webbläsare, avsluta sedan webbservern genom att välja Kernel/Interrupt i menyn\n",
    "* Använd Pandas (utan Geo-biten denna gång) för att köra följande SQL:\n",
    "```sql\n",
    "SELECT OBJECTSTATUS, COUNT(*) FROM GDS_BUI.BUILDING GROUP BY OBJECTSTATUS\n",
    "```\n",
    "    * Du kan köra SQL med Pandas genom att använda `pandas.read_sql('din SQL-fråga här', con=...resultatet från cx_Oracle.connect här...)`\n",
    "* Gör det lättare att läsa resultatet genom att byta ut siffrorna i `OBJECTSTATUS` kolumnen mot passande text (0 är planerad, 1 är gällande, 2 är gällande under utredning och 3 är avregistrerad)\n",
    "* Modifiera din webbserver för att visa statistiken för samtliga objektstatus\n",
    "\n",
    "Gör så många av dessa som du hinner:\n",
    "\n",
    "* Skriv om din webbserver så att du har en sida för byggnader och en för addressplatser (tabellen `gds_adr.addressplace`)\n",
    "* Använd din DataFrame eller kör ett eget anrop med cx_Oracle för att skapa en Word-rapport över samtliga objektstatus för byggnad och adress\n",
    "* Använd [detta officiella exempel](https://github.com/oracle/python-cx_Oracle/blob/master/samples/SpatialToGeoPandas.py#L170-L177) eller det förenklade exemplet nedan för att få in resultatet av följande fråga i en GeoDataFrame:\n",
    "```sql\n",
    "SELECT pl.postcode, pl.posttown, sdo_util.to_wkbgeometry(po.geometry) FROM GDS_ADR.addresspoint po\n",
    "JOIN GDS_ADR.addressplace pl ON pl.id = po.addressplace_id\n",
    "WHERE ROWNUM < 20\n",
    "```\n",
    "```python\n",
    "cursor.execute({})\n",
    "gdf = geopandas.GeoDataFrame(cursor.fetchall(), columns=[{}, 'wkbgeometry'])\n",
    "gdf['geometry'] = geopandas.GeoSeries(gdf['wkbgeometry'].apply(lambda x: loads(x.read())))\n",
    "del gdf['wkbgeometry']\n",
    "```\n",
    "* Plotta resultatet och gör det tillgängligt i din Word-rapport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q cx_Oracle\n",
    "\n",
    "import cx_Oracle\n",
    "import pandas, geopandas\n",
    "from docx import Document\n",
    "from shapely.wkb import loads\n",
    "import matplotlib\n",
    "\n",
    "doc = Document()\n",
    "\n",
    "conn = cx_Oracle.connect('jandal', 'ksj22xs7m',\n",
    "                         '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oe11gdlr.lmv.lm.se)(PORT=1521))(CONNECT_DATA=(SID=oe11gdlr)))',\n",
    "                         encoding='utf-8')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('SELECT COUNT(*) FROM gds_bui.building WHERE OBJECTSTATUS = 1')\n",
    "print('Det finns {} gällande byggnader'.format(next(cursor)[0]))\n",
    "\n",
    "df = pandas.read_sql('SELECT OBJECTSTATUS, COUNT(*) AS COUNT FROM GDS_BUI.BUILDING GROUP BY OBJECTSTATUS', con=conn)\n",
    "df = df.replace(dict(OBJECTSTATUS={0: 'planerad', 1: 'gällande', 2: 'gällande under utredning', 3: 'avregistrerad'}))\n",
    "print(df)\n",
    "\n",
    "doc.add_heading('Byggnad', 0)\n",
    "doc.add_paragraph('Det finns {} planerade byggnader'.format(list(df[df.OBJECTSTATUS == 'planerad'].COUNT)[0]))\n",
    "\n",
    "df = pandas.read_sql('SELECT OBJECTSTATUS, COUNT(*) AS COUNT FROM GDS_ADR.ADDRESSPLACE GROUP BY OBJECTSTATUS', con=conn)\n",
    "df = df.replace(dict(OBJECTSTATUS={0: 'planerad', 1: 'gällande', 2: 'gällande under utredning', 3: 'avregistrerad'}))\n",
    "print(df)\n",
    "\n",
    "doc.add_heading('Adress', 0)\n",
    "doc.add_paragraph('Det finns {} planerade adresser'.format(list(df[df.OBJECTSTATUS == 'planerad'].COUNT)[0]))\n",
    "\n",
    "cursor.execute('SELECT pl.postcode, pl.posttown, sdo_util.to_wkbgeometry(po.geometry) FROM GDS_ADR.addresspoint po JOIN GDS_ADR.addressplace pl ON pl.id = po.addressplace_id WHERE ROWNUM < 20')\n",
    "gdf = geopandas.GeoDataFrame(cursor.fetchall(), columns=['postcode', 'posttown', 'wkbgeometry'])\n",
    "gdf['geometry'] = geopandas.GeoSeries(gdf['wkbgeometry'].apply(lambda x: loads(x.read())))\n",
    "del gdf['wkbgeometry']\n",
    "gdf.plot()\n",
    "matplotlib.pyplot.savefig('adressplot.png')\n",
    "doc.add_picture('adressplot.png')\n",
    "\n",
    "doc.save('gdsbal.docx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q Flask\n",
    "\n",
    "from flask import Flask\n",
    "import pandas\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "@app.route('/bui')\n",
    "def bui():\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute('SELECT COUNT(*) FROM GDS_BUI.BUILDING WHERE OBJECTSTATUS = 1')\n",
    "        return str(next(cursor)[0])\n",
    "\n",
    "@app.route('/adr')\n",
    "def adr():\n",
    "    with conn.cursor() as cursor:\n",
    "        cursor.execute('SELECT COUNT(*) FROM GDS_ADR.ADDRESSPLACE WHERE OBJECTSTATUS = 1')\n",
    "        return str(next(cursor)[0])\n",
    "\n",
    "app.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgiftsalternativ 2.3.2: Externa datakällor\n",
    "\n",
    "* Använd `requests` och den inbyggda modulen `zipfile` för att ladda ned och extrahera [befolkningsstatistik från SCB](https://www.scb.se/hitta-statistik/regional-statistik-och-kartor/geodata/oppna-geodata/statistik-pa-rutor/) (välj _Befolkning totalt_)\n",
    "* Läs in det i GeoPandas och plotta det som en kloroplet-karta\n",
    "* Skapa en enkel webbserver med `Flask` som visar den totala folkmängden i hela Sverige\n",
    "    * Börja med första kodstycket [här](https://flask.palletsprojects.com/en/1.1.x/quickstart/) och avsluta med raden `app.run()`\n",
    "    * När du kör koden kommer den inte att bli klar, utan starta den, vänta på meddelandet att servern är redo och besök sedan den addressen som visas i en ny flik i din webbläsare, avsluta sedan webbservern genom att välja Kernel/Interrupt i menyn\n",
    "* Ladda ned och extrahera listan över [myndighetskontor från SCB](https://www.scb.se/hitta-statistik/regional-statistik-och-kartor/geodata/oppna-geodata/myndighets--och-kommunkontor/)\n",
    "* Använd GeoPandas eller Shapely för att få en lista över samtliga myndighetskontor inklusive den lokala befolkningstätheten\n",
    "    * Tips: GeoPandas har funktion för spatiala joins, men detta kräver att paketet `rtree` är installerat. Om du är på Windows behöver måste du installera detta med `%conda install rtree` för att få med alla beroenden.\n",
    "\n",
    "Tips: Nedladdningar kan ta tid, här är ett perfekt tillfälle att använda IPython/Jupyters möjligheter till att använda celler. Lägg in koden för att ladda ned och extrahera respektive .zip-fil i en varsin cell, och resten av koden därefter så behöver du bara ladda ned och extrahera en gång.\n",
    "\n",
    "Gör så många av dessa som du hinner:\n",
    "\n",
    "* Skapa en Word- eller Excel-rapport över vilka myndighetskontor som ligger i de mest tätbefolkade områdena\n",
    "    * Avgör ditt val av Word eller Excel hur många myndighetskontor du tar med i rapporten?\n",
    "* Skapa en plot av myndighetskontor, färglagd efter befolkningstäthet\n",
    "* Lägg till någon av de plottar du gjort som en ny `route` i din webbserver\n",
    "* Skapa en Word-rapport (eller lägg till i den du skapat tidigare) med någon av de plottar du gjort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q rtree\n",
    "%conda install rtree\n",
    "%matplotlib inline\n",
    "\n",
    "import requests\n",
    "import zipfile, io\n",
    "import geopandas, rtree\n",
    "import matplotlib\n",
    "\n",
    "response = requests.get('https://www.scb.se/contentassets/790b7863da264730b626e4289dcb15a5/grid1km_totpop_20181231.zip', verify=False)\n",
    "z = zipfile.ZipFile(io.BytesIO(response.content))\n",
    "z.extractall('scb')\n",
    "\n",
    "fig, ax = matplotlib.pyplot.subplots(1, 1, figsize=(5, 15))\n",
    "df_befolk = geopandas.read_file('scb/Inspire_TotPop_Sweref_region.shp')\n",
    "df_befolk.plot(column='Pop', ax=ax)\n",
    "\n",
    "response = requests.get('https://www.scb.se/contentassets/521300ad9be647c599ee67717882b64f/myndighetskontor2017.zip', verify=False)\n",
    "z = zipfile.ZipFile(io.BytesIO(response.content))\n",
    "z.extractall('scb_kontor')\n",
    "\n",
    "df_kontor = geopandas.read_file('scb_kontor/Myndighetskontor.shp')\n",
    "df_befolk.sindex\n",
    "df = geopandas.sjoin(df_kontor, df_befolk, 'inner', 'intersects')\n",
    "df = df.sort_values(by=['Pop'], ascending=False)\n",
    "print(df.head())\n",
    "\n",
    "from docx import Document\n",
    "\n",
    "doc = Document()\n",
    "\n",
    "doc.add_heading('Myndighetskontor', 0)\n",
    "df.head(20).plot(column='Pop')\n",
    "matplotlib.pyplot.savefig('kontor.png')\n",
    "doc.add_picture('kontor.png')\n",
    "\n",
    "for index, row in df.head(20).iterrows():\n",
    "    doc.add_paragraph('Runt {} på {} i {} bor {} personer'.format(row.FÖRETAGSNA, row.BESÖKSADRE, row.BESÖKSORT, row.Pop))\n",
    "\n",
    "doc.save('kontor.docx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from flask import Flask, send_file\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "@app.route('/')\n",
    "def index():\n",
    "    return 'Det bor {} personer i Sverige'.format(df_befolk.Pop.sum())\n",
    "\n",
    "@app.route('/kontor.png')\n",
    "def kontor():\n",
    "    fig, ax = matplotlib.pyplot.subplots(1, 1, figsize=(15, 5))\n",
    "    df.plot(column='Pop', ax=ax)\n",
    "    matplotlib.pyplot.savefig('_kontor.png')\n",
    "    return send_file('_kontor.png')\n",
    "\n",
    "app.run()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uppgiftsalternativ 2.3.3: TOPO\n",
    "\n",
    "**OBS:** Denna uppgift måste genomföras på egen dator uppkopplad på intranätet!\n",
    "\n",
    "* Använd paketet [`cx_Oracle`](https://cx-oracle.readthedocs.io/en/latest/) och SQL för att hitta antalet anläggningsområdespunkter i databasen\n",
    "    * Använd `'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=topoprd.lmv.lm.se)(PORT=1521))(CONNECT_DATA=(SID=topoprd)))'` som serveradress och `encoding='utf-8'`, fråga Jan om inloggningsuppgifter\n",
    "    * Anläggningsområdespunkter finns i tabellen `TOPO.ANLAGGNINGSOMRADEP`\n",
    "* Skapa en enkel webbserver med `Flask` som visar anläggningsområdespunkter\n",
    "    * Börja med första kodstycket [här](https://flask.palletsprojects.com/en/1.1.x/quickstart/) och avsluta med raden `app.run()`\n",
    "    * När du kör koden kommer den inte att bli klar, utan starta den, vänta på meddelandet att servern är redo och besök sedan den addressen som visas i en ny flik i din webbläsare, avsluta sedan webbservern genom att välja Kernel/Interrupt i menyn\n",
    "* Använd Pandas (utan Geo-biten denna gång) för att köra följande SQL:\n",
    "```sql\n",
    "SELECT SUBTYP, COUNT(*) FROM TOPO.ANLAGGNINGSOMRADEP GROUP BY SUBTYP\n",
    "```\n",
    "    * Du kan köra SQL med Pandas genom att använda `pandas.read_sql('din SQL-fråga här', con=...resultatet från cx_Oracle.connect här...)`\n",
    "* Gör det lättare att läsa resultatet genom att byta ut siffrorna i `SUBTYP` kolumnen mot passande text (2841 för industri, 2842 för samhällsfunktion, 2843 för rekreation och 2844 för idrottsplan)\n",
    "* Modifiera din webbserver för att visa statistiken för samtliga andamål\n",
    "\n",
    "Gör så många av dessa som du hinner:\n",
    "\n",
    "* Skriv om din webbserver så att du har en sida för punkter och en för ytor (tabellen `TOPO.ANLAGGNINGSOMRADEY`, `SUBTYP` 2831 för industri, 2832 för samhällsfunktion, 2833 för rekreation, 2834 för skjutfält, 2835 för idrottsplan)\n",
    "* Använd din DataFrame eller kör ett eget anrop med cx_Oracle för att skapa en Word-rapport över samtliga ändamål för punkter och ytor\n",
    "* Använd [detta officiella exempel](https://github.com/oracle/python-cx_Oracle/blob/master/samples/SpatialToGeoPandas.py#L170-L177) eller det förenklade exemplet nedan för att få in resultatet av följande fråga i en GeoDataFrame:\n",
    "```sql\n",
    "SELECT GANDRADAV, SUBTYP, sde.st_astext(SHAPE) FROM TOPO.ANLAGGNINGSOMRADEP WHERE ROWNUM < 20\n",
    "```\n",
    "```python\n",
    "cursor.execute({})\n",
    "gdf = geopandas.GeoDataFrame(cursor.fetchall(), columns=[{}, 'wktgeometry'])\n",
    "gdf['geometry'] = geopandas.GeoSeries(gdf['wktgeometry'].apply(lambda x: loads(str(x))))\n",
    "del gdf['wktgeometry']\n",
    "```\n",
    "* Plotta resultatet och gör det tillgängligt i din Word-rapport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -q cx_Oracle\n",
    "\n",
    "import cx_Oracle\n",
    "import pandas, geopandas\n",
    "from docx import Document\n",
    "from shapely.wkt import loads\n",
    "import matplotlib\n",
    "\n",
    "doc = Document()\n",
    "\n",
    "conn = cx_Oracle.connect('topor', 'topor',\n",
    "                         '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=topoprd.lmv.lm.se)(PORT=1521))(CONNECT_DATA=(SID=topoprd)))',\n",
    "                         encoding='utf-8')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('SELECT COUNT(*) FROM TOPO.ANLAGGNINGSOMRADEP')\n",
    "print('Det finns {} anläggningsområdespunkter'.format(next(cursor)[0]))\n",
    "\n",
    "df = pandas.read_sql('SELECT SUBTYP, COUNT(*) AS COUNT FROM TOPO.ANLAGGNINGSOMRADEP GROUP BY SUBTYP', con=conn)\n",
    "df = df.replace(dict(SUBTYP={2841: 'industri', 2842: 'samhällsfunktion', 2843: 'rekreation', 2844: 'idrottsplan'}))\n",
    "print(df)\n",
    "\n",
    "doc.add_heading('Anläggningsområden', 0)\n",
    "doc.add_heading('Punkter', 1)\n",
    "doc.add_paragraph('Det finns {} industrier'.format(list(df[df.SUBTYP == 'industri'].COUNT)[0]))\n",
    "\n",
    "df = pandas.read_sql('SELECT SUBTYP, COUNT(*) AS COUNT FROM TOPO.ANLAGGNINGSOMRADEY GROUP BY SUBTYP', con=conn)\n",
    "df = df.replace(dict(SUBTYP={2831: 'industri', 2832: 'samhällsfunktion', 2833: 'rekreation', 2834: 'skjutfält', 2835: 'idrottsplan'}))\n",
    "print(df)\n",
    "\n",
    "doc.add_heading('Ytor', 1)\n",
    "doc.add_paragraph('Det finns {} industriområden'.format(list(df[df.SUBTYP == 'industri'].COUNT)[0]))\n",
    "\n",
    "cursor.execute('SELECT GANDRADAV, SUBTYP, sde.st_astext(SHAPE) FROM TOPO.ANLAGGNINGSOMRADEP WHERE ROWNUM < 20')\n",
    "gdf = geopandas.GeoDataFrame(cursor.fetchall(), columns=['GANDRADAV', 'SUBTYP', 'wktgeometry'])\n",
    "gdf['geometry'] = geopandas.GeoSeries(gdf['wktgeometry'].apply(lambda x: loads(str(x))))\n",
    "del gdf['wktgeometry']\n",
    "gdf.plot(column='SUBTYP', categorical=True, legend=True)\n",
    "matplotlib.pyplot.savefig('anlaggningsplot.png')\n",
    "doc.add_picture('anlaggningsplot.png')\n",
    "\n",
    "doc.save('anlaggningar.docx')"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

idrottsplatser.geojson