geoparquet_demo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ff4f98-8a1a-4ba5-9dda-b3ef737feeb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5796d0ca-df65-4ae1-9dd7-0f7c303f9a24",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "#### disable progress bar to show up\n",
    "ibis.get_backend().raw_sql(\"PRAGMA disable_progress_bar;\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "335d5b49-75dc-4182-aa20-2507c8a6f716",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibis import _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9642b74f-30a3-4a05-840c-58926f8176c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.options.interactive = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93e2f3d9-abad-425c-9b50-6bf6a21c2df3",
   "metadata": {},
   "source": [
    "# Data from source cooperative\n",
    "\n",
    "**Shoutout:** Matt Forrest\n",
    "\n",
    "Download the data from (it's a 2.3 GB parquet file): [https://beta.source.coop/vida/google-microsoft-open-buildings/geoparquet/by_country/country_iso=ARG/ARG.parquet](\"https://beta.source.coop/vida/google-microsoft-open-buildings/geoparquet/by_country/country_iso=ARG/ARG.parquet\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b96d7981-cc61-4ac3-a5f4-e7d07a6ab73c",
   "metadata": {},
   "outputs": [],
   "source": [
    "t = ibis.read_parquet(\"ARG.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e61346fd-6dbf-44e9-900b-55b4e49b7042",
   "metadata": {},
   "outputs": [],
   "source": [
    "t.schema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9232f8af-c410-423e-9818-cfcae2924513",
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of rows \n",
    "t.count() / 1e6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "504c41bd-35f0-4757-b550-e6f3cb615989",
   "metadata": {},
   "outputs": [],
   "source": [
    "t.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be4a3329-9910-442d-8eba-9d10660eae25",
   "metadata": {},
   "outputs": [],
   "source": [
    "t.geometry"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78f465b9-3259-4765-8630-d61e0bde6f0b",
   "metadata": {},
   "source": [
    "# Filter the building within a certain radius of Córdoba city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc7ad96f-5c6f-4629-b09f-3141d9bbae7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cordoba_lat = -31.4135\n",
    "cordoba_lon = -64.18105"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5b46ba3-35e8-41f0-86fa-52e0f3f97e54",
   "metadata": {},
   "outputs": [],
   "source": [
    "import shapely"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78f45b94-26e3-4ff7-9ed3-c72a3e7af282",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba = shapely.Point(cordoba_lon, cordoba_lat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91fc4123-d988-4f39-b6f3-5f65b2ca7b80",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fccc2030-1e32-44f8-9089-d71b6f07719e",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba_buildings = t.filter(t.geometry.d_within(cba, 0.1))\n",
    "cba_buildings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f657c5f-8c4f-4aa4-bdf6-e2cb837e4012",
   "metadata": {},
   "outputs": [],
   "source": [
    "#cache this to not do the filter everytime\n",
    "cba_buildings = cba_buildings.cache()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea59d146-827e-40a1-8939-93d3a6c7280e",
   "metadata": {},
   "source": [
    "# Let's plot "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "030b32eb-8599-418a-a6d6-940fc6384eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from lonboard import Map, ScatterplotLayer, PolygonLayer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "771a440e-a385-4ad8-a1b6-c723e7241bc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba_buildings.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45ba6170-7b59-4a47-bff9-7fe787c48158",
   "metadata": {},
   "source": [
    "## To geopandas to plot with lonboard \n",
    "\n",
    "### (hoping that during the sprints we can plot directly from an Ibis table)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "979940f2-984d-4501-803d-28abcd3e27f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to geopandas to plot (hoping that during the sprints we can plot directly from an Ibis table)\n",
    "cba_bd_gdf = cba_buildings.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac108b21-12f8-4115-a060-96ce9e3a128f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba_bd_gdf.crs = \"EPSG:4326\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d461fe17-c4c6-41d8-a372-d180b7cdda97",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba_ctr_gdf = ibis.literal(cba, \"geometry\").as_table().to_pandas()\n",
    "cba_ctr_gdf.crs = \"EPSG:4326\"\n",
    "type(cba_ctr_gdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31ffa7bd-ae4c-4534-a67f-579ced549e55",
   "metadata": {},
   "outputs": [],
   "source": [
    "cba_ctr = ScatterplotLayer.from_geopandas(\n",
    "    cba_ctr_gdf, get_fill_color=\"red\", get_radius=100\n",
    ")\n",
    "\n",
    "building_layer = PolygonLayer.from_geopandas(\n",
    "    cba_bd_gdf, get_fill_color=\"blue\", opacity=0.1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61d95d80-5e97-42ab-8a7d-2cd96cf365a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "m1 = Map(\n",
    "    [\n",
    "    cba_ctr,\n",
    "    building_layer, \n",
    "    ],\n",
    "    view_state={\"longitude\": -64.18105, \"latitude\": -31.4135, \"zoom\": 10}\n",
    ")\n",
    "m1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf164198-d45c-488c-9d9c-34aea85ce93c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#long, lat university of Cordoba =  -64.185999256 , -31.43583159"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55177041-62ba-48d4-a756-e662537dbac2",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc =  ibis.literal(shapely.Point(-64.185999256, -31.43583159), \"geometry\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33a0fd9a-8f81-4105-8696-b5049ed97e99",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_buf = unc.buffer(0.01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73bfa1f4-630d-4bf5-bdff-a39cee34a1d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_buf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc16709e-c378-4270-a5bd-b5aa566e70c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_buildings = cba_buildings.filter(_.geometry.within(unc_buf))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49bf65c2-f08c-4081-a361-8969bf1db66a",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_buildings.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b5537ac-2efa-4406-b9d7-b9673fef5c0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_gdf = unc_buildings.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1e48a40-88fb-42c2-9d96-68a03b646282",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_gdf.crs = \"EPSG:4326\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcf614a1-12a5-4412-831e-cff2d6154529",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_buf_gdf = unc_buf.as_table().to_pandas()\n",
    "unc_buf_gdf.crs = \"EPSG:4326\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2a65b05-6833-4631-8a72-5a2e85c03b52",
   "metadata": {},
   "outputs": [],
   "source": [
    "unc_layer = PolygonLayer.from_geopandas(\n",
    "    unc_gdf, get_fill_color=\"red\", opacity=0.1\n",
    ")\n",
    "\n",
    "unc_buf_layer = PolygonLayer.from_geopandas(\n",
    "    unc_buf_gdf, get_fill_color=\"blue\", opacity=0.005, \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bc9fe59-8eda-4617-948e-800dd83ad8e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "m2 = Map(\n",
    "    [\n",
    "    unc_buf_layer, \n",
    "    unc_layer,\n",
    "    ],\n",
    "    view_state={\"longitude\": -64.185999256, \"latitude\": -31.43583159, \"zoom\": 10}\n",
    ")\n",
    "m2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cbbd202-c5d2-4499-b822-e20a3814d481",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.get_backend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ed88be6-b509-4fe9-b044-7d933de5a075",
   "metadata": {},
   "outputs": [],
   "source": [
    "con.raw_sql(f\"COPY ({ibis.to_sql(unc_buildings)}) TO 'unc_buildings.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dabfbcc0-e7fd-46ce-8957-0eb47750739c",
   "metadata": {},
   "source": [
    "In VSCode install the Geo Data Viewer extension, then right click on `unc_buildings.geojson` and select view map to see it in csvcode. "
   ]
  }
 ],
 "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
}