GIS/Spatial Support

LeanXcale’s GIS support and LeanXcale’s high performance over GIS data is based a search algorithm based on Geohashingh. Geohash is a public domain geocode system invented in 2008 by Gustavo Niemeyer and G.M. Morton that encodes a geographic location into a short string of letters and digits. Geohashes offer properties like arbitrary precision and the possibility of gradually removing characters from the end of the code to reduce its size with a gradual lose of precision. LeanXcale leverages this gradual precision to perform ST geometric and geographic operations over GIS data following the Geohash property that lies in nearby places will often present similar prefixes. In particular, LeanXcale’s geometry predicates and operators exercise this property.

In this section, we will follow the following conventions:

In the “C” (for “compatibility”) column, “o” indicates that the function implements the OpenGIS Simple Features Implementation Specification for SQL, version 1.2.1, while “p” indicates that the function is a PostGIS extension to OpenGIS.

1. Geohash Indexes

LeanXcale can speed up queries over geospatial data by using Geohash indexing. Geohash is an encoding geographic location on Base 32 where the even bits represent the longitude precision and the odd bits represent the latitude precision. Geohash has some interesting properties so from an envelop you can narrow down the bins in which you have to look for geometries.

More information can be found in the following links:

The geohash index can be the primary key of a table or a secondary. In general the primary key performs better and is the preferred way to define a geom table.

To create a table with geohash you need the following syntax:

  • Create a table whose primary key is a Geohash key. This will create a hidden geohash field in your table that will be used as primary key

CREATE TABLE
	geohashedcountries(
		 countrycode VARCHAR,
		 geomLocation VARCHAR,
		 name VARCHAR,
	PRIMARY GEOHASH KEY(geomLocation));

  • Create the table, this time with a secondary index for the geohash. Again a hidden geohash table will be used:

CREATE TABLE
	geohashedcountries(
		 countrycode VARCHAR,
		 geomLocation VARCHAR,
		 name VARCHAR,
	PRIMARY KEY(name),
	GEOHASH(geomLocation));

Instead of a geometry field which can be an arbitrary geometry, you may use two fields that will be used as latitude and longitude:

CREATE TABLE geohashedcitypoints(
  citycode VARCHAR,
  latitude DOUBLE,
  longitude DOUBLE,
  name VARCHAR,
  PRIMARY GEOHASH KEY(latitude, longitude));

When running a geo query, LeanXcale’s query optimizer will automatically detect the Geohash index and use it to narrow down your search:

EXPLAIN PLAN FOR (
	SELECT name FROM geohashedcountries
	WHERE ST_Contains(
		ST_Buffer(ST_MakePoint(cast(-3.67 as double), cast(40.42 as double)), cast(0.5 as double)),
			ST_GeomFromText(geomLocation)
  ));

