connect-sqlite.ipynb

connect-sqlite.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNur+W2Cj+22Rv0C14WnxQA",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/gist/mmc1718/abcea9807143c9ef5610c5f4282c133c/dependencies.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Download data file for London from Geofabrik\n",
        "\n",
        "!wget http://download.geofabrik.de/europe/great-britain/england/greater-london-latest.osm.pbf -O london-latest.osm.pbf"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Mk1AfLhp6vtR",
        "outputId": "848d8f13-67bf-4550-aef5-0719af9860d2"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2023-05-22 10:05:38--  http://download.geofabrik.de/europe/great-britain/england/greater-london-latest.osm.pbf\n",
            "Resolving download.geofabrik.de (download.geofabrik.de)... 65.109.48.72, 65.109.50.43\n",
            "Connecting to download.geofabrik.de (download.geofabrik.de)|65.109.48.72|:80... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 93045636 (89M) [application/octet-stream]\n",
            "Saving to: ‘london-latest.osm.pbf’\n",
            "\n",
            "london-latest.osm.p 100%[===================>]  88.73M  19.5MB/s    in 5.2s    \n",
            "\n",
            "2023-05-22 10:05:44 (17.0 MB/s) - ‘london-latest.osm.pbf’ saved [93045636/93045636]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# get osm data into a vector format\n",
        "# to make it easier to parse the outputted geometries, we will use well known text format (wkt)\n",
        "\n",
        "!ogr2ogr -f SQLite -lco FORMAT=WKT london.sqlite london-latest.osm.pbf"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cKanFTiw6wbO",
        "outputId": "088c62ce-3545-431b-aa46-c5eab011f420"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0...10...20...30...40...50...60...70...80...90...100 - done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# connect to tiles\n",
        "\n",
        "DATA = \"./london.sqlite\"\n",
        "con = sqlite3.connect(DATA)\n",
        "cursor = con.cursor()\n",
        "\n",
        "# inspect the result\n",
        "query = \"\"\"\n",
        "SELECT \n",
        "    name\n",
        "FROM \n",
        "    sqlite_master\n",
        "WHERE \n",
        "    type ='table' AND \n",
        "    name NOT LIKE 'sqlite_%';\n",
        "    \"\"\"\n",
        "\n",
        "tables = cursor.execute(query)\n",
        "tables.fetchall()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0hgLszFr60Cb",
        "outputId": "e2450209-7a4a-4b8c-da25-5149ec2c2b23"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('geometry_columns',),\n",
              " ('spatial_ref_sys',),\n",
              " ('points',),\n",
              " ('lines',),\n",
              " ('multilinestrings',),\n",
              " ('multipolygons',),\n",
              " ('other_relations',)]"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    }
  ]
}