1

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?

  • ST_DumpPoints? postgis.net/docs/ST_DumpPoints.html – Mapperz Feb 18 at 16:23
  • 4
    I think you are looking for ST_MakeLine. However, why not store the positions as points, and then, create a linestring on demand, based on time ranges, spatial extents, etc, which is more flexible. If you really do have to store everything as a LineString, then, you can use ST_AddPoint. – John Powell Feb 18 at 17:31
  • 1
    if that is a direct copy of your code, you are updating the wrong table (i.e.the one with the points, thus the error)! note that ST_AddPoint needs a geometry to work on; you can pass it a LineString having only one Point (invalid, making it valid), but can't add a point to an empty field! I also agree with @JohnPowell that you should consider creating lines on-the-fly, wrapped in a View; creating lines is cheap, and using e.g. Views to access/summarize (very) large, highly updated tables is quite the standard business of an RDBMS... – ThingumaBob Feb 18 at 19:27
  • @JohnPowell I have tried using ST_MakeLine, see below: <br/> SELECT t2.name, ST_MakeLine(ARRAY(SELECT t1.position FROM vesselpositions t1 where t1.name = t2.name ORDER BY t1.timestamp)) as newgeom from vesselpositions t2 group by t2.name; <br/> And it does what I want but only one time and after that when I run the python script again it only updates the point table and not the line data, any suggestions? – Chase Feb 19 at 7:56
3

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.

enter image description here

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.

  • Thanks for the update, The line is purely used for cosmetic purposes in ArcMap/Qgis as we want to see how a group of vessels moves when operating in rivers and seas and is performing soil/depth measurements, the point layer (vesselpositions) would contain all the data and the table (vesselpositionslijn) with the linestring geometry would connect the points as a separate layer (as this is not posible in GIS symbology). I will dig a bit deeper into your suggestion. If you have some other ideas/suggestions I would love to hear them, kinda new to working with AIS data in GIS systems. – Chase Feb 19 at 9:54
  • I have plenty of ideas re AIS, but, it is probably better to ask a specific question based on your use case, rather than my making random suggestions. Enjoy working with AIS, it is fun, and if this has helped you, please consider accepting the answer. – John Powell Feb 19 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.