[postgis-users] Creating trajectory/lines from millions ofpoints[PostGIS]
Rémi Cura
remi.cura at gmail.com
Wed Nov 26 02:21:24 PST 2014
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.francois at irstea.fr>:
> 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-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *De la part de* Roxanne
> Reid-Bennett
> *Envoyé :* mardi 25 novembre 2014 19:17
> *À :* postgis-users at lists.osgeo.org
> *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
> <postgi... at lists.osgeo.org%20>>
> *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
>
> postgis-users at lists.osgeo.org
>
> 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
> postgis-users 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/20141126/9795a0b9/attachment.html>
More information about the postgis-users
mailing list