Geometry

There are two ways to hold shapes here, and the first is the route in.

Declare the store. DEFINE GEO gives you a place whose records are places, with the geometry field and the spatial index already agreeing with each other. Nothing has to exist first.

Or put a geometry in a table you already have. A field of an ordinary record holds a shape just as well, and that shape is not going anywhere: it is the right one when the geometry is one aspect of a record among several — a delivery with a destination is still a delivery.

Everything about the shapes themselves — how one is written, what is refused, and the eight predicates that ask about them — is the same in both, and is below the store.

A geo store you declare

When the places are the data, declare the store as one:

DEFINE NAMESPACE atlas;
USE NAMESPACE atlas;
DEFINE DATABASE world;
USE DATABASE world;
DEFINE GEO places;
CREATE places:'paris' = { geometry: geometry { type: 'Point', coordinates: [2.35, 48.85] }, name: 'Paris' };
SELECT name FROM places;
INFO FOR GEO places;

One word, and no clause. It stands for three statements:

DEFINE COLLECTION places;
DEFINE FIELD geometry ON places TYPE geometry REQUIRED;
DEFINE INDEX geometry ON places FIELDS geometry SPATIAL;

A geometry field with no spatial index makes every place query a scan; a spatial index with no declared field indexes nothing; and neither without REQUIRED admits a record with no geometry at all — legal in a table, and not a record a store of places can answer for.

The store does not narrow the shape it holds, and that is a decision rather than an omission. DEFINE VECTOR must declare a width because nothing else refuses a row of the wrong shape: a wrong-width vector is not an error, it is infinitely far from everything, which is a plausible ordering rather than a complaint. Geometry has no such silence — a read that orders by distance from a point refuses a record that is not one, and says so. So a region is a place too, and a table of them is a geo store:

CREATE places:'ward' = { geometry: geometry { type: 'Polygon', coordinates: [[[2, 48], [3, 48], [3, 49], [2, 49], [2, 48]]] }, name: 'a ward' };
REBUILD INDEX geometry ON places;

What the store reports

The rebuild is what produces a figure, and the two records above are enough for one: the ward reaches the point and the point reaches the ward. INFO FOR GEO reports the store's field, its index, and what refining that index's candidates last cost:

{"name": "places", "field": "geometry", "index": "geometry",
 "refinement": {"relation": "meets", "refinement": 100, "fragmentation": 100,
                "entries": 2, "reached": 2, "admitted": 2, "sample": 2,
                "records": 2}}

A spatial index answers exactly — but it does not filter exactly. It filters by bounding box, and a box is not a geometry, so a read offers candidates and the predicate above it decides. refinement is the percentage of records offered per record kept: at 100 the boxes wasted nothing, and a large number means the index is doing work the predicate throws away. fragmentation is entries read per record reached — the separate complaint that one record with an awkward shape, a river or a road or a border, sits in many cells at once.

How finely a query is covered is fixed rather than tuned per store: sixteen cells per query box, the budget a sweep over a skewed corpus at three query sizes chose (from 0.26.0-beta; the measurement is recorded beside the engine). The budget moves only what the read costs, never which records it offers: a coarse covering reads many more index entries, a fine one issues many more range scans, and sixteen sat lowest or within a few percent of it at every size measured.

relation says which query the figures answer for, and it is not decoration. The measurement asks meets — "what is in this box" — because that is the relation whose candidate set is largest and therefore the one that exposes a loose covering. A store you only ever read with geo::within refines a smaller set, at a cost this figure describes only as an upper bound. Read without the label, refinement looks like a property of the index; it is a property of the index under one relation.

Both are none when there is nothing to divide by, and the whole refinement object is none when nothing was measured at all. Absence is never a zero: a zero would read as a filter that wastes nothing, which is the opposite of what an unmeasured store means.

The figure is taken by a build, from the store's own places used as queries, so a store filled by writes since its last build reports the figure from that build. REBUILD INDEX geometry ON places is how a current one is obtained — the same contract a vector store's recall has.

Removing the store

DROP GEO places;

The word that made the store removes it, and takes its collection with it: the records a declared store holds are part of the structure rather than something depending on it. What no drop in this language does is reach further — it removes its own definition and refuses while anything still points at it. It refuses a table that is not a geo store rather than dropping it, because the two words name different things even where they would remove the same rows — and a DROP GEO that quietly removed an ordinary table would be a typo with the blast radius of a table.

Shapes in a table of your own

The other shape: a geometry in a field of an ordinary record. A shape is written in the language, with the word geometry and a GeoJSON body:

DEFINE COLLECTION shapes;
CREATE shapes:1 = { s: geometry { type: 'Point', coordinates: [2.35, 48.85] } };
CREATE shapes = { s: geometry { type: 'LineString', coordinates: [[0, 0], [1, 1]] } };
CREATE shapes = { s: geometry { type: 'Polygon', coordinates: [[[0, 0], [4, 0], [4, 4], [0, 4], [0, 0]]] } };

