geo-demo-nyc-ddb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6db29215-55a5-4aef-bfa6-6939cd196224",
   "metadata": {},
   "outputs": [],
   "source": [
    "#lonboard warns you about changing the projection upon plotting, I'm filtering this warning\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26854153-1fd9-46e0-a6d6-c45b8a9f0783",
   "metadata": {},
   "source": [
    "## Playing with a geospatial database \n",
    "\n",
    "**Shoutout**: Dr. Wu - Talk at Scipy Friday 10.45am - Room 316\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d8d8eaa-7e50-43a1-b1e6-089bdbd11c55",
   "metadata": {},
   "source": [
    "## NYC data database\n",
    "\n",
    "You can download the data from: https://github.com/opengeos/data/tree/main/duckdb/nyc_data.db.zip\n",
    "the datasets in the database are in NAD83 / UTM zone 18N projection, EPSG:26918. (https://epsg.io/26918)\n",
    "\n",
    "To follow this notebook, unzip the file, and put the `nyc_data.db` file in at the same leve of this notebook in your repo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2da3fc10-9676-4511-84e7-b159c01bfe8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "\n",
    "ibis.options.interactive = True\n",
    "\n",
    "con = ibis.duckdb.connect(\"nyc_data.db\")\n",
    "con.list_tables()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d9d31ec-edd2-4c6f-bbbf-34e663ed7a5e",
   "metadata": {},
   "source": [
    "### Problem statement.\n",
    "Pick a subway station, check in which neighborhood and street is, and then check if there are any homicides that happened within a radius of that station. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02c9cbbd-5489-452a-bf41-0722d49abf4a",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "### this is to avoid a progress bar showing up\n",
    "ibis.get_backend().raw_sql(\"PRAGMA disable_progress_bar;\");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2700e7f-5df0-45a1-8c56-168a9f26fa29",
   "metadata": {},
   "source": [
    "## Subway stations "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b28e7fc7-404c-4048-96d0-7ac993d30a92",
   "metadata": {},
   "outputs": [],
   "source": [
    "subway_stations = con.table(\"nyc_subway_stations\")\n",
    "subway_stations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "809dd46a-5112-47ed-bc6b-63bae8a6b882",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station = subway_stations.filter(subway_stations.NAME == \"Broad St\")\n",
    "broad_station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbc616a8-1018-4b60-bf4e-88d83c964894",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station.geom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "115fb778-e485-4cc5-8927-8d4fa73c1e0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station_coords = broad_station.geom.as_scalar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fff88e0d-bb78-4192-bf34-804cc2fe45ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station_coords"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a01ef33a-62b6-4264-aac2-a10c35cfade4",
   "metadata": {},
   "source": [
    "## Boroughs and streets tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "632cbf4a-2d9b-4856-a32e-326e35ed3686",
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs = con.table(\"nyc_neighborhoods\")\n",
    "boroughs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7987d15b-4c00-4062-a3ec-8ea7825477a0",
   "metadata": {},
   "source": [
    "### In which Borough is Broad station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca58f761-318d-495f-b78e-6eaf0438aefb",
   "metadata": {},
   "outputs": [],
   "source": [
    "boroughs.filter(boroughs.geom.intersects(broad_station_coords))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88769cab-20db-4a2d-be55-55ddf22c429a",
   "metadata": {},
   "source": [
    "### Streets within 10m of broad station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cba9805-e33c-4e9e-83ef-2223a7d28611",
   "metadata": {},
   "outputs": [],
   "source": [
    "streets = con.table(\"nyc_streets\")\n",
    "streets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09ee0d9a-9c03-4fe0-ad1f-30ee9c5d5869",
   "metadata": {},
   "outputs": [],
   "source": [
    "sts_near_broad = streets.filter(_.geom.d_within(broad_station_coords, 10))\n",
    "sts_near_broad"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d063112f-9275-4294-9ac3-f3e35441caaf",
   "metadata": {},
   "source": [
    "### homicides table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ea1b344-df24-41aa-85e4-2e1e867b2722",
   "metadata": {},
   "outputs": [],
   "source": [
    "homicides = con.table(\"nyc_homicides\")\n",
    "homicides"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9c7cd61-a88b-4a95-9273-b100e125ebde",
   "metadata": {},
   "source": [
    "### Perimeter of 200m around broad station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2130e1eb-0b56-4d21-ae66-d32e019caf0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station.geom.buffer(200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02521106-7dd9-46ce-b857-1a8df5317c6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station.geom.buffer(200).area()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57e458a7-5d79-4b18-b887-a3e5bd3d3d0e",
   "metadata": {},
   "source": [
    "### Homicides within 200m of broad station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c9d1c7b-9e7d-47a2-ba45-e0371f0783b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "h_near_broad = homicides.filter(_.geom.intersects(broad_station.geom.buffer(200).as_scalar()))\n",
    "h_near_broad"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f3546cf-cd4b-4538-9f58-78e337ac1c59",
   "metadata": {},
   "source": [
    "### Where did it happen?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f89eb51-6c66-44a5-b11a-3cb16d7013ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "h_street = streets.filter(_.geom.d_within(h_near_broad.geom.as_scalar(), 2))\n",
    "h_street"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5165c78a-1b85-4ad6-8e47-cc7c7c7d13c8",
   "metadata": {},
   "source": [
    "### Let's plot this\n",
    "\n",
    "**Shoutout**: Kyle Barron - Talk at Scipy Friday 11.25am - Room 316"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "611ea182-2531-41f5-91eb-bf3d6fba21a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from lonboard import Map, ScatterplotLayer, PathLayer, PolygonLayer"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f7aaa09-150b-45d9-ac7f-52d053c5d9db",
   "metadata": {},
   "source": [
    "**Converting to Geopandas to plot**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ed32b6a-4e8d-4d13-98eb-dd19305ceaec",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station_gdf = broad_station.to_pandas()\n",
    "broad_station_gdf.crs = \"EPSG:26918\"\n",
    "\n",
    "broad_station_zone = broad_station.mutate(geom=broad_station.geom.buffer(200))\n",
    "broad_station_zone = broad_station_zone.to_pandas()\n",
    "broad_station_zone.crs = \"EPSG:26918\"\n",
    "\n",
    "streets_gdf = streets.to_pandas()\n",
    "streets_gdf.crs = \"EPSG:26918\"\n",
    "\n",
    "h_near_broad_gdf = h_near_broad.to_pandas()\n",
    "h_near_broad_gdf.crs = \"EPSG:26918\"\n",
    "\n",
    "h_street_gdf = h_street.to_pandas()\n",
    "h_street_gdf.crs = \"EPSG:26918\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b06dc8d3-b2bc-45a1-9162-ad4a325a891c",
   "metadata": {},
   "source": [
    "**Plotting layers with lonboard**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b98d0163-7ca6-415c-b946-e159a5b3c3a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "broad_station_layer = ScatterplotLayer.from_geopandas(\n",
    "    broad_station_gdf, get_fill_color=\"orange\", get_radius=5\n",
    ")\n",
    "\n",
    "broad_station_zone_layer = PolygonLayer.from_geopandas(\n",
    "    broad_station_zone, get_fill_color=\"orange\", opacity=0.1\n",
    ")\n",
    "\n",
    "h_near_broad_layer = ScatterplotLayer.from_geopandas(\n",
    "    h_near_broad_gdf, get_fill_color=\"red\", get_radius=5\n",
    ")\n",
    "\n",
    "h_street_layer = PathLayer.from_geopandas(\n",
    "    h_street_gdf, get_color=\"blue\", opacity=0.5, get_width=2\n",
    ")\n",
    "\n",
    "streets_layer = PathLayer.from_geopandas(streets_gdf, get_color=\"grey\", opacity=0.3)\n",
    "\n",
    "\n",
    "mh = Map(\n",
    "    [\n",
    "        broad_station_layer,\n",
    "        broad_station_zone_layer,\n",
    "        h_near_broad_layer,\n",
    "        h_street_layer,\n",
    "        streets_layer,\n",
    "    ],\n",
    "    view_state={\"longitude\": -74.01066, \"latitude\": 40.7069, \"zoom\": 16}\n",
    ")\n",
    "mh"
   ]
  }
 ],
 "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}