Using geometry functions

The GeoSpock database provides the following optimized geometry expressions:

Note that all geometry queries should assume a 2D world with a minimum longitude of -180 degrees and a maximum longitude of 180 degrees. Refer to Queries with a geometry that crosses the anti-meridian for examples of adapting queries for this constraint.

The GeoSpock DB Presto connector has been optimized to run a subset of SQL functions. You can use other SQL functions in your queries but they may run slower. Refer to GeoSpock SQL optimized functions for the table summary of SQL geometry functions optimized in the GeoSpock database.

Simple bounding boxes

Using a single bounding box

To provide a simple bounding box for your query, you can provide a maximum and minimum longitude and latitude for the events (large) table. Defining a bounding box improves the performance of your query because if there is no bounding box defined, the GeoSpock database assumes you are querying the whole world.

These example queries include a single simple bounding box:

SELECT * FROM geospock.default.largetable AS large
WHERE large.longitude BETWEEN 146.567 AND 146.772 AND large.latitude BETWEEN 52.532 AND 52.845
SELECT * FROM geospock.default.largetable AS large
WHERE large.longitude > 146.567 AND large.longitude < 146.772 AND large.latitude > 52.532 AND large.latitude < 52.845

Using multiple bounding boxes

Using AND, OR and NOT you can specify multiple bounding boxes, for example:

SELECT * FROM geospock.default.largetable AS large
WHERE (large.longitude BETWEEN 146.567 AND 146.772 AND large.latitude BETWEEN 52.532 AND 52.845
OR large.longitude BETWEEN 147.227 AND 147.332 AND large.latitude BETWEEN 52.532 AND 52.845)
AND NOT (large.longitude BETWEEN 146.666 AND 146.672 AND large.latitude BETWEEN 52.832 AND 52.845)

Points within a geometry shape

In SQL queries using optimized functions, you can specify a single geometry using the WKT notation, including the following geometry types:

  • POINT
  • MULTIPOINT
  • LINESTRING
  • POLYGON

An example query using POLYGON and ST_Intersects:

SELECT * FROM geospock.default.largetable AS large
WHERE ST_Intersects(ST_Point(large.longitude, large.latitude), ST_Polygon('POLYGON((1 0, 1 5, 20 5, 20 0))'))

Geometries can also be read from a location-based dataset (POI table) that has a field for geometries, stored as a WKT string.

An example query using a field called geometry in the POI table:

SELECT * FROM geospock.default.largetable AS large 
JOIN geospock.default.poitable AS poi ON ST_Within(ST_Point(large.latitude, large.longitude), ST_GeometryFromText(poi.geometry));

Line locate point

The line locate point function returns all points that are closest to the line section specified in the query. This function assumes a 2D world, so points on the other side of the antimeridian from the line may not be returned. If no other geometry is specified (such as a bounding box or a geometry shape), this function queries the whole world.

In order for optimizations to be applied, the section of the line you want to query must be at one end of the line.

For example, the following query finds the points in the bounding box that are closer to the first 10% of the line than the remaining 90%:

SELECT * from geospock.default.largetable large 
WHERE line_locate_point(ST_LineFromText('LINESTRING(-50 33, -40 39, -40 40, -41 40)'), ST_Point(large.longitude, large.latitude)) < 0.1 
AND large.latitude BETWEEN 33.50 AND 33.60 AND large.longitude BETWEEN 135.10 AND 135.20

Recorded geometries intersect with a query geometry shape

For some queries, you may need to analyze the point where a recorded geometry (stored as e.g. a LINESTRING in a dataset) intersects with a geometry shape in a query. To optimize these queries, you should specify a bounding box or a bounding geometry for the points associated with each event, for example:

SELECT * from geospock.default.largetable large 
WHERE ST_Intersects(ST_LineFromText(large.linestring), ST_LineFromText('LINESTRING(-50 33, -40 39, -40 40, -41 40)') 
AND large.latitude BETWEEN 33.50 AND 33.60 AND large.longitude BETWEEN 135.10 AND 135.20

Queries with a geometry that crosses the anti-meridian

There is no support in the GeoSpock DB for queries that cross the anti-meridian (e.g. specifying a latitude > 180 degrees or < -180 degrees). The following query would have a bounding box that crosses the anti-meridian:

SELECT * FROM geospock.default.events 
WHERE longitude BETWEEN 170 AND 190
AND latitude BETWEEN 50 AND 60

Mapping 190 degrees to -170 degrees, this query could be modified as follows (which uses two POLYGON geometries to represent two boxes, one on each side of the anti-meridian):

SELECT * FROM geospock.default.events 
WHERE ST_Within(ST_Point(longitude, latitude), ST_GeometryFromText('POLYGON((170 50, 180 50, 180 60, 170 60, 170 50))'))
OR ST_Within(ST_Point(longitude, latitude), ST_GeometryFromText('POLYGON((-170 50, -180 50, -180 60, -170 60, -170 50)))'))

GeoSpock SQL optimized functions

The GeoSpock database optimizes queries using the following standard geospatial functions:

SQL function Returns Description
ST_Within(g1,g2) boolean Returns 1 (TRUE) or 0 (FALSE) to indicate whether the first geometry, g1, is spatially within a second geometry, g2.
ST_Distance(g1,g2) double Calculates the shortest distance between two spatial objects.The distance is measured in degrees.
line_locate_point(line, point) double Calculates where on a line (line) a point (point) lies or interpolates it onto the line, then returns the fraction of distance from the start point as a measurement. The first argument must be of type LineString using the WKT notation. The second argument must be of type Point using the WKT notation.
ST_Intersects(g1,g2) boolean Returns 1 (TRUE) if any part of two geometries intersect or touch at a single point.

In addition, the GeoSpock database includes and optimizes the following custom functions for defining geofences:

Custom function Returns Description
GS_Distance_Within(sg1,sg2,dist) boolean Returns 1 (TRUE) if the two spatial objects are within the specified distance of one another. Define each spatial object as a SphericalGeometry. Specify the distance in meters.
gs_great_circle_distance_within(lon1,lat1,lon2,lat2,dist) boolean Returns 1 (TRUE) if the two locations are within the specified distance of one another. A location is defined using latitude and longitude coordinates in decimal degrees (in WGS84). Specify the distance in meters.