Apache.Calcite.Geography 2.0.1-pre.190

Apache.Calcite.Geography

Apache.Calcite.Geography gives Apache Calcite a set of CLR_ST_GEOG_* operators that read coordinates as WGS84 and answer in metres.

Calcite has GEOMETRY and no GEOGRAPHY. Its spatial library is planar JTS over an unprojected coordinate system, answering in the units of that system. The stores that speak WGS84 — PostGIS geography, BigQuery, Snowflake, Elasticsearch geo_shape, MongoDB's 2dsphere — are geodesic, and answer in metres. The two disagree about what identically-named functions mean, and the disagreement is not a scale factor: the ratio varies with latitude and with bearing, so no conversion of a result recovers it and no transformation of the inputs does either. An ordering is not merely wrong, it is differently ordered.

This package is optional and nothing else in the repository depends on it. Reference it when you have geodesic data.

Targets .NET 8, and is verified on .NET 8 and .NET 10.

Install

dotnet add package Apache.Calcite.Geography

Using it

The operators are declared in a SqlOperatorTable, which a host chains onto whatever it already has:

using Apache.Calcite.Geography.Sql;
using org.apache.calcite.sql.fun;
using org.apache.calcite.sql.util;

var operatorTable = SqlOperatorTables.chain(
    SqlStdOperatorTable.instance(),
    GeographyOperatorTable.Instance());

var config = Frameworks.newConfigBuilder()
    .defaultSchema(schema)
    .operatorTable(operatorTable)
    .build();

A column holds a geography by declaring GeographyTypes.Of(typeFactory) as its type:

using Apache.Calcite.Geography.Rel.Type;

typeFactory.builder()
    .add("ID", typeFactory.createSqlType(SqlTypeName.INTEGER))
    .add("LOCATION", GeographyTypes.Of(typeFactory))
    .build();

and the values in it are ordinary JTS Geometry objects — the same class Calcite's own spatial library carries, because it is the same type.

SELECT ID
FROM PLACES
WHERE CLR_ST_GEOG_DWITHIN(LOCATION, CLR_ST_GEOG_GEOMFROMTEXT('POINT(-0.1278 51.5074)'), 5000.0)

The operators can be reached two ways. A host driving its own planner chains GeographyOperatorTable.Instance(), as above. Anyone else registers them on a schema:

using Apache.Calcite.Geography.Schema;

GeographySchema.AddTo(rootSchema);

which works through the stock jdbc:calcite: driver with nothing chained and nothing subclassed — an adapter can call it on the schema it builds, and its functions arrive with its tables. Registering on the root schema makes every operator visible unqualified everywhere on the connection, views in other schemas included. Do one or the other, not both: a name found twice resolves to whichever the lookup reaches first.

The names

CLR_ is this repository's namespace, carried by every SQL function it adds to Calcite, and it is what makes the collision argument structural rather than empirical. A connection chains the operator table its fun property names ahead of the catalog reader, and overload resolution takes the first candidate whose arity fits — so a name Calcite also used would shadow these silently, and only for hosts that set fun. That is not hypothetical here the way it is for full text: Calcite's spatial surface is already 144 names and still growing out of PostGIS and H2GIS, and ST_GEOG_ is a plausible thing for it to add. No Calcite library will ever ship a CLR_ function.

It also survives these being supplanted upstream. If Calcite grows a geodesic reading of its own, ours does not collide with it, is not shadowed by it, and can be mapped onto it or deprecated deliberately.

ST_GEOG_ inside the namespace is unchanged: ST_ is the family every spatial function has carried since OpenGIS Simple Feature Access, and GEOG_ is what says the geodesic reading is meant. These were ST_GEOG_* before, and the rename is a breaking change taken deliberately while this package is unpublished.

There is no GEOGRAPHY type

A geography and a geometry are one type — Calcite's GEOMETRY, over org.locationtech.jts.geom.Geometry. What says a value is to be read geodesically is the name of the operator applied to it, and nothing else.

