Interactive Data Visualization - Spatial Data Management with DuckDB

Introduction¶

Learning Objectives¶

Sample Datasets¶

Installation and Setup¶

# %pip install -U leafmap

import duckdb
import leafmap.maplibregl as leafmap

Downloading Sample Data¶

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

Connecting to DuckDB and Loading Extensions¶

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

Installing and Loading the Spatial Extension¶

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

Exploring the Database Contents¶

con.sql("SHOW TABLES;")

Visualizing Point Data¶

Querying and Converting Point Data¶

subway_stations_df = con.sql(
    "SELECT * EXCLUDE geom, ST_AsText(geom) as geometry FROM nyc_subway_stations"
).df()
subway_stations_df.head()

Transforming to GeoDataFrame with CRS Conversion¶

subway_stations_gdf = leafmap.df_to_gdf(
    subway_stations_df, src_crs="EPSG:26918", dst_crs="EPSG:4326"
)
subway_stations_gdf.head()

Creating a Basic Point Map¶

m = leafmap.Map(style="liberty")
m.add_vector(subway_stations_gdf, name="Subway Stations")
m

Customizing Point Symbology¶

m = leafmap.Map(style="liberty")
paint = {
    "circle-radius": 5,
    "circle-color": "#ff0000",
    "circle-opacity": 0.5,
    "circle-stroke-color": "#ffffff",
    "circle-stroke-width": 1,
}
m.add_vector(
    subway_stations_gdf, layer_type="circle", paint=paint, name="Subway Stations"
)
m

Visualizing Line Data¶

Querying and Converting Line Data¶

streets_df = con.sql(
    "SELECT * EXCLUDE geom, ST_AsText(geom) as geometry FROM nyc_streets"
).df()
streets_df.head()

Transforming Streets to GeoDataFrame¶

streets_gdf = leafmap.df_to_gdf(streets_df, src_crs="EPSG:26918", dst_crs="EPSG:4326")
streets_gdf.head()

Creating a Basic Line Map¶

m = leafmap.Map(style="liberty")
m.add_vector(streets_gdf, name="Streets")
m

Customizing Line Symbology¶

m = leafmap.Map(style="liberty")
paint = {"line-color": "#ff0000", "line-width": 2, "line-opacity": 0.5}
m.add_vector(streets_gdf, layer_type="line", paint=paint, name="Streets")
m

Visualizing Polygon Data¶

Querying and Converting Polygon Data¶

census_blocks_df = con.sql(
    "SELECT * EXCLUDE geom, ST_AsText(geom) as geometry FROM nyc_census_blocks"
).df()
census_blocks_df.head()

Transforming Census Blocks to GeoDataFrame¶

census_blocks_gdf = leafmap.df_to_gdf(
    census_blocks_df, src_crs="EPSG:26918", dst_crs="EPSG:4326"
)
census_blocks_gdf.head()

Creating a Basic Polygon Map¶

m = leafmap.Map(style="liberty")
m.add_vector(census_blocks_gdf, name="Census Blocks")
m

Customizing Polygon Symbology¶

m = leafmap.Map(style="liberty")
paint = {
    "fill-color": "#ff0000",
    "fill-opacity": 0.5,
    "fill-outline-color": "#ffffff",
}
m.add_vector(census_blocks_gdf, layer_type="fill", paint=paint, name="Census Blocks")
m

Creating Choropleth Maps¶

m = leafmap.Map(style="positron")
m.add_data(census_blocks_gdf, column="POPN_TOTAL")
m

Creating 3D Extrusion Maps¶

m = leafmap.Map(
    style="positron", pitch=60, bearing=73.5, center=(-73.923449, 40.694178), zoom=11
)
m.add_data(
    census_blocks_gdf,
    column="POPN_TOTAL",
    cmap="coolwarm",
    extrude=True,
    fit_bounds=False,
)
m

Introduction¶

Learning Objectives¶

Sample Datasets¶

Installation and Setup¶

Downloading Sample Data¶

Connecting to DuckDB and Loading Extensions¶

Installing and Loading the Spatial Extension¶

Exploring the Database Contents¶

Visualizing Point Data¶

Querying and Converting Point Data¶

Transforming to GeoDataFrame with CRS Conversion¶

Creating a Basic Point Map¶

Customizing Point Symbology¶

Visualizing Line Data¶

Querying and Converting Line Data¶

Transforming Streets to GeoDataFrame¶

Creating a Basic Line Map¶

Customizing Line Symbology¶

Visualizing Polygon Data¶

Querying and Converting Polygon Data¶

Transforming Census Blocks to GeoDataFrame¶

Creating a Basic Polygon Map¶

Customizing Polygon Symbology¶

Creating Choropleth Maps¶

Creating 3D Extrusion Maps¶

Key Takeaways¶

Exercises¶

Exercise 1: Basic Point Visualization¶

Exercise 2: Attribute-Based Filtering and Visualization¶

Exercise 3: Line Network Visualization¶

Exercise 4: Basic Polygon Visualization¶

Exercise 5: Choropleth Mapping¶

Exercise 6: Custom Choropleth Color Schemes¶

Exercise 7: 3D Extrusion Visualization¶

Exercise 8: Multi-Layer Visualization¶

Exercise 9: Spatial Query Visualization¶

Exercise 10: Custom Analysis and Visualization Pipeline¶