Using geometry functions
The GeoSpock database provides the following optimized geometry expressions:
- Simple bounding boxes
- Points within a geometry shape
- Line locate point
- Recorded geometries intersect with a query geometry shape
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
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:
An example query using
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,
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:
||Calculates the shortest distance between two spatial objects.The distance is measured in degrees.|
||Calculates where on a line (
In addition, the GeoSpock database includes and optimizes the following custom functions for defining geofences: