Spatial Queries and Relationships

# %pip install duckdb leafmap

import duckdb
import leafmap

print(f"DuckDB version: {duckdb.__version__}")

url = "https://data.gishub.org/duckdb/nyc_data.db.zip"
leafmap.download_file(url, unzip=True)

con = duckdb.connect("nyc_data.db")

con.install_extension("spatial")
con.load_extension("spatial")

con.sql("SHOW TABLES;")

con.sql("SELECT * from nyc_subway_stations LIMIT 5")

con.sql(
    """
SELECT NAME, geom, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';
"""
)

con.sql(
    """
SELECT name
FROM nyc_subway_stations
WHERE ST_Equals(geom, ST_GeomFromText('POINT (583571.9059213118 4506714.341192182)'));
"""
)

con.sql(
    """
SELECT name, ST_AsText(geom)
FROM nyc_subway_stations
WHERE name = 'Broad St';
"""
)

con.sql(
    """
SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)'));
"""
)

con.sql(
    """
SELECT ST_Distance(
  ST_GeomFromText('POINT(0 5)'),
  ST_GeomFromText('LINESTRING(-2 2, 2 2)')) as distance;
"""
)

con.sql(
    """
SELECT
  a.name AS from_station,
  b.name AS to_station,
  ROUND(ST_Distance(a.geom, b.geom)) AS distance_meters
FROM nyc_subway_stations a, nyc_subway_stations b
WHERE a.name = 'Broad St'
  AND b.name IN ('Wall St', 'Bowling Green', 'Fulton St')
ORDER BY distance_meters;
"""
)

con.sql("SELECT name FROM nyc_streets WHERE name IS NOT NULL LIMIT 5")

con.sql(
    """
SELECT name
FROM nyc_streets
WHERE ST_DWithin(
        geom,
        ST_GeomFromText('POINT(583571 4506714)'),
        10
      )
AND name IS NOT NULL
ORDER BY name;
"""
)

con.sql(
    """
SELECT name,
       ST_Distance(geom, ST_GeomFromText('POINT(583571 4506714)')) AS distance_m
FROM nyc_subway_stations
ORDER BY distance_m
LIMIT 3;
"""
)