Using geotemporal queries

During ingest, the GeoSpock database converts timestamps to the UTC time zone for storage, so by default timestamps returned by queries are in UTC. However, any timezone adjustments you make in your queries will be respected.

In your data analysis, you may be interested in the events that occur within a specific time range or at a certain time. You can use the GeoSpock database's optimized time queries by specifying a:

  • single time window in the SQL query, for example:
SELECT * FROM geospock.default.largetable 
WHERE logtime BETWEEN timestamp '2019-02-01 00:00:00' AND timestamp '2019-03-01 00:00:00'
  • time interval around a specific time point set per POI, for example:
SELECT large.* FROM geospock.default.largetable large 
JOIN geospock.default.smalltable small 
ON ST_Distance(ST_Point(large.longitude, large.latitude), ST_Point(small.longitude, small.latitude)) < 0.001 
AND small.poitime - large.eventtime < interval '1' day;

Using geotemporal queries with geometries in SQL queries

You can use these geotemporal queries in conjunction with any of the GeoSpock database's geometry optimizations; see Using geometry functions.