PLAN=EnumerableCalc(expr#0..2=[{inputs}], expr#3=[-3.67:DECIMAL(19, 0)], expr#4=[40.42:DECIMAL(19, 0)], expr#5=[ST_MAKEPOINT($t3, $t4)], expr#6=[0.5:DOUBLE], expr#7=[ST_BUFFER($t5, $t6)], expr#8=[ST_GEOMFROMTEXT($t0)], expr#9=[ST_CONTAINS($t7, $t8)], NAME=[$t1], $condition=[$t9])
            KiviPKTableScanRel(table=[[leanxcale, ADHOC, GEOHASHEDCOUNTRIES, filter:ST_GH_in($2, ST_GH_minMaxRegions(ST_BUFFER(ST_MAKEPOINT(-3.67:DOUBLE(19, 0), 40.42:DOUBLE(19, 0)), 0.5:DOUBLE))), project:[1, 2, 3]]], project=

Most frequently, all the functionality for geohashing is done internally so there is no need to explicitly define any condition for geohash. Anyway, the following functions are supported to query through the Geohash indexes:

ResultType Function Description

boolean

ST_GH_in(String geohash, String[][] minMaxRegions)

Check if a string is between any region of the list

String[][]

ST_GH_minMaxRegions(Geom geom)

Calculate the min max geohash regions(bins) the geom belongs to

String[]

ST_GH_encodeGeom(Geom geom)

Encodes the given geometry into a list of geoHash that contains it. The first item in the list would be the center

String

ST_GH_minGH(String geohash)

Generate the min value in geohash region

String

ST_GH_maxGH(String geohash)

Generate the max value in geohash region

String

ST_GH_encodeLatLon(double latitude, double longitude, int precision)

Encodes the given latitude and longitude into a geohash with the indicated precision (number of characters==number of 5bits groups)

String

ST_GH_encodeLatLon(double latitude, double longitude)

Encodes the given latitude and longitude into a geohash. Default precision is 12

String

ST_GH_encodeFromBinaryString(String binaryString)

Encodes the given binary string into a geohash.

String

ST_GH_encodeFromLong(long hashVal, int precision)

Encodes the given long into a geohash.

Double[]

ST_GH_decode(String geoHash)

Decodes the given geohash into a latitude and longitude

long

ST_GH_decodeToLong(String geoHash)

Decodes the given geoHash into bits as long value

String

ST_GH_decodeToBinaryString(String geoHash)

Decodes the given geoHash into a binary string

String

ST_GH_adjacent(String geoHash)

Returns the 8 adjacent hashes in the following order: N, NE, E, SE, S, SW, W, NW

String

ST_GH_regionsWithinDistance(double latitude, double longitude, double distance)

Returns the hashes that include the points within the specified distamce

String[]

ST_GH_regionsWithinDistance(Geom geom, Object distance)

Returns the hashes that include the points within the specified distance from the Geom.

String

ST_GH_northernNeighbour(String geoHash)

Returns the immediate neighbouring hash to the north

String

ST_GH_southernNeighbour(String geoHash)

Returns the immediate neighbouring hash to the south

String

ST_GH_westernNeighbour(String geoHash)

Returns the immediate neighbouring hash to the west

String

ST_GH_easternNeighbour(String geoHash)

Returns the immediate neighbouring hash to the east

Double[]

ST_GH_boundingBox(String geoHash)

Return the list of geohash limits for: min Latitude, min Longitude, max Latitude, max Longitude

2. Geometry Conversion Functions (2D)

C Operator syntax Description

p

ST_AsText(geom)

Alias for ST_AsWKT

o

ST_AsWKT(geom)

Converts geom → WKT

o

ST_GeomFromText(wkt [, srid ])

Returns a specified GEOMETRY value from WKT representation

o

ST_LineFromText(wkt [, srid ])

Converts WKT → LINESTRING

o

ST_MLineFromText(wkt [, srid ])

Converts WKT → MULTILINESTRING

o

ST_MPointFromText(wkt [, srid ])

Converts WKT → MULTIPOINT

o

ST_MPolyFromText(wkt [, srid ])

Converts WKT → MULTIPOLYGON

o

ST_PointFromText(wkt [, srid ])

Converts WKT → POINT

o

ST_PolyFromText(wkt [, srid ])

Converts WKT → POLYGON

3. Geometry Creation Functions (2D)

C Operator syntax Description

o

ST_MakeLine(point1 [, point ]*)

Creates a line-string from the given POINTs (or MULTIPOINTs)

p

ST_MakePoint(x, y [, z ])

Alias for ST_Point

o

ST_Point(x, y [, z ])

Constructs a point from two or three coordinates

4. Geometry Properties (2D)

C Operator syntax Description

o

ST_Boundary(geom [, srid ])

Returns the boundary of geom

o

ST_Distance(geom1, geom2)

Returns the distance between geom1 and geom2

o

ST_GeometryType(geom)

Returns the type of geom

o

ST_GeometryTypeCode(geom)

Returns the OGC SFS type code of geom

o

ST_Envelope(geom [, srid ])

Returns the envelope of geom (which may be a GEOMETRYCOLLECTION) as a GEOMETRY

o

ST_X(geom)

Returns the x-value of the first coordinate of geom

o

ST_Y(geom)

Returns the y-value of the first coordinate of geom

5. Geometry Properties (3D)

C Operator syntax Description

p

ST_Is3D(s)

Returns whether geom has at least one z-coordinate

o

ST_Z(geom)

Returns the z-value of the first coordinate of geom

6. Geometry Predicates

C Operator syntax Description

o

ST_Contains(geom1, geom2)

Returns whether geom1 contains geom2

p

ST_ContainsProperly(geom1, geom2)

Returns whether geom1 contains geom2 but does not intersect its boundary

o

ST_Crosses(geom1, geom2)

Returns whether geom1 crosses geom2

o

ST_Disjoint(geom1, geom2)

Returns whether geom1 and geom2 are disjoint

p

ST_DWithin(geom1, geom2, distance)

Returns whether geom1 and geom are within distance of one another

o

ST_EnvelopesIntersect(geom1, geom2)

Returns whether the envelope of geom1 intersects the envelope of geom2

o

ST_Equals(geom1, geom2)

Returns whether geom1 equals geom2

o

ST_Intersects(geom1, geom2)

Returns whether geom1 intersects geom2

o

ST_Overlaps(geom1, geom2)

Returns whether geom1 overlaps geom2

o

ST_Touches(geom1, geom2)

Returns whether geom1 touches geom2

o

ST_Within(geom1, geom2)

Returns whether geom1 is within geom2

7. Geometry Operators (2D)

The following functions combine 2D geometries.

C Operator syntax Description

o

ST_Buffer(geom, distance [, quadSegs | style ])

Computes a buffer around geom

o

ST_Union(geom1, geom2)

Computes the union of geom1 and geom2

o

ST_Union(geomCollection)

Computes the union of the geometries in geomCollection

See also: the ST_Union aggregate function.

8. Geometry Projection Functions

C Operator syntax Description

o

ST_SetSRID(geom, srid)

Returns a copy of geom with a new SRID

o

ST_Transform(geom, srid)

Transforms geom from one coordinate reference system (CRS) to the CRS specified by srid