PostGIS (geography / geometry)

The @voltro/plugin-postgis package adds first-class PostGIS columns and spatial operators to the Voltro schema DSL. Postgres only. PostGIS is a postgres extension; other dialects have no portable equivalent, so schema emission (voltro migrate / auto-migrate) FAILS LOUD with a postgres-only error on non-postgres backends, and spatial predicates throw in the SQL compiler.

#Install + enable

pnpm add @voltro/plugin-postgis

On postgres, the migrator emits a one-time CREATE EXTENSION IF NOT EXISTS postgis; alongside the first spatial column's DDL. If your database user lacks the privilege to create extensions, run that statement once per database as a superuser before migrating.

#Geography vs geometry

Type	Coordinate system	Distance calculation	When to use
geography(...)	Spheroidal (curves with the earth)	Great-circle	Global apps, WGS-84 lat/lon (SRID 4326). Easy mental model.
geometry(...)	Planar / projected	Straight-line in the SRID's frame	Local apps with projected coords (UTM zone, state plane). Faster + works with non-spherical analysis.

If you're unsure: pick geography with SRID 4326. It's the default mental model for "GPS coordinates" and the distance calculations are correct for any global scale.

#Declare a location column

import { geography, point } from '@voltro/plugin-postgis'
import { id, text, table, queryFor } from '@voltro/database'

export const venues = table('venues', {
  id:       id(),
  name:     text(),
  location: geography('Point', 4326),   // lon/lat
})

Insert a row using the point() helper to build the EWKT literal:

await ctx.store.insert('venues', {
  name:     'Café Mockup',
  location: point(8.682, 50.110, 4326),   // Frankfurt
})

The literal serialises as SRID=4326;POINT(8.682 50.11), which postgres parses into the geography column via implicit cast.

#Query within a radius

import { ST_DWithin, point } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

const nearMe = await ctx.store.query(
  queryFor(venues)
    .where(ST_DWithin('location', point(8.68, 50.11), 1000))  // 1 km
    .descriptor,
)

ST_DWithin(column, point, meters) is the canonical "find rows within X distance" predicate. PostGIS handles the great-circle math.

#Query inside a polygon

import { ST_Intersects, polygon } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

const cityCenter = polygon([
  [[8.65, 50.10], [8.72, 50.10], [8.72, 50.13], [8.65, 50.13], [8.65, 50.10]],
])

const inside = await ctx.store.query(
  queryFor(venues)
    .where(ST_Intersects('location', cityCenter))  // venue location ∈ polygon
    .descriptor,
)

Every predicate takes the column first, then the WKT geometry:

• ST_DWithin(column, geom, meters) — within a metric radius.
• ST_Within(column, geom) — the column's geometry is fully inside geom (the right shape for "point column inside a polygon").
• ST_Contains(column, geom) — the column's geometry fully contains geom (e.g. a region boundary column containing a point).
• ST_Intersects(column, geom) — any shared point.
• ST_Buffer(column, geom, meters) — the column intersects geom grown by meters (a metric buffer; shape-aware "within N metres").
• bboxOverlaps(column, geom) — the && bounding-box overlap operator: cheap, GiST-index-only, approximate. Build the box with envelope(minLon, minLat, maxLon, maxLat, srid).

Spatial predicates work in one-shot ctx.store.query reads only; they throw in the in-memory reactive matcher, so keep them out of subscriptions.

#Geometry constructors

point / lineString / polygon / multiPoint / multiLineString / multiPolygon build EWKT literals; geoJson lowers a GeoJSON geometry to the same EWKT (SRID 4326 by the GeoJSON spec). Coordinates are [lon, lat] (x before y) throughout.

import { point, lineString, polygon, multiPolygon, geoJson } from '@voltro/plugin-postgis'

const route = lineString([[8.68, 50.11], [8.69, 50.12]], 4326)
const zones = multiPolygon([[[[8.6, 50.1], [8.7, 50.1], [8.7, 50.2], [8.6, 50.1]]]], 4326)
const fromGeoJson = geoJson({ type: 'Point', coordinates: [8.682, 50.110] })

#Surfacing distance + nearest-neighbour ordering