Nothing refuses a mixture. ST_DISTANCE(LOCATION, LOCATION) over geodesic coordinates answers in degrees, and CLR_ST_GEOG_DISTANCE over projected ones answers metres as though they were degrees. Both validate, both run. Worse than either, an expression can be half of each: CLR_ST_GEOG_DISTANCE(ST_BUFFER(LOCATION, 0.1), OTHER) buffers in degrees and then measures in metres.

There is no run-time guard underneath, either. The SRID is a tag on the JTS instance, and Calcite's own spatial functions mostly drop it off a geometry they derive — ST_Buffer, ST_Centroid, ST_Envelope and ST_Intersection all return zero from an operand stamped 4326 (SridPropagationTests pins this). So a CLR_ST_GEOG_ operator cannot refuse a value for want of a stamp: it may be geodesic and merely have passed through one of Calcite's functions.

This was a choice, and the alternative was measured. A JavaType subclass answering SqlTypeName.OTHER gives the whole guarantee back — Calcite's planar functions then refuse a geography at validation. It cannot be registered on a schema: CalciteCatalogReader.toOp builds a fixed-parameter operand checker for every schema function, that checker's parameter types go through the assignment rules, and SqlTypeAssignmentRule has no entry for OTHER — so routine resolution throws AssertionError: No assign rules for OTHER defined rather than rejecting. Nor can the type be added to SqlTypeName, which is a closed Java enum and the key to that table and several others.

Since a schema is the only way an adapter can bring its functions with it, and bringing them is the point, the type gave way to the registration.

What is here

The names mirror Calcite's ST_* one for one, under this repository's CLR_ namespace — ST_DISTANCE becomes CLR_ST_GEOG_DISTANCE. This is the first increment; Calcite's spatial library is about 130 names and every one of them needs a declaration, because Calcite's own read the plane.

Constructors — how a geography comes into existence in a query. The return type is GEOMETRY, and the result carries SRID 4326, though see above for how little that is worth once one of Calcite's own functions has touched it.

Both arities are Calcite's. The SRID a caller may name has to be 4326 and anything else is refused rather than ignored — a geography is WGS84 and there is no second reference system to reproject into, which is the same reason ST_SETSRID and ST_TRANSFORM have no counterpart at all.

The crossing between the two readings. Free at run time, since both sides are the same JTS object; explicit, because losing the geodesic reading should be something you wrote down.

Relations.

WITHIN and CONTAINS are the DE-9IM relations JTS means by the words, not plain containment: a point on a polygon's boundary is covered by it and not within it.

Measurements, in metres and square metres rather than in degrees, on the WGS84 ellipsoid.

The two halves run on different engines, and deliberately. The predicates are S2's, which is a sphere; the measurements are GeographicLib's, which is the ellipsoid. S2 answers which points of two shapes are closest and GeographicLib answers how far apart they are. Measuring on the sphere was wrong by up to 0.56% against a live geodesic service — enough that an adapter could not recheck a predicate it had pushed down, since the recheck discards rows the store correctly returned. A degree of longitude at the equator is 111319.49 m and a degree of latitude is 110574.39 m; that these differ is the whole of what says the answers are ellipsoidal.

An area shows the difference plainly: a one-degree box at the equator is about 12,309 square kilometres, and it is a little larger than the region between the parallels through its corners, because its northern edge is a geodesic that runs north of the parallel joining its two northern corners.

Reading a geography. Accessors, which read or rearrange coordinates without interpreting the space between them, so each is a delegation to the very JTS method Calcite's ST_* of that name calls.

CLR_ST_GEOG_XMIN and its four relatives are computed structurally and are wrong in the usual way for anything crossing the antimeridian, where the least longitude of a shape spanning the seam is not its westmost point. That is inherited from the planar reading rather than introduced here.

Building one from parts.

Editing. Rearranging coordinates without interpreting the space between them.

Every geography one of these hands back is stamped WGS84, which is a small divergence: ST_FORCE2D answers something with an SRID of zero, because the transformer underneath builds through a geometry factory that carries none across. Calcite has no reference system to keep there and this package does.

