# 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. {% code lineNumbers="true" %} ``` ais_{YYYYMM}_static # table with static AIS messages ais_{YYYYMM}_dynamic # table with dynamic AIS message ``` {% endcode %} 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. {% code lineNumbers="true" %} ``` static_{YYYYMM}_aggregate # table of aggregated static vessel data ``` {% endcode %} Additional tables are also included for storing data not directly derived from AIS message reports. {% code lineNumbers="true" %} ``` coarsetype_ref # a reference table that maps numeric ship type codes to their descriptions hashmap ``` {% endcode %} For quick reference to data types and detailed explanations of these table entries, please see the [Detailed Table Description](#detailed-table-description). ### Custom SQL Queries In addition to querying the database using [`DBQuery`](https://aisdb.meridian.cs.dal.ca/doc/api/aisdb.database.dbqry.html) module, there is an option to customize the query with your own SQL code. Example of listing all the tables in your database: {% code lineNumbers="true" %} ```python 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() ``` {% endcode %} 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\`: {% code lineNumbers="true" %} ```sql 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() ``` {% endcode %} More information about SQL queries can be looked up from [online tutorials](https://sqlbolt.com/). 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: {% code lineNumbers="true" %} ```python 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() ``` {% endcode %} For more examples, please see the SQL code in [`aisdb_sql/`](https://github.com/AISViz/AISdb/tree/master/aisdb/aisdb_sql) that is used to create database tables and associated queries. ### Detailed Table Description #### `ais_{YYYYMM}_dynamic` tables
ColumnData TypeDescription
mmsiINTEGERMaritime Mobile Service Identity, a unique identifier for vessels.
timeINTEGERTimestamp of the AIS message, in epoch seconds.
longitudeREALLongitude of the vessel in decimal degrees.
latitudeREALLatitude of the vessel in decimal degrees.
rotREALRate of turn, indicating how fast the vessel is turning.
sogREALSpeed over ground, in knots.
cogREALCourse over ground, in degrees.
headingREALHeading of the vessel, in degrees.
maneuverBOOLEANIndicator for whether the vessel is performing a special maneuver.
utc_secondINTEGERSecond of the UTC timestamp when the message was generated.
sourceTEXTSource of the AIS data.
#### `ais_{YYYYMM}_static` tables
ColumnData TypeDescription
mmsiINTEGERMaritime Mobile Service Identity, a unique identifier for vessels.
timeINTEGERTimestamp of the AIS message, in epoch seconds.
vessel_nameTEXTName of the vessel.
ship_typeINTEGERNumeric code representing the type of ship.
call_signTEXTInternational radio call sign of the vessel.
imoINTEGERInternational Maritime Organization number, another unique vessel identifier.
dim_bowINTEGERDistance from the AIS transmitter to the bow (front) of the vessel.
dim_sternINTEGERDistance from the AIS transmitter to the stern (back) of the vessel.
dim_portINTEGERDistance from the AIS transmitter to the port (left) side of the vessel.
dim_starINTEGERDistance from the AIS transmitter to the starboard (right) side of the vessel.
draughtREALMaximum depth of the vessel's hull below the waterline, in meters.
destinationTEXTDestination port or location where the vessel is heading.
ais_versionINTEGERAIS protocol version used by the vessel.
fixing_deviceTEXTType of device used for fixing the vessel's position (e.g., GPS).
eta_monthINTEGEREstimated time of arrival month.
eta_dayINTEGEREstimated time of arrival day.
eta_hourINTEGEREstimated time of arrival hour.
eta_minuteINTEGEREstimated time of arrival minute.
sourceTEXTSource of the AIS data (e.g., specific AIS receiver or data provider).
#### `static_{YYYYMM}_aggregate` tables
ColumnData TypeDescription
mmsiINTEGERMaritime Mobile Service Identity, a unique identifier for vessels.
imoINTEGERInternational Maritime Organization number, another unique vessel identifier.
vessel_nameTEXTName of the vessel.
ship_typeINTEGERNumeric code representing the type of ship.
call_signTEXTInternational radio call sign of the vessel.
dim_bowINTEGERDistance from the AIS transmitter to the bow (front) of the vessel.
dim_sternINTEGERDistance from the AIS transmitter to the stern (back) of the vessel.
dim_portINTEGERDistance from the AIS transmitter to the port (left) side of the vessel.
dim_starINTEGERDistance from the AIS transmitter to the starboard (right) side of the vessel.
draughtREALMaximum depth of the vessel's hull below the waterline, in meters.
destinationTEXTDestination port or location where the vessel is heading.
eta_monthINTEGEREstimated time of arrival month.
eta_dayINTEGEREstimated time of arrival day.
eta_hourINTEGEREstimated time of arrival hour.
eta_minuteINTEGEREstimated time of arrival minute.
--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://aisviz.gitbook.io/documentation/default-start/sql-database.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.