withDistance(...) projects ST_Distance(column, geom) as a selectable column (metric metres by default), and nearestBy(...) adds a <-> KNN ORDER BY for indexed nearest-neighbour search. Both are QueryTransforms applied via the builder's .use(...) seam (the same mechanism as hybridSearch), so they compose with .where(...) and .limit(k). Postgres-only.

import { withDistance, nearestBy, ST_DWithin, point } from '@voltro/plugin-postgis'
import { queryFor } from '@voltro/database'

// Distance-as-a-value in metres.
const withMeters = await ctx.store.query(
  queryFor(venues)
    .use(withDistance('location', point(8.68, 50.11), { as: 'meters' }))
    .where(ST_DWithin('location', point(8.68, 50.11), 5000))
    .descriptor,
)

// The 5 nearest venues via the indexed `<->` operator.
const nearest5 = await ctx.store.query(
  queryFor(venues).use(nearestBy('location', point(8.68, 50.11))).limit(5).descriptor,
)

withDistance(column, geom, { as?, useGeography? }) aliases the distance column (default distance); nearestBy(column, geom, { direction? }) leads any explicit .orderBy(...). <-> is planar (postgres has no geography <->), so KNN ranks by planar distance — add withDistance when you need a metric value.

#Indexes

GiST is the PostGIS-recommended index access method for geometry/geography columns. The framework's regular .index([...]) uses B-tree, which is useless for spatial queries — declare the index with kind: 'gist' on .expressionIndex():

export const venues = table('venues', {
  id:       id(),
  name:     text(),
  location: geography('Point', 4326),
}).expressionIndex(
  'venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist' },
)

The framework emits:

CREATE INDEX IF NOT EXISTS "venues_loc_gist"
  ON "venues" USING GIST (("location"));

on postgres. On every other dialect the migrator warns + falls back to a plain B-tree index (spatial queries will then fall back to sequential scan — there's no portable equivalent of GiST).

Combine kind: 'gist' with where: … for a partial GiST index:

.expressionIndex(
  'active_venues_loc_gist',
  [{ expr: '"location"' }],
  { kind: 'gist', where: `"active" = true` },
)

#Changing the SRID or the subtype

The subtype and the SRID are part of the schema, so the migration planner sees a change to either and the schema fingerprint moves with it. What it does about the change depends on whether a transform exists.

A changed SRID reprojects. ST_Transform moves every stored value into the new frame exactly, so the operation applies like any other schema change:

// before
location: geography('Point', 4326),
// after — planned as alter-column-type, applied with
//   USING ST_Transform("location"::geometry, 25832)::geography
location: geography('Point', 25832),

The rewrite touches every row in the table, so time it like any other full-table operation. PostGIS refuses a projected target on a geography column at all (Only lon/lat coordinate systems are supported in geography) — use geometry() for projected frames.

A changed subtype, or a change between geometry and geography, is refused. A point is not a polygon and no expression makes it one, so the planner blocks the operation rather than guessing. Acknowledge it the same way you acknowledge any other type change the planner will not guess at, with the PostGIS expression that converts your values:

area: geometry('MultiPolygon', 4326).narrowedFrom('text', {
  using: 'ST_Multi("area")',
}),

The database still has the last word: if a stored value does not fit the declared type, the ALTER fails and the migration stops.

Why this is worth knowing. Before these parameters were part of the schema, changing either planned nothing at all — and the first write in the declared frame came back Geometry SRID (3857) does not match column SRID (4326). A declaration the database does not enforce is a promise to your code that fails at runtime instead of at migrate time.

#Cross-dialect

PostGIS is a postgres extension. A schema using geography() / geometry() fails loud at schema emission when the active dialect isn't postgres — no silent TEXT fallback, no graceful degradation. Apps that need spatial queries on MySQL/MariaDB/MSSQL/SQLite should either:

• Use the dialect's native spatial type (mysql's POINT/GEOMETRY, mssql's geography/geometry) via a file-based migration + raw SQL
• Store coords as numeric columns + filter app-side (acceptable up to ~100k rows)

Voltro doesn't ship a cross-dialect spatial abstraction — the behavioural gaps are too large to paper over.

#See also

• Columns — the regular schema-DSL types
• Expression indexes — the framework's index API (kind: 'gist' | 'gin' on postgres)
• PostGIS docs — the official manual, authoritative for every spatial function the framework re-exports