PostGIS

@voltro/plugin-postgis adds postgres-native spatial column types and operators to the schema DSL. PostGIS is a postgres extension with no portable cross-dialect equivalent, so the plugin is postgres-only — a geography()/geometry() column FAILS LOUD at schema emission (voltro migrate / auto-migrate throws a postgres-only error) on any other dialect, and a spatial predicate throws in the SQL compiler.

Status: ✓ shipped.

#Columns — geography / geometry

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

export const venues = table('venues', {
  id:       id(),
  name:     text(),
  // SRID 4326 = WGS-84 lat/lon. Most common for global apps.
  location: geography('Point', 4326),
})

• geography(kind, srid) — spheroid-aware coordinates; distance calculations honour the earth's curvature. Use for global apps with WGS-84 lat/lon (SRID 4326).
• geometry(kind, srid) — planar (cartesian) coordinates; distance is straight-line in the SRID's reference frame. Use for local apps with projected coordinates (e.g. a UTM zone for one country).

kind is 'Point' | 'LineString' | 'Polygon' | 'MultiPoint' | 'MultiLineString' | 'MultiPolygon' | 'GeometryCollection'. On postgres the migrator emits the real geography(Point,4326) DDL plus a one-time CREATE EXTENSION IF NOT EXISTS postgis; on any other dialect schema emission throws.

#Value constructors

These build PostGIS WKT/EWKT (well-known text) values that the SQL compiler binds as literals; postgres' implicit text→geometry cast parses them at insert time. Coordinates are [lon, lat] (x before y) throughout.

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

await ctx.store.insert('venues', {
  name:     'HQ',
  location: point(8.682, 50.110, 4326),   // (lon, lat, srid) — Frankfurt
})

// A path / route.
const route = lineString([[8.68, 50.11], [8.69, 50.12], [8.70, 50.13]], 4326)

// polygon(rings, srid) — each ring is an array of [lon, lat] tuples; the first
// ring is the exterior, the rest are holes; each ring MUST be closed.
const area = polygon([[[8.6, 50.1], [8.7, 50.1], [8.7, 50.2], [8.6, 50.1]]], 4326)

// Multi-geometries.
const cluster = multiPoint([[8.68, 50.11], [8.70, 50.13]], 4326)
const lines   = multiLineString([[[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)

// GeoJSON input — lowered to the same EWKT (SRID 4326 by the GeoJSON spec).
const fromGeoJson = geoJson({ type: 'Point', coordinates: [8.682, 50.110] })

#Spatial predicates

import { queryFor } from '@voltro/database'
import { ST_DWithin, ST_Within, ST_Contains, ST_Intersects, ST_Buffer, bboxOverlaps, point, polygon, envelope } from '@voltro/plugin-postgis'

// Venues within 1km of a target point (meters; great-circle on geography).
await ctx.store.query(
  queryFor(venues)
    .where(ST_DWithin('location', point(8.682, 50.110, 4326), 1000))
    .descriptor,
)

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

// Points that lie INSIDE a polygon (the column is within the geom).
queryFor(venues).where(ST_Within('location', cityCenter))

// A region column that fully CONTAINS a point (column-first, always).
queryFor(regions).where(ST_Contains('boundary', point(8.682, 50.110, 4326)))

// Everything a 500 m buffer around a route touches.
queryFor(venues).where(ST_Buffer('location', point(8.682, 50.110, 4326), 500))

// Coarse, index-only bounding-box overlap — pair with an exact predicate or use alone.
queryFor(venues).where(bboxOverlaps('location', envelope(8.6, 50.05, 8.75, 50.15, 4326)))

• ST_DWithin(column, geom, meters) — column is within meters of geom (metric distance; the column is cast to geography).
• ST_Within(column, geom) — column's geometry is fully inside geom (the converse of ST_Contains). The right shape for "rows inside this polygon".
• ST_Contains(column, geom) — column's geometry fully contains geom.
• ST_Intersects(column, geom) — column's geometry shares any point with geom.
• ST_Buffer(column, geom, meters) — column intersects the geom grown by meters (a metric buffer via a geography round-trip). Shape-aware "within N metres of a line/polygon".
• 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 are for one-shot ctx.store.query reads — they throw in the in-memory reactive matcher, so don't use them in subscriptions.

#Distance projection + KNN ordering

withDistance(...) and nearestBy(...) are QueryTransforms applied via the builder's .use(...) seam (same mechanism as hybridSearch), so they compose with .where(...), .limit(k), and the spatial predicates. Postgres-only — the compiler throws on other dialects.

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

// Distance-as-a-value: project ST_Distance in METRES onto each row.
const withMeters = await ctx.store.query(
  queryFor(venues)
    .use(withDistance('location', point(8.682, 50.110), { as: 'meters' }))
    .where(ST_DWithin('location', point(8.682, 50.110), 5000))
    .descriptor,
)
// withMeters[i].meters === distance in metres

// KNN: the k nearest venues via the indexed `<->` operator.
const nearest5 = await ctx.store.query(
  queryFor(venues)
    .use(nearestBy('location', point(8.682, 50.110)))
    .limit(5)
    .descriptor,
)

• withDistance(column, geom, { as?, useGeography? }) — surfaces ST_Distance(column, geom) AS "<alias>" (default alias distance). Metric metres by default; pass { useGeography: false } for planar SRID-unit distance.
• nearestBy(column, geom, { direction? }) — orders by column <-> geom (the KNN operator; leads any explicit .orderBy(...)). Pair with .limit(k) for ORDER BY geom <-> point LIMIT k — an indexed nearest-neighbour scan when a GiST index covers the column. <-> is planar; add withDistance too if you need a metric distance value.

#GiST index

A regular B-tree index is useless for spatial queries — declare a GiST index, the PostGIS-recommended access method, via the framework's .expressionIndex(...) with { kind: 'gist' }:

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

On postgres this emits CREATE INDEX … USING GIST natively. Other dialects warn and fall back to a B-tree (which won't accelerate spatial predicates) — another reason this plugin is postgres-first.