[postgis-users] Creating trajectory/lines from millions ofpoints[PostGIS]
Oliver Burgfeld
oliver.burgfeld at gmail.com
Fri Nov 28 12:32:05 PST 2014
Just another short question...
I would like to select a specific taxi route and create lines which
represent this route over a specific time range.
CREATE TABLE taxiroutexy_lines AS WITH multis AS(
SELECT routeid,vehicleid,timedate,ST_MakeLine(geom ORDER BY timedate)
AS mylines
FROM taxitable
GROUP BY routeid,vehicleid,timedate
)
SELECT routeid,vehicleid,timedate,(ST_Dump(mylines)).geom
FROM multis;
But this only creates one line for each vehicleid over the whole timedate.
So let's say I have timestamps from two weeks, then I will have one line
for vehicle xy from the beginning of the timestamp to the end.
How can I modify this to get one line for each day?
Am Mittwoch, 26. November 2014 11:21:30 UTC+1 schrieb Rémi Cura:
>
> Or, the simplest solution of all,
> put the time in the M of each point !
>
> WITH multis AS (
> SELECT
> id
> ,status
> ,ST_MakeLine(
> ST_MakePointM(ST_X(point_geom), ST_Y(point_geom), timestamp)
> ORDER BY timestamp
> ) AS mylines
> FROM your_table
> GROUP BY id, status
> )
> SELECT
> id
> ,status
> ,ST_M(ST_StartPoint(simple_lines)) AS time_start
> ,ST_M(ST_EndPoint(simple_lines)) AS time_end
> ,ST_SetSRID(simple_lines,4326) AS simple_lines
> FROM multis, (ST_Dump(mylines)).geom AS simple_lines
>
> 2014-11-26 9:05 GMT+01:00 Hugues François <hugues.... at irstea.fr
> <javascript:>>:
>
>> Hi,
>>
>>
>>
>> I may be wrong, but with this method, I’m afraid you will have the same
>> start and end time for each vehicle and status (the start time of the first
>> line and le end time of the last one by vehicle and status) instead of a
>> unique start / end time for each line.
>>
>>
>>
>> To have the start and end for each line, I think you will have to
>> retrieve them in a second time comparing start and end point of each line
>> with the original gps points. Another solution would be to create a plpgsql
>> function to build the linestring from a loop.
>>
>>
>>
>> Regards,
>>
>>
>>
>> Hugues.
>>
>>
>>
>> *De :* postgis-us... at lists.osgeo.org <javascript:> [mailto:
>> postgis-us... at lists.osgeo.org <javascript:>] *De la part de* Roxanne
>> Reid-Bennett
>> *Envoyé :* mardi 25 novembre 2014 19:17
>> *À :* postgi... at lists.osgeo.org <javascript:>
>> *Objet :* Re: [postgis-users] Creating trajectory/lines from millions
>> ofpoints[PostGIS]
>>
>>
>>
>> On 11/25/2014 11:48 AM, Oliver Burgfeld wrote:
>>
>> 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.
>>
>> From the quer below change
>> SELECT id, status, (ST_Dump(mylines)).geom
>> to
>>
>> SELECT id, status, (ST_Dump(mylines)).geom, time_start, time_end
>>
>> Something to keep an eye out for... Depending upon your version of
>> PostGIS and the underlying libraries, because we were working with an older
>> version of the underlying libraries, I don't know if this is still a
>> potential issue. We ran into issues with "stacked" points (2 GPS points
>> with the same coordinates), and GPS drift causing issues with the
>> linestrings being pretty goofy (jagged points in the linestring that were
>> clearly NOT what the vehicle did). We wrote cleanup scripts to take care
>> of those issues (that are still in place). I've not taken time to revisit
>> the library routines to see if they now handle those conditions cleanly.
>>
>> Roxanne
>>
>>
>>
>> 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_table
>>
>> GROUP BY id, status
>>
>> )
>>
>>
>>
>> SELECT id, status, (ST_Dump(mylines)).geom
>>
>> FROM 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_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>:
>>
>> 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>
>> *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_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 [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
>> <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
>> <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
>>
>>
>>
>>
>> --
>>
>> [At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
>>
>> Donald Knuth
>>
>>
>> _______________________________________________
>> 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/20141128/69c6c916/attachment-0001.html>
More information about the postgis-users
mailing list