Converting Shapefiles to FlatGeobuf

Converting a legacy Shapefile to FlatGeobuf collapses a fragile four-file set with a 2 GB ceiling and 10-character column names into one streamable, spatially indexed file, and this walkthrough — a companion to FlatGeobuf and GeoPackage ingestion — does it entirely inside DuckDB with st_read and the GDAL COPY writer. The goal is a conversion that preserves every feature, carries the CRS forward correctly, and repairs invalid geometry on the way out, rather than one that merely produces a file GDAL will open.

Root-Cause Analysis of Conversion Failures #

A Shapefile is not one file but a set — .shp holds geometry, .dbf holds attributes, .shx is the record index, and .prj holds the CRS as WKT — and every conversion defect traces back to a property of that legacy container. Four failure modes dominate, each with a distinct symptom:

• Attribute names silently truncated. The .dbf format caps field names at 10 characters, so population_density was already stored as populatio before you ever opened the file. st_read faithfully returns the truncated name; the conversion is your one chance to rename it back to something meaningful, because nothing downstream can recover the original.
• Missing or wrong .prj (unknown CRS). If the .prj sidecar is absent, GDAL reports an unknown CRS and st_read returns bare coordinates with no reference frame. Writing that straight to FlatGeobuf bakes in an undefined CRS, and every later join against the file is suspect. The frame must be asserted at conversion time — the rules are in the CRS mapping and transformations reference.
• The 2 GB per-file limit. Both .shp and .dbf use 32-bit byte offsets, so a single Shapefile cannot exceed 2 GB. Large exports are often split or silently corrupted at the boundary; FlatGeobuf has no such ceiling, which is frequently the reason for the conversion in the first place.
• Encoding and invalid geometry. The .dbf character encoding is declared in an optional .cpg sidecar that is often missing, mangling non-ASCII attributes; and Shapefiles routinely carry self-intersecting or unclosed polygons that the source tool never validated. Both surface only when something downstream chokes.

Why convert to FlatGeobuf specifically rather than keep the Shapefile? For DuckDB the answer is mechanical: a Shapefile presents no spatial index DuckDB can push a bounding box into, so any regional read scans the entire .shp, whereas FlatGeobuf embeds a packed R-tree that spatial_filter_box seeks against. FlatGeobuf is also a single self-describing file — CRS, schema, and features travel together — so there is no sidecar to lose, no 10-character name ceiling on future columns, and no 2 GB wall. The conversion is a one-time cost that turns a fragile exchange artifact into an indexed one, exactly the container the parent ingestion guide is built to read.

Deterministic Configuration #

Load the extension and set a memory ceiling sized for the materialization plus any geometry repair. The read and write are both serial OGR operations, so thread count matters only for the validation and reprojection in between.

INSTALL spatial;
LOAD spatial;             -- st_read, the GDAL COPY writer, and ST_MakeValid all live here

-- Headroom for ST_MakeValid, which can multiply vertex counts on pathological
-- polygons; size below total RAM so a repair pass never OOM-kills the process.
SET memory_limit = '8GB';

-- Helps the CTAS materialization and ST_MakeValid; the OGR read/write stay serial.
SET threads = 8;

-- Feature order is not meaningful across the conversion, so drop the guarantee
-- and let the intermediate table build in parallel.
SET preserve_insertion_order = false;

Before converting, confirm the source is complete and its frame is known:

• All sidecars present — .shp, .dbf, .shx, and crucially .prj.
• Source CRS recovered from ST_Read_Meta and recorded, ready to pass as the writer SRS.
• A quick count(*) captured from the source, to verify against the output later.

Optimized Execution Pattern #

The naive conversion reads the Shapefile and writes a FlatGeobuf in one statement. It works, but it inherits the truncated .dbf names, trusts whatever CRS GDAL inferred, and passes invalid geometry straight through.

-- Before: one-shot copy. Truncated names, implicit CRS, no geometry repair.
COPY (SELECT * FROM st_read('data/roads.shp'))
TO 'data/roads.fgb'
WITH (FORMAT GDAL, DRIVER 'FlatGeobuf');

The robust conversion stages the read into a table, renames columns, repairs geometry, and stamps the CRS explicitly on the write. The SRS option is the load-bearing change: DuckDB GEOMETRY carries no SRID, so without it the FlatGeobuf header records an undefined CRS.

-- After: staged conversion — rename fields, repair geometry, stamp CRS on write.
CREATE OR REPLACE TABLE roads_clean AS
SELECT
    ST_MakeValid(geom)                AS geom,        -- repair self-intersections
    populatio                         AS population,  -- undo the 10-char DBF truncation
    rd_class                          AS road_class
FROM st_read('data/roads.shp');

-- SRS stamps the CRS into the .fgb header; DRIVER selects FlatGeobuf, whose GDAL
-- default writes the packed spatial index so downstream boxed reads are accelerated.
COPY roads_clean
TO 'data/roads.fgb'
WITH (FORMAT GDAL, DRIVER 'FlatGeobuf', SRS 'EPSG:27700');

