🗄️SQL Database

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, 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 sqlite3

dbpath='YOUR_DATABASE.db' # Define the path to your database

# Connect to the database
connection = sqlite3.connect(dbpath)

# Create a cursor object
cursor = connection.cursor()

# Query to list all tables
query = "SELECT name FROM sqlite_master WHERE type='table';"
cursor.execute(query)

# Fetch the results
tables = cursor.fetchall()

# Print the names of the tables
print("Tables in the database:")
for table in tables:
    print(table[0])

# Close the connection
connection.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

# Connect to the database
connection = sqlite3.connect('YOUR_DATABASE.db')

# Create a cursor object
cursor = connection.cursor()

# Define the JOIN SQL query
query = f"""
SELECT
    d.mmsi, 
    d.time,
    d.longitude,
    d.latitude,
    d.sog,
    d.cog,
    s.vessel_name,
    s.ship_type
FROM ais_{YYYYMM}_dynamic d
LEFT JOIN ais_{YYYYMM}_static s ON d.mmsi = s.mmsi
WHERE 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 query
cursor.execute(query)

# Fetch the results
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
connection.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 range
start_datetime = datetime(2018, 1, 1, 0, 0, 0)
end_datetime = datetime(2018, 1, 1, 1, 59, 59)

# Convert datetime to epoch time
start_epoch = dt_2_epoch(start_datetime)
end_epoch = dt_2_epoch(end_datetime)

# Connect to the database
connection = sqlite3.connect('YOUR_DATABASE.db')

# Create a cursor object
cursor = connection.cursor()

# Define the JOIN SQL query using an epoch time range
query = f"""
SELECT
    d.mmsi, 
    d.time,
    d.longitude,
    d.latitude,
    d.sog,
    d.cog,
    s.vessel_name,
    s.ship_type
FROM ais_201801_dynamic d
LEFT JOIN ais_201801_static s ON d.mmsi = s.mmsi
WHERE 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 query
cursor.execute(query)

# Fetch the results
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
connection.close()

For more examples, please see the SQL code in aisdb_sql/ that is used to create database tables and associated queries.

Detailed Table Description

ais_{YYYYMM}_dynamic tables

Column	Data Type	Description
mmsi	INTEGER	Maritime Mobile Service Identity, a unique identifier for vessels.
time	INTEGER	Timestamp of the AIS message, in epoch seconds.
longitude	REAL	Longitude of the vessel in decimal degrees.
latitude	REAL	Latitude of the vessel in decimal degrees.
rot	REAL	Rate of turn, indicating how fast the vessel is turning.
sog	REAL	Speed over ground, in knots.
cog	REAL	Course over ground, in degrees.
heading	REAL	Heading of the vessel, in degrees.
maneuver	BOOLEAN	Indicator for whether the vessel is performing a special maneuver.
utc_second	INTEGER	Second of the UTC timestamp when the message was generated.
source	TEXT	Source of the AIS data.

ais_{YYYYMM}_static tables

Column	Data Type	Description
mmsi	INTEGER	Maritime Mobile Service Identity, a unique identifier for vessels.
time	INTEGER	Timestamp of the AIS message, in epoch seconds.
vessel_name	TEXT	Name of the vessel.
ship_type	INTEGER	Numeric code representing the type of ship.
call_sign	TEXT	International radio call sign of the vessel.
imo	INTEGER	International Maritime Organization number, another unique vessel identifier.
dim_bow	INTEGER	Distance from the AIS transmitter to the bow (front) of the vessel.
dim_stern	INTEGER	Distance from the AIS transmitter to the stern (back) of the vessel.
dim_port	INTEGER	Distance from the AIS transmitter to the port (left) side of the vessel.
dim_star	INTEGER	Distance from the AIS transmitter to the starboard (right) side of the vessel.
draught	REAL	Maximum depth of the vessel's hull below the waterline, in meters.
destination	TEXT	Destination port or location where the vessel is heading.
ais_version	INTEGER	AIS protocol version used by the vessel.
fixing_device	TEXT	Type of device used for fixing the vessel's position (e.g., GPS).
eta_month	INTEGER	Estimated time of arrival month.
eta_day	INTEGER	Estimated time of arrival day.
eta_hour	INTEGER	Estimated time of arrival hour.
eta_minute	INTEGER	Estimated time of arrival minute.
source	TEXT	Source of the AIS data (e.g., specific AIS receiver or data provider).

static_{YYYYMM}_aggregate tables

Column	Data Type	Description
mmsi	INTEGER	Maritime Mobile Service Identity, a unique identifier for vessels.
imo	INTEGER	International Maritime Organization number, another unique vessel identifier.
vessel_name	TEXT	Name of the vessel.
ship_type	INTEGER	Numeric code representing the type of ship.
call_sign	TEXT	International radio call sign of the vessel.
dim_bow	INTEGER	Distance from the AIS transmitter to the bow (front) of the vessel.
dim_stern	INTEGER	Distance from the AIS transmitter to the stern (back) of the vessel.
dim_port	INTEGER	Distance from the AIS transmitter to the port (left) side of the vessel.
dim_star	INTEGER	Distance from the AIS transmitter to the starboard (right) side of the vessel.
draught	REAL	Maximum depth of the vessel's hull below the waterline, in meters.
destination	TEXT	Destination port or location where the vessel is heading.
eta_month	INTEGER	Estimated time of arrival month.
eta_day	INTEGER	Estimated time of arrival day.
eta_hour	INTEGER	Estimated time of arrival hour.
eta_minute	INTEGER	Estimated time of arrival minute.