Spatial SQL ATL · Atlanta
Every DuckDB spatial function, in plain English, typed into Dekart so the result lands on a map. Every other Thursday, two hours, Oct 2026 to Apr 2027.
Arrive. Name, and what you map. Newcomers first.
Recap: one person reruns last session's challenge from the saved map.
Functions. Each one: a volunteer reads the plain-English line, everyone types it in Dekart, we look at the map.
Atlanta challenge. Pairs. Save and share the map link.
Adopt next session's functions. Then drinks.
Rule for every query: return a geometry column named geometry, so something appears on the map. A number alone shows nothing.
Map line Some functions return a 2D, 3D or 4D point, or a box, rather than a GEOMETRY. Dekart and DuckGL draw only GEOMETRY, so the Map line casts the output. The cast keeps z and m values.
⚑ may not run in Dekart's browser DuckDB (file and GDAL readers). Test before the session. Challenge SQL and function examples run in DuckDB 1.4.3 and 1.5.6. Dekart's browser build is untested.
No downloads. Each query makes its own points, so the room can start in the first ten minutes. Run each one inside the session named.
-- 500 fake phone pings around downtown, one row each
WITH pings AS (
SELECT i AS ping_id,
ST_Point(-84.3900 + (hash(i) % 1000) / 1000.0 * 0.04,
33.7400 + (hash(i * 7) % 1000) / 1000.0 * 0.03) AS geom
FROM range(500) t(i)),
zone AS (
SELECT ST_MakeEnvelope(-84.4000, 33.7450, -84.3800, 33.7650) AS geom)
SELECT p.ping_id, p.geom,
ST_Within(p.geom, z.geom) AS in_zone
FROM pings p, zone z;Roughly a quarter of pings should be in_zone. Widen the zone until half are. Then count them: count(*) FILTER (WHERE in_zone).
-- Snap each fake ping to the nearest point on a route
WITH route AS (
SELECT ST_MakeLine(ST_Point(-84.3915, 33.7539),
ST_Point(-84.3346, 33.9462)) AS line),
pings AS (
SELECT i AS ping_id,
ST_Point(-84.3915 + (hash(i) % 1000) / 1000.0 * 0.06,
33.7539 + (hash(i * 7) % 1000) / 1000.0 * 0.19) AS geom
FROM range(40) t(i))
SELECT p.ping_id,
ST_LineLocatePoint(r.line, p.geom) AS fraction_along,
ST_LineInterpolatePoint(r.line,
ST_LineLocatePoint(r.line, p.geom)) AS snapped
FROM pings p, route r;fraction_along is 0 to 1. Plot snapped on the map, then ask: what does a ping 2 km from the line become?
read_csv('stops.txt') and use ST_Point(stop_lon, stop_lat).Check each licence before sharing a file in the group workspace. I confirmed the links above, not the licence terms or the current download files.