SQL Database Info

Table Naming

When loading data into the database, messages will be sorted into SQL tables determined by the message type and month. The names of these tables follow the following format, where {MONTH} indicates the table month in the format YYYYMM

ais_MONTH_static
ais_MONTH_dynamic

Some additional tables containing computed data may be created depending on the indexes used. For example, an aggregate of vessel static data by month, or a virtual table used as a covering index. For more info, see SQLite R* Tree Indexes

static_MONTH_aggregate
rtree_MONTH_dynamic

Additional tables are also included for storing data not directly derived from AIS message reports.

rtree_polygons
coarsetype_ref
hashmap

Custom SQL Queries

Instead of using the included run_qry and gen_qry functions for querying the database, SQL code can be written manually. As messages are separated into tables by message type and month, queries spanning multiple message types or multiple months should use UNIONs and JOINs to combine results as is appropriate.

For AIS position reports, the R* tree virtual tables should be queried instead of the default tables. Query performance can be significantly improved using the R* tree index when restricting output to a narrow range of MMSIs, timestamps, longitudes, and latitudes, although querying a wide range will not yield much benefit. If custom indexes are required for specific manual queries, these should be defined on message tables 1_2_3, 5, 18, and 24 directly instead of upon the virtual tables.

Timestamps are stored as epoch-minutes in the database. For convenience, import the dt_2_epoch and epoch_2_dt functions for conversion between datetime format when querying the database manually.

Schema

See aisdb_sql/ to see the SQL code used to create database tables and associated queries.