Choosing a Storage CRS for Lineage Extents

Part of: CRS and Datum Transformation Provenance

Lineage extents are geometry, and geometry needs a coordinate reference system. The decision is made once, constrains every extent the store will ever hold, and is expensive to reverse — so it is worth ten minutes of thought rather than inheriting whatever the first pipeline happened to emit. This how-to works through the choice, its consequences for queries and indexing, and the constraint that makes it stick.

The short answer is that lineage extents want one CRS across the whole estate, and that WGS 84 is usually it. The reasoning is worth following, because the exception cases are real and knowing them prevents a decision that has to be undone.

Prerequisites

  • A PostGIS lineage schema with geometry columns, per PostGIS Lineage Schema Design.
  • An inventory of the CRSs your source data actually arrives in.
  • A sense of which spatial questions the lineage store is expected to answer.
  • Rights to add column type constraints, ideally before significant data exists.

Why Uniformity Beats Fidelity Here

The instinct is to store extents in the dataset’s own CRS, preserving fidelity and avoiding a transformation. For the data itself that instinct is correct; for lineage extents it produces a store where nothing can be compared to anything.

Mixed CRSs versus one storage CRS Extents in native CRSs require per-row transformation before comparison; a single storage CRS allows one index and direct comparison. NATIVE CRS PER ROW extent · 26910 extent · 27700 extent · 3857 no index can span these Every comparison needs ST_Transform per row — which defeats the GiST index entirely. ONE STORAGE CRS, TRANSFORMED AT INGESTION extent · 4326 extent · 4326 extent · 4326 one GiST index serves all The native CRS is not lost — it stays on the record as a text field. You give up nothing except the ability to store the extent geometry in that CRS.

The caption’s final point resolves most objections. Recording source_crs and target_crs as text on the step, while storing the extent geometry in a single system, keeps every fact and loses only the redundant option of holding the same footprint in several incompatible representations. Nothing about the transformation is forgotten; the extent simply becomes comparable.

The cost of uniformity is a transformation at ingestion, and it is small because an extent is a bounding box rather than a full geometry. Transforming four corner coordinates is cheap even at high ingestion rates, and doing it once at write time is far less expensive than doing it per row on every query — which is what the alternative amounts to.

Why WGS 84 Rather Than Something Projected

Given uniformity, the remaining question is which system. The considerations differ from those for the data itself because extents are used for filtering rather than for measurement.

A geographic CRS is unsuitable for area and distance calculation, and that objection does not apply here. Lineage extent queries ask “does this footprint intersect that region” — a containment test, not a measurement — and containment is unaffected by the distortion that makes geographic CRSs poor for metric work. The property that matters instead is global validity: WGS 84 covers everything, so a store using it never encounters a dataset outside its zone.

Projected systems fail exactly on that point. A state plane or national grid CRS is defined for a region, and extents outside it are either invalid or badly distorted. An agency that is confident it will never handle data beyond its own boundary is usually wrong on a five-year horizon — a partner dataset, a national programme, an emergency response — and discovering the limit after a million rows exist is an expensive migration.

Web Mercator is worth mentioning to dismiss. It is globally valid, widely supported, and distorts area severely at high latitudes while also failing near the poles. Its virtue is tile alignment, which lineage extents do not need. Where a projected system is genuinely wanted, an equal-area projection covering the working region is a better choice than the tiling default.

The case for a projected storage CRS is narrow and real: a store whose queries are predominantly metric — “every product within five kilometres of this point” — pays a per-query cost in a geographic CRS that a projected one avoids. If that pattern genuinely dominates, store a second geometry column in a suitable projected system and index both, rather than switching the canonical one.

Making the Choice Stick

A decision that is not enforced is a convention, and conventions decay. The mechanism that makes this one durable is a typed column.

Declaring the column as geometry(Polygon, 4326) rather than bare geometry means the database rejects any insert carrying a different SRID. That converts a discipline problem into an error at write time, which is where it can be fixed cheaply and by the person who caused it. Without the constraint, a pipeline that forgets to transform writes silently, and the store accumulates mixed-CRS rows that produce wrong intersection results rather than errors.

