Lines from points in postgresql postgis

Question

I have this table (all row aren't in order):
time, id, latitude, longitude

time is character varying and the format is: dd/mm/year hh:mm
id, latitude and longitude are numeric
I would create lines from latitude and longitude but from id and time. In each row it is possible found same id, but I want associate to the same id (example 20 same id is like 1 id) multiple time (each time have a latitude and longitude). So the line that will be create must respect a specific order. After I create the lines I will simply import the table in QGIS from postgres.

example

line1:
id 12345 ---> time 01/01/2001 00:00 ---> (lat, long)
id 12345 ---> time 01/01/2001 00:02 ---> (lat, long)
id 12345 ---> time 01/01/2001 00:04 ---> (lat, long)

line2:
id 54663 ---> time 01/01/2001 00:24 ---> (lat, long)
id 54663 ---> time 01/01/2001 00:54 ---> (lat, long)

line3
id 234252 ---> time 01/01/2001 00:54 ---> (lat, long)

....

lineN:
id 3223423 ---> ........
id 3223423 ---> ........
id 3223423 ---> ........

Answer 1

First thing you need to do is to set your date field correctly as otherwise you can´t order it.

Something like this(refer to the documentation for the correct time format): alter column time TYPE timestamp USING time::timestamp without time zone Or if you want to have directly in your query without changing the column you can use the CAST function.

Then you need a nested query with two ORDER BY clauses for your id (the inner query) and for the time to get your data in the correct order.

And the nested queries you put into your main query for the function ST_MakeLine. You can use the following link for an example how to do it.

http://www.bostongis.com/postgis_makeline.snippet