πŸ““
Documentation
  • βš“Introduction
  • Default Start
    • πŸ›°οΈQuick Start
    • πŸ—„οΈSQL Database
    • πŸ“‘AIS Hardware
  • Tutorials
    • πŸ“₯Database Loading
    • πŸ”ŽData Querying
    • 🚿Data Cleaning
    • πŸ—ΊοΈData Visualization
    • πŸ–‡οΈTrack Interpolation
    • 🌎Haversine Distance
    • 🚀Vessel Speed
    • 🏝️Coast, shore, and ports
    • πŸ”Using Your AIS Data
    • ⬇️Vessel Metadata
    • πŸ“’AIS Data to CSV
    • πŸ“Decimation with AISdb
    • 🌊Bathymetric Data
    • 🌦️Weather Data
    • AIS - Automatic Identification System
  • Machine Learning
    • seq2seq in PyTorch
    • AutoEncoders in Keras
    • Using Newtonian PINNs
    • Embedding with traj2vec
    • TGNs with TorchGeometric
    • Clustering with Scikit Learn
    • Kalman Filters with FilterPy
    • Deploying an AISdb ChatBot
  • Keep Exploring
    • ReadTheDocs
    • MARS Group
    • MAPS Lab
    • MERDIAN
Powered by GitBook
On this page
  • Table Naming
  • Custom SQL Queries
  • Detailed Table Description
Export as PDF
  1. Default Start

SQL Database

PreviousQuick StartNextAIS Hardware

Last updated 7 months ago

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 .

Custom SQL Queries

In addition to querying the database using 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()

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()

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.

More information about SQL queries can be looked up from .

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

online tutorials
aisdb_sql/
DBQuery
Detailed Table Description
πŸ—„οΈ
Page cover image