# Read a geospatial file

Acquire / file

A variant of [Read another file format](https://meridian.online/operations/read-another-file-format).

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

Page: <https://meridian.online/operations/read-another-file-format/read-a-geospatial-file>

## Status

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.

## What other tools call it

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

- Duckle: [`Esri File Geodatabase (.gdb)`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- Duckle: [`Geospatial (GeoParquet / GeoJSON / Shapefile / GeoPackage)`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Pentaho Data Integration: [`ESRI Shapefile Reader`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- VisiData: [`geojson (loader)`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/loaders/geojson.py#L11) (read 2026-09-23)
- VisiData: [`mbtiles (loader)`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/loaders/mbtiles.py#L11) (read 2026-09-23)
- VisiData: [`pbf (loader)`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/loaders/mbtiles.py#L7) (read 2026-09-23)
- VisiData: [`shp (loader)`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/loaders/shp.py#L10) (read 2026-09-23)