geodataframe.ipynb

geodataframe.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyNaO7MYA3MNtYihKdfEeMFn",
      "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/53f60a0219a0b1c721147c4b21684fba/geodataframe.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from shapely import wkt"
      ],
      "metadata": {
        "id": "GbwV867wHxJg"
      },
      "execution_count": 63,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# get all geometries into one dataframe\n",
        "\n",
        "complete_df = pd.DataFrame()\n",
        "for geom_type in ['lines', 'points', 'multilinestrings', 'multipolygons', 'other_relations']:\n",
        "  df = pd.read_sql(f\"SELECT *, '{geom_type}' AS table_name FROM {geom_type};\", con)\n",
        "  complete_df = pd.concat([complete_df, df], ignore_index=True)"
      ],
      "metadata": {
        "id": "i0NEXs49EiWl"
      },
      "execution_count": 61,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "complete_df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BwsD5_AZHY3R",
        "outputId": "2e31aeda-3623-4c2c-b68d-895da0be19ff"
      },
      "execution_count": 62,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 2255248 entries, 0 to 2255247\n",
            "Data columns (total 35 columns):\n",
            " #   Column        Dtype  \n",
            "---  ------        -----  \n",
            " 0   ogc_fid       int64  \n",
            " 1   WKT_GEOMETRY  object \n",
            " 2   osm_id        object \n",
            " 3   name          object \n",
            " 4   highway       object \n",
            " 5   waterway      object \n",
            " 6   aerialway     object \n",
            " 7   barrier       object \n",
            " 8   man_made      object \n",
            " 9   z_order       float64\n",
            " 10  other_tags    object \n",
            " 11  table_name    object \n",
            " 12  ref           object \n",
            " 13  address       object \n",
            " 14  is_in         object \n",
            " 15  place         object \n",
            " 16  type          object \n",
            " 17  osm_way_id    object \n",
            " 18  aeroway       object \n",
            " 19  amenity       object \n",
            " 20  admin_level   object \n",
            " 21  boundary      object \n",
            " 22  building      object \n",
            " 23  craft         object \n",
            " 24  geological    object \n",
            " 25  historic      object \n",
            " 26  land_area     object \n",
            " 27  landuse       object \n",
            " 28  leisure       object \n",
            " 29  military      object \n",
            " 30  natural       object \n",
            " 31  office        object \n",
            " 32  shop          object \n",
            " 33  sport         object \n",
            " 34  tourism       object \n",
            "dtypes: float64(1), int64(1), object(33)\n",
            "memory usage: 602.2+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# convert dataframe into geodataframe\n",
        "\n",
        "complete_df['geom'] = gpd.GeoSeries.from_wkt(complete_df['WKT_GEOMETRY'])\n",
        "gdf = gpd.GeoDataFrame(complete_df, geometry='geom')\n",
        "gdf.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "G2LYrzKTHtO4",
        "outputId": "0b315c92-18d1-4f98-b4a7-8203b21b11f2"
      },
      "execution_count": 65,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
            "RangeIndex: 2255248 entries, 0 to 2255247\n",
            "Data columns (total 36 columns):\n",
            " #   Column        Dtype   \n",
            "---  ------        -----   \n",
            " 0   ogc_fid       int64   \n",
            " 1   WKT_GEOMETRY  object  \n",
            " 2   osm_id        object  \n",
            " 3   name          object  \n",
            " 4   highway       object  \n",
            " 5   waterway      object  \n",
            " 6   aerialway     object  \n",
            " 7   barrier       object  \n",
            " 8   man_made      object  \n",
            " 9   z_order       float64 \n",
            " 10  other_tags    object  \n",
            " 11  table_name    object  \n",
            " 12  ref           object  \n",
            " 13  address       object  \n",
            " 14  is_in         object  \n",
            " 15  place         object  \n",
            " 16  type          object  \n",
            " 17  osm_way_id    object  \n",
            " 18  aeroway       object  \n",
            " 19  amenity       object  \n",
            " 20  admin_level   object  \n",
            " 21  boundary      object  \n",
            " 22  building      object  \n",
            " 23  craft         object  \n",
            " 24  geological    object  \n",
            " 25  historic      object  \n",
            " 26  land_area     object  \n",
            " 27  landuse       object  \n",
            " 28  leisure       object  \n",
            " 29  military      object  \n",
            " 30  natural       object  \n",
            " 31  office        object  \n",
            " 32  shop          object  \n",
            " 33  sport         object  \n",
            " 34  tourism       object  \n",
            " 35  geom          geometry\n",
            "dtypes: float64(1), geometry(1), int64(1), object(33)\n",
            "memory usage: 619.4+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "7TR66LK1LToS"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}