repro_geo_literal.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78d71e4b-09d4-40f2-928b-ca51402ae4b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "428050b0-a36f-4794-bd6f-8c1ab1b57472",
   "metadata": {},
   "outputs": [],
   "source": [
    "#get data\n",
    "!wget \"https://open.gishub.org/data/duckdb/nyc_data.db.zip\" && tar -xvf \"nyc_data.db.zip\" && rm \"nyc_data.db.zip\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a8b93d2-6889-4588-ac76-f9889dad40cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.duckdb.connect(\"nyc_data.db\")\n",
    "#con.list_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdbc072f-5cdb-4762-8bc5-46ad4e62138f",
   "metadata": {},
   "outputs": [],
   "source": [
    "t1 = con.table('nyc_subway_stations')\n",
    "t1 = t1.rename(\"snake_case\")\n",
    "\n",
    "#this results in just 1 row dataframe and the geom columns is a point\n",
    "broad_st =  t1.filter(t1.name == 'Broad St')\n",
    "\n",
    "t2 = con.table('nyc_streets')\n",
    "\n",
    "#trying to pass a literal to replicate a query like this on DuckDB\n",
    "# ddb_con.sql(\"\"\"\n",
    "# SELECT name\n",
    "# FROM nyc_streets\n",
    "# WHERE ST_DWithin(\n",
    "#         geom,\n",
    "#         ST_GeomFromText('POINT(583571 4506714)'),\n",
    "#         10\n",
    "#       );\n",
    "# \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c63a05a-4ecd-4442-b608-f4a8fb12aa0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a point\n",
    "from shapely import Point\n",
    "\n",
    "#grab x, y from the point in broad_st.geom\n",
    "px = broad_st.geom.x().to_pandas()[0]\n",
    "py = broad_st.geom.y().to_pandas()[0]\n",
    "\n",
    "p = Point(px, py)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58a036c5-e9db-46a8-afaa-41a08e5c718d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# FAIL: try to create literal directly from shapely point\n",
    "pliteral = ibis.literal(p, type=\"geometry\")\n",
    "con.execute(pliteral)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc0bb4fb-42a7-4ba6-bb04-d7a366f6abf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#query I'm trying to to run \n",
    "t2.geom.d_within(pliteral, 10).to_pandas()"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}