[postgis-users] Creating trajectory/lines from millions of points[PostGIS]

[Brent Wood]

My fault, you also need to select these in the final select, try:
WITH multis AS (                SELECT id, status, min(timestamp) as time_start, max(timestamp) as time_end, ST_MakeLine( point_geom ORDER BY timestamp) AS mylines                FROM your_tableGROUP BY id, status) SELECT id, status, time_start, time_end, (ST_Dump(mylines)).geom
Cheers,
  Brent

      From: Oliver Burgfeld <oliver.burgfeld at gmail.com>
 To: postgis-users at googlegroups.com 
Cc: postgis-users at lists.osgeo.org; pcreso at pcreso.com; postgis-users at lists.osgeo.org; pcreso at pcreso.com 
 Sent: Wednesday, November 26, 2014 5:48 AM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]
   
I also tried that and it works but it does not give me those two columns in my new table. There are only id and status inside.

 
Am Dienstag, 25. November 2014 17:39:21 UTC+1 schrieb Brent Wood:
as in my previous reply, I figured that would be useful...
WITH multis AS (                SELECT id, status, min(timestamp) as time_start, max(timestamp) as time_end, ST_MakeLine( point_geom ORDER BY timestamp) AS mylines                FROM your_tableGROUP BY id, status) SELECT id, status, (ST_Dump(mylines)).geomFROM multisBrent Wood
      From: Oliver Burgfeld <oliver.... at gmail.com>
 To: postgi... at googlegroups.com 
Cc: pcr... at pcreso.com; postgi... at lists.osgeo.org; postgi... at lists.osgeo.org; remi... at gmail.com 


 Sent: Wednesday, November 26, 2014 5:10 AM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]
   
Thank you and all the others who were answering :)

I tried that and it seems that its working. Nevertheless I only tried it with a small part of my data (round about 1 million rows out of ~500 million) but if it's working now, it should also work with the whole dataset.

Is there a way to also include the time_field into the result? I created a new table with this statement given but there are only two columns (vehicleid and status) included. 
I know thats logical because I only included those two into my select clause but it would be great to not only order by time but also have a time column in my table.

For example:

vehicleid | status | time_start | time_end


I hope its understandable and not to mixed up...

Thanks!


Am Dienstag, 25. November 2014 16:06:33 UTC+1 schrieb Rémi Cura:
Hey, a small correction :
ST_MakeLine is already an aggregate, and you may want to enforce the order inside the aggregate (see at the end).
Another interesting point is the possiblity to pu somehting in the M value of each point of the line, for instance the time.
This comes very handy when you want to extrat parts of the lines.


So for instance for the first proposition :

WITH multis AS (                SELECT id, status, ST_MakeLine( point_geom ORDER BY time_field) AS mylines                FROM your_tableGROUP BY id, status) SELECT id, status, (ST_Dump(mylines)).geomFROM multis

Cheers,
Rémi-c


2014-11-25 9:53 GMT+01:00 Brent Wood <pcr... at pcreso.com>:

or automatically get the start & end times for each trackline in the record like this:

WITH multis AS (                SELECT id, min(time_field) AS time_start, max(time_field) as time_end, status, ST_MakeLine(array_agg(point_ geom )) AS mylines                FROM your_tableGROUP BY id, statusORDER BY time_field)  SELECT id, status, (ST_Dump(mylines)).geomFROM multis;

Cheers,   Brent Wood
      From: Hugues François <hugues.... at irstea.fr>
 To: PostGIS Users Discussion <postgi... at lists.osgeo.org > 
 Sent: Tuesday, November 25, 2014 8:13 PM
 Subject: Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]
   
Hello,  In your case I would have try to make multilines for each taxi and each status (i.e. two multi by taxi) and then dump them into  simple linestrings. All in a query that may look like this assuming you have a taxi id field:  WITH multis AS (                SELECT id, status, ST_MakeLine(array_agg(point_ geom )) AS mylines                FROM your_tableGROUP BY id, statusORDER BY time_field)  SELECT id, status, (ST_Dump(mylines)).geomFROM multis  You may want to add a time reference to your lines. To do this, you can add an extraction from your timestamp field (e.g. day or month) and add it into the WITH and to the group by clause.  Hugues.          De : postgis-us... at lists. osgeo.org [mailto:postgis-us...@ lists.osgeo.org] De la part de Oliver Burgfeld
Envoyé : mardi 25 novembre 2014 07:09
À : postgi... at lists.osgeo.org
Objet : [postgis-users] Creating trajectory/lines from millions of points[PostGIS]  

Hi,I have millions of points in a PostGIS database containing taxi gps tracks. Now I want to create lines from these points by vehicleid and ordered by timestamp. But, and that's my problem right now, at first I want to include every column of my point table into the "line table" and I also need to intersect those lines at specific points.I have one column representing the "taxi_is_occupied" status with 0 or 1. What I want now is to create lines which are divided every time this status changes. In the end I need lines which show the path of every taxi over time, divided every time the status of the car changes so that I can query all lines where the taxi is occupied, for example.What do I have to use therefore? I know that there is the ST_MakeLines tool existing in PostGIS, but as I am a new PostGIS user... I do not know exactly how to use it to get the results I need.   Thanks a lot
______________________________ _________________
postgis-users mailing list
postgi... at lists.osgeo.org
http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users

   
______________________________ _________________
postgis-users mailing list
postgi... at lists.osgeo.org


http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users





   


  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141125/b299291f/attachment.html>