Handling AIS data using Python and PostGIS?

Question

I want to turn points into a linestring in PostgreSQL with the extension of PostGIS using Python. I want the lines in a new table because every few minutes more points are added and it needs to keep updating as a separate table.

I have this SQL code to create a new database with a linestring column.

CREATE TABLE public.vesselpositionslijn ( 
  id bigint NOT NULL DEFAULT nextval('vesselpositionslijn_id_seq'::regclass),
  name character varying COLLATE pg_catalog."default",
  mmsi bigint,
  "position" geometry(LineString,32634),
  CONSTRAINT vesselpositionslijn_pkey PRIMARY KEY (id)) WITH (
  OIDS = FALSE) 
TABLESPACE pg_default; 

ALTER TABLE public.vesselpositionslijn
OWNER to postgres;

There is a Python code (at the bottom of this question) that stores the data in the points table (vesselpositions) but it doesn't store the data in the vesselpositionslijn table, as a response it is saying: cur.execute ( update_stmt, ( longitude, latitude, name) ) psycopg2.InternalError: First argument must be a LINESTRING

This is the relevant piece of the Python code:

name = str(response.json()[i]['AIS']['NAME'])
mmsi = int(response.json()[i]['AIS']['MMSI'])
latitude = float(response.json()[i]['AIS']['LATITUDE'])
longitude = float(response.json()[i]['AIS']['LONGITUDE'])
timestamp = datetime.datetime.strptime(str(response.json()[i]['AIS']['TIMESTAMP'][:-4]), '%Y-%m-%d %H:%M:%S')
insert_stmt = 'insert into vesselpositions ( name, mmsi, "position", "timestamp" ) values ( %s, %s, ST_SetSRID(ST_MakePoint(%s, %s),4326), %s )' 
cur.execute ( insert_stmt, ( name, mmsi, longitude, latitude, timestamp ) )
update_stmt = 'update vesselpositionslijn set position = ST_AddPoint(position, ST_MakePoint(%s, %s)) where name = %s' 
cur.execute ( update_stmt, ( longitude, latitude, name) )

Does anybody know what I'm doing wrong?

Answer 1

As stated in the comments, if you want to add a point to an existing line, use ST_AddPoint. However, although you don't state how you are planning on using this AIS data, it is almost certainly a really bad design choice to store AIS positions as LineStrings instead of Points (as they are received). To give an idea way, the image below comes from the Marine Traffic app and shows the trail of one barge that has been going past my house, from a quarry in El Garraf to the port of Barcelona, several times a day, for several months.

If you store this track as a LINESTRING, without any information linking each constituent point to a specific point in time, you could not answer a question, such as where has this boat been in the last day or how many boats have passed this point in the last day. You also lose all the information about where the vessel is going (which is also sent of the AIS message packet).

If you have Vessel positions as points, then you can easily recreate any track between any two dates in any geographical area, by indexing the points spatially and by partitioning on date or using a BRIN index, which is good for data that is sequential, such as temporal data. If your data are stored as points and have the appropriate spatial and/or range indexes and/or partitions you will be able to handle arbitrarily large volumes of data -- billions of rows -- and be able to answer any question about a boat(s) current/past position and recreate any track.

If you looks at the docs for ST_MakeLine, you will see that the first example, shows how to recreate a LineString from a set of GPS points, which is exactly your use case. Modifying this using the AIS mmsi ship ID:

SELECT 
   ais.mmsi, 
   ST_MakeLine(ais.geom ORDER BY utc) As track
FROM ais_points as ais
GROUP BY mmsi;

So, if you want to do this by datetime or geograhic area you would put this in the WHERE clause, eg,

    SELECT 
        ais.mmsi, 
        ST_MakeLine(ais.geom ORDER BY datetime) As track
      FROM ais_points as ais
     WHERE ST_Intersects(ST_SetSRID(
             ST_MakeBox2D(St_MakePoint(-50, 1), ST_MakePoint(-51, 2)), 4326), ais.geom)
       AND datetime BETWEEN startdate AND enddate
     GROUP BY mmsi;

I worked on a live AIS application many years ago and via date partitioning and spatial indexing was able to handle sub-second queries on billions of rows of AIS point data, so, this approach is tried and tested. So, while I realize that this does not answer the question as stated, it is a far more flexible and scalable approach.

You would probably want an index on the MMSI column also, so you can filter by vessel and this would be a foreign key into the second AIS message type, the ships voyage information and ship meta data.