AIS Data to CSV
Export decoded AIS tracks from a SQLite or PostgreSQL AISdb database to CSV, using the built-in write_csv() writer or a custom column selection.
Building on the Database Loading tutorial, where we used AIS data to create AISdb databases, you can export that data back out to CSV for use in spreadsheets, R, pandas, or any tool that doesn't speak SQLite or PostgreSQL directly. AISdb ships a built-in writer for this, aisdb.write_csv(), which takes a track generator and handles column ordering, static-versus-dynamic fields, and value sanitization for you. This section covers that built-in path first, then shows how to write a custom CSV yourself when you only want a handful of columns.
Exporting to CSV
Connect to the database, run a query, and hand the resulting tracks to aisdb.write_csv(). The whole flow, query and write, needs to happen inside the same with block, because TrackGen and DBQuery.gen_qry() are lazy generators that read from the database connection as they're consumed. Once the with block exits and the connection closes, iterating over tracks later will fail. The pattern is identical for SQLite and PostgreSQL, only the connection object changes; PostgresDBConn accepts the same keyword arguments as psycopg, including host, port, user, dbname, and password, or a single libpq_connstring.
import aisdb
from aisdb import SQLiteDBConn, DBQuery, DomainFromPoints
from datetime import datetime
dbpath = 'YOUR_DATABASE.db' # path to your database
start_time = datetime.strptime("2018-01-01 00:00:00", '%Y-%m-%d %H:%M:%S')
end_time = datetime.strptime("2018-01-02 00:00:00", '%Y-%m-%d %H:%M:%S')
# a 50 km radius around a point off Halifax harbor
domain = DomainFromPoints(points=[(-63.6, 44.6)], radial_distances=[50000])
with SQLiteDBConn(dbpath=dbpath) as dbconn:
qry = DBQuery(
dbconn=dbconn, start=start_time, end=end_time,
xmin=domain.boundary['xmin'], xmax=domain.boundary['xmax'],
ymin=domain.boundary['ymin'], ymax=domain.boundary['ymax'],
callback=aisdb.database.sqlfcn_callbacks.in_time_bbox_validmmsi,
)
tracks = aisdb.track_gen.TrackGen(qry.gen_qry(), decimate=False)
aisdb.write_csv(tracks, 'output_sqlite.csv')
print("All tracks have been written to output_sqlite.csv")Checking the output file:
Checking the output file:
write_csv() accepts the track generator, an output path (or an io.BytesIO / SpooledTemporaryFile buffer if you'd rather write in memory), and an optional skipcols list of column names to leave out, which defaults to ['label', 'in_zone']. Internally it calls aisdb.proc_util.tracks_csv(), which figures out the column order from the static and dynamic fields present on the first track, appends a datetime column derived from the epoch time values, and rounds a handful of numeric fields (lon, lat, and, when present, distance and depth columns from other tutorials) to a fixed number of decimals. Each AIS message gets its own row in the CSV, with one value per dynamic field, tagged with a Track_ID column so you can group rows belonging to the same voyage segment back together after loading the file elsewhere.
mmsi and maneuver are static per-track values (each vessel keeps one MMSI and reports its maneuver indicator once per track vector), while lon, lat, cog, sog, heading, rot, and utc_second are dynamic and change with every reported position.
Writing a Custom CSV
write_csv() writes every column it finds, which is usually what you want. If you'd rather control the exact set of columns yourself, iterate the tracks manually and write rows with the standard library csv module. Two things trip people up here, and both come from the static-versus-dynamic distinction in a track dictionary. First, mmsi and maneuver are static, so they're scalar values on the track and shouldn't be indexed. Second, lon, lat, cog, sog, heading, rot, and utc_second are dynamic arrays with one entry per AIS message, so they need to be indexed by position.
If you're building rows yourself outside of csv.DictWriter, AISdb also exposes the two lower-level pieces write_csv() is built from. aisdb.proc_util.tracks_csv(tracks, skipcols=['label', 'in_zone']) is a generator that yields the header row followed by one row per AIS message, already column-ordered and sanitized, which you can feed to your own writer. aisdb.proc_util.write_csv_rows(rows, pathname='output.csv', mode='a') takes any iterable of row tuples and appends them to a file, useful when you're assembling rows from several sources before writing. Neither function is re-exported at the top level, so import them from aisdb.proc_util directly.
Last updated