Querying spatial data with SQL on a Mac

Most spatial questions are SQL questions with one geometric condition in them. Which stores are inside which district, how far is each site from the nearest station, which parcels are largest. DuckDB's spatial extension answers them in plain SQL, on files, without loading anything into a database first.

Reading the files

Map files are read with ST_Read, which handles GeoJSON, GeoPackage, FlatGeobuf, Shapefiles and more:

SELECT * FROM ST_Read('districts.geojson')

Unlike a Parquet or CSV file, a GeoJSON file cannot be queried by its name alone. FROM 'districts.geojson' fails, and ST_Read is the way in.

A CSV with latitude and longitude columns becomes spatial with ST_Point, longitude first:

SELECT name, ST_Point(lon, lat) AS geom
FROM 'stores.csv'

Which points fall inside which areas

The classic spatial join. Every store, with the district it is in:

SELECT s.name AS store, d.name AS district
FROM (SELECT name, ST_Point(lon, lat) AS geom FROM 'stores.csv') s
JOIN ST_Read('districts.geojson') d
  ON ST_Contains(d.geom, s.geom)

Add GROUP BY d.name and count(*) and it becomes stores per district.

Distances and areas

Coordinates in latitude and longitude are degrees, so a plain ST_Distance or ST_Area returns degrees too, which is rarely what anyone wants. For metres on the real globe:

-- Run this first: coordinates are longitude, latitude
SET geometry_always_xy = true;

-- Square metres
SELECT name, ST_Area_Spheroid(geom) FROM ST_Read('districts.geojson');

-- Metres between two points, longitude first
SELECT ST_Distance_Sphere(ST_Point(-74.0060, 40.7128),
                          ST_Point(-0.1276, 51.5072));

That last query returns about 5,570 km, New York to London.

The SET line matters. Without it, DuckDB 1.x reads the coordinates in these two functions as latitude, longitude, the opposite of how GeoJSON and ST_Point(lon, lat) store them. An area in Germany then comes out about half as large again as it is, and one in the United States comes out as NaN. DuckDB 2.0 refuses to run them at all until the setting is chosen. With it, both versions give the right answer. Checking against a distance you know is still worth doing.

Where to run it

DuckDB's command line (free)

brew install duckdb
duckdb -c "INSTALL spatial; LOAD spatial; SELECT * FROM ST_Read('districts.geojson')"

Everything on this page works there. The results are printed text, which is fine for a count and harder for a few thousand rows.

PostGIS (free)

The long-standing answer, and still the right one for spatial data many people share. It is a database server, so the files have to be loaded into it first.

Doing it in Datapuddle

Datapuddle is a Mac app with DuckDB and the spatial extension compiled in. Nothing is installed or downloaded when you first use a spatial function.

Open GeoJSON, GeoPackage, FlatGeobuf and Shapefile files from Finder like any other data file, and each opens as a table.
Open each file a query reads in Datapuddle first. Mac App Store apps can only read files you have chosen.
Results land in a grid that scrolls the whole result, with geometry shown as readable text and a profile drawn in every column header.
⌘⇧↩ runs a query with its plan, which shows whether a slow spatial join is spending its time where you think.

7-day trial, then $39.99 once · Apple silicon, macOS 26 or later

Datapuddle on the Mac App Store — 7 day trial, then $39.99 once. Apple silicon, macOS 26 or later. · Docs Guides · Home