# 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](storage-types.md)). 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. | Field type | PostgreSQL | |---|---| | `Point`, `LineString`, `Polygon`, `MultiPoint`, `MultiLineString`, `MultiPolygon` | `geography`, with `CHECK (ST_SRID(x) = 4326 AND GeometryType(x) = 'POINT')` naming its own type | | `Geometry` | `geography`, the check allowing any of the six | | `BoundingBox` | four `numeric` columns, `_south`, `_west`, `_north`, `_east` | | `Circle` | `_centre geography`, checked as a `Point`'s, and `_radius numeric(38, 6)` in metres | | `Latitude`, `Longitude`, `Bearing`, `Crs`, `Geohash`, `H3Cell`, `MapTile`, `EastingNorthing` | as [Geospatial types](geospatial-types.md) has them: `numeric`, `text`, or a column per part | You never write or change these columns yourself; Jadpo's migrations make them ([Migrations](migrations.md)). Here a bakery's position and its delivery patches are stored, each as it was given: ```jadpo 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 } } } ``` ```jadpo 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 } ``` ```jadpo 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: ```sql 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](data.md)). [A store with no PostGIS](geospatial-storage.md#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 "__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](precision-and-determinism.md)), 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](geospatial-interop.md)). ## Indexes You don't declare the indexes your queries need: the compiler derives them ([Indexes](indexes.md)), and a special kind of index takes a `kind:` option. For geometry fields it derives GiST indexes, the kind PostGIS searches shapes with: | Needed by | Index | |---|---| | `geo.within`, `geo.contains`, `geo.intersects` or `within(distance:)` on a field | `USING gist ("")`, named `
__idx`, partial for `visible:` as every derived index is | | `geo.within(field, box)` with a `BoundingBox` | `USING gist ((""::geometry))`, named `
__box_idx` | | A lookup made from outside the application | `indexes: { by_spot: { fields: [spot], kind: IndexKind.geographic } }` | 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](ai.md). 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: | Store | What happens | |---|---| | Any store, in an application with no geometry column or index | Nothing: PostGIS is never needed | | PostgreSQL with PostGIS available | The first migration with a geometry column or index creates the extension; nothing to configure. Neon, Supabase, Amazon RDS and Google Cloud SQL all offer PostGIS (assumption from their documentation, not tested here) | | PostgreSQL without it, or a role that can't create it | `jadpo migrate apply` stops before changing anything, with `migration.extension_missing`. The error names the extension and says that a role allowed to create it runs `CREATE EXTENSION postgis` once. PostGIS isn't a trusted extension, so creating it needs a superuser or the provider's own admin role (verified). The application already refuses to start against a database whose migrations aren't applied ([Migrations](migrations.md)), which covers the rest | | Redis ([Entities in Redis](queries-on-entities-in-redis.md)), and D1 when it arrives | A geometry field is kept in the entity's JSON as its GeoJSON, as a Redis entity keeps all its fields. Any `geo.` condition or order is the compile error that names PostgreSQL, as for any query a Redis entity can't serve | 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: ```text 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 ```