Spatial Data in dbt Models

This page covers how geometry, geography, and raster columns behave in the Wherobots dbt adapter, how to load geometry from a seed file, and how to test spatial correctness.

Type	In models	In seeds	In `dbt docs generate`
`geometry`	Stored and queried normally	Loadable from four text formats	Reported as `geometry`
`geography`	Stored and queried normally	Not loadable	Reported as `geography`
`raster`	Stored and queried normally	Not loadable	Reported as `raster`

Geometry

geometry columns behave like any other column in a model. Wherobots stores them in Havasu tables, and dbt docs generate reports them as geometry.

models/intermediate/int_trips_zoned.sql

{{ config(materialized='table') }}

select
    t.pickup_ts,
    cast(t.pickup_ts as date) as trip_date,
    hour(t.pickup_ts) as trip_hour,
    t.fare_amount,
    t.pickup_geom,
    z.zip
from {{ ref('stg_taxi_trips') }} t
join {{ ref('stg_zipcode_zones') }} z
  on ST_Contains(z.zone_geom, t.pickup_geom)

Always set a spatial reference identifier

A spatial reference identifier (SRID) names the coordinate reference system a geometry is expressed in. Wherobots geometry constructors that take no SRID argument produce SRID 0, meaning “unknown”. A spatial join between geometries in different or unknown coordinate systems returns wrong results rather than an error. Normalize every geometry to a single SRID in your staging layer, before any cross-source operation. Two functions do different things:

ST_SetSRID relabels the coordinate metadata and leaves the coordinates alone. Use it to attach the SRID the data is already in, typically because the source arrived as SRID 0.
ST_Transform reprojects the coordinates into another coordinate reference system. Use it when the source is in a different CRS from the one you are normalizing to. See CRS Transformation.

When the source coordinates are already in EPSG:4326 and only the label is missing, ST_SetSRID on its own is enough:

models/staging/stg_zipcode_zones.sql

{{ config(materialized='view') }}

select
    zcta5ce10 as zip,
    ST_SetSRID(geometry, 4326) as zone_geom
from {{ source('us_census', 'zipcode') }}

When the source is in another CRS, ST_SetSRID alone is wrong. It labels the geometry EPSG:4326 while leaving the original coordinates in place, which is how you get the wrong joins described above. Label the real source SRID, then reproject. For a source column geometry stored in Web Mercator (EPSG:3857) without an SRID, the staging expression is:

ST_Transform(ST_SetSRID(geometry, 3857), 'EPSG:4326') as parcel_geom

Some Wherobots-hosted datasets store geometries with SRID 0 and others with 4326, so check a source’s documented CRS before you label it rather than assuming it is normalized. Then test the result: see Testing spatial invariants.

Declaring geometry column types

A model’s YAML can declare a column’s data_type, including geometry. The adapter does not enforce dbt model contracts yet: a model with contract: {enforced: true} still builds as a plain create table as select, and the stored type comes from the select expression. One declaration does change what the adapter builds. When an incremental model adds a geometry column through on_schema_change='append_new_columns', the adapter uses a data_type: geometry(<srid>) declared for that column as its coordinate reference system. Declare it for a new geometry column that has no non-null values yet; see Handling schema changes. For example, to add a dropoff_geom column to the fct_trip_events model after the table exists, set on_schema_change='append_new_columns' in the model’s config, then declare the column:

models/marts/_marts.yml

version: 2

models:
  - name: fct_trip_events
    columns:
      - name: dropoff_geom
        data_type: geometry(4326)

Seed geometry columns

dbt seed can load geometry directly from a comma-separated values (CSV) file. Two configurations control it.

Declare the column as geometry

dbt cannot infer a geometry column from CSV text, so you must declare it with +column_types.The adapter creates the column as geometry(<srid>): an Apache Iceberg V3 geometry column, with one coordinate reference system (CRS) for every value in the column. To set the SRID in the type itself, write it there, for example geom: geometry(3857).

dbt_project.yml

seeds:
  my_project:
    landmark_points:
      +column_types:
        geom: geometry

Set the SRID for the column

+column_srids maps a column name to an integer SRID. The adapter uses that SRID as the column’s CRS and applies it to every value that does not state its own SRID.

dbt_project.yml

seeds:
  my_project:
    landmark_points:
      +column_types:
        geom: geometry
      +column_srids:
        geom: 4326

