Sedona_OvertureMaps_Parquet.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92984a1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sedona.spark import *\n",
    "import os\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e4392353",
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_LINK = \"s3a://overturemaps-us-west-2/release/2023-07-26-alpha.0/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "08c71acb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning: Ignoring non-Spark config property: fs.s3a.aws.credentials.provider\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "23/08/05 22:41:49 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "config = SedonaContext.builder()\\\n",
    "    .config(\"fs.s3a.aws.credentials.provider\", \"org.apache.hadoop.fs.s3a.AnonymousAWSCredentialsProvider\")\\\n",
    "    .config(\"spark.hadoop.fs.s3a.aws.credentials.provider\", \"org.apache.hadoop.fs.s3a.AnonymousAWSCredentialsProvider\")\\\n",
    "    .getOrCreate()\n",
    "\n",
    "sedona = SedonaContext.create(config)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da9b9b84",
   "metadata": {},
   "source": [
    "## Converts geometry column from binary to geometry type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ee8298ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "def geomTypeConverter(df, df_name):\n",
    "    cols =','.join(df.schema.names[:-1])\n",
    "    df.createOrReplaceTempView(df_name)\n",
    "    df = sedona.sql(\"SELECT \"+cols+\", ST_GeomFromWKB(geometry) AS geometry FROM \"+df_name)\n",
    "    df.createOrReplaceTempView(df_name)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31e81f8b",
   "metadata": {},
   "source": [
    "# Building Dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3e687e9f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "23/08/05 22:42:00 WARN MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-s3a-file-system.properties,hadoop-metrics2.properties\n",
      "[Stage 3:>                                                          (0 + 1) / 1]\r"
     ]
    },
    {
     "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",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_building = sedona.read.format(\"parquet\").load(DATA_LINK+\"theme=buildings/type=building\")\n",
    "df_building = geomTypeConverter(df_building, \"df_building\")\n",
    "df_building.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fc540993",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 4:======================================================>(887 + 1) / 888]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+\n",
      "|summary|           height|\n",
      "+-------+-----------------+\n",
      "|  count|         27871758|\n",
      "|   mean|22.26968765265663|\n",
      "| stddev|82080.48443441925|\n",
      "|    min|              0.0|\n",
      "|    max|     4.33333333E8|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_building.describe([\"level\"]).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d2cd7e5",
   "metadata": {},
   "source": [
    "# Place Dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e05b4f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_place = sedona.read.format(\"parquet\").load(DATA_LINK+\"theme=places/type=place\")\n",
    "df_place = geomTypeConverter(df_place, \"df_place\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df5a8911",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_place.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c387af88",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_place.selectExpr(\"phones\").filter(~(size(col(\"addresses\")) == 1)).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23ea74b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_place.selectExpr(\"phones\").filter(size(col(\"phones\")) == 1).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0809afb",
   "metadata": {},
   "source": [
    "# Admins Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "930b6c81",
   "metadata": {},
   "source": [
    "## Administrative Boundary Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15fda6e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_admin = sedona.read.format(\"parquet\").load(DATA_LINK+\"theme=admins/type=administrativeBoundary\")\n",
    "df_admin = geomTypeConverter(df_admin, \"df_admin\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c911825e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_admin_boundary.filter(col(\"isocountrycodealpha2\").isNotNull()).selectExpr(\"isocountrycodealpha2\").show(200, False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0acad0d7",
   "metadata": {},
   "source": [
    "## Locality Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8991bef5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_locality = sedona.read.format(\"parquet\").load(DATA_LINK + \"theme=admins/type=locality\").limit(10000)\n",
    "df_locality = geomTypeConverter(df_locality, \"df_locality\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "328ffd56",
   "metadata": {},
   "source": [
    "# Transportation Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b914f57",
   "metadata": {},
   "source": [
    "## Connector Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "743113f2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_connector = sedona.read.format(\"parquet\").load(DATA_LINK+\"theme=transportation/type=connector\")\n",
    "df_connector = geomTypeConverter(df_connector, \"df_connector\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1e7ab2d",
   "metadata": {},
   "source": [
    "## Segment Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a5cb1b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_segment = sedona.read.format(\"parquet\").load(DATA_LINK+\"theme=transportation/type=segment\")\n",
    "df_segment = geomTypeConverter(df_segment, \"df_segment\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c83b85c2",
   "metadata": {},
   "source": [
    "# San Francisco boundary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c62818e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bay_area_boundary_RDD = ShapefileReader.readToGeometryRDD(sc, \"data/bay-area-counties\")\n",
    "san_francisco = Adapter.toDf(bay_area_boundary_RDD, sedona)\n",
    "san_francisco.createOrReplaceTempView(\"san_francisco\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df3aeccb",
   "metadata": {},
   "source": [
    "# Filtering out non-geometry columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b7e113a",
   "metadata": {},
   "source": [
    "## Building dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a4aac72",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_building = df_building.filter(~(size(col(\"names\")) <= 0)).filter(col(\"height\") >= 0)\\\n",
    "                                  .filter(col(\"height\") <= 100 )\\\n",
    "                                  .filter(~(size(col(\"sources\")) <= 0)).filter(col(\"numfloors\") == 1)\n",
    "df_filtered_building.createOrReplaceTempView(\"df_filtered_building\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9fc8f49",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc948de7",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "print(df_filtered_building.count())\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "799a8f9c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e2d8729c",
   "metadata": {},
   "source": [
    "## Place DataSet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbbca0ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_place = df_place.filter((~(size(col(\"addresses\")) == 1)) & (col(\"confidence\") == 0.7087309956550598))\n",
    "df_filtered_place.createOrReplaceTempView(\"df_filtered_place\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54e63813",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "print(\" count: \" + str(df_filtered_place.count()))\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "295654d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_place.selectExpr(\"confidence\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11f55786",
   "metadata": {},
   "source": [
    "## Admins Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bbfe2e0",
   "metadata": {},
   "source": [
    "### Administrative Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88e16025",
   "metadata": {},
   "source": [
    "It doesn't need filtering as it is a small dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "751edf25",
   "metadata": {},
   "source": [
    "### Locality Dataset "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1d27949",
   "metadata": {},
   "source": [
    "It doesn't need filtering as it is a small dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c96344a",
   "metadata": {},
   "source": [
    "## Transportation Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01607337",
   "metadata": {},
   "source": [
    "### Connector "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de071c3c",
   "metadata": {},
   "source": [
    "Nothing to filter on. As most of the non-geometry columns are null."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b9112b7",
   "metadata": {},
   "source": [
    "### Segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65b7aaac",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_segment = sedona.sql(\"select * from df_segment where level < 6 and level > 2\")\n",
    "df_filtered_segment.createOrReplaceTempView(\"df_filtered_segment\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f772c82b",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "print(\" count: \" + str(df_segment.count()))\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6d1e923",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_segment.filter((col(\"level\") < 6) & (col(\"level\") > 2)).selectExpr(\"level\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52768938",
   "metadata": {},
   "source": [
    "# Intersecting data points to San Francisco Bay Area"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97d65ebb",
   "metadata": {},
   "source": [
    "## Building Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20e51aeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_building = sedona.sql(\"SELECT * FROM df_filtered_building b, san_francisco s WHERE ST_Intersects(b.geometry, s.geometry)\")\n",
    "building_geom = result_building.selectExpr(\"b.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76aa9692",
   "metadata": {},
   "source": [
    "## Place Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81a4ed61",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_place = sedona.sql(\"SELECT * FROM df_place p, san_francisco s WHERE ST_Intersects(p.geometry, s.geometry)\")\n",
    "place_geom = result_place.selectExpr(\"p.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bf10b6a",
   "metadata": {},
   "source": [
    "## Admins Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38949c90",
   "metadata": {},
   "source": [
    "### Administrative Boundary Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48958ccb",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_admin = sedona.sql(\"SELECT * FROM df_admin a, san_francisco s WHERE ST_Intersects(a.geometry, s.geometry)\")\n",
    "admin_geom = result_admin.select(\"a.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "414d48f3",
   "metadata": {},
   "source": [
    "### Locality Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9062b3ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_locality = sedona.sql(\"SELECT * FROM df_locality p, san_francisco s WHERE ST_Intersects(p.geometry, s.geometry)\")\n",
    "locality_geom = result_locality.selectExpr(\"p.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfc3831a",
   "metadata": {},
   "source": [
    "## Transportation Datasets "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e502dc4a",
   "metadata": {},
   "source": [
    "### Connector Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a797507",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_connector = sedona.sql(\"SELECT * FROM df_connector c, san_francisco s WHERE ST_Intersects(c.geometry, s.geometry)\")\n",
    "connector_geom = result_connector.selectExpr(\"c.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e9e2008",
   "metadata": {},
   "source": [
    "### Segment Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e39d70d",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_segment = sedona.sql(\"SELECT * FROM df_filtered_segment f, san_francisco s WHERE ST_Intersects(f.geometry, s.geometry)\")\n",
    "segment_geom = result_segment.selectExpr(\"f.geometry\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c0e17b5",
   "metadata": {},
   "source": [
    "# SedonaKepler to Visualize"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44ed48e5",
   "metadata": {},
   "source": [
    "## Building Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d18a022",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_building = SedonaKepler.create_map(building_geom, 'Building')\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_building"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0261c83b",
   "metadata": {},
   "source": [
    "## Place Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c25cdfba",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_place = SedonaKepler.create_map(place_geom, \"Place\")\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_place"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1ad4478",
   "metadata": {},
   "source": [
    "## Admins Datasets "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2537c823",
   "metadata": {},
   "source": [
    "### Admisnistrative Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86ea8eef",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_admin = SedonaKepler.create_map(admin_geom, \"Admin\")\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_admin"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0084a0eb",
   "metadata": {},
   "source": [
    "### Locality Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b57ea3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_locality = SedonaKepler.create_map(locality_geom, 'Locality')\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_locality"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37b9aaa2",
   "metadata": {},
   "source": [
    "## Transportation Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8da38a4",
   "metadata": {},
   "source": [
    "### Connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "571f8719",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_connector = SedonaKepler.create_map(connector_geom, \"Connector\")\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_connector"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35f086bf",
   "metadata": {},
   "source": [
    "### Segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9735db63",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "map_segment = SedonaKepler.create_map(segment_geom, \"Segment\")\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_segment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae54aeba",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "admin_geom_whole = df_locality.selectExpr(\"geometry\")\n",
    "map_admin_whole = SedonaKepler.create_map(admin_geom_whole, \"Admin whole\")\n",
    "print(\"--- %s milliseconds ---\" % ((time.time() - start_time)* 1000))\n",
    "map_admin_whole\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfd0e837",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}