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_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... – geozelot Feb 18 '19 at 19:27SELECT 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 '19 at 7:56