+column_srids is optional. When it is unset, the adapter uses 4326 (WGS 84). Set the SRID in one place. The adapter fails a seed that declares geometry(3857) in +column_types and 4326 in +column_srids for the same column.

Write the CSV

Each cell may use any of the four supported formats, and a single column may mix them.

seeds/landmark_points.csv

landmark_id,landmark_name,geom
1,Empire State Building,POINT (-73.9857 40.7484)
2,Times Square,SRID=4326;POINT (-73.9855 40.7580)
3,Statue of Liberty,"{""type"":""Point"",""coordinates"":[-74.0445,40.6892]}"
4,Brooklyn Bridge,01010000007958a835cd7f52c051da1b7c615a4440

Load the seed

dbt seed --select landmark_points

Supported geometry formats

Format	How a cell is recognized	SRID source
Well-known text (WKT)	Anything that does not match the formats below	`+column_srids`, else 4326
Extended well-known text (EWKT)	Starts with `SRID=`	The value’s own inline SRID, which must equal the column’s SRID
GeoJSON	Starts with `{`	`+column_srids`, else 4326
Well-known binary (WKB)	Hexadecimal, as `X'...'`, `0x...`, or bare hex digits	`+column_srids`, else 4326. An SRID embedded in extended WKB is not read.

How the SRID is resolved

The column gets one SRID, and the adapter never emits SRID 0:

The SRID in the type, as in +column_types: {geom: geometry(3857)}.
Otherwise the +column_srids value, if you set one for that column.
Otherwise 4326.

WKT, GeoJSON, and WKB values get the column’s SRID. An EWKT value is written with its own inline SRID, so that SRID must equal the column’s. Wherobots rejects a value in any other SRID, and the seed fails with Cannot write geometry with SRID 3857 into a column with SRID 4326. To load data from another coordinate system, reproject it to the column’s SRID before you seed it, or put it in a separate seed with its own SRID. Two cases fail the seed instead of loading a geometry you probably do not want:

SRID=0;... in an EWKT value. Passing it through would store an unknown coordinate system, and substituting another SRID would overwrite one the file states outright. The seed fails instead.
A zero or negative +column_srids value. Configure a real SRID for the column, or use EWKT values that state their own.

An empty CSV cell loads as NULL, not as an empty geometry.

An extended WKB (EWKB) value can carry its own SRID, but the adapter does not read it. Every WKB value gets the column’s SRID, with no reprojection and no error. For example, an EWKB point in EPSG:3857 seeded into a 4326 column loads with its Web Mercator coordinates labeled 4326. Reproject WKB data to the column’s SRID before you seed it, or set the column’s SRID to the data’s SRID when every value in the column uses it.

Geography

geography columns can be stored. A table, view, or incremental model that selects a geography expression, such as ST_GeogFromWKT(wkt, 4326), materializes a geography column (an Iceberg V3 type), and dbt docs generate reports it as geography. Later incremental runs keep writing to an existing geography column. Adding a new geography column to an existing incremental table through on_schema_change='append_new_columns' is not supported: the adapter would add a bare geography column, which the engine rejects even when the YAML declares data_type: geography(4326). Rebuild the model with --full-refresh to add one. A bare geography column definition is still rejected by the engine with UNSUPPORTED_DATATYPE because it declares no coordinate reference system, so a seed column cannot be declared geography. Most geodesic work does not need the type at all. Spheroidal and great-circle functions such as ST_DistanceSphere and ST_DistanceSpheroid take geometry arguments in EPSG:4326 and return a plain number, so the result stores as an ordinary double:

models/intermediate/int_zone_landmarks.sql

{{ config(materialized='table') }}

select
    z.zip,
    m.landmark_name,
    -- Great-circle distance over geometry columns. The result is a double.
    ST_DistanceSphere(
        ST_Centroid(z.zone_geom),
        m.geom
    ) as crow_flies_m
from {{ ref('stg_zipcode_zones') }} as z
cross join {{ ref('landmark_points') }} as m

The same applies to area. ST_Area on an EPSG:4326 geometry returns square degrees, which is not a usable unit. Use ST_AreaSpheroid, which returns square meters. The next example counts Overture places per ZIP code, so it first stages the places inside the study area:

models/staging/stg_overture_places.sql

{{ config(materialized='view') }}

select
    ST_SetSRID(p.geometry, 4326) as place_geom,
    p.names.primary as place_name
