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.franc...@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-boun...@lists.osgeo.org [mailto: > postgis-users-boun...@lists.osgeo.org] *De la part de* Roxanne > Reid-Bennett > *Envoyé :* mardi 25 novembre 2014 19:17 > *À :* postgis-users@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....@gmail.com> > *To:* postgi...@googlegroups.com > *Cc:* pcr...@pcreso.com; postgi...@lists.osgeo.org; > postgi...@lists.osgeo.org; remi...@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...@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....@irstea.fr> > *To:* PostGIS Users Discussion <postgi...@lists.osgeo.org > <postgi...@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...@lists. osgeo.org [mailto:postgis-us...@ > lists.osgeo.org] *De la part de* Oliver Burgfeld > *Envoyé :* mardi 25 novembre 2014 07:09 > *À :* postgi...@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...@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...@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@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@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users