Querying your data

The GeoSpock database enables you to run SQL queries on your ingested data, and has optimized a subset of SQL functions that are used frequently in the analysis of geo-spatial data. These optimized functions use the indexes generated when your source input data was ingested.

For the best performance and to make sure that you are taking advantage of the benefits of the indexed data, you should follow the guidelines for creating optimized queries. Refer to Using the GeoSpock DB Presto connector for more information.

Auto-scaling SQL cluster behavior

As you run queries, the GeoSpock database automatically adds worker machines to your SQL cluster as required. You should be aware that the GeoSpock database will not use the new SQL machines for queries that are already in the queue at time the scaling up happens. The GeoSpock database scales down the SQL cluster once it has completed all the queries in the queue.

The number of machines in the SQL cluster never falls below the minimum count value (supplied when deploying the cluster) and never rises above the maximum count value (also supplied when deploying).

Optimized SQL functions

Optimized functions include:

  • ST_Within()
  • ST_Distance()
  • line_locate_point()
  • ST_Intersects()

Custom GeoSpock functions include:

  • GS_Distance_Within()
  • gs_great_circle_distance_within()

See GeoSpock database optimized SQL functions

Accessing the GeoSpock database

The are a number of ways that you can access the GeoSpock database by:

Geotemporal functions

In your data analysis, to find events that occur within a specific time range or at a certain time, use the GeoSpock database's optimized time queries by specifying a:

  • single time window
  • time interval

See Using geotemporal queries

Geometry functions

As part of your data analysis, you may want to focus on a specific region or postcode area to answer specific questions about your dataset. The GeoSpock database provides the following optimized geometry functions:

  • Simple bounding box
  • Points within/intersects with a geometry shape
  • Line locate point
  • Recorded geometries intersect with a query geometry shape

See Using geometry functions

Using User-Defined Functions (UDFs)

You may wish to write your own custom functions and deploy them with the SQL cluster. The GeoSpock DB Presto connector can then use your UDFs in a SQL query.

See Deploying and using UDFs