Queries

You find records by where they are with a geo. call as a query's where: condition: the bakeries within six kilometres of a customer, the pins inside a map's view, the patches that deliver to a door. You can also list records nearest first inside a radius.

A query's condition must be one an index can serve: a field, named first, compared with a value (Filters). A GiST index serves each geo. condition, so they are allowed too, with the field first and the values after. Any other function of a field is still refused.

Here are the bakery's queries, on the bakeries and patches stored above:

use baking/entities { Bakery }use baking/types { BakeryCard, SearchReach }/// Bakeries near a customer, nearest first.query bakeries_near(doorstep: Point, max_trip: SearchReach) -> List<BakeryCard> {    from: Bakery    where: geo.within(Bakery.spot, doorstep, distance: max_trip)    order: [geo.distance(Bakery.spot, doorstep) asc]    limit: 20}

use baking/entities { Bakery }use baking/types { BakeryCard }/// The pins inside the map a person is looking at.query bakeries_in_view(view: BoundingBox) -> List<BakeryCard> {    from: Bakery    where: geo.within(Bakery.spot, view)    order: [Bakery.name asc]    limit: 200}

use baking/entities { DeliveryPatch }use baking/types { PatchCard }/// The patches that deliver to a customer's door.query patches_for(customer: Point) -> List<PatchCard> {    from: DeliveryPatch    where: geo.contains(DeliveryPatch.outline, customer)    order: [DeliveryPatch.name asc]    limit: 10}

The tests run on PostGIS. A customer on a patch's edge is delivered to by the query, as by geo.contains, and the bakery 5.3 km away is inside a six-kilometre trip and outside a five-kilometre one:

use baking/entities { Bakery, DeliveryPatch }use baking/types { BakeryCard, BakeryName, PatchName }use baking/actions { draw_patch, open_bakery }use baking/queries { bakeries_in_view, bakeries_near }test "bakeries near a door come nearest first, within the trip" {    var big_ben: Point = Point {        latitude: 51.5007        longitude: -0.1246        altitude: none    }    var crumbs: Bakery = open_bakery(        BakeryName("Crumbs"),        Point {            latitude: 51.5            longitude: -0.2            altitude: none        }    )    var loaf: Bakery = open_bakery(        BakeryName("Loaf"),        Point {            latitude: 51.5072            longitude: -0.1276            altitude: none        }    )    var in_reach: List<BakeryCard> = bakeries_near(big_ben, 6km)    assert text.join(list.map(in_reach, card -> card.name), ", ") == text.join(        [loaf.name, crumbs.name],        ", "    )    var within_five: List<BakeryCard> = bakeries_near(big_ben, 5km)    assert text.join(list.map(within_five, card -> card.name), ", ") == loaf.name}test "a customer on a patch's edge is delivered to" {    var crumbs: Bakery = open_bakery(        BakeryName("Crumbs"),        Point {            latitude: 51.5            longitude: -0.2            altitude: none        }    )    var outline: Polygon = Polygon {        outer: [            Point {                latitude: 51.49                longitude: -0.2                altitude: none            },            Point {                latitude: 51.49                longitude: -0.05                altitude: none            },            Point {                latitude: 51.54                longitude: -0.05                altitude: none            },            Point {                latitude: 51.54                longitude: -0.2                altitude: none            },            Point {                latitude: 51.49                longitude: -0.2                altitude: none            }        ]        holes: []    }    var westminster: DeliveryPatch = draw_patch(crumbs, PatchName("Westminster"), outline)    assert text.join(        list.map(patches_for(crumbs.spot), card -> card.name),        ", "    ) == westminster.name    var in_view: List<BakeryCard> = bakeries_in_view(geo.bounding_box(outline))    assert text.join(list.map(in_view, card -> card.name), ", ") == crumbs.name}

jadpo sql shows each query's statement, and under then: what runs after it. The box's envelopes and the number of rows to fetch are worked out as the read runs. Below are the statements and the GiST indexes derived for them. Bakery's declared by_spot index already serves spot, so the only index derived on spot is the box's:

-- bakeries_nearSELECT "id", "name", "spot", ST_Distance("spot", $1) AS "jadpo_measured"FROM "bakery"WHERE ST_DWithin("spot", $1, $2 + 0.001)ORDER BY "jadpo_measured", "id"LIMIT $3-- bakeries_in_viewSELECT "id", "name", "spot"FROM "bakery"WHERE ("spot"::geometry && ST_MakeEnvelope($3, $1, $4, $2, 4326) OR "spot"::geometry && ST_MakeEnvelope($5, $1, $6, $2, 4326))ORDER BY "name" COLLATE "C" ASC, "id" ASCLIMIT $7-- patches_for, its outline selected for the code to decideSELECT "id", "name", "outline"FROM "delivery_patch"WHERE ST_DWithin("outline", $1, 0.001)ORDER BY "name" COLLATE "C" ASC, "id" ASCLIMIT $2CREATE INDEX "bakery_spot_box_idx" ON "bakery" USING gist (("spot"::geometry));CREATE INDEX "delivery_patch_outline_idx" ON "delivery_patch" USING gist ("outline");

