Using geometry functions

The GeoSpock database provides the following optimized geometry functions:

To add a time range to your queries as well, refer to Using geotemporal queries.

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.

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)))'))