The type constraint is what enforces the decision A bare geometry column accepts mixed SRIDs silently; a typed column rejects the insert and surfaces the problem immediately. extent geometry accepts any SRID, including 0 ST_Intersects returns nonsense extent geometry(Polygon, 4326) rejects a mismatched insert the pipeline fails, loudly, today SRID 0 is the default for an untyped column and means "unknown", not "the usual one". Rows with SRID 0 compare successfully against each other and meaninglessly against anything else. That is the failure mode the type constraint exists to make impossible.

Add the constraint before data exists if you possibly can. Retrofitting it onto a populated table requires validating or transforming every existing row, and any row that cannot be resolved — an extent whose true CRS nobody recorded — blocks the migration until somebody decides what to assume. That decision is much easier to make one row at a time at ingestion than in bulk under time pressure.

Pair the constraint with an explicit transformation at ingestion rather than relying on callers to have done it. A single helper that accepts an extent and its CRS, transforms to the storage CRS, and returns the typed geometry means the constraint is satisfied by construction and there is one place to look when the behaviour needs to change.

The Bounding-Box Transformation Trap

The one technical detail that reliably goes wrong is transforming a bounding box by transforming its corners. It is the obvious implementation and it under-covers, which is the direction that silently loses results.

Corners are not enough — edges curve A rectangle in one CRS transforms to a curved quadrilateral; a box built from its four corners omits the bulge along the edges. SOURCE CRS — a true rectangle transform TARGET CRS — edges bow outward dashed box from corners alone — misses the bulge Densify the boundary before transforming, then take extremes.

The bulge is small for a small extent and substantial for a large one, and it is always outward for the common projections — meaning a corners-only box is always too small. An extent that under-covers its true footprint fails to match query regions it genuinely overlaps, and the resulting omission is invisible: the query returns fewer rows and nothing indicates that any were missed.

The fix is to densify before transforming. Adding intermediate vertices along each edge — a dozen per side is ample for typical extents — and then taking the extremes of the transformed vertex set produces a box that contains the true footprint. PostGIS offers this directly, and the cost is negligible on four edges.

Erring outward is always the right direction when in doubt. An extent slightly larger than the truth causes a query to return a candidate that turns out not to match, which downstream filtering removes; an extent slightly smaller causes a real match to be dropped, which nothing downstream can recover.

Verification

Insert an extent with the wrong SRID and assert the insert fails. This is the whole test, and it takes a minute: without it, the constraint might be absent, the column might be bare, or a helper might be silently coercing. A constraint that has never rejected anything has not been shown to exist.

Then insert a valid extent, query it with a region in the storage CRS, and confirm the GiST index is used — an EXPLAIN showing an index scan rather than a sequential one. That confirms the second half of the benefit, since uniformity is worth having precisely because it makes the index usable.

Finally, take a real extent, transform it to the storage CRS and back, and assert the round trip stays within a tolerance you state. Bounding-box round trips through a datum transformation are not exactly lossless, and knowing the magnitude tells you whether extent precision is adequate for the filtering you intend.

Gotchas & edge cases

  • A bounding box in one CRS is not a bounding box in another. Transforming four corners and taking their extremes can under-cover the true footprint, because the edges curve. Densify the boundary before transforming, or accept a slightly enlarged box — never a smaller one, since an under-covering extent silently drops matches.
  • Antimeridian-crossing extents break naive boxes. A footprint spanning the dateline produces a box covering the whole world when expressed as min/max longitude. Detect and split it, or store it as a multipolygon rather than a rectangle.
  • SRID 0 is not a CRS. It means unknown, and rows carrying it compare happily with each other and meaninglessly with anything else. Reject it explicitly rather than treating it as a default.
  • Global datasets have extents that are technically correct and useless. A world-covering source intersects every query region, so it appears in every impact result. Consider a separate flag for global sources so queries can exclude them deliberately.
  • The storage CRS is not the publication CRS. Nothing about this decision constrains what CRS you publish data in; it governs only how lineage extents are held internally, and conflating the two is how a reasonable internal choice turns into an argument about output formats.