Storage

A geometry field of an entity is stored in one PostgreSQL column of PostGIS's geography type. geography holds longitude and latitude on WGS84 and measures in metres, so nothing converts between the column and geo.distance. The other geospatial types follow the usual rule: native types where PostgreSQL has them, and a named column for each part of a value with several (Storage types).

A BoundingBox and a Circle aren't geography. A box's edges are parallels and meridians, which a geography polygon would turn into great circles, and a circle has no geography form at all.

You never write or change these columns yourself; Jadpo's migrations make them (Migrations). Here a bakery's position and its delivery patches are stored, each as it was given:

use baking/types { BakeryName }/// A shop that bakes and delivers.entity Bakery {    id: Uuid    name: BakeryName    spot: Point    indexes: {        by_spot: {            fields: [spot]            kind: IndexKind.geographic  // the map service's own lookups        }    }}

use baking/entities { Bakery }use baking/types { PatchName }/// An area one bakery delivers to.entity DeliveryPatch {    id: Uuid    bakery_id: Bakery.id {        on_delete: OnDelete.cascade    }    name: PatchName    outline: Polygon}

use baking/entities { Bakery }use baking/types { BakeryName }test "a bakery's spot reads back as it was given" {    var spot: Point = Point {        latitude: 51.5072        longitude: -0.1276        altitude: 35metre    }    var new_bakery: Bakery = open_bakery(BakeryName("Crumbs"), spot)    var stored_bakery: Bakery = attempt Bakery.get(new_bakery.id)    assert stored_bakery.spot == spot}

The initial migration's SQL for Bakery creates PostGIS first, in the same transaction as the table:

CREATE EXTENSION IF NOT EXISTS "postgis";CREATE TABLE "bakery" (    "id" uuid NOT NULL,    "name" text NOT NULL,    "spot" geography NOT NULL,    CONSTRAINT "bakery_pkey" PRIMARY KEY ("id"));ALTER TABLE "bakery" ADD CONSTRAINT "bakery_spot_check" CHECK (ST_SRID("spot") = 4326 AND GeometryType("spot") = 'POINT');CREATE INDEX "bakery_by_spot_idx" ON "bakery" USING gist ("spot");

PostGIS, only when something needs it

The first migration that adds a geometry column or a geometry index starts with CREATE EXTENSION IF NOT EXISTS postgis;, in the migration's transaction like the rest. An application with no geometry column or index never creates the extension and never needs PostGIS on its server, as an application with no entity needs no database (Data). A store with no PostGIS says what happens when the server can't create it.

One geography column, with a check

The column is geography with no type of its own, and a check constraint says which geometry it holds. A typed column such as geography(Point, 4326) would refuse some points: a Point's altitude may be none or present, value by value, and geography(Point, 4326) and even geography(Geometry, 4326) refuse a point with an altitude ("Geometry has Z dimension but column does not", verified). An untyped column takes both. The check refuses a line in a point's column, and a point in another coordinate system, such as ETRS89's SRID=4258 (both verified).

An altitude is stored as the third coordinate. GeometryType gives POINT for a point with an altitude (verified, PostGIS 3.4.2), so the same check holds for both.

The check is added after the table, as ALTER TABLE ... ADD CONSTRAINT "<table>_<column>_check", as Jadpo adds every check constraint. A Geometry's check lists the six types with IN, and a circle's centre has a point's check. A box's sides aren't checked in the database, like every compound type's rules, and a circle's radius column is commented as a Length in metres.

Stored exactly

PostGIS keeps each coordinate as a double. A coordinate held as the shortest decimal of a double comes back unchanged; one with more digits than a double holds doesn't (51.50720000001234567 came back 51.50720000001235, verified). Jadpo holds every coordinate as the shortest decimal of a double (Precision and determinism), as it holds a Vector's items as single-precision floats, so every coordinate comes back unchanged. An altitude has 6 decimal places, so it comes back unchanged up to a million kilometres (15 significant digits).

Stored as given, read back as stored

A ring keeps the order its points were given, clockwise or not, as PostGIS keeps it (verified). Only GeoJSON winds rings by the right-hand rule. Each coordinate read back is the shortest decimal of its double, held to its range, and an altitude is that decimal rounded half to even to the micrometre.

Another program may have written the row, so a polygon read back is checked as a polygon read from GeoJSON is: closed rings of four points or more, and rings_valid. A polygon that fails, or a geometry of another kind than the field's, is a fault that names the rule, never the value.

How a geometry travels to the database

A geometry is sent as a shape of positions, each coordinate the double its decimal is, so the doubles keep their exact bits. The PostgreSQL driver sends it in PostGIS's extended WKB (EWKB), a binary form, with SRID 4326 and a third coordinate where the positions have altitudes. It sends no type number, so the server takes the column's own type, as pgvector's values are sent. It reads back the EWKB of geography_send. When the compiler writes a geometry into a statement, it writes EWKT, each coordinate the shortest decimal of its double. WKB is used only inside the driver (Interop).

Indexes

You don't declare the indexes your queries need: the compiler derives them (Indexes), and a special kind of index takes a kind: option. For geometry fields it derives GiST indexes, the kind PostGIS searches shapes with:

A GiST index holds one geometry column. A query that also filters on other fields uses their ordinary indexes beside it, and PostgreSQL combines them. ST_DWithin, ST_Covers and the box test each use these indexes (verified): the first two through && on the geography's box, then the exact test.

• Derived indexes. A field gets one index however many queries test it, and none where an index you declared as geographic already holds the field. When the name is taken, the index is named _gist_idx instead. The box index records its cast to geometry in its method.
• Declared indexes. For lookups made from outside the application, an indexes: entry may be a block of fields: and kind:. kind: IndexKind.geographic is a GiST index with geography's own operator class, on exactly one geometry field (one of the six or Geometry); anything else is refused as type.setting_value. A setting a block doesn't have is type.unknown_setting, a block without fields: is data.field_list_required, and the same fields and kind twice is data.duplicate_rule. IndexKind has geographic and hnsw, the vector index from AI. A plain index on a geometry field stays a plain one.

A store with no PostGIS

An application with a geometry field needs PostGIS on its PostgreSQL server. What happens on each kind of store:

So the compiler refuses what it can see, a geo. condition on a store that has no spatial queries, and jadpo migrate apply refuses what only the server knows. Nothing waits for the first query to fail.

jadpo migrate apply finds out whether its role may create the extension by trying. Before anything runs, it creates each extension that a pending migration needs and the database lacks, inside a savepoint, and rolls it back. Whether a role may create an extension that isn't trusted depends on the server and the provider's roles: Amazon RDS's rds_superuser and Neon's neon_superuser aren't PostgreSQL superusers (assumption from their documentation). Once the database has the extension, the migration's IF NOT EXISTS needs nothing more. The same holds for pgvector.

What jadpo migrate apply prints when its role can't create PostGIS:

error: `main` has no `postgis` extension, and its role can't create it  reason: `postgis` is PostGIS, which keeps the project's geometries in `geography` columns; there is no fallback, since a geometry kept any other way would be measured and compared differently. The project's migrations create it, and its database server offers it, but the role applying migrations isn't allowed to create it, so nothing was applied. PostGIS isn't a trusted extension: a superuser, or the provider's own admin role, creates it.  next: Have a role allowed to create it run `CREATE EXTENSION postgis` once in this database, then run the command again (human)  rule: migration.extension_missing  help: https://jadpo.dev/docs/errors/migration.extension_missing