Apache.Calcite.Geography 2.0.1-pre.280

Apache.Calcite.Geography

Apache.Calcite.Geography adds CLR_ST_GEOG_* functions to Apache Calcite that read coordinates as WGS84 longitude and latitude and answer in metres.

Calcite's own ST_* functions are planar JTS: they treat longitude and latitude as x and y on a flat plane and answer in degrees. That is not a scale factor away from the geodesic answer — how far a degree reaches depends on latitude and direction, so planar distances do not even rank places in the same order, and a shape crossing the antimeridian means something entirely different on the plane. If your data is WGS84 and you want the answers PostGIS geography, BigQuery, Snowflake or a 2dsphere index would give, use these functions.

The package is optional; nothing else in this repository depends on it. It targets .NET 8.

Install

dotnet add package Apache.Calcite.Geography

Registering the functions

There are two ways to make the functions visible to SQL. Use one, not both: a name found twice resolves to whichever the lookup reaches first.

Chain the operator table, if you configure the validator yourself (for example through Frameworks):

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

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

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

Or declare them on a schema, which needs no validator configuration and so works through Calcite's jdbc:calcite: driver, or from an adapter that wants its functions to arrive with its tables:

using Apache.Calcite.Geography.Schema;

GeographySchema.AddTo(rootSchema);

Functions added to the root schema are visible unqualified everywhere on the connection, including in views defined in other schemas.

Declaring a geography column

There is no GEOGRAPHY SQL type. A geography is Calcite's GEOMETRY, and its values are ordinary JTS org.locationtech.jts.geom.Geometry objects. Declare a column with GeographyTypes.Of:

using Apache.Calcite.Geography.Rel.Type;

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

Coordinates are (x, y) = (longitude, latitude) in degrees, the order WKT and GeoJSON use:

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

Because a geography and a geometry share one type, nothing stops you mixing the two readings. The name of the function is the only thing that says a value is read geodesically. ST_DISTANCE(LOCATION, OTHER) validates and answers in degrees, and CLR_ST_GEOG_DISTANCE(ST_BUFFER(LOCATION, 0.1), OTHER) buffers by 0.1 degrees and then measures in metres. The SRID cannot be relied on either: the constructors here stamp 4326, but Calcite's own spatial functions generally return results with an SRID of 0. CLR_ST_GEOG_ASGEOM and CLR_ST_GEOM_ASGEOG convert nothing; use them to make the point where a query changes reading explicit.

Conventions

• Every function returns NULL when any argument is NULL.
• An argument shown as DOUBLE accepts any numeric type, so 5000 and 5000.0 both work without a CAST.
• Distances are in metres and areas in square metres, measured on the WGS84 ellipsoid.
• Functions that take an SRID accept only 4326 and throw for anything else, since no reprojection takes place. EWKT and EWKB input naming an SRID other than 0 or 4326 is refused the same way.
• Where a function mirrors a Calcite ST_* function, it means the same thing with the plane replaced by the Earth. CLR_ST_GEOG_WITHIN is JTS within, the DE-9IM relation, so a point on a polygon's boundary is covered by the polygon but not within it.

Functions

Reading and writing

Typed readers return NULL when the input describes a different kind of shape: CLR_ST_GEOG_POINTFROMTEXT, CLR_ST_GEOG_LINEFROMTEXT, CLR_ST_GEOG_POLYFROMTEXT, CLR_ST_GEOG_MPOINTFROMTEXT, CLR_ST_GEOG_MLINEFROMTEXT, CLR_ST_GEOG_MPOLYFROMTEXT, CLR_ST_GEOG_POINTFROMWKB, CLR_ST_GEOG_LINEFROMWKB and CLR_ST_GEOG_POLYFROMWKB, each with an optional SRID.

Building

Relations

All return BOOLEAN. INTERSECTS, WITHIN, CONTAINS, COVERS, COVEREDBY, EQUALS and ENVELOPESINTERSECT are FALSE when either argument is empty, and DISJOINT is then TRUE; DWITHIN is TRUE for any non-negative distance, because the distance to an empty geography is 0.

Measurements

Deriving shapes

Every result is stamped with SRID 4326.

CLR_ST_GEOG_BUFFER is built rather than exact: it unions 32-sided rings drawn at exactly d metres around every vertex and around points spaced along each edge. The rings are inscribed, so the result lies slightly inside the true buffer, and for a shape that is long compared with d the spacing is widened to bound the work, which reduces accuracy.

Accessors and editing

These read or rearrange coordinates without interpreting the space between them, so each calls the Calcite ST_* function of the same name. Those in the editing group stamp their result with SRID 4326.

