Skip to main content

Writing SQL Queries

Every time a vehicle moves, its data updates, or a task, area, or instruction changes, Hivekit adds a row to a table that stores all updates for a given realm.

You can use this data to create reports or to visualize it in charts and widgets. Both reports and charts are driven by a concept we call a “Data Source”.

A data source lets you query data from the realm table, select fields, filters and aggregators or just write your own SQL query. Datasources can contain placeholders that make them reusable. Say you’d want two reports, one on overspeeding Firetrucks, one on overspeeding police cars, you’d create one datasources with “Give me all vehicles where speed is greater than the speed limit and category=categoryID” and then enter different category ids for both.

Database & Table Structure​

Hivekit stores realm-data in a TimescaleDB Hypertable. This means its a normal PostgreSQL table with additional functions and optimisations that make working with Timeseries Data faster and more efficient. Hivekit also uses PostGIS to store the location of objects and the shapes of areas. PostGIS makes it possible to run complex spatial queries.

Columns​

The Realm Table has the following columns

id (text, not null)​

The unique id of the vehicle, object, area, task or instruction.

type (text, not null)​

The type of the entity, can be "obj" for vehicles, "are" for areas, "tsk" for tasks or "ins" for instructions. You might also find some other types that Hivekit uses internally to store e.g. the intermediate state of an instruction execution "aks" = ("active set"), a log line "log" or a system call "sys".

time (timestamp with timezone, not null)​

The time of the entry. This can either be the time that's written as part of an object's location to indicitate that the object was at this location at the given time, or the time the event was processed, e.g. for a data change or area update.

label (text)​

The text label for the given object.

location (geometry)​

The location the object or task is at. his is a PostGIS Point Geometry consisting of a latitude and longitude - which makes it great to run queries against it. If you want to get the raw latitude and longitude, use ST_X() and ST_Y() respectively. E.g.

SELECT
ST_X(location) as longitude,
ST_Y(location) as latitude
FROM {{realmTable}}

accuracy (numeric)​

The accuracy of the location in meters

speed (numeric)​

The speed of the object, in meters per second. (To get from m/s to kph, multiply by 3.6)

heading (numeric)​

The direction the object is moving in degrees, relative to true north (0° is north, 90° is east, 180° is south, 270° is west)

altitude (numeric)​

The altitude of the object in meters above mean sea level

altitude_accuracy (numeric)​

The accuracy of the altitude in meters

data (jsonb)​

Objects, Areas and Instructions can all have associated data. This data is stored as jsonb. Say your data looks like this:

{
  "engineTemperature": 90,
  "driver": {
    "firstname": "Bob"
  },
  "tirePressures": [30, 29, 28, 30]
}

You can query it like this:

select 
    (data->>'engineTemperature')::numeric as engine_temp,
    data->'driver'->>'firstname' as driver_firstname,
    (data->'tirePressures'->>2)::numeric as tire_top_left_pressure
from {{realmTable}}

task_ids (text[])​

Objects can have zero or more tasks assigned to them in a given order. These are stored in the task_ids array. You can select individual ids e.g. like this:

  SELECT 
    task_ids[1] 
  FROM 
    {{realmTable}} 
  WHERE array_length(task_ids,1) IS NOT NULL;

shape_name (text)​

The type of shape for a given area. Can be "rec" (rectangle), "cir" (circle) or "pol" (polygon)

shape_data (geometry)​

A PostGIS polygon, outlining the shape of an area. There's a few important things to note:

  • Postgis Polygons support areas with holes, but Hivekit does not. So all areas are stored as three layers of arrays, e.g.

    [
      [
        [0,0],
        [0,1],
        [1,1],
        [1,0],
        [0,0]
      ]
    ]

    however, there will only ever be one array in the outer array, and the last point will always be the same as the first point.

    • Postgis doesn't support circles. So instead, circles are stored as rectangles with the center as the center of the rectangle and the radius as the width and height of the rectangle. You can get the center of the circle and the radius in meters like this:
    select 
      ST_X(ST_Centroid(shape_data)) as center_longitude,
      ST_Y(ST_Centroid(shape_data)) as center_latitude,
      ST_DISTANCE(
        ST_Transform(ST_PointN(ST_ExteriorRing(shape_data), 1),3857),
        ST_Transform(ST_PointN(ST_ExteriorRing(shape_data), 2),3857) 
      ) / 2 as radius
    from {{realmTable}}
    where 
      type='are' and 
      shape_name='cir';

instruction_code (text)​

The code of an instruction

description (text)​

The description of a task

status (text)​

The status of a task, can be one of the following:

  • "$hkt_not_started"
  • "$hkt_in_progress"
  • "$hkt_completed"
  • "$hkt_failed"
  • "$hkt_canceled"
  • "$hkt_paused"
  • "$hkt_blocked"

target_id (text)​

The id of an object that this task is relating to. Please note: this is not the id of the object that the task is assigned to, but the object that the task is about. E.g. if you have a task "Follow vehicle with id 123", the target_id would be 123.

steps (jsonb)​

The serialized steps of a task

deleted (bool)​

Hivekit doesn't delete data for individual entries, it just marks it as deleted. This is the column that stores that information.

Placeholders​

There are certain placeholders that you can use in your queries to make them reusable. These are:

{{realmTable}} required​

This is the name of the table that stores the data for the realm. This allows you to use the same datasource accross realms.

SELECT * FROM {{realmTable}}

{{areaConstraint}} optional​

Users can highlight an area to restrict reports or chart output to results from that area. You can use {{areaConstraint}} to filter the results to only include objects that are in the highlighted area. E.g.

SELECT * FROM {{realmTable}} WHERE 
  {{areaConstraint}} AND
  type='obj'

{{timeConstraint}} optional​

Users can also select a time range to restrict reports or chart output to results from that time range. You can use {{timeConstraint}} to filter the results to only include objects that are between the min and max time. This can be used by itself or in conjunction with other filters, e.g.

SELECT * FROM {{realmTable}} WHERE 
  {{areaConstraint}} AND
  {{timeConstraint}} AND
  type='obj'

{{yourVariable}} optional​

You can also define your own placeholders. These can be used to inject variables into your queries. E.g. to only select objects from a given category, you'd specify:

SELECT id, label
FROM {{realmTable}}
WHERE 
  type='obj' AND
  data->>'$hkt_category' = '{{categoryId}}'