Duckdb Spatial

DuckDB Spatial Skill

H3 hexagonal indexing, PostGIS-compatible spatial queries, and geographic analysis with GF(3) coloring.

Trigger

• Spatial SQL queries, geographic data analysis
• H3 hexagonal grid operations
• Point-in-polygon, distance queries
• Geospatial joins, spatial indexing

GF(3) Trit: 0 (Ergodic/Coordinator)

Coordinates spatial data flow and transforms between coordinate systems.

Installation

INSTALL spatial;
LOAD spatial;

-- Also useful
INSTALL h3 FROM community;
LOAD h3;

Core Spatial Types

-- Point, LineString, Polygon, MultiPoint, etc.
SELECT ST_Point(-122.4194, 37.7749) as san_francisco;
SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') as square;

-- GeoJSON parsing
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-122.4,37.7]}');

Colored Spatial Table Schema

CREATE TABLE geo_features (
    feature_id VARCHAR PRIMARY KEY,
    name VARCHAR,
    geometry GEOMETRY,
    feature_type VARCHAR,
    -- GF(3) coloring
    seed BIGINT,
    gay_color VARCHAR,
    gf3_trit INTEGER,
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with color derivation
INSERT INTO geo_features VALUES (
    'sf-001',
    'San Francisco',
    ST_Point(-122.4194, 37.7749),
    'city',
    4815162342,  -- seed
    '#DC6B3B',   -- gay_color
    1,           -- trit (+1)
    CURRENT_TIMESTAMP
);

H3 Hexagonal Indexing

-- Convert lat/lon to H3 index at resolution 9
SELECT h3_latlng_to_cell(37.7749, -122.4194, 9) as h3_index;

-- Get cell boundary as polygon
SELECT h3_cell_to_boundary_wkt(h3_latlng_to_cell(37.7749, -122.4194, 9));

-- Get neighbors (k-ring)
SELECT h3_grid_disk(h3_latlng_to_cell(37.7749, -122.4194, 9), 1) as neighbors;

-- Color H3 cells
CREATE TABLE h3_colored AS
SELECT 
    h3_latlng_to_cell(lat, lon, 9) as h3_index,
    COUNT(*) as point_count,
    -- Color from H3 index
    h3_latlng_to_cell(lat, lon, 9) % 3 - 1 as gf3_trit
FROM points
GROUP BY 1;

Spatial Queries with Color

-- Find all features within 10km of a point
SELECT 
    f.name,
    f.gay_color,
    f.gf3_trit,
    ST_Distance_Spheroid(f.geometry, ST_Point(-122.4194, 37.7749)) / 1000 as dist_km
FROM geo_features f
WHERE ST_DWithin_Spheroid(
    f.geometry,
    ST_Point(-122.4194, 37.7749),
    10000  -- 10km in meters
)
ORDER BY dist_km;

-- Spatial join with GF(3) balance check
SELECT 
    a.name as region,
    COUNT(*) as point_count,
    SUM(b.gf3_trit) as trit_sum,
    SUM(b.gf3_trit) % 3 as gf3_balance
FROM regions a
JOIN points b ON ST_Contains(a.geometry, b.geometry)
GROUP BY a.name;

Coordinate Reference Systems

-- Transform between CRS
SELECT ST_Transform(
    ST_Point(-122.4194, 37.7749),
    'EPSG:4326',  -- WGS84
    'EPSG:3857'   -- Web Mercator
) as web_mercator;

-- Area calculation in proper units
SELECT ST_Area(
    ST_Transform(geometry, 'EPSG:4326', 'EPSG:32610')  -- UTM Zone 10N
) / 1e6 as area_km2
FROM regions;

GeoParquet Integration

-- Read GeoParquet files
SELECT * FROM read_parquet('cities.parquet');

-- Write with geometry
COPY (SELECT * FROM geo_features) TO 'features.parquet' (FORMAT PARQUET);

-- Create spatial index
CREATE INDEX geo_features_spatial_idx ON geo_features USING RTREE (geometry);

Example: Colored City Analysis

import duckdb
import hashlib

def seed_from_string(s: str) -> int:
    return int(hashlib.sha256(s.encode()).hexdigest()[:16], 16) & 0x7FFFFFFFFFFFFFFF

def analyze_cities_with_color(cities_geojson):
    conn = duckdb.connect()
    conn.execute("INSTALL spatial; LOAD spatial;")
    
    conn.execute("""
        CREATE TABLE cities AS
        SELECT 
            name,
            ST_GeomFromGeoJSON(geometry) as geom,
            population
        FROM read_json_auto(?)
    """, [cities_geojson])
    
    # Add colors
    conn.execute("""
        ALTER TABLE cities ADD COLUMN seed BIGINT;
        ALTER TABLE cities ADD COLUMN gay_color VARCHAR;
        ALTER TABLE cities ADD COLUMN gf3_trit INTEGER;
    """)
    
    # Update with deterministic colors
    cities = conn.execute("SELECT name FROM cities").fetchall()
    for (name,) in cities:
        seed = seed_from_string(name)
        hue = seed % 360
        trit = 1 if (hue < 60 or hue >= 300) else (0 if hue < 180 else -1)
        conn.execute("""
            UPDATE cities SET seed = ?, gf3_trit = ? WHERE name = ?
        """, [seed, trit, name])
    
    return conn

# Query with spatial + color
conn.execute("""
    SELECT 
        name,
        gf3_trit,
        ST_X(geom) as lon,
        ST_Y(geom) as lat,
        population
    FROM cities
    WHERE ST_DWithin_Spheroid(geom, ST_Point(-122, 37), 500000)
    ORDER BY population DESC
""")

Triads

duckdb-spatial (0) ⊗ geodesic-manifold (-1) ⊗ geohash-coloring (+1) = 0 ✓
duckdb-spatial (0) ⊗ osm-topology (-1) ⊗ map-projection (+1) = 0 ✓

References

• DuckDB Spatial Extension
• H3 Hexagonal Grid
• PostGIS Documentation