The bounds functions compare coordinates as numbers, so for a shape crossing the antimeridian XMIN is not its western edge; use CLR_ST_GEOG_ENVELOPE for that. The tolerance of CLR_ST_GEOG_REMOVEREPEATEDPOINTS is in degrees, not metres, because it is passed to Calcite unchanged. Typed accessors return NULL where Calcite's do: POINTN and INTERIORRING out of range, STARTPOINT of a polygon.

What is not provided

• CLR_ST_GEOG_CROSSES, TOUCHES, OVERLAPS and CONTAINSPROPERLY. Each depends on classifying where a line touches itself as boundary or interior, which needs a node graph over both shapes that is not available.
• CLR_ST_GEOG_RELATE, a two-argument union, and the spatial aggregates and table functions.
• CLR_ST_GEOG_SIMPLIFYPRESERVETOPOLOGY, since the simplification used does not guarantee what the JTS one does.
• ST_SETSRID and ST_TRANSFORM equivalents: a geography is always WGS84. Nor the planar affine transforms (ST_ROTATE, ST_SCALE, ST_TRANSLATE) or precision reduction.
• The overlay functions and CLR_ST_GEOG_SIMPLIFY work on polygons only.
• CLR_ST_GEOG_OFFSETCURVE takes two arguments; Calcite's third, a JTS buffer style, has no meaning here.

Any ST_* function without a CLR_ST_GEOG_ counterpart listed above is not provided.

How answers are computed

Topology — whether shapes meet, which contains which, and which pair of points is closest — is decided by Google's S2, which models the Earth as a sphere and joins vertices with great-circle arcs. Lengths, distances and areas are then measured on the WGS84 ellipsoid with GeographicLib. A spherical distance can differ from the ellipsoidal one by about half a percent; at the equator a degree of longitude is 111,319.49 m and a degree of latitude 110,574.39 m.

Edges are geodesics, not straight lines in degrees. The northern edge of POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) bows north of the 10° parallel, so a point just north of that parallel can be inside the geography and outside the planar polygon. POLYGON((179 -1, -179 -1, -179 1, 179 1, 179 -1)) is a two-degree box across the antimeridian here and a 358-degree band on the plane.

The pairwise operations compare every edge of one shape with every edge of the other, so their cost grows with the product of the vertex counts.

Results on a boundary — a point exactly on an edge, a distance exactly equal to a threshold — can differ from a particular store's. An adapter that pushes a predicate down should not re-evaluate it in process with these functions without first checking that they agree with that store at such cases, since a disagreeing recheck discards rows the store correctly returned.

Simplifying plans

GeographyRules.Program() is a rewrite pass to run ahead of your own program:

using Apache.Calcite.Geography.Rel.Rules;
using org.apache.calcite.rex;
using org.apache.calcite.tools;

var config = Frameworks.newConfigBuilder()
    .defaultSchema(schema)
    .executor(RexUtil.EXECUTOR)
    .programs(Programs.sequence(GeographyRules.Program(), Programs.standard()))
    .build();

Each rewrite replaces an expression with one of equal value, including under three-valued logic, so it applies in projections, filters and join conditions alike:

The last is not cheaper to evaluate in process; it gives an adapter a within-distance predicate a geodesic index can answer. GeographyRules.Simplify applies the same rewrites to a single expression, for an adapter that wants the canonical form before matching function names.

Run it as a separate pass, not as rules on a VolcanoPlanner: that planner compares row counts only, so it generally keeps the unrewritten expression.

Give the planner an executor. Constant folding — reducing CLR_ST_GEOG_GEOMFROMTEXT('POINT(0 0)') to a literal — is Calcite's own ReduceExpressionsRule, which silently does nothing without an executor. A jdbc:calcite: connection has one; a Frameworks configuration has one only if you set it as above. Without it a WKT literal is parsed once per row.

Null handling and symmetry are declared on the operators. The measurements, the relations and the IS*/count functions are declared to return NULL exactly when an argument is NULL, which lets Calcite's own simplifications, for example, turn a LEFT JOIN filtered on one of them into an INNER JOIN. DISTANCE, MAXDISTANCE, INTERSECTS, DISJOINT, EQUALS and ENVELOPESINTERSECT are declared symmetrical, so f(a, b) and f(b, a) are recognised as one expression. These declarations are on the operator objects of GeographyOperatorTable. A function resolved through a schema reaches the plan as an operator Calcite builds itself, without them; the rewrite pass puts this package's operators back, so on the schema route run the pass to get them.