> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wherobots.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Spatial Data in dbt Models

> How the Wherobots dbt adapter handles geometry, geography, and raster columns, how seeds load geometry from four text formats, and how to test spatial invariants.

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`.

```sql title="models/intermediate/int_trips_zoned.sql" theme={"system"}
{{ 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](/reference/wherobots-db/geometry-data/crs-transformation).

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

```sql title="models/staging/stg_zipcode_zones.sql" theme={"system"}
{{ 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:

```sql theme={"system"}
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](#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](/develop/dbt/materializations#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:

```yaml title="models/marts/_marts.yml" theme={"system"}
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.

<Steps>
  <Step title="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)`.

    ```yaml title="dbt_project.yml" theme={"system"}
    seeds:
      my_project:
        landmark_points:
          +column_types:
            geom: geometry
    ```
  </Step>

  <Step title="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.

    ```yaml title="dbt_project.yml" theme={"system"}
    seeds:
      my_project:
        landmark_points:
          +column_types:
            geom: geometry
          +column_srids:
            geom: 4326
    ```

    <Note>
      `+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.
    </Note>
  </Step>

  <Step title="Write the CSV">
    Each cell may use any of the four supported formats, and a single column may mix them.

    ```csv title="seeds/landmark_points.csv" theme={"system"}
    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
    ```
  </Step>

  <Step title="Load the seed">
    ```bash theme={"system"}
    dbt seed --select landmark_points
    ```
  </Step>
</Steps>

### 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:

1. **The SRID in the type,** as in `+column_types: {geom: geometry(3857)}`.
2. **Otherwise the `+column_srids` value,** if you set one for that column.
3. **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.

<Warning>
  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.
</Warning>

## 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`:

```sql title="models/intermediate/int_zone_landmarks.sql" theme={"system"}
{{ 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:

```sql title="models/staging/stg_overture_places.sql" theme={"system"}
{{ 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
```

```sql title="models/marts/fct_place_density_by_zone.sql" theme={"system"}
{{ 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:

```sql theme={"system"}
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.

<Warning>
  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.
</Warning>

## 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](/develop/rasterflow/index) 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

```sql title="tests/assert_geometries_srid_4326.sql" theme={"system"}
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.

```sql title="tests/assert_geometries_valid.sql" theme={"system"}
select zone_geom
from {{ ref('stg_zipcode_zones') }}
where not ST_IsValid(zone_geom)
```

### Assert geometries fall inside an expected area

```sql title="tests/assert_pickups_in_nyc.sql" theme={"system"}
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:

```bash theme={"system"}
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

<CardGroup cols={3}>
  <Card title="Materializations" icon="layer-group" href="/develop/dbt/materializations">
    Views, tables, ephemeral models, incremental strategies, snapshots, and seeds.
  </Card>

  <Card title="Geometry functions" icon="draw-polygon" href="/reference/wherobots-db/geometry-data/geometry-functions">
    The full Spatial SQL function reference available to your models.
  </Card>

  <Card title="Adapter overview" icon="plug" href="/develop/dbt">
    Install, configure a profile, and verify the connection.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.