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

[Oliver Burgfeld]

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_table
>
> GROUP BY id, status
>
> )
>
>  
>
> SELECT id, status, (ST_Dump(mylines)).geom
> FROM multis
>
> Cheers,
> Rémi-c
>
>
> 2014-11-25 9:53 GMT+01:00 Brent Wood <pcr... at pcreso.com <javascript:>>:
>
>> 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_table
>> GROUP BY id, status
>> ORDER BY time_field
>> )
>>  
>> SELECT id, status, (ST_Dump(mylines)).geom
>> FROM multis;
>>
>>
>> Cheers,
>>    Brent Wood
>>   ------------------------------
>>  *From:* Hugues François <hugues.... at irstea.fr <javascript:>>
>> *To:* PostGIS Users Discussion <postgi... at lists.osgeo.org <javascript:>> 
>> *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_table
>> GROUP BY id, status
>> ORDER BY time_field
>> )
>>  
>> SELECT id, status, (ST_Dump(mylines)).geom
>> FROM 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 <javascript:> [mailto:
>> postgis-us... at lists.osgeo.org <javascript:>] *De la part de* Oliver 
>> Burgfeld
>> *Envoyé :* mardi 25 novembre 2014 07:09
>> *À :* postgi... at lists.osgeo.org <javascript:>
>> *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 <javascript:>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>>  
>> _______________________________________________
>> postgis-users mailing list
>> postgi... at lists.osgeo.org <javascript:>
>> 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/e36a05dc/attachment-0001.html>