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.

Simple 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.

This example query includes a 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

Points within or intersects with 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
  • MULTILINE
  • POLYGON
  • MULTIPOLYGON

Note that you cannot use these optimized functions with multiple geometries including an AND or an OR in the SQL. If you need to specify multiple POLYGON geometries, use MULTIPOLYGON instead.

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

An example query using MULTIPOLYGON and st_intersects:

SELECT * FROM geospock.default.largetable AS large
WHERE st_intersects(st_point(large.longitude, large.latitude), st_geometryfromtext('MULTIPOLYGON(((10 10, 10 20, 20 10)), ((-10 10, -10 20, -20 10)), ((10 -10, 10 -20, 20 -10)), ((-10 -10, -10 -20, -20 -10)))'))

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.smalltable AS small ON st_within(st_point(large.latitude, large.longitude), st_geometryfromtext(small.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 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