from {{ source('overture_maps_foundation', 'places_place') }} as p
where p.bbox.xmin >= -74.05 and p.bbox.xmax <= -73.90
  and p.bbox.ymin >= 40.68 and p.bbox.ymax <= 40.85

models/marts/fct_place_density_by_zone.sql

{{ config(materialized='table') }}

with place_counts as (

    select
        z.zip,
        any_value(z.zone_geom) as zone_geom,
        count(*) as place_count
    from {{ ref('stg_overture_places') }} as p
    inner join {{ ref('stg_zipcode_zones') }} as z
      on ST_Contains(z.zone_geom, p.place_geom)
    group by z.zip

)

select
    zip,
    place_count,
    -- ST_AreaSpheroid returns square meters; divide for km².
    ST_AreaSpheroid(zone_geom) / 1e6 as zone_area_km2,
    place_count / nullif(ST_AreaSpheroid(zone_geom) / 1e6, 0) as places_per_km2
from place_counts

To store a geography value as text instead, for consumers that cannot read the Iceberg V3 geography type, serialize it:

select
    zip,
    -- A geography value serialized to text before it lands in a column.
    ST_AsEWKT(ST_GeomToGeography(ST_Centroid(zone_geom))) as centroid_ewkt
from {{ ref('stg_zipcode_zones') }}

ST_AsEWKT(), ST_AsText(), ST_AsBinary(), and ST_AsEWKB() serialize geography values, and ST_SRID() reads their SRID. ST_AsGeoJSON(), ST_AsGML(), ST_AsKML(), and ST_GeoHash() accept geometry only and reject a geography value, so convert it with ST_GeogToGeometry() first.

Declaring geography in a seed +column_types returns the engine’s raw UNSUPPORTED_DATATYPE error rather than a dbt-level message: the seed’s create table spells out the column type, and a bare geography declares no coordinate reference system. A model contract’s data_type: geography does not cause this error, because contracts do not reach the table DDL.

Raster

raster columns pass through untouched. They build in models, survive create table as select, appear as raster in dbt docs generate, and come back from dbt show. You cannot seed a raster. A raster value has no compact text encoding that fits a CSV cell, so +column_types: {col: raster} is not supported. Load rasters through RasterFlow or a Wherobots notebook, then reference the resulting table as a dbt source.

Testing spatial invariants

The adapter does not ship spatial generic tests. Write spatial checks as singular tests: a SQL file under tests/ that returns the rows breaking the rule. The test passes when it returns nothing.

Assert a consistent SRID

tests/assert_geometries_srid_4326.sql

with srids as (

    select ST_SRID(pickup_geom) as srid
    from {{ ref('int_trips_zoned') }}

    union all

    select ST_SRID(zone_geom) as srid
    from {{ ref('stg_zipcode_zones') }}

)

select srid
from srids
where srid != 4326

Assert geometries are topologically valid

Self-intersecting polygons and other malformed shapes corrupt spatial joins without raising an error, so check for them explicitly.

tests/assert_geometries_valid.sql

select zone_geom
from {{ ref('stg_zipcode_zones') }}
where not ST_IsValid(zone_geom)

Assert geometries fall inside an expected area

tests/assert_pickups_in_nyc.sql

select pickup_geom
from {{ ref('int_trips_zoned') }}
where not ST_Within(
    pickup_geom,
    ST_MakeEnvelope(-74.05, 40.68, -73.90, 40.85, 4326)
)

Run them with the rest of your tests:

dbt test

Generic tests such as unique, not_null, relationships, and accepted_values work on the non-spatial columns of the same models, so a single dbt test run covers both.

Next steps

Materializations

Views, tables, ephemeral models, incremental strategies, snapshots, and seeds.

Geometry functions

The full Spatial SQL function reference available to your models.

Adapter overview

Install, configure a profile, and verify the connection.

​Geometry

​Always set a spatial reference identifier

​Declaring geometry column types

​Seed geometry columns

​Supported geometry formats

​How the SRID is resolved

​Geography

​Raster

​Testing spatial invariants

​Assert a consistent SRID

​Assert geometries are topologically valid

​Assert geometries fall inside an expected area

​Next steps

Materializations

Geometry functions

Adapter overview

Geometry

Always set a spatial reference identifier

Declaring geometry column types

Seed geometry columns

Supported geometry formats

How the SRID is resolved

Geography

Raster

Testing spatial invariants

Assert a consistent SRID

Assert geometries are topologically valid

Assert geometries fall inside an expected area

Next steps