<div dir="ltr">Just another short question...<br><br>I would like to select a specific taxi route and create lines which represent this route over a specific time range.<br><br>CREATE TABLE taxiroutexy_lines AS WITH multis AS(<br>    SELECT routeid,vehicleid,timedate,ST_MakeLine(geom ORDER BY timedate) AS mylines<br>    FROM taxitable<br>    GROUP BY routeid,vehicleid,timedate<br>    )<br>SELECT routeid,vehicleid,timedate,(ST_Dump(mylines)).geom<br>FROM multis;<br><br>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.<br>How can I modify this to get one line for each day?<br><br><br>Am Mittwoch, 26. November 2014 11:21:30 UTC+1 schrieb Rémi Cura:<blockquote class="gmail_quote" style="margin: 0;margin-left: 0.8ex;border-left: 1px #ccc solid;padding-left: 1ex;"><div dir="ltr"><div>Or, the simplest solution of all,<br></div>put the time in the M of each point !<br><br>WITH multis AS (<br>    SELECT<br>    id<br>    ,status<br>    ,ST_MakeLine(<br>        ST_MakePointM(ST_X(point_geom)<wbr>, ST_Y(point_geom), timestamp)<br>        ORDER BY timestamp<br>    ) AS mylines<br>                FROM your_table<br>GROUP BY id, status<br>)<br>SELECT <br>  id<br>  ,status<br>  ,ST_M(ST_StartPoint(simple_<wbr>lines)) AS time_start<br>  ,ST_M(ST_EndPoint(simple_<wbr>lines)) AS time_end<br>  ,ST_SetSRID(simple_lines,4326) AS simple_lines<br>FROM multis, (ST_Dump(mylines)).geom AS simple_lines<br><div><br><div class="gmail_quote">2014-11-26 9:05 GMT+01:00 Hugues François <span dir="ltr"><<a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">hugues....@irstea.fr</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor="white" link="blue" vlink="purple" lang="FR"><div><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)">Hi,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">Regards,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB">Hugues.</span><span style="font-size:13.5pt" lang="EN-GB"><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri","sans-serif";color:rgb(31,73,125)" lang="EN-GB"><u></u> <u></u></span></p><div><div style="border-width:1pt medium medium;border-style:solid none none;border-color:rgb(181,196,223) -moz-use-text-color -moz-use-text-color;padding:3pt 0cm 0cm"><p class="MsoNormal"><b><span style="font-size:10pt;font-family:"Tahoma","sans-serif";color:windowtext">De :</span></b><span style="font-size:10pt;font-family:"Tahoma","sans-serif";color:windowtext"> <a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">postgis-us...@lists.<wbr>osgeo.org</a> [mailto:<a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">postgis-us...@<wbr>lists.osgeo.org</a>] <b>De la part de</b> Roxanne Reid-Bennett<br><b>Envoyé :</b> mardi 25 novembre 2014 19:17<br><b>À :</b> <a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">postgi...@lists.osgeo.org</a><br><b>Objet :</b> Re: [postgis-users] Creating trajectory/lines from millions ofpoints[PostGIS]<u></u><u></u></span></p></div></div><div><div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">On 11/25/2014 11:48 AM, Oliver Burgfeld wrote:<u></u><u></u></p></div><blockquote style="margin-top:5pt;margin-bottom:5pt"><div><p class="MsoNormal" style="margin-bottom:12pt">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.<u></u><u></u></p></div></blockquote><p class="MsoNormal">From the quer below change<br><span style="font-size:11pt" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom<br>to <br></span><br><span style="font-size:11pt" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom, time_start, time_end<br><br>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.<br><br>Roxanne<br><br><br></span><u></u><u></u></p><div><p class="MsoNormal"> <br>Am Dienstag, 25. November 2014 17:39:21 UTC+1 schrieb Brent Wood: <u></u><u></u></p><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">as in my previous reply, I figured that would be useful...<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">WITH multis AS (</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                SELECT id, status,<b> min(timestamp) as time_start, max(timestamp) as time_end, </b>ST_MakeLine( point_geom ORDER BY timestamp) AS mylines</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                FROM your_table</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">GROUP BY id, status</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">)</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">FROM multis</span><span style="font-family:"Helvetica","sans-serif"">Brent Wood<u></u><u></u></span></p><div><div><div><div class="MsoNormal" style="text-align:center;background:none repeat scroll 0% 0% white" align="center"><span style="font-family:"Helvetica","sans-serif""><hr size="1" width="100%" align="center"></span></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><b><span style="font-size:10pt;font-family:"Arial","sans-serif"">From:</span></b><span style="font-size:10pt;font-family:"Arial","sans-serif""> Oliver Burgfeld <<a>oliver....@gmail.com</a>><br><b>To:</b> <a>postgi...@googlegroups.com</a> <br><b>Cc:</b> <a>pcr...@pcreso.com</a>; <a>postgi...@lists.osgeo.org</a>; <a>postgi...@lists.osgeo.org</a>; <a>remi...@gmail.com</a> <br><b>Sent:</b> Wednesday, November 26, 2014 5:10 AM<br><b>Subject:</b> Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Thank you and all the others who were answering :)<br><br>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.<br><br>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. <br>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.<br><br>For example:<br><br>vehicleid | status | time_start | time_end<br><br><br>I hope its understandable and not to mixed up...<br><br>Thanks!<br><br><br>Am Dienstag, 25. November 2014 16:06:33 UTC+1 schrieb Rémi Cura: <u></u><u></u></span></p><div><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Hey, a small correction :<u></u><u></u></span></p></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">ST_MakeLine is already an aggregate, and you may want to enforce the order inside the aggregate (see at the end).<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Another interesting point is the possiblity to pu somehting in the M value of each point of the line, for instance the time.<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">This comes very handy when you want to extrat parts of the lines.<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">So for instance for the first proposition :<u></u><u></u></span></p><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">WITH multis AS (</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                SELECT id, status,<b> ST_MakeLine( point_geom ORDER BY time_field) </b>AS mylines</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                FROM your_table</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">GROUP BY id, status</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">)</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">FROM multis</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Cheers,<br>Rémi-c<u></u><u></u></span></p><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div></div></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">2014-11-25 9:53 GMT+01:00 Brent Wood <<a>pcr...@pcreso.com</a>>:<u></u><u></u></span></p><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">or automatically get the start & end times for each trackline in the record like this:<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">WITH multis AS (</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                SELECT id, min(time_field) AS time_start, max(time_field) as time_end, status, ST_MakeLine(array_agg(point_ geom )) AS mylines</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                FROM your_table</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">GROUP BY id, status</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">ORDER BY time_field</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">)</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">FROM multis;</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">Cheers,</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">   Brent Wood</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><div><div><div class="MsoNormal" style="text-align:center;background:none repeat scroll 0% 0% white" align="center"><span style="font-family:"Helvetica","sans-serif""><hr size="1" width="100%" align="center"></span></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><b><span style="font-family:"Arial","sans-serif"">From:</span></b><span style="font-family:"Arial","sans-serif""> Hugues François <<a>hugues....@irstea.fr</a>><br><b>To:</b> PostGIS Users Discussion <<a>postgi...@lists.osgeo.org </a>> <br><b>Sent:</b> Tuesday, November 25, 2014 8:13 PM<br><b>Subject:</b> Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p><div><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"">Hello,</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif""> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">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:</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">WITH multis AS (</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                SELECT id, status, ST_MakeLine(array_agg(point_ geom )) AS mylines</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">                FROM your_table</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">GROUP BY id, status</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">ORDER BY time_field</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">)</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">FROM multis</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">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.</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB">Hugues.</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="text-indent:35.4pt;background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-size:11pt;font-family:"Helvetica","sans-serif"" lang="EN-GB"> </span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><b><span style="font-size:10pt;font-family:"Helvetica","sans-serif"">De :</span></b><span style="font-size:10pt;font-family:"Helvetica","sans-serif""> <a>postgis-us...@lists</a>. <a href="http://osgeo.org" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fosgeo.org\46sa\75D\46sntz\0751\46usg\75AFQjCNGzo58xIncbWoe_IjgrUm3Hi05phA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fosgeo.org\46sa\75D\46sntz\0751\46usg\75AFQjCNGzo58xIncbWoe_IjgrUm3Hi05phA';return true;">osgeo.org</a> [mailto:<a>postgis-us...@</a> <a href="http://lists.osgeo.org" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org\46sa\75D\46sntz\0751\46usg\75AFQjCNGMc9OLd_qZxg33LCV_lF870PnRpA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org\46sa\75D\46sntz\0751\46usg\75AFQjCNGMc9OLd_qZxg33LCV_lF870PnRpA';return true;">lists.osgeo.org</a>] <b>De la part de</b> Oliver Burgfeld<br><b>Envoyé :</b> mardi 25 novembre 2014 07:09<br><b>À :</b> <a>postgi...@lists.osgeo.org</a><br><b>Objet :</b> [postgis-users] Creating trajectory/lines from millions of points[PostGIS]</span><span style="font-family:"Helvetica","sans-serif""><u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""> <u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><div><div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Hi,<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">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.<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">I have one column representing the "taxi_is_occupied" status with 0 or 1. <u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">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.<u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">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. <u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""> <u></u><u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">Thanks a lot<u></u><u></u></span></p></div></div></div></div></div></div></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif"">______________________________ _________________<br>postgis-users mailing list<br><a>postgi...@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;">http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users</a><u></u><u></u></span></p></div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div></div></div></div></div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><br>______________________________ _________________<br>postgis-users mailing list<br><a>postgi...@lists.osgeo.org</a> <u></u><u></u></span></p><div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;">http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users</a><u></u><u></u></span></p></div></div><div><p class="MsoNormal" style="background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div></div></div></div></div><p class="MsoNormal" style="margin-bottom:12pt;background:none repeat scroll 0% 0% white"><span style="font-family:"Helvetica","sans-serif""><u></u> <u></u></span></p></div></div></div></div></div></div><p class="MsoNormal"><br><br><br><u></u><u></u></p><pre>______________________________<wbr>_________________<u></u><u></u></pre><pre>postgis-users mailing list<u></u><u></u></pre><pre><a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">postgi...@lists.osgeo.org</a><u></u><u></u></pre><pre><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;">http://lists.osgeo.org/cgi-<wbr>bin/mailman/listinfo/postgis-<wbr>users</a><u></u><u></u></pre><p class="MsoNormal"><br><br><br><u></u><u></u></p><pre>-- <u></u><u></u></pre><pre>[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.<u></u><u></u></pre><pre>Donald Knuth<u></u><u></u></pre></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="javascript:" target="_blank" gdf-obfuscated-mailto="bEtXiXR1zw4J" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">postgi...@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Flists.osgeo.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fpostgis-users\46sa\75D\46sntz\0751\46usg\75AFQjCNFq7nnxngR7sOb6icF73DL5HQVrbA';return true;">http://lists.osgeo.org/cgi-<wbr>bin/mailman/listinfo/postgis-<wbr>users</a><br></blockquote></div><br></div></div>
</blockquote></div>