GeoSilo

Setup

Load spatial first, then geosilo:

INSTALL spatial; LOAD spatial;
INSTALL geosilo FROM community; LOAD geosilo;

The GEOSILO column type and the native ST_* overloads only register once both are loaded — geosilo overlays spatial’s ST_* namespace.

Define a compact geometry table

The recommended shape — explicit CRS, ZSTD compression:

CREATE TABLE parcels (
  id    INTEGER,
  name  VARCHAR,
  geom  GEOSILO('EPSG:4326') USING COMPRESSION zstd
);

The CRS lets GeoSilo pick a sensible integer scale automatically (1e7 for degrees, 100 for projected meters). USING COMPRESSION zstd is where the storage win comes from — without it, raw GEOSILO blobs are larger than DuckDB’s columnar GEOMETRY storage. See CREATE TABLE for the DDL surface.

Load from any GEOMETRY source

The implicit GEOMETRY → GEOSILO cast auto-encodes on insert:

-- From another DuckDB table
INSERT INTO parcels
SELECT id, name, geom FROM raw_parcels;

-- From WKT in CSV
INSERT INTO parcels
SELECT id, name, ST_GeomFromText(wkt) FROM read_csv('parcels.csv');

-- From Parquet with a WKB column
INSERT INTO parcels
SELECT id, name, ST_GeomFromWKB(geom_wkb)
FROM read_parquet('parcels.parquet');

Sources can be any expression returning GEOMETRY — see spatial’s constructor functions for the full list.

Native bounding-box prefilter

The four ST_XMin / ST_XMax / ST_YMin / ST_YMax calls have GEOSILO-native implementations — they walk the integer delta stream without rebuilding a GEOMETRY. Use them in WHERE clauses to prefilter before any GEOS-backed work:

-- Everything in a longitude band — entirely native
SELECT id, name
FROM parcels
WHERE ST_XMin(geom) > -78
  AND ST_XMax(geom) < -76;

-- Hand off to GEOS only for the survivors of the prefilter
SELECT id, ST_AsText(ST_Centroid(geom))
FROM parcels
WHERE ST_XMin(geom) > -78
  AND ST_XMax(geom) < -76
  AND ST_YMin(geom) >  39
  AND ST_YMax(geom) <  41;

See ST_XMin / ST_XMax / ST_YMin / ST_YMax.

Native area, length, perimeter

ST_Area on a GEOSILO column runs the shoelace formula on int64 coordinates rebuilt from the delta stream — no WKB decode:

-- Per-feature area
SELECT id, ST_Area(geom) AS area
FROM parcels
ORDER BY area DESC
LIMIT 10;

-- Total length of a road network
SELECT SUM(ST_Length(geom)) AS total_road_length
FROM roads;

-- Perimeter histogram
SELECT FLOOR(LOG(ST_Perimeter(geom))) AS bucket, COUNT(*) AS n
FROM parcels
GROUP BY bucket
ORDER BY bucket;

See ST_Area, ST_Length, ST_Perimeter.

Per-point coordinate access

ST_X / ST_Y on point geometries decode straight from the silo header:

SELECT id,
       ST_X(geom) AS lon,
       ST_Y(geom) AS lat
FROM stations;

For non-point geometries, use ST_Centroid (which auto-decodes to GEOMETRY) and then ST_X / ST_Y on the resulting point.

Filter by geometry type without decoding

geosilo_metadata reads the silo header directly. Combine with ST_GeometryType (which is also native) to filter cheaply by type:

-- Counts by geometry type — no WKB decode per row
SELECT ST_GeometryType(geom) AS gtype, COUNT(*) AS n
FROM mixed_geom_table
GROUP BY gtype
ORDER BY n DESC;

-- Inspect the integer scale of stored blobs
SELECT geosilo_metadata(silo_blob) AS meta
FROM compact_table
LIMIT 5;
-- {'geometry_type':'POLYGON','vertex_type':'XY','scale':10000000}

Manual encode / decode for BLOB pipelines

When you control the BLOB pipeline directly — exporting to a file format, shipping over a non-Arrow protocol, staging — use geosilo_encode and geosilo_decode:

-- Encode for export
COPY (
  SELECT id, geosilo_encode(geom) AS silo
  FROM parcels_geometry
) TO 'parcels_silo.parquet' (FORMAT 'parquet');

-- Decode on read
SELECT id, ST_AsText(geosilo_decode(silo)) AS wkt
FROM read_parquet('parcels_silo.parquet')
LIMIT 5;

For a custom integer scale (e.g. millimeter-precision projected data), pass the second argument:

SELECT geosilo_encode(geom, 1000) FROM mm_precision_data;

Mix native fast paths with GEOS-backed operations

Anything outside the native overload set auto-decodes through the implicit GEOSILO → GEOMETRY cast. You don’t need to think about it — but if you’re scanning many rows, push the native operations first to shrink the candidate set:

-- ST_Buffer auto-decodes; ST_XMin/XMax/YMin/YMax run native first
SELECT id, ST_AsText(ST_Buffer(geom, 0.001)) AS buffered_wkt
FROM parcels
WHERE ST_XMin(geom) > -78
  AND ST_XMax(geom) < -76
LIMIT 100;

See the spatial extension for the GEOS-backed surface that auto-decodes.

Pair with sibling geo extensions

GeoSilo handles compact storage; pair it with the other geo bucket extensions for indexing and ordering:

-- a5: pentagonal geospatial index for aggregation / joins
INSTALL a5 FROM community; LOAD a5;

-- Bin parcels by A5 cell at resolution 12
SELECT a5_lonlat_to_cell(ST_X(ST_Centroid(geom)),
                         ST_Y(ST_Centroid(geom)),
                         12) AS cell,
       COUNT(*) AS parcels_in_cell
FROM parcels
GROUP BY cell
ORDER BY parcels_in_cell DESC;

See a5 for the indexing surface, and lindel for space-filling curve ordering — sort a GEOSILO table by a Hilbert-encoded centroid to keep nearby geometries on the same DuckDB block / Parquet row group.