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` },
)

#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