Choosing a projected CRS for distance and area

This page picks the projection a dbt model should compute in, for the two measurements that matter most in analytics — distance and area — and adds a test that quantifies the error the choice accepts rather than leaving it unstated.

When to use this approach

  • A model computes an area, a length or a buffer. All three are meaningless in degrees and merely wrong in the wrong projection.
  • Your data spans more than one UTM zone. A single zone’s numbers degrade quickly outside it, and “just use zone 32” stops being defensible somewhere around the neighbouring meridian.
  • Two teams report different areas for the same polygon. Almost always a projection difference, and the reconciliation starts here. The storage-side policy is in spatial reference system management.

Prerequisites

  • PostGIS with spatial_ref_sys populated, or an engine that can reproject.
  • The dataset’s extent, which decides which projections are candidates at all.
  • A statement of what the numbers are for — statutory area reporting and a heat-map legend tolerate very different error.
  • A canonical storage SRID already agreed, since computation CRS and storage CRS are separate decisions.

Step-by-step instructions

1. Separate the storage CRS from the computation CRS

Storage wants one SRID for the whole project, usually EPSG:4326, because it joins cleanly and interchanges everywhere. Computation wants whichever projection makes the specific measurement accurate. These are different jobs and the same model can do both.

sql
-- models/marts/mart_zone_metrics.sql
{{ config(materialized = 'table') }}

select
    zone_id,
    geom,                                                        -- stored in 4326
    round(st_area(st_transform(geom, {{ var('area_srid') }}))::numeric, 1)      as area_m2,
    round(st_perimeter(st_transform(geom, {{ var('area_srid') }}))::numeric, 1) as perimeter_m,
    round(st_length(st_transform(centreline, {{ var('length_srid') }}))::numeric, 1) as centreline_m
from {{ ref('stg_zones') }}

Two vars, not one: an equal-area projection for areas and a conformal one for lengths and shapes. Using a single projection for both is a common shortcut and the reason two dashboards disagree at the third significant figure.

Verify the transform is doing something by comparing against the degrees answer:

sql
select st_area(geom) as square_degrees, st_area(st_transform(geom, 3035)) as square_metres
from {{ ref('stg_zones') }} limit 1;
-- The first number has no physical meaning; the second is the one to publish
No projection preserves everything — what each family gives up Three projection families with the property each preserves and the property each sacrifices. Equal-area preserves area and distorts shape and angle. Conformal preserves local shape and angle and distorts area. Equidistant preserves distance from one point or along one line only. A note underneath states that a projection preserving both area and shape everywhere does not exist, so the choice follows the measurement. equal-area preserves: area distorts: shape, angle EPSG:3035, 6933 use for area and density conformal preserves: local shape, angle distorts: area UTM zones, EPSG:3857 use for length and buffers equidistant preserves: distance from one origin distorts: everything else azimuthal equidistant use for travel-from-a-hub work A projection preserving area and shape everywhere does not exist. The measurement chooses the projection.

2. Choose from the extent, not from habit

sql
-- analyses/crs_candidates.sql
select
    round(st_xmin(e)::numeric, 3) as min_lon,
    round(st_xmax(e)::numeric, 3) as max_lon,
    round(st_ymin(e)::numeric, 3) as min_lat,
    round(st_ymax(e)::numeric, 3) as max_lat,
    round((st_xmax(e) - st_xmin(e))::numeric, 2) as lon_span,
    floor((st_xmin(e) + 180) / 6) + 1 as utm_zone_west,
    floor((st_xmax(e) + 180) / 6) + 1 as utm_zone_east
from (select st_extent(geom) as e from {{ ref('stg_zones') }}) x

The rule of thumb the output supports: if lon_span is under about six degrees and both UTM zone numbers agree, a UTM zone is an excellent conformal choice. If they differ, a single UTM zone will distort at the edges and a continental projection — Lambert azimuthal equal-area for areas, Lambert conformal conic for shapes — is the better answer.

Verify the choice against a known quantity: take a polygon whose official area you can look up, compute it in each candidate, and see which lands closest.

3. Measure the distortion you are accepting

Every projection choice has an error budget, and stating it turns an argument into a number.

sql
-- models/ops/int_crs_distortion.sql
with sample as (
    select zone_id, geom from {{ ref('stg_zones') }} tablesample system (2) repeatable (42)
)

select
    zone_id,
    st_area(st_transform(geom, 3035))                       as area_equal_area,
    st_area(st_transform(geom, 32632))                      as area_utm32,
    st_area(geom::geography)                                as area_geodesic,
    round((st_area(st_transform(geom, 32632)) / nullif(st_area(geom::geography), 0))::numeric, 5)
        as utm_vs_geodesic_ratio
