PostGIS vs GeoPandas vs DuckDB: The One Spatial SQL Engine That Runs On Your Laptop
Most geospatial tools were not built with spatial as a core feature. Spatial got bolted on later, and you can feel it the moment you try to run a real query.
I ran the same four spatial queries across PostGIS, DuckDB, and a newer single node engine called SedonaDB. One of them did a spatial join on 1.2 million NYC building footprints in 0.24 seconds. Another took 43 seconds for the same join. That gap is the whole story of why the spatial stack is changing right now.
This post walks through what each of these engines is actually good at, where DuckDB GIS pulls ahead, where it falls over, and how a spatial-first engine changes the math for geospatial analysis tools you run locally.
The problem with most geospatial analysis tools
When I talk about a spatial SQL engine, I mean a system that lets you ask analytical questions of spatial data. How many of these points fall inside those polygons. What are the five nearest features. How much population sits inside this buffer.
GeoPandas does this in Python. PostGIS does it as a full database. DuckDB does it with an extension. Each one solves a different part of the job, and each one carries a different cost to get your data in and get an answer out.
The trade-off used to be simple. You either accepted a slow tool that was easy to install, or a fast database that took real setup. Spatial-first engines change that trade-off.
DuckDB spatial: fast to start, uneven under load
DuckDB is the reason a lot of people stopped reaching for a full database. You install it in seconds, point it at a file, and write SQL. The spatial extension reads GeoParquet and other formats directly, and for many jobs that is enough.
I keep DuckDB in my stack for exactly that reason. It is fast to install and fast to start.
The trouble shows up on heavier spatial work. On the same NYC dataset, DuckDB took 43 seconds for a basic spatial join that other engines finished in under a second. On an area-weighted interpolation it took 97 seconds. And on a k-nearest-neighbor join, DuckDB threw a memory error and never finished.
DuckDB GIS is a strong default for getting up and running. It is not always the engine you want for complex spatial joins at scale.
PostGIS: the full database, with full setup
PostGIS is the engine I have leaned on the longest. It is a real database. You get inserts, updates, deletes, indexes, and decades of function coverage.
That power comes with a setup tax. To run my benchmark on PostGIS I had to load the data, build separate geometry columns for EPSG:4326 and EPSG:3857, and create a spatial index before any query was fair to measure.
Once it is tuned, PostGIS is steady. It ran the basic spatial join in 6 seconds and the distance aggregation in 21 seconds. Slower than a spatial-first engine on these analytical queries, but PostGIS is doing a different job. It is the system of record, not a throwaway analysis tool.
GeoPandas: the Python bridge you still need
GeoPandas is not a SQL engine, so I did not pit it head to head in the SQL benchmark. It earns its place a different way.
When your data arrives as GeoJSON or a shapefile, GeoPandas is how you read it in. In the demo I loaded all of NYC’s building footprints into a GeoDataFrame, then handed that frame to the spatial engine as a registered table. GeoPandas is the on-ramp from messy file formats into a query-ready frame.
SedonaDB: a spatial-first single node engine
SedonaDB is an open source, single node analytical engine with geospatial as a first-class citizen. It runs right on your computer or any VM, no Spark or distributed backend required. You drive it with Python and SQL.
It is an analytical engine, not a full database. No inserts, updates, or deletes. It works on data files you have locally or can reach over the web, and it answers analytical questions about them in memory.
Two things make it stand out for cloud native geospatial work. It reads data directly from S3 and public or private buckets without downloading it first. And it handles projection systems inline, so reprojecting between EPSG:4326 and a state plane CRS does not become its own slow step.
Getting started with SedonaDB
Install is a single pip command, then connect and read a file over the web.
import sedona.db
sd = sedona.db.connect()
# Read a remote file directly, no download
df = sd.read_parquet("s3://example-bucket/buildings.parquet")
df.show(3)
For formats SedonaDB does not read natively, GeoPandas does the loading.
import geopandas as gpd
# Read GeoJSON or shapefile with GeoPandas
gdf = gpd.read_file("nyc_building_footprints.geojson")
# Register it as a SedonaDB table
buildings = sd.create_data_frame(gdf)
buildings.to_view("buildings")
Once your frames are registered as views, you write plain spatial SQL against them.
SELECT n.neighborhood, COUNT(*) AS building_count
FROM buildings b
JOIN neighborhoods n
ON ST_Intersects(b.geometry, n.geometry)
GROUP BY n.neighborhood
That spatial join across roughly 1.2 million buildings and 263 neighborhood polygons ran in 0.3 seconds.
Where the spatial-first design pays off
The cleaner wins show up on the queries that are painful elsewhere.
A k-nearest-neighbor join is normally a strange cross-join lateral in PostGIS or DuckDB. In SedonaDB it collapses into one function.
SELECT b.bin AS building_id,
h.unit_id AS hydrant_id,
ST_DistanceSpheroid(b.geometry, h.geometry) AS dist
FROM ST_KNN(buildings b, hydrants h, 5)
For each building you get the five nearest fire hydrants and the distance to each. With a spheroid distance you skip transforming geometries back and forth.
Distance queries are just as direct. Counting buildings within 200 meters of each of 109,000 hydrants, with the reprojection done inline, ran in 1.8 seconds.
PostGIS vs GeoPandas vs DuckDB: the benchmark
I ran the SQL engines on the same NYC data and measured wall-clock time. GeoPandas sits out the table because it is the loader, not the query engine.
| Query | SedonaDB | DuckDB | PostGIS |
|---|---|---|---|
| Basic spatial join | 0.24s | 43s | 6s |
| Distance with aggregation | 1.3s | 41s | 21s |
| Area-weighted interpolation | 6s | 97s | 28.9s |
| K-nearest-neighbor join | 1.8s | memory error | 83s |
Speed is not the only factor. DuckDB still wins on how fast you can install it and get moving. PostGIS still wins as your system of record. The point is to match the tool to the moment.
When to use each engine
- Reach for DuckDB when you want to query a file fast with near-zero setup and the spatial work is light to moderate.
- Reach for PostGIS when you need a real database with inserts, updates, deletes, indexes, and a system of record.
- Reach for GeoPandas to read messy formats like GeoJSON and shapefiles into a frame you can hand to a SQL engine.
- Reach for SedonaDB when you need fast, complex spatial joins on small to medium data, want cloud native reads, and want inline reprojection without the headache.
These are not rivals you pick once. I run them together. GeoPandas loads, SedonaDB crunches the heavy analytical queries, PostGIS holds the data of record, and everything moves between them as GeoParquet.
Writing results back out
When the analysis is done, you write results to GeoParquet, partition them if you want, and move on.
result.write_parquet("output/population_by_building.parquet")
That makes the output ready for the next tool in the chain, whether that is a map in Kepler.gl or a load into PostGIS.
FAQ
Is DuckDB or PostGIS better for spatial analysis?
It depends on the job. DuckDB is faster to install and great for querying files directly, but it slowed badly on heavy spatial joins in my benchmark and hit a memory error on a KNN join. PostGIS is a full database with indexes and a stable system of record, but it needs setup. Use DuckDB for quick file analysis and PostGIS as your database of record.
What is a spatial SQL engine?
A spatial SQL engine lets you run analytical SQL queries over spatial data, including spatial joins, distance queries, nearest-neighbor searches, and aggregations. PostGIS, DuckDB with its spatial extension, and SedonaDB are all examples. GeoPandas does similar analysis in Python rather than SQL.
Do I need Spark to run SedonaDB?
No. SedonaDB is a single node engine that runs on your laptop or any VM with a pip install. It does not need Spark or any distributed backend, which is what separates it from the distributed Apache Sedona setup many people know.
How does GeoPandas fit alongside DuckDB and SedonaDB?
GeoPandas is the loader. When data comes as GeoJSON or a shapefile, you read it into a GeoDataFrame with GeoPandas, then register that frame as a table in DuckDB or SedonaDB to run SQL against it.
If you want to build a spatial stack where the right engine handles the right job, that is exactly what we work through inside the Spatial Lab. Come join us: forrest.nyc/go/lab/
