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

• lausent: ok I made the first step and now my time colum data type is "timestamp without time zone". All row in column time is in this format is dd-mm-yyyy hh:mm:ss. Thanks for the link but I don't understand nothing of what he did because I don't understand his table how is builded. For the moment I create a new column call geom and I put single data from latitude and longitude by using: update mytable. SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326)
• thibautg: Now that you have a geom column, can you try: SELECT id, ST_MakeLine(geom ORDER BY time) FROM your_table GROUP BY id