from sample
sql
-- tests/assert_crs_distortion_bounded.sql
select zone_id, utm_vs_geodesic_ratio
from {{ ref('int_crs_distortion') }}
where abs(utm_vs_geodesic_ratio - 1) > {{ var('max_area_distortion', 0.002) }}

A 0.2 per cent bound is generous for a dataset inside one UTM zone and will fail immediately for one spanning three, which is exactly the signal that the projection choice needs revisiting.

Verify the distortion is where you expect it — plot the ratio against longitude and it should be lowest near the zone’s central meridian and rise toward the edges.

Area error across a UTM zone and beyond its edges A curve of area error against longitude for one UTM zone. Error is near zero at the central meridian, rises modestly toward the standard six-degree zone boundaries, and then climbs steeply outside them. A shaded band marks the zone's intended extent and a horizontal line marks a 0.2 percent tolerance, crossed shortly after the data leaves the zone. the zone's 6° extent 0.2% tolerance central meridian outside the zone outside the zone area error A zone is a promise about a six-degree strip — outside it, the numbers stop being defensible.

4. Record the choice where the numbers are consumed

yaml
models:
  - name: mart_zone_metrics
    description: >
      Zone areas and lengths. Geometry stored in EPSG:4326.
      area_m2 computed in EPSG:3035 (ETRS89-LAEA, equal-area) — correct for area,
      distorts shape. centreline_m computed in EPSG:25832 (UTM 32N, conformal) —
      correct for length within the zone. Measured area error against geodesic
      area is below 0.05% across the current extent (see int_crs_distortion).
    columns:
      - name: area_m2
        description: Square metres, equal-area projection. Not comparable with areas computed in UTM.

Verify that anyone reading the column description learns which projection produced the number. Areas quoted without a projection are the reason reconciliations take a week.

A buffer computed in degrees against one computed in a projected CRS Two buffers around the same point at latitude fifty-two. The one computed on degrees is a wide ellipse, stretched east to west because a degree of longitude is shorter than a degree of latitude at that latitude. The one computed in a projected coordinate system is a true circle of the intended radius. A caption notes that the first is wrong by about forty percent in one axis and is accepted silently by every function downstream. ST_Buffer on degrees, at 52°N an ellipse — wider east–west than intended ST_Buffer in a projected CRS a circle of the radius you asked for

Configuration reference

Var Typical value Use
area_srid 3035 in Europe, 5070 in the US, 6933 globally Equal-area, for areas and densities
length_srid local UTM zone, or a national grid Conformal, for lengths, buffers and shapes
max_area_distortion 0.002 The error budget the tests enforce
storage SRID 4326 Joins and interchange, never computation
tablesample fraction 1–5% Enough for a distortion estimate without a full scan

Gotchas & edge cases

  • EPSG:3857 is not a measurement projection. Web Mercator’s area error reaches a factor of several at high latitudes; it is for tiles, not for numbers.
  • geography casts give geodesic answers, which are correct on the ellipsoid and often the right reference to test against — but they are slower and lack a projected plane for buffers.
  • A buffer in degrees is not a circle. ST_Buffer(geom, 0.01) on 4326 geometry produces an ellipse that stretches with latitude. Buffer in a projected CRS, then transform back.
  • National grids beat UTM inside their country and are usually what statutory figures are computed in — worth matching if your numbers will be compared with official ones.
  • Reprojecting in the join disables the index. Compute the projected geometry in staging when it is used repeatedly, as in tuning ST_Intersects joins with bounding-box prefilters.

FAQ

Can I just use the geography type and skip projections?

For distances and areas, largely yes — geodesic answers on the ellipsoid are correct and need no zone choice, which is why BigQuery works this way. The limits are performance, since geography functions are slower, and operations that genuinely need a plane, such as buffering and offset construction, where you still project.

How wrong is a single UTM zone for a whole country?

For a country a few degrees wide, a fraction of a per cent — usually acceptable. For one spanning three zones or more, errors reach several per cent at the extremes, which is visible in any total. The distortion test above turns that from an opinion into a measured number for your own extent.

Should the projected geometry be stored or computed on the fly?

Computed, unless it is used repeatedly in joins or predicates. Storing a second geometry column doubles the geometry storage, which is the largest column in most spatial tables; storing it is justified when it removes a per-row transform from a hot path, not merely to save typing.

What if different consumers need different projections?

Publish the measurement, not the geometry: an area_m2 computed in a stated equal-area projection serves everyone, and a consumer needing a different one can recompute from the stored 4326 geometry. Publishing several projected geometry columns multiplies storage and invites the columns to drift apart.

Up: Part of Spatial Reference System Management.