Densifying and segmentizing geometries in dbt

This page adds vertices to geometry on purpose — the opposite of simplification — because a long straight edge is only straight in the coordinate system it was drawn in, and every reprojection or geodesic comparison of that edge is wrong until it has intermediate points.

When to use this approach

• A reprojected shape has the wrong outline. A boundary defined by two distant points becomes a straight line in the target projection, when the true path curves.
• A planar and a geodesic engine disagree on a shape. This is the fix that makes them agree, as noted in warehouse-native GIS adapters.
• A buffer or intersection along a long edge looks angular. Densifying before the operation gives it something to work with.

Prerequisites

• ST_Segmentize on the engine, plus ST_Segmentize(geography, …) if you need geodesic segmentation.
• A projected CRS in which to express the segment length, or geography for metres directly.
• Storage headroom: densification multiplies vertices, and vertices are the geometry’s size, as costed in estimating storage cost of geometry columns.

Step-by-step instructions

1. Recognise the problem before applying the fix

-- analyses/long_edge_audit.sql
with segments as (
    select
        zone_id,
        (st_dumpsegments(geom)).geom as seg
    from {{ ref('stg_zones') }}
)
select
    zone_id,
    count(*)                                          as segment_count,
    round(max(st_length(seg::geography))::numeric, 0) as longest_segment_m
from segments
group by zone_id
having max(st_length(seg::geography)) > {{ var('max_edge_length_metres', 5000) }}
order by longest_segment_m desc

An edge of a few hundred metres is harmless in any projection. An edge of fifty kilometres is where planar and geodesic interpretations visibly part company, and where a reprojection will produce a straight line that the real boundary does not follow.

Verify the audit finds the shapes you expect — administrative outer boundaries and coastline simplifications usually dominate the list, while urban parcels never appear.

2. Densify before the transformation, not after

Order matters and only one order is correct: add vertices in the source CRS, then transform. Densifying afterwards adds points to a line that is already wrong.

-- models/intermediate/int_zones_densified.sql
{{ config(materialized = 'table') }}

select
    zone_id,
    -- segmentize in geography so the interval is real metres, then transform
    st_transform(
        st_segmentize(geom::geography, {{ var('densify_interval_metres', 1000) }})::geometry,
        {{ var('target_srid') }}
    ) as geom,
    st_npoints(geom) as vertices_before,
    st_npoints(st_segmentize(geom::geography, {{ var('densify_interval_metres', 1000) }})::geometry) as vertices_after
from {{ ref('stg_zones') }}

ST_Segmentize on geography inserts points along the great-circle path at the given interval, which is what makes the subsequent transform follow the true curve. The same function on geometry interpolates along a straight line in whatever units the SRID uses — useful for a different purpose, and wrong for this one.

Verify the vertex growth is proportionate:

select
    sum(vertices_before) as before,
    sum(vertices_after)  as after,
    round(sum(vertices_after)::numeric / nullif(sum(vertices_before), 0), 2) as growth_factor
from {{ ref('int_zones_densified') }};
-- A factor of 2–5 is typical; 50 means the interval is far too small

3. Choose the interval from the error you will tolerate

The deviation between a straight chord and the true curve grows with the square of the segment length. That relationship gives an interval rather than a guess.

Verify the achieved deviation directly rather than trusting the table:

select
    max(st_distance(
        st_closestpoint(d.geom, st_pointn(st_boundary(o.geom), 2)),
        st_pointn(st_boundary(o.geom), 2)
    )) as max_deviation_m
from {{ ref('int_zones_densified') }} d
join {{ ref('stg_zones') }} o using (zone_id);

4. Densify only what needs it

Densifying every geometry to satisfy a handful of long edges multiplies storage across the whole table for no benefit.

-- models/intermediate/int_zones_prepared.sql
with flagged as (
    select
        zone_id,
        geom,
        (select max(st_length(s.geom::geography))
         from (select (st_dumpsegments(geom)).geom) s) as longest_segment_m
    from {{ ref('stg_zones') }}
)

select
    zone_id,
    case
        when longest_segment_m > {{ var('max_edge_length_metres', 5000) }}
        then st_segmentize(geom::geography, {{ var('densify_interval_metres', 1000) }})::geometry
        else geom
    end as geom,
    longest_segment_m > {{ var('max_edge_length_metres', 5000) }} as was_densified
from flagged

Verify the selective approach saved what you hoped:

select
    was_densified,
    count(*) as zones,
    pg_size_pretty(sum(st_memsize(geom))::bigint) as geometry_size
from {{ ref('int_zones_prepared') }}
group by was_densified;

5. Keep the densified copy out of the storage layer

Densification is a preparation step for a specific operation, not a property of the data. Keep it in an intermediate model that feeds the operation, and let the mart store the original.

-- The transformed output is what persists; the densified intermediate is ephemeral
{{ config(materialized = 'ephemeral') }}

An ephemeral intermediate is inlined into whichever model consumes it, so the densified vertices exist only for the duration of the query — the right trade when the densified copy has no independent consumers.

Verify nothing else depends on the densified model before making it ephemeral:

dbt ls --select int_zones_densified+ --output name

Configuration reference

Gotchas & edge cases

• Densifying after transforming does nothing useful. The line was already redrawn straight; adding points to it just stores the mistake at higher resolution.
• ST_Segmentize adds points, never removes them. Applying it to already-dense geometry inflates it for no gain — hence the selective form in step 4.
• Geography segmentize can be slow on large tables. It computes great-circle interpolations per segment; run it in a filtered intermediate rather than across a whole mart.
• Simplification undoes it. A pipeline that densifies for correctness and then simplifies for payload has to do them in that order, in different models, or the second discards the first.
• Antimeridian-crossing edges need care. Segmentizing across the date line can produce points on the wrong side; split such geometry first.

FAQ

Related

• Geometry Transformation Pipelines — the transformation layer this belongs to.
• Batch Transforming Coordinate Systems with dbt — the reprojection step that needs it.
• Simplifying Geometries for Map Payloads — the inverse operation, and where it belongs.

Up: Part of Geometry Transformation Pipelines.