What each condition means

The index finds, the code decides

A query gives exactly the records that the code's own geo. operation keeps, comparing the field's stored value with the call's values. list.filter over every record with the same operation would give the same list.

PostGIS's own predicates can't be the condition. PostGIS decides in doubles with its own tolerances, and its ST_Covers on geography compares two polygons ring by ring, which parts from geo.contains in the cases under contains, within and intersects. So the statement uses the GiST index to find every record within a millimetre of what the condition says, and no record the code keeps is left out. The runtime then works the condition out again on each record found, with the same geo.within, geo.contains and geo.intersects that a body calls, so the interpreter and every compiled target decide alike. A point on an edge is in, and those cases give this page's answer.

A margin of a millimetre

The search is a millimetre wider than the condition. For a radius or a circle it is ST_DWithin(field, point, distance + 0.001). For within, contains and intersects it is ST_DWithin(field, shape, 0.001), since anything inside a shape, on its edge or touching it is no distance from it, whether PostGIS compares ring by ring or not.

PostGIS's distances are good to some nanometres, so a millimetre, some hundred thousand times its error, is far more than it can miss. It is also close enough that the search rarely finds a row the code turns down. The cases where ST_Covers parts from geo.contains are each within the margin: ST_DWithin is true where ST_Covers is false (verified).

Searching a box

A box's sides are parallels and meridians, so the box filter searches the point cast to geometry, whose && compares longitude and latitude directly. It has an index of its own.

The filter is always two envelopes, so the statement's text stays fixed. A box across the antimeridian searches from its west side to 180 and from -180 to its east side. A box with a side on the antimeridian also searches the line at the other end, -180 being 180. Any other box is searched twice as one envelope. The envelopes are worked out from the box's west and east sides when the read runs.

&& rounds to single precision, outwards. A point at a pole is in only a box of every longitude, which the code decides.

Nearest first inside a radius

order: [geo.distance(field, point) asc] lists records nearest first, and needs a geo.within(distance:) condition on the same field and point. The statement orders the rows inside the radius by ST_Distance, and the runtime sorts them again by geo.distance, rounded to the micrometre as code gives it, then by id. So two bakeries the same distance away come in id order, and the order is the code's to the last digit.

PostGIS's index-ordered <-> operator can't give that order, because it measures on a sphere: London to Paris is 343,530 m by <-> and 343,897 m by ST_Distance (verified). Ordering by it would disagree with geo.distance for two shops a few hundred metres apart in distance.

Only asc, and only in a List

For "nearest anywhere", use a large radius. Farthest first can't use the radius to limit anything, and a page by distance would need a cursor on a computed value, so both are refused, as query.order_direction and query.order_field, with reasons naming this page. geo.distance is the query's only order.

How many rows a query fetches

The statement asks for as many rows as the read keeps: a list's limit: as LIMIT $n, a page's size and one more, or every row for a query of one record found by a unique field.

It may have to ask again. The code may turn some rows down, or a row past those fetched could still be nearer than the last one kept: PostGIS's distance to it, less the margin, isn't past the code's distance to the last. Then it asks for four times as many, until it has enough or the rows run out. Each ask runs the whole statement again in the same transaction and keeps the last answer, so it reads one state of the table. A page's cursor comes from the rows the code kept. The margin is a millimetre, so a second ask is rare: it needs rows within a millimetre of the condition, or one of the cases where ST_Covers parts from geo.contains.

The result doesn't carry the distance

A result type picks fields (Reads and writes), and a distance isn't one. The action works the distance out with geo.distance, which gives the same metres the query ordered by.

Where a condition can go

A geo. condition is a where: line of its own, or joined to the others with and. An index finds the records a condition holds for, never those it doesn't, so a geo. condition can't be under not or or. Under or, the rows the code turns down would need the other side's answer, which the statement doesn't give. Its value is always given, so it isn't a where_present: line.

A record whose field is none meets no geo. condition. The values are parameters, or fields inside a parameter. within's distance: may also be a length written out, proved never negative as in code.

What each form takes

Each form takes what it takes in code:

• within(distance:) and a circle need a Point field, or the query is type.mismatch;
• a box needs a Point field, or the query is query.condition_unsupported;
• within takes a Polygon, a MultiPolygon, a BoundingBox or a Circle;
• contains and intersects take any geometry.

The messages cite the decisions on point-only distance and on the box filter. A BoundingBox or Circle field, kept in named columns, can't be tested.

Comparing geometries with ==

== on a geometry field compares exactly, as PostGIS's = on geography does: an altitude, a ring's winding and a line's direction all count (verified), as they do for Jadpo's own ==. You can't order by a geometry; that is still query.order_field.

Errors in a geo. query

These are the usual query rules, with reasons of their own:

• a geo. call with the field anywhere but first, a box on a field that isn't a Point, a BoundingBox or Circle field, or a condition under not or or is query.condition_unsupported;
• a value of the wrong kind is type.mismatch, and a distance that may be negative is type.argument_negative, as in code;
• an order by geo.distance without its radius, beside another order or in a page, and any other function of a field in order:, is query.order_field.

migration.extension_missing is the only rule of this page's own.