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, which {YYYYMM} indicates the table year and month in the format YYYYMM.
ais_{YYYYMM}_static # table with static AIS messages
ais_{YYYYMM}_dynamic # table with dynamic AIS message
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 is used as a covering index.
static_{YYYYMM}_aggregate # table of aggregated static vessel data
Additional tables are also included for storing data not directly derived from AIS message reports.
coarsetype_ref # a reference table that maps numeric ship type codes to their descriptions hashmap
For quick reference to data types and detailed explanations of these table entries, please see the Detailed Table Description.
Custom SQL Queries
In addition to querying the database using DBQuery module, there is an option to customize the query with your own SQL code.
Example of listing all the tables in your database:
import sqlite3dbpath='YOUR_DATABASE.db'# Define the path to your database# Connect to the databaseconnection = sqlite3.connect(dbpath)# Create a cursor objectcursor = connection.cursor()# Query to list all tablesquery ="SELECT name FROM sqlite_master WHERE type='table';"cursor.execute(query)# Fetch the resultstables = cursor.fetchall()# Print the names of the tablesprint("Tables in the database:")for table in tables:print(table[0])# Close the connectionconnection.close()
As messages are separated into tables by message type and month, queries spanning multiple message types or months should use UNIONs and JOINs to combine results as appropriate.
Example of querying tables with `JOIN`:
import sqlite3# Connectto the databaseconnection= sqlite3.connect('YOUR_DATABASE.db')# Create a cursorobjectcursor= connection.cursor()# Define the JOINSQL queryquery = f"""SELECT d.mmsi, d.time, d.longitude, d.latitude, d.sog, d.cog, s.vessel_name, s.ship_typeFROM ais_{YYYYMM}_dynamic dLEFT JOIN ais_{YYYYMM}_static s ON d.mmsi = s.mmsiWHERE d.time BETWEEN 1707033659 AND 1708176856 -- Filter by time range AND d.longitude BETWEEN -68 AND -56 -- Filter by geographical area AND d.latitude BETWEEN 45 AND 51.5;"""# Execute the querycursor.execute(query)# Fetch the resultsresults = cursor.fetchall()# Print the resultsforrowin results:print(row)# Close the connectionconnection.close()
More information about SQL queries can be looked up from online tutorials.
The R* tree virtual tables should be queried for AIS position reports 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. However, 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. To facilitate querying the database manually, use the dt_2_epoch() function to convert datetime values to epoch minutes and the epoch_2_dt() function to convert epoch minutes back to datetime values. Here is how you can use dt_2_epoch() with the example above:
from aisdb.gis import dt_2_epoch# Define the datetime rangestart_datetime =datetime(2018, 1, 1, 0, 0, 0)end_datetime =datetime(2018, 1, 1, 1, 59, 59)# Convert datetime to epoch timestart_epoch =dt_2_epoch(start_datetime)end_epoch =dt_2_epoch(end_datetime)# Connect to the databaseconnection = sqlite3.connect('YOUR_DATABASE.db')# Create a cursor objectcursor = connection.cursor()# Define the JOIN SQL query using an epoch time rangequery =f"""SELECT d.mmsi, d.time, d.longitude, d.latitude, d.sog, d.cog, s.vessel_name, s.ship_typeFROM ais_201801_dynamic dLEFT JOIN ais_201801_static s ON d.mmsi = s.mmsiWHERE d.time BETWEEN {start_epoch} AND {end_epoch} -- Filter by time range AND d.longitude BETWEEN -68 AND -56 -- Filter by geographical area AND d.latitude BETWEEN 45 AND 51.5;"""# Execute the querycursor.execute(query)# Fetch the resultsresults = cursor.fetchall()# Print the resultsfor row in results:print(row)# Close the connectionconnection.close()
For more examples, please see the SQL code in aisdb_sql/ that is used to create database tables and associated queries.