Coordinates are [longitude, latitude], in that order, in degrees. Seven types are accepted and no others: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon and GeometryCollection.

geometry is a contextual marker rather than a reserved word, so it is still available as a table name and as a field name:

DEFINE COLLECTION geometry;
CREATE geometry = { note: 'a table may be called that' };
CREATE shapes = { geometry: 'a word, not a shape' };

A shape is a literal

CREATE shapes:10 = { s: geometry { type: 'Point', coordinates: [$east, 0] } };

Refused — ComputedGeometry. A shape written in a statement is written out; a shape that has to be computed arrives as a bound parameter instead. Validation happens once, where the value is written, rather than partly at parse time and partly later.

Every refusal is a wrong answer that did not happen

This is the part of the store where being strict pays for itself, because geospatial mistakes do not raise errors on their own — a wrong shape produces a plausible wrong answer and nothing anywhere reports a fault.

CREATE shapes:10 = { s: geometry { type: 'Circle', coordinates: [0, 0] } };

MalformedGeometry — not one of the seven.

CREATE shapes:10 = { s: geometry { type: 'Point', coordinates: [1, 2, 3] } };

MalformedGeometry — a third coordinate is refused rather than dropped. An altitude silently discarded is a record that no longer means what it was written to mean.

CREATE shapes:10 = { s: geometry { type: 'Polygon', coordinates: [[[0, 0], [1, 0], [1, 1]]] } };

GeometryRefused — the ring does not close.

CREATE shapes:10 = { s: geometry { type: 'Point', coordinates: [181, 0] } };

GeometryRefused — that is not a place on the sphere.

CREATE shapes:10 = { s: geometry { type: 'MultiPolygon', coordinates: [
  [[[0, 0], [6, 0], [6, 6], [0, 6], [0, 0]]],
  [[[4, 4], [9, 4], [9, 9], [4, 9], [4, 4]]]] } };

GeometryRefused — two members of a multi-polygon may not share ground. Their combined area would depend on whether the store counted the overlap once or twice, and both answers look reasonable.

The date line

CREATE shapes:10 = { s: geometry { type: 'Polygon', coordinates: [
  [[179, 0], [-179, 0], [-179, 1], [179, 1], [179, 0]]] } };

GeometryRefused, and this is the sharpest example of the rule. Written that way, a two-degree strip across the date line and a band around the rest of the planet are the same six numbers. They are each other's complement, so a store that picked one would be wrong half the time in a way no reader could see. It keeps neither.

Both intended shapes are perfectly sayable. The short way is two shapes meeting at the meridian, which is what RFC 7946 asks producers to do:

CREATE shapes = { s: geometry { type: 'MultiPolygon', coordinates: [
  [[[179, 0], [180, 0], [180, 1], [179, 1], [179, 0]]],
  [[[-180, 0], [-179, 0], [-179, 1], [-180, 1], [-180, 0]]]] } };

The long way is one vertex in the middle, so that no edge reaches half the world and the reading is unique:

CREATE shapes = { s: geometry { type: 'Polygon', coordinates: [
  [[179, 0], [0, 0], [-179, 0], [-179, 1], [0, 1], [179, 1], [179, 0]]] } };

Asking about shapes

DEFINE COLLECTION places;
CREATE places = { name: 'middle', at: geometry { type: 'Point', coordinates: [5, 5] } };
CREATE places = { name: 'edge',   at: geometry { type: 'Point', coordinates: [2, 5] } };
CREATE places = { name: 'away',   at: geometry { type: 'Point', coordinates: [20, 20] } };
SELECT * FROM places WHERE geo::intersects(at, geometry { type: 'Polygon', coordinates: [[[2, 2], [8, 2], [8, 8], [2, 8], [2, 2]]] });

Two rows — middle is inside and edge is on the boundary, which counts. The eight predicates differ exactly at boundaries, which is where every geospatial bug lives:

The other two geo:: functions are measurements rather than predicates: geo::area(a) answers with the ground a shape covers, which only a shape with an interior has, and geo::distance is below. They answer with a number, so they belong in a projection or a comparison rather than alone in a WHERE — a filter needs something that is true or false, and ConditionNotBoolean is what it says when it is given a length.

The within / covered_by pair is worth twenty seconds now rather than an afternoon later:

SELECT * FROM places WHERE geo::covered_by(at, geometry { type: 'Polygon', coordinates: [[[2, 2], [8, 2], [8, 8], [2, 8], [2, 2]]] });
SELECT * FROM places WHERE geo::within(at, geometry { type: 'Polygon', coordinates: [[[2, 2], [8, 2], [8, 8], [2, 8], [2, 2]]] });

Two rows, then one. edge sits on the boundary: covered by the square, not within it.

