Spatial

Calcite is aiming to implementOpenGIS Simple Features Implementation Specification for SQL,version 1.2.1,a standard implemented by spatial databases such asPostGISand H2GIS.

We also aim to add optimizer support forspatial indexesand other forms of query optimization.

Introduction

A spatial database is a database that is optimized for storing and query datathat represents objects defined in a geometric space.

Calcite’s support for spatial data includes:

  • A GEOMETRY data type andsub-types including POINT, LINESTRINGand POLYGON
  • Spatial functions (prefixed ST_;we have implemented about 35 of the 150 in the OpenGIS specification)

and will at some point also include query rewrites to use spatial indexes.

Enabling spatial support

Though the GEOMETRY data type is built-in, the functions are not enabled bydefault. You need to add fun=spatial to the JDBC connect string to enablethe functions. For example, sqlline:

  1. $ ./sqlline
  2. > !connect jdbc:calcite:fun=spatial "sa" ""
  3. SELECT ST_PointFromText('POINT(-71.064544 42.28787)');
  4. +-------------------------------+
  5. | EXPR$0                        |
  6. +-------------------------------+
  7. | {"x":-71.064544,"y":42.28787} |
  8. +-------------------------------+
  9. 1 row selected (0.323 seconds)

Acknowledgements

Calcite’s OpenGIS implementation uses theEsri geometry API. Thanks for thehelp we received from their community.

While developing this feature, we made extensive use of thePostGIS documentation and tests,and the H2GIS documentation, and consulted both as reference implementationswhen the specification wasn’t clear. Thank you to these awesome projects.