Using indexes

A database index improves the speed and efficiency of data retrieval, reducing the time it takes to provide a response to your query in the GeoSpock database. By default, the GeoSpock database's ingestion engine creates indexes for the following types of data:

  • time
  • location (latitude and longitude)
  • device ID

The Geospock database creates indexes whilst ingesting data. The indexes that the Geospock database creates depends upon the type of data which is being ingested and is determined by the data source description file used during the ingest.

The GeoSpock database supports datasets containing:

  • location-based data
  • events-based data

For each type of dataset, you must provide the details of the fields in the source data that describe either the event or the location, for example:

Event-based data

...
"event": [
    {
        "id": "longitude",
        "sourceFieldIndex": 1,
        "purpose": "LONGITUDE",
        "sqlType": "DOUBLE"
    },
    {
        "id": "latitude",
        "sourceFieldIndex": 2,
        "purpose": "LATITUDE",
        "sqlType": "DOUBLE"
    },
    {
        "id": "date",
        "sourceFieldIndex": 7,
        "sqlType": "TIMESTAMP",
        "sourceFieldFormat": "TIMESTAMP_ISO_8601"
    },
    {
        "id": "taxi-id",
        "sourceFieldIndex": 8,
        "purpose": "SOURCE_ID",
        "sqlType": "VARCHAR"
    }
    ],
...

Location-based data

...
"location": [
        {
            "id": "longitude",
            "sourceFieldIndex": 1,
            "purpose": "LONGITUDE",
            "sqlType": "DOUBLE"
        },
        {
            "id": "latitude",
            "sourceFieldIndex": 2,
            "purpose": "LATITUDE",
            "sqlType": "DOUBLE"
        },
        {
            "id": "organization_id",
            "sourceFieldIndex": 8,
            "purpose": "SOURCE_ID",
            "sqlType": "VARCHAR"
        }
    ],
...

The Geospock database uses the fields in the event and location blocks to generate the indexes for the dataset.

See The data source description structure for more information about these the fields that get used for creating indexes.