geo::equals is about ground and not about spelling — the same square written with a redundant vertex is the same square:

DEFINE COLLECTION zones;
CREATE zones = { z: geometry { type: 'Polygon', coordinates: [[[0, 0], [10, 0], [10, 10], [0, 10], [0, 0]]] } };
CREATE zones = { z: geometry { type: 'Polygon', coordinates: [[[0, 0], [5, 0], [10, 0], [10, 10], [0, 10], [0, 0]]] } };
CREATE zones = { z: geometry { type: 'Polygon', coordinates: [[[0, 0], [9, 0], [9, 9], [0, 9], [0, 0]]] } };
SELECT * FROM zones WHERE geo::equals(z, geometry { type: 'Polygon', coordinates: [[[0, 0], [10, 0], [10, 10], [0, 10], [0, 0]]] });

Two rows. And two positions never touch, because a position's interior is the position, so meeting at all is meeting inside:

SELECT * FROM places WHERE geo::touches(at, geometry { type: 'Point', coordinates: [5, 5] });

No rows.

Distance is in metres

DEFINE COLLECTION marks;
CREATE marks = { m: geometry { type: 'Point', coordinates: [0, 0] } };
CREATE marks = { m: geometry { type: 'Point', coordinates: [0, 0.9] } };
SELECT * FROM marks WHERE geo::distance(m, geometry { type: 'Point', coordinates: [0, 1] }) > 100000;

Metres, on the sphere — never degrees. A degree of latitude is a hundred and ten kilometres, so the two readings differ by five orders of magnitude and a filter written at a hundred kilometres cannot pass by accident.

geo::distance from a position to a path or an area is the distance to the shape's nearest point — and zero when the shape covers the position:

SELECT geo::distance(z, geometry { type: 'Point', coordinates: [12, 5] }) AS far FROM zones;

About 221 km to each of the two ten-degree squares — two degrees of longitude east of their edge at five degrees north — and about 332 km to the nine-degree one. A position inside a shape is at zero, by the same exact rule geo::covers uses, so a point on the boundary is inside too:

SELECT geo::distance(z, geometry { type: 'Point', coordinates: [5, 5] }) AS far FROM zones;

Zero, three times. The nearest point is found by a search that never drops a piece of an edge that could hold a nearer one; edges are the straight lines in longitude and latitude that the coordinates say.

Between two shapes that are both larger than a position, the distance is the least one between a point of each — and zero when they share a point, by the same exact rule geo::intersects answers with:

SELECT geo::distance(z, geometry { type: 'Polygon', coordinates: [[[20, 0], [21, 0], [21, 1], [20, 1], [20, 0]]] }) AS far FROM zones;

About 1 113 km to each of the two ten-degree squares — ten degrees of longitude along the equator from their east edge to the small square's west edge — and about 1 224 km to the nine-degree one. Move the small square so it overlaps them, and all three answer zero.

It is found the way the distance to a position is: the edges of one shape are cut into pieces, a piece is dropped once a floor proves nothing on it can be nearer, and the pieces left are settled by a local search. So the figure is a distance between two actual points, never between corners chosen in advance, and it is at most a millimetre above the true least distance. Very long edges passing far apart close to a pole are where that local search is weakest.

Absence narrows, a wrong type refuses

CREATE places = { name: 'nowhere' };
SELECT * FROM places WHERE geo::intersects(at, geometry { type: 'Point', coordinates: [5, 5] });

One row. A record with no shape is simply not in the answer — the same rule as everywhere else in the language: an absence narrows a read instead of failing it. But a value that is present and is not a shape is a mistake, and says so:

CREATE places = { name: 'wrong', at: 'over there' };
SELECT * FROM places WHERE geo::intersects(at, geometry { type: 'Point', coordinates: [5, 5] });

WrongArgument.

The spatial index

DEFINE COLLECTION sites;
CREATE sites = { at: geometry { type: 'Point', coordinates: [2.35, 48.85] } };
CREATE sites = { at: geometry { type: 'Point', coordinates: [-73.99, 40.75] } };
DEFINE INDEX by_at ON sites FIELDS at SPATIAL;
SELECT * FROM sites WHERE geo::intersects(at, geometry { type: 'Point', coordinates: [2.35, 48.85] });

One row, with the index — and one row without it. That is the property that matters and the reason a spatial index is safe in a way a vector index is not: the index proposes candidates and the exact predicate decides, so it changes the cost and never the answer.

Nearest-first is an order and a bound, needing no new syntax:

DEFINE COLLECTION stops;
CREATE stops:'gate' = { at: geometry { type: 'Point', coordinates: [2.35, 48.85] } };
CREATE stops:'bridge' = { at: geometry { type: 'Point', coordinates: [2.41, 48.79] } };
CREATE stops:'mill' = { at: geometry { type: 'Point', coordinates: [2.09, 48.93] } };
DEFINE INDEX by_stop ON stops FIELDS at SPATIAL;
SELECT * FROM stops ORDER BY geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) LIMIT 3;