Every format, both ways. A reader and a writer for each, plus a typed reader for each shape — CLR_ST_GEOG_POINTFROMTEXT, CLR_ST_GEOG_LINEFROMTEXT, CLR_ST_GEOG_POLYFROMTEXT, their MULTI counterparts and the three FROMWKB forms — each answering NULL for text that names a different shape.

Each answers NULL for a NULL argument.

What these mean is what Calcite means, with the plane swapped for the sphere: ST_Within is geom1.within(geom2), so CLR_ST_GEOG_WITHIN is the DE-9IM relation and not containment — a point on a polygon's boundary is not within it. GeographyDifferentialTests runs every one of them against Calcite's over shapes small enough that the two models must agree, which is what holds them to that.

The engine

Google's S2, consumed as a Java library the same way calcite-core is. It models the Earth as a sphere of radius 6,371,010 m and joins two vertices with a great-circle arc.

One model rather than two, deliberately. An ellipsoidal distance from GeographicLib next to a spherical containment from S2 would put two readings of the same coordinates in one plan; the sphere costs a few tenths of a percent against an ellipsoidal distance, and it is what BigQuery and Snowflake compute in. The choice is reversible.

The difference this makes is not a scale factor. The northern edge of POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) runs between two points at ten degrees north: as a straight line in longitude and latitude it stays on that parallel, and as a great circle it reaches about 10° 2′ at the midpoint. A point between the two is inside one polygon and outside the other — not a different distance, a different answer.

At the antimeridian the two readings are exact inversions. POLYGON((179 -1, -179 -1, -179 1, 179 1, 179 -1)) is a two-degree box straddling the seam on the sphere, and in the plane it is the 358-degree band that is everything except that box: every point is inside one and outside the other. The same seam turns a fifth of a degree into 359.8, and near a pole the shortest way between opposite meridians runs over the pole rather than 180 degrees around.

The pairwise operations are quadratic in the vertex counts. S2 has an indexed form of these queries and this does not use it yet.

The relations are this package's own rather than a library's. S2BooleanOperation would settle CLR_ST_GEOG_WITHIN by construction, and it cannot be had: the S2 published to Maven Central is the 2021 release, which does not have it, and the current source is compiled to Java 11, which IKVM does not read. What stands in for it is the size of the oracle — GeographyDifferentialTests over hand-written shapes, and GeographyRandomDifferentialTests over thirty thousand generated pairs a run, both answered by Calcite. Four defects in the relations were found by the generated half after the hand-written half was green.

What is not here

• The rest of the mapping in the design issue — the constructed-geometry group (buffer, the boolean overlay set, hulls, simplification, triangulation, grids), the point-returning measurements, CLR_ST_GEOG_RELATE, and the aggregates and table functions, which need machinery this package does not have.
• CLR_ST_GEOG_CROSSES, CLR_ST_GEOG_TOUCHES, CLR_ST_GEOG_OVERLAPS and CLR_ST_GEOG_CONTAINSPROPERLY. All four turn on whether the interiors of two geographies meet, and where a line comes back and touches itself that question has no answer without a node graph over both geometries: the place is the end of the whole line and the middle of one of its own edges at once, so it is boundary by the rule that counts ends and interior by the rule that reads the curve. Both rules were tried and each is wrong somewhere. That is the same machinery S2BooleanOperation would have brought.
• ST_SETSRID and ST_TRANSFORM have no counterpart, and will not: geography is WGS84 by definition and there is no second reference system to reproject into. Nor do the planar affine transforms — ST_ROTATE, ST_SCALE, ST_TRANSLATE — or precision reduction, which snaps to a planar grid.
• Rechecking a pushed-down predicate. These implementations must not be wired into a filter-recheck path until their agreement with each live store has been measured at the boundaries — a point on a polygon edge, an antimeridian crossing, the poles, a distance sitting on a threshold. A recheck that disagrees discards rows the store returned.