Geospatial data

Geospatial data represents locations and shapes on the Earth's surface — points, lines, and polygons with coordinates — and is queried with specialized spatial functions like distance, containment, and intersection.

Overview

Geospatial data encodes real-world location and shape information: points (a single coordinate, like a store location), lines (a road or route), and polygons (a region, like a delivery zone or country boundary). Common file formats include GeoJSON, Shapefiles, and GeoPackage, and the standard in-database representation is the GEOMETRY type, following the Open Geospatial Consortium's "Simple Features" model. Spatial data usually also carries a coordinate reference system (CRS) describing how coordinates map to real-world positions (for example, WGS 84 / EPSG:4326 for standard latitude/longitude).

Geospatial queries in DuckDB

DuckDB's spatial extension adds a GEOMETRY type and a large library of spatial functions and file format readers (Shapefile, GeoJSON, GeoPackage, and more via GDAL).

Copy code
INSTALL spatial;
LOAD spatial;

SELECT name, geom
FROM ST_Read('stores.geojson');

SELECT s.name, c.name
FROM stores s
JOIN city_boundaries c
  ON ST_Within(s.geom, c.geom);

SELECT name, ST_Distance(geom, ST_Point(-122.42, 37.77)) AS distance
FROM stores
ORDER BY distance
LIMIT 10;

Common functions include ST_Distance, ST_Within, ST_Intersects, ST_Area, and ST_AsText, following naming conventions shared with PostGIS and other spatial databases, which makes spatial SQL fairly portable between systems.

Why it matters

Geospatial analysis powers use cases like store-location and delivery-zone analysis, geofencing, mapping demographic or environmental data to regions, and routing. Being able to run spatial joins and distance calculations directly in SQL alongside ordinary business data — rather than exporting to a separate GIS tool — makes it much easier to combine "where" questions with the rest of an analytical workflow.

Related terms

extension →

An extension in DuckDB is a modular component that adds functionality to the core database system.

SQL function →

A SQL function is a reusable piece of code that performs a specific operation and returns a result.

FAQS

Yes, the spatial extension can read and write formats like GeoJSON, Shapefile, and GeoPackage, largely via an embedded GDAL library, in addition to providing native GEOMETRY columns and spatial functions.

DuckDB's spatial extension follows similar naming conventions to PostGIS for many common functions (like ST_Distance and ST_Within), which makes spatial queries reasonably portable, though it is a separate implementation and not a drop-in PostGIS replacement for every feature.