Either argument may be the field — geo::within(Q, at) and geo::contains(at, Q) are the same question written from two ends, and both are served by the index. A store that understood only one phrasing would answer both correctly and read the whole table for one of them.

The walk takes records in the order of a floor — the distance to a record's stored box, which nothing inside it can be nearer than — and measures each one it takes with the statement's own geo::distance. So a path or an area is ranked by its nearest point, exactly as the scan would rank it, and under a WHERE the walk tests the whole condition on each record it takes and keeps going until it holds as many as the read asked for:

CREATE stops:'park' = { at: geometry { type: 'Polygon', coordinates: [[[2.36, 48.84], [2.37, 48.84], [2.37, 48.85], [2.36, 48.85], [2.36, 48.84]]] }, open: true };
UPDATE stops:'bridge' SET open = true;
SELECT id FROM stops WHERE open = true ORDER BY geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) LIMIT 1;

'park': {  }
(1 record(s), via ordered)

The park, about 730 m away at its nearest corner, and not gate, which sits on the point but has no open. The plan is access: 'ordered', shape: 'nearest' — the walk served it, area, condition and all.

The walk stops once the next floor is beyond the worst record it holds. It gives the read to the scan, with the note fell-back, when it would have to take more than 4 096 records, when the index holds anything near the query position's antipode (where a distance stops converging), or when it runs out before the bound is filled — the same answer, at the scan's cost. Ordering by the distance from a shape larger than a position is still answered by the scan.

Within a distance

"Everything within ten kilometres" is an ordinary condition, and the spatial index serves it:

SELECT * FROM stops WHERE geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) < 10000;

gate and bridge — the mill is about twenty-one kilometres out. The read widens the query's box by the distance, far enough in latitude and in longitude that every position within it is inside, reads the records whose boxes meet the widened box, and tests the distance on each. The widening decides only what the read costs. A widening that reaches a pole or crosses ±180 covers every longitude, which is wider than needed and never narrower.

EXPLAIN SELECT * FROM stops WHERE geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) < 10000;

Access index, shape region. < and <= are served, written either way round and with the place as either argument. A lower bound — geo::distance(at, $here) > 10000 — is the outside of a disc, which no box holds, and stays an exact scan.

A name and a place together

"Cafés called blue near me" needs no new syntax. Filter by name within a radius and order by distance, and the name is served by the search index while the radius is tested on what it finds:

DEFINE ANALYZER words FILTERS lowercase;
DEFINE TABLE cafes SCHEMALESS;
DEFINE FIELD name ON cafes TYPE string ANALYZER words;
DEFINE INDEX by_name ON cafes FIELDS name SEARCH;
CREATE cafes:1 = { name: 'Blue Cafe', at: geometry { type: 'Point', coordinates: [2.35, 48.86] } };
CREATE cafes:2 = { name: 'Blue Door Cafe', at: geometry { type: 'Point', coordinates: [2.60, 48.95] } };
CREATE cafes:3 = { name: 'Red Cafe', at: geometry { type: 'Point', coordinates: [2.35, 48.85] } };
SELECT name FROM cafes WHERE name MATCHES 'blue' AND geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) < 5000
 ORDER BY geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }) LIMIT 10;

Blue Cafe alone. Or rank by both at once, with the two orders fused by rank:

SELECT name FROM cafes WHERE name MATCHES 'cafe'
 ORDER BY FUSE (search::score(name, 'blue cafe') DESC, geo::distance(at, geometry { type: 'Point', coordinates: [2.35, 48.85] }))
 LIMIT 3;

Counting by cell

A map zoomed out does not want every point; it wants how many fall in each part of the view. geo::cell(position, level) answers the spatial index's own cell holding the position at that level — 0 is the whole world, each level splits every cell in four, 32 is the finest — as a polygon, so it groups and draws:

SELECT geo::cell(at, 2) AS cell, count(*) AS n FROM sites GROUP BY geo::cell(at, 2);

Two cells, one site each: Paris and New York fall in different quarters of a level-2 map. GROUP BY takes the expression itself, not the alias.

The cells of one level tile the world with nothing counted twice. They divide longitude and latitude evenly, so they are not equal in area — a cell near a pole covers less ground than one at the equator. A count is for drawing; a density is count(*) / geo::area(geo::cell(at, 2)). Only a position has a cell:

SELECT geo::cell(z, 2) AS cell FROM zones;

WrongArgument — a path or an area spans several cells. So is a level that is not a whole number from 0 to 32.

An index is one kind and not two:

DEFINE INDEX bad_at ON sites FIELDS at SPATIAL UNIQUE;

UnexpectedToken.