Sedona_OvertureMaps_Parquet_To_GeoParquet.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92984a1c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:48:46.674846Z",
     "iopub.status.busy": "2023-08-07T20:48:46.674481Z",
     "iopub.status.idle": "2023-08-07T20:49:23.601328Z",
     "shell.execute_reply": "2023-08-07T20:49:23.600385Z",
     "shell.execute_reply.started": "2023-08-07T20:48:46.674809Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1f9d114d681c4ca9b3d5c8f4e0e1b179",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Spark application\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n<tbody><tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>User</th><th>Current session?</th></tr><tr><td>7</td><td>application_1691435962515_0008</td><td>pyspark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-172-31-41-245.us-west-2.compute.internal:20888/proxy/application_1691435962515_0008/\" class=\"emr-proxy-link j-1H0UL1ES7GEHX application_1691435962515_0008\" emr-resource=\"j-1H0UL1ES7GEHX\n\" application-id=\"application_1691435962515_0008\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-172-31-36-126.us-west-2.compute.internal:8042/node/containerlogs/container_1691435962515_0008_01_000001/livy\">Link</a></td><td>None</td><td>✔</td></tr></tbody></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SparkSession available as 'spark'.\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "from pyspark.sql.functions import expr\n",
    "\n",
    "from sedona.spark import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e4392353",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:49:23.603615Z",
     "iopub.status.busy": "2023-08-07T20:49:23.603200Z",
     "iopub.status.idle": "2023-08-07T20:49:23.675536Z",
     "shell.execute_reply": "2023-08-07T20:49:23.674715Z",
     "shell.execute_reply.started": "2023-08-07T20:49:23.603571Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "14a729d9dd6a4a83a7800fc6738bafb3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "INPUT_PATH = \"s3a://overturemaps-us-west-2/release/2023-07-26-alpha.0/\"\n",
    "OUTPUT_PATH = \"s3://wherobots-examples/data/overturemaps-us-west-2/release/2023-07-26-alpha.0/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "08c71acb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:49:23.678229Z",
     "iopub.status.busy": "2023-08-07T20:49:23.677814Z",
     "iopub.status.idle": "2023-08-07T20:49:33.007807Z",
     "shell.execute_reply": "2023-08-07T20:49:33.006900Z",
     "shell.execute_reply.started": "2023-08-07T20:49:23.678186Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e895c1a41a1e4a14bb771018bf070736",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sedona = SedonaContext.create(spark)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da9b9b84",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Define a function to convert the geometry column from WKB to Sedona geometry type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ee8298ce",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:49:33.009732Z",
     "iopub.status.busy": "2023-08-07T20:49:33.009409Z",
     "iopub.status.idle": "2023-08-07T20:49:33.066754Z",
     "shell.execute_reply": "2023-08-07T20:49:33.065982Z",
     "shell.execute_reply.started": "2023-08-07T20:49:33.009704Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "15d47f4a247e4f289a16f3de856aa141",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def geomTypeConverter(df, df_name):\n",
    "    new_columns = []\n",
    "    # Find the geometry column and convert it to a Sedona geometry type column\n",
    "    for col_name in df.schema.names:\n",
    "        if col_name == \"geometry\":\n",
    "            new_columns.append(\"ST_GeomFromWKB(geometry) AS geometry\")\n",
    "        else:\n",
    "            new_columns.append(col_name)\n",
    "    cols =','.join(new_columns)\n",
    "    df.createOrReplaceTempView(df_name)\n",
    "    df = sedona.sql(\"SELECT \" + cols + \" FROM \"+df_name)\n",
    "    df.createOrReplaceTempView(df_name)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b98f6d5c-869c-4e04-b312-54937703aa91",
   "metadata": {},
   "source": [
    "## Define a function to repartition the data by GeoHash value\n",
    "\n",
    "This will greatly accelerate the spatial queries on GeoParquet files. \n",
    "\n",
    "The chosen GeoHash length is 2.\n",
    "\n",
    "| GeoHash length | Grid Area width x height |\n",
    "|:--------------:|:------------------------:|\n",
    "|        1       | ≤ 5,000km X 5,000 Km     |\n",
    "|        2       | ≤ 1,250km X 625km        |\n",
    "|        3       | ≤ 156km X 156km          |\n",
    "|        4       | ≤ 39.1km X 19.5km        |\n",
    "|        5       | ≤ 4.89km X 4.89km        |\n",
    "|        6       | ≤ 1.22km X 0.61km        |\n",
    "|        7       | ≤ 153m X 153m            |\n",
    "|        8       | ≤ 38.2m X 19.1m          |\n",
    "|        9       | ≤ 4.77m X 4.77m          |\n",
    "|       10       | ≤ 1.19m X 0.596m         |\n",
    "|       11       | ≤ 149mm X 149mm          |\n",
    "|        12      | ≤ 37.2mm X 18.6mm        |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9f76c1d7-ece8-4513-bd08-a0c7d0ef5ffc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:49:33.068143Z",
     "iopub.status.busy": "2023-08-07T20:49:33.067861Z",
     "iopub.status.idle": "2023-08-07T20:49:33.127859Z",
     "shell.execute_reply": "2023-08-07T20:49:33.126912Z",
     "shell.execute_reply.started": "2023-08-07T20:49:33.068116Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7f364d2095714fd08d762a8c9e2eddcc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def reorgByGeoHash(df):\n",
    "    return df.withColumn(\"geohash\", expr(\"ST_GeoHash(geometry, 2)\")).repartition(\"geohash\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d2cd7e5",
   "metadata": {},
   "source": [
    "# Place Dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0e05b4f3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:49:33.129309Z",
     "iopub.status.busy": "2023-08-07T20:49:33.129037Z",
     "iopub.status.idle": "2023-08-07T20:54:41.807469Z",
     "shell.execute_reply": "2023-08-07T20:54:41.806622Z",
     "shell.execute_reply.started": "2023-08-07T20:49:33.129283Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e97ac733630c426e96a90532626ffbb6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- names: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |-- key: string\n",
      " |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- categories: struct (nullable = true)\n",
      " |    |-- main: string (nullable = true)\n",
      " |    |-- alternate: array (nullable = true)\n",
      " |    |    |-- element: string (containsNull = true)\n",
      " |-- confidence: double (nullable = true)\n",
      " |-- websites: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- socials: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- emails: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- phones: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- brand: struct (nullable = true)\n",
      " |    |-- names: map (nullable = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |    |-- key: string\n",
      " |    |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |    |-- wikidata: string (nullable = true)\n",
      " |-- addresses: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_place\"\n",
    "tbl_name_s3 = \"theme=places/type=place\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d43b7ba3-8dec-400f-be51-72b5746df45b",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Building Dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "444c9479-3e6e-4585-997b-ce55d1a2a26e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T20:54:41.809450Z",
     "iopub.status.busy": "2023-08-07T20:54:41.808882Z",
     "iopub.status.idle": "2023-08-07T21:12:15.371575Z",
     "shell.execute_reply": "2023-08-07T21:12:15.370586Z",
     "shell.execute_reply.started": "2023-08-07T20:54:41.809405Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d010ea13825a4ab0b0dd610cb5032e7b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- names: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |-- key: string\n",
      " |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- level: integer (nullable = true)\n",
      " |-- height: double (nullable = true)\n",
      " |-- numfloors: integer (nullable = true)\n",
      " |-- class: string (nullable = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_building\"\n",
    "tbl_name_s3 = \"theme=buildings/type=building\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0809afb",
   "metadata": {},
   "source": [
    "# Admins Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "930b6c81",
   "metadata": {},
   "source": [
    "## Administrative Boundary Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15fda6e6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T21:12:15.374025Z",
     "iopub.status.busy": "2023-08-07T21:12:15.373736Z",
     "iopub.status.idle": "2023-08-07T21:12:26.708530Z",
     "shell.execute_reply": "2023-08-07T21:12:26.707464Z",
     "shell.execute_reply.started": "2023-08-07T21:12:15.373992Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6eb58cbd860047c2a35a4f98afd31510",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- names: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |-- key: string\n",
      " |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- adminlevel: integer (nullable = true)\n",
      " |-- maritime: string (nullable = true)\n",
      " |-- subtype: string (nullable = true)\n",
      " |-- localitytype: string (nullable = true)\n",
      " |-- context: string (nullable = true)\n",
      " |-- isocountrycodealpha2: string (nullable = true)\n",
      " |-- isosubcountrycode: string (nullable = true)\n",
      " |-- defaultlanugage: string (nullable = true)\n",
      " |-- drivingside: string (nullable = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_admin\"\n",
    "tbl_name_s3 = \"theme=admins/type=administrativeBoundary\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0acad0d7",
   "metadata": {},
   "source": [
    "## Locality Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8991bef5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T21:12:26.710300Z",
     "iopub.status.busy": "2023-08-07T21:12:26.709945Z",
     "iopub.status.idle": "2023-08-07T21:12:48.086075Z",
     "shell.execute_reply": "2023-08-07T21:12:48.085067Z",
     "shell.execute_reply.started": "2023-08-07T21:12:26.710269Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b7ed945b94a14f5f84831e5e21644515",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- names: map (nullable = true)\n",
      " |    |-- key: string\n",
      " |    |-- value: array (valueContainsNull = true)\n",
      " |    |    |-- element: map (containsNull = true)\n",
      " |    |    |    |-- key: string\n",
      " |    |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- adminlevel: integer (nullable = true)\n",
      " |-- maritime: string (nullable = true)\n",
      " |-- subtype: string (nullable = true)\n",
      " |-- localitytype: string (nullable = true)\n",
      " |-- context: string (nullable = true)\n",
      " |-- isocountrycodealpha2: string (nullable = true)\n",
      " |-- isosubcountrycode: string (nullable = true)\n",
      " |-- defaultlanugage: string (nullable = true)\n",
      " |-- drivingside: string (nullable = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_locality\"\n",
    "tbl_name_s3 = \"theme=admins/type=locality\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51273966",
   "metadata": {},
   "source": [
    "# Transportation Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "986e030c",
   "metadata": {},
   "source": [
    "## Connector Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "743113f2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T21:12:48.087880Z",
     "iopub.status.busy": "2023-08-07T21:12:48.087469Z",
     "iopub.status.idle": "2023-08-07T21:16:50.373636Z",
     "shell.execute_reply": "2023-08-07T21:16:50.372605Z",
     "shell.execute_reply.started": "2023-08-07T21:12:48.087836Z"
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5240cd724ac74b31a0c2f274baa2e9e4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: timestamp (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- level: integer (nullable = true)\n",
      " |-- subtype: string (nullable = true)\n",
      " |-- connectors: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- road: string (nullable = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_connector\"\n",
    "tbl_name_s3 = \"theme=transportation/type=connector\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18956ed8",
   "metadata": {},
   "source": [
    "## Segment Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "70ecb2a2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-08-07T21:16:50.375104Z",
     "iopub.status.busy": "2023-08-07T21:16:50.374793Z",
     "iopub.status.idle": "2023-08-07T21:21:57.032662Z",
     "shell.execute_reply": "2023-08-07T21:21:57.031677Z",
     "shell.execute_reply.started": "2023-08-07T21:16:50.375075Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6ff8523f3dd848f1b0eeec3bbbab2899",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- updatetime: timestamp (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- level: integer (nullable = true)\n",
      " |-- subtype: string (nullable = true)\n",
      " |-- connectors: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- road: string (nullable = true)\n",
      " |-- sources: array (nullable = true)\n",
      " |    |-- element: map (containsNull = true)\n",
      " |    |    |-- key: string\n",
      " |    |    |-- value: string (valueContainsNull = true)\n",
      " |-- bbox: struct (nullable = true)\n",
      " |    |-- minx: double (nullable = true)\n",
      " |    |-- maxx: double (nullable = true)\n",
      " |    |-- miny: double (nullable = true)\n",
      " |    |-- maxy: double (nullable = true)\n",
      " |-- geometry: geometry (nullable = true)\n",
      " |-- geohash: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "tbl_name = \"df_segment\"\n",
    "tbl_name_s3 = \"theme=transportation/type=segment\"\n",
    "df = sedona.read.format(\"parquet\").load(INPUT_PATH + tbl_name_s3)\n",
    "df = geomTypeConverter(df, tbl_name)\n",
    "df = reorgByGeoHash(df)\n",
    "df.printSchema()\n",
    "df.write.partitionBy(\"geohash\").mode(\"overwrite\").format(\"geoparquet\").save(OUTPUT_PATH + tbl_name_s3)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "python",
   "name": "pysparkkernel"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "pyspark",
   "pygments_lexer": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}