Acquire / file · All operations

Read a geospatial file

A variant of Read another file format

Read features and their geometry from shapefiles, GeoJSON, geodatabases and tile sets.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the spatial extension.

The DuckDB step

Read features and geometry from a shapefile, GeoJSON, a geodatabase and a tile set

It reads the customers table the proofs share. Create it first:

SQL
CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
INSTALL spatial; LOAD spatial;
SET geometry_always_xy = true;
CREATE TEMP TABLE places AS SELECT customer, ST_Point(lon, lat) AS geom FROM customers;
COPY places TO '<your_scratch_dir>/places.shp' (FORMAT gdal, DRIVER 'ESRI Shapefile');
COPY places TO '<your_scratch_dir>/places.geojson' (FORMAT gdal, DRIVER 'GeoJSON');
COPY places TO '<your_scratch_dir>/places.gdb' (FORMAT gdal, DRIVER 'OpenFileGDB', GEOMETRY_TYPE 'POINT');
CREATE TEMP TABLE shp AS SELECT customer, round(ST_X(geom), 2) AS x, round(ST_Y(geom), 2) AS y FROM ST_Read('<your_scratch_dir>/places.shp');
CREATE TEMP TABLE gj AS SELECT customer, round(ST_X(geom), 2) AS x, round(ST_Y(geom), 2) AS y FROM ST_Read('<your_scratch_dir>/places.geojson');
CREATE TEMP TABLE gdb AS SELECT customer, round(ST_X("SHAPE"), 2) AS x, round(ST_Y("SHAPE"), 2) AS y FROM ST_Read('<your_scratch_dir>/places.gdb');
SELECT * FROM shp; SELECT * FROM gj; SELECT * FROM gdb;
SELECT CASE WHEN ((SELECT count(*) = 4 AND max(x) FILTER (WHERE customer = 'Alice Ng') = 13.4 AND max(y) FILTER (WHERE customer = 'Alice Ng') = 52.52 AND min(x) = -3.7 FROM shp) AND (SELECT count(*) = 4 AND max(x) FILTER (WHERE customer = 'Alice Ng') = 13.4 AND max(y) FILTER (WHERE customer = 'Alice Ng') = 52.52 AND min(x) = -3.7 FROM gj) AND (SELECT count(*) = 4 AND max(x) FILTER (WHERE customer = 'Alice Ng') = 13.4 AND max(y) FILTER (WHERE customer = 'Alice Ng') = 52.52 AND min(x) = -3.7 FROM gdb)) THEN 'as expected' ELSE error('the shapefile, GeoJSON file and geodatabase did not read back as the four points written') END AS verdict;

-- a tile set: the points go in as web-mercator coordinates and are cut into tiles for zoom 0 to 12 (the driver keeps 0 to 5)
COPY (SELECT customer, ST_Transform(geom, 'EPSG:4326', 'EPSG:3857', true) AS geom FROM places) TO '<your_scratch_dir>/places.pmtiles'
  (FORMAT gdal, DRIVER 'PMTiles', LAYER_CREATION_OPTIONS ('MINZOOM=0', 'MAXZOOM=12'));
CREATE TEMP TABLE tiles AS
SELECT customer, round(ST_X(g), 2) AS x, round(ST_Y(g), 2) AS y
FROM (SELECT customer, ST_Transform(d.geom, 'EPSG:3857', 'EPSG:4326', true) AS g FROM (SELECT customer, unnest(ST_Dump(geom)) AS d FROM ST_Read('<your_scratch_dir>/places.pmtiles')));
SELECT * FROM tiles ORDER BY customer;
SELECT CASE WHEN ((SELECT count(*) = 4 AND max(x) FILTER (WHERE customer = 'Alice Ng') = 13.4 AND max(y) FILTER (WHERE customer = 'Alice Ng') = 52.52 AND min(x) = -3.7 AND list(customer ORDER BY customer) = ['Alice Ng', 'Bob Okafor', 'Carla Diaz', 'Frank Moore'] FROM tiles) AND (SELECT count(*) = 4 FROM tiles t JOIN shp s USING (customer, x, y))) THEN 'as expected' ELSE error('the tile set did not give back the four features at the same 2-decimal coordinates as the shapefile') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes places.gdb, places.geojson, places.pmtiles and places.shp itself, in <your_scratch_dir>.
  • It needs the spatial extension.
  • The statement checks its own result and raises an error when it is wrong: “the shapefile, GeoJSON file and geodatabase did not read back as the four points written; then the tile set did not give back the four features at the same 2-decimal coordinates as the shapefile”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (2)
Pentaho Data Integration (1)
  • ESRI Shapefile Readerread 2026-09-27

    Read shapes and attributes from an ESRI shapefile and its linked DBF file

VisiData (4)

Background

In DuckDB, the spatial extension's ST_Read reads spatial data from a variety of geospatial vector file formats as if they were DuckDB tables.[1] DuckDB's spatial extension reads geospatial vector formats through the GDAL translator library, and bundles its own copy of GDAL.[2] The shapefile format is a geospatial vector data format for geographic information system (GIS) software, developed and regulated by Esri.[3]

When to use it

A shapefile is a collection of files with a common filename prefix in one directory, and its three mandatory files have the extensions .shp, .shx and .dbf.[3] Copy or download a shapefile as its whole set of files, never the .shp file alone. (Meridian's view) A shapefile may also carry a .prj file describing its projection and a .cpg file specifying the code page used to encode the .dbf attributes.[3] When attribute text from a shapefile comes out garbled, look for a missing .cpg file before blaming the data. (Meridian's view)

GeoJSON was published by the Internet Engineering Task Force as Request for Comments (RFC) 7946 in August 2016.[4][5] Under RFC 7946, the first two elements of a GeoJSON position are longitude and latitude, or easting and northing, precisely in that order.[5] Under RFC 7946, all GeoJSON coordinates use a geographic coordinate reference system on the World Geodetic System 1984 (WGS 84) datum, in decimal degrees.[5] In DuckDB's spatial extension, ST_Distance_Sphere expects its input in WGS 84 coordinates, using a [latitude, longitude] axis order.[1] Before measuring distances between points read from a file, check which axis order each function expects, and swap the coordinates where the two disagree. (Meridian's view)

Only vector drivers are supported by DuckDB's GDAL integration, so reading raster formats is not supported.[2] GDAL is single-threaded, so DuckDB's ST_Read cannot make full use of parallelism.[1]

The method

The shapefile format was introduced with ArcView GIS version 2 in the early 1990s.[3] A shapefile's .shp file holds the feature geometry, its .shx file a positional index of that geometry, and its .dbf file the attributes of each shape, in dBase IV format.[3] In a shapefile's .shp, .shx and .dbf files, the records correspond to each other in sequence, so the first record in each describes the same shape.[3] The shapefile format lacks the capacity to store topological information.[3]

GeoJSON is an open standard format designed for representing simple geographical features, along with their non-spatial attributes.[4] Through ST_Read, DuckDB's spatial extension lets common geospatial files be queried directly as tables, recognising .shp, .gpkg and .fgb files by their extension.[1]

Sources

  1. [1]Spatial Functions – DuckDBdocumentation
  2. [2]GDAL Integration – DuckDBdocumentation
  3. [3]Shapefile - Wikipediasecondary source
  4. [4]GeoJSON - Wikipediasecondary source
  5. [5]RFC 7946: The GeoJSON Formatstandard