The behavioral change is that the intermediate table gives you a place to intervene: renaming restores meaning the .dbf destroyed, ST_MakeValid guarantees the output is topologically sound, and the explicit SRS makes the file self-describing. FlatGeobuf’s GDAL writer produces the packed Hilbert R-tree by default, so the result supports the index-accelerated spatial_filter_box reads described in the parent ingestion guide — a capability the Shapefile’s separate .shx index never gave DuckDB.

When the .dbf encoding is non-ASCII and the .cpg sidecar is missing, GDAL guesses and mangles the text. Force the encoding at read time with open_options rather than accepting corrupted attributes in the output:

-- Force the DBF encoding when the .cpg sidecar is absent or wrong; ENCODING is an
-- OGR layer-creation option the Shapefile driver honours, saving a lossy re-import.
CREATE OR REPLACE TABLE roads_clean AS
SELECT ST_MakeValid(geom) AS geom, populatio AS population, rd_class AS road_class
FROM st_read('data/roads.shp', open_options = ['ENCODING=ISO-8859-1']);

The renaming step is worth calling out because it is the only irreversible gain in the conversion. Once written to FlatGeobuf, population stays population — there is no 10-character ceiling — so a downstream reader never has to reverse-engineer what populatio or mn_hh_incm was supposed to mean. Map every truncated column back to its full name in the staging SELECT while you still know the source schema.

Diagnostic Queries & Plan Validation #

Verify the conversion by counting features on both sides and confirming the CRS landed in the output header. The counts must match exactly — a shortfall means features were dropped, usually by an invalid-geometry or encoding error the writer rejected.

-- Source count, captured before the write.
SELECT count(*) AS src_features FROM st_read('data/roads.shp');

-- Output count and the CRS the FlatGeobuf header now advertises.
SELECT
    (SELECT count(*) FROM st_read('data/roads.fgb'))            AS out_features,
    UNNEST(layers).name                                        AS layer_name
FROM ST_Read_Meta('data/roads.fgb');

Because both sides are st_read, each shows as a single TABLE_FUNCTION (ST_READ) node under EXPLAIN; there is no parallel scan to inspect. The meaningful check is the row count the function emits and the CRS string from ST_Read_Meta — confirm the output count equals the source count and the CRS matches the SRS you passed. To prove the embedded index is usable, run a boxed read and confirm it emits fewer rows than the full file:

-- Selectivity check: a boxed read should prune to the overlapping features,
-- proving the FlatGeobuf packed R-tree was written and is queryable.
SELECT count(*)
FROM st_read(
        'data/roads.fgb',
        spatial_filter_box = ST_Extent(ST_MakeEnvelope(529000, 179000, 533000, 183000))
     );

A boxed count equal to the total feature count means the writer produced an unindexed file — re-export and confirm the FlatGeobuf driver wrote its index. The structure and rebuild cost of that index are detailed in the R-tree index internals reference.

Geometry Validation & Fallback Routing #

Shapefiles are a notorious source of invalid polygons, and an invalid geometry either aborts the write or bakes a broken boundary into the .fgb. Quarantine offenders first so you can see the scale of the problem before repairing blindly:

-- Isolate invalid geometry and read WHY it is invalid before repairing.
SELECT rd_class, ST_IsValidReason(geom) AS reason
FROM st_read('data/roads.shp')
WHERE NOT ST_IsValid(geom);

The default fix is ST_MakeValid, which repairs self-intersections and unclosed rings deterministically. For a small residue that ST_MakeValid cannot rescue — degenerate zero-area slivers, for instance — route those rows to a quarantine table rather than letting them fail the whole conversion:

-- Repair the recoverable majority; quarantine what cannot be fixed so the
-- conversion completes on the clean set instead of aborting on one bad row.
CREATE OR REPLACE TABLE roads_clean AS
SELECT ST_MakeValid(geom) AS geom, populatio AS population, rd_class AS road_class
FROM st_read('data/roads.shp')
WHERE ST_IsValid(ST_MakeValid(geom));

CREATE OR REPLACE TABLE roads_rejected AS
SELECT geom, rd_class
FROM st_read('data/roads.shp')
WHERE NOT ST_IsValid(ST_MakeValid(geom));

COPY roads_clean TO 'data/roads.fgb'
WITH (FORMAT GDAL, DRIVER 'FlatGeobuf', SRS 'EPSG:27700');

If the source .prj is missing, do not guess: recover the intended CRS from the data provider, set it explicitly with a reprojection in the staging step, and only then write. When a Shapefile is genuinely too large or complex to hold in memory for repair, chunk it with a row_number() window or a bounding-box partition and convert each partition to its own .fgb, then read the set with a glob — the same chunked-execution discipline used across the parent ingestion guide. For deciding whether FlatGeobuf is even the right target versus a columnar store, the scan-and-storage numbers in GeoParquet vs Shapefile performance make the case for GeoParquet when the file will be queried analytically rather than exchanged.

Related #

See also

• GeoParquet vs Shapefile performance — when a columnar target beats FlatGeobuf for repeated analytical reads.
• R-tree index internals — the packed spatial index the FlatGeobuf writer embeds.
• CRS mapping and transformations — recovering and stamping the CRS the .prj sidecar carried.

Up: FlatGeobuf and GeoPackage Ingestion · DuckDB Spatial Architecture & Fundamentals