<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div id="yui_3_16_0_1_1416636367288_504422"><span id="yui_3_16_0_1_1416636367288_504731"></span></div><div dir="ltr" id="yui_3_16_0_1_1416636367288_504522">or automatically get the start & end times for each trackline in the record like this:<br></div><div id="yui_3_16_0_1_1416636367288_504574" dir="ltr"><br></div><div style="" id="yui_3_16_0_1_1416636367288_504535" class=""><span class="" id="yui_3_16_0_1_1416636367288_504536" style="font-size:11.0pt;" lang="EN-GB">WITH multis AS (</span></div><div dir="ltr" style="" id="yui_3_16_0_1_1416636367288_504537" class=""><span class="" id="yui_3_16_0_1_1416636367288_504538" style="font-size:11.0pt;" 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></div><div style="" id="yui_3_16_0_1_1416636367288_504540" class=""><span class="" id="yui_3_16_0_1_1416636367288_504539" style="font-size:11.0pt;" lang="EN-GB"> FROM your_table</span></div><div id="yui_3_16_0_1_1416636367288_504541" class="" style="text-indent:35.4pt;"><span id="yui_3_16_0_1_1416636367288_504572" class="" style="font-size:11.0pt;" lang="EN-GB">GROUP BY id, status</span></div><div id="yui_3_16_0_1_1416636367288_504542" class="" style="text-indent:35.4pt;"><span id="yui_3_16_0_1_1416636367288_504573" class="" style="font-size:11.0pt;" lang="EN-GB">ORDER BY time_field</span></div><div id="yui_3_16_0_1_1416636367288_504543" class="" style="text-indent:35.4pt;"><span class="" id="yui_3_16_0_1_1416636367288_504544" style="font-size:11.0pt;" lang="EN-GB">)</span></div><div style="" id="yui_3_16_0_1_1416636367288_504545" class=""><span class="" style="font-size:11.0pt;" lang="EN-GB"> </span></div><div style="" id="yui_3_16_0_1_1416636367288_504546" class=""><span class="" id="yui_3_16_0_1_1416636367288_504547" style="font-size:11.0pt;" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span></div><div style="" id="yui_3_16_0_1_1416636367288_504548" class=""><span class="" id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB">FROM multis;</span></div><div id="yui_3_16_0_1_1416636367288_504640" style="" class=""><br><span class="" id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB"></span></div><div id="yui_3_16_0_1_1416636367288_504641" style="" class=""><br><span class="" id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB"></span></div><div id="yui_3_16_0_1_1416636367288_504642" dir="ltr" style="" class=""><span class="" id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB">Cheers,</span></div><div id="yui_3_16_0_1_1416636367288_504643" dir="ltr" style="" class=""><span class="" id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB"> Brent Wood<br></span></div><div dir="ltr"> </div><div id="yui_3_16_0_1_1416636367288_504425" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1416636367288_504424" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1416636367288_504423" dir="ltr"> <hr id="yui_3_16_0_1_1416636367288_504506" size="1"> <font id="yui_3_16_0_1_1416636367288_504526" face="Arial" size="2"> <b id="yui_3_16_0_1_1416636367288_504525"><span id="yui_3_16_0_1_1416636367288_504524" style="font-weight:bold;">From:</span></b> Hugues François <hugues.francois@irstea.fr><br> <b id="yui_3_16_0_1_1416636367288_504645"><span id="yui_3_16_0_1_1416636367288_504644" style="font-weight: bold;">To:</span></b> PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br> <b id="yui_3_16_0_1_1416636367288_504647"><span id="yui_3_16_0_1_1416636367288_504646" style="font-weight: bold;">Sent:</span></b> Tuesday, November 25, 2014 8:13 PM<br> <b id="yui_3_16_0_1_1416636367288_504649"><span id="yui_3_16_0_1_1416636367288_504648" style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]<br> </font> </div> <div id="yui_3_16_0_1_1416636367288_504531" class="y_msg_container"><br><div id="yiv8453726355"><style>#yiv8453726355 #yiv8453726355 --
_filtered #yiv8453726355 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
_filtered #yiv8453726355 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
#yiv8453726355
#yiv8453726355 p.yiv8453726355MsoNormal, #yiv8453726355 li.yiv8453726355MsoNormal, #yiv8453726355 div.yiv8453726355MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}
#yiv8453726355 a:link, #yiv8453726355 span.yiv8453726355MsoHyperlink
{color:blue;text-decoration:underline;}
#yiv8453726355 a:visited, #yiv8453726355 span.yiv8453726355MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}
#yiv8453726355 p
{margin-right:0cm;margin-left:0cm;font-size:12.0pt;}
#yiv8453726355 span.yiv8453726355EmailStyle18
{color:#1F497D;}
#yiv8453726355 .yiv8453726355MsoChpDefault
{}
_filtered #yiv8453726355 {margin:70.85pt 70.85pt 70.85pt 70.85pt;}
#yiv8453726355 div.yiv8453726355WordSection1
{}
#yiv8453726355 </style><div id="yui_3_16_0_1_1416636367288_504530"><div id="yui_3_16_0_1_1416636367288_504529" class="yiv8453726355WordSection1"><div id="yui_3_16_0_1_1416636367288_504552" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504650" style="font-size:11.0pt;">Hello,</span></div><div id="yui_3_16_0_1_1416636367288_504528" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504527" style="font-size:11.0pt;"> </span></div><div id="yui_3_16_0_1_1416636367288_504533" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504532" style="font-size:11.0pt;" 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></div><div id="yui_3_16_0_1_1416636367288_504534" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504535" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504536" style="font-size:11.0pt;" lang="EN-GB">WITH multis AS (</span></div><div id="yui_3_16_0_1_1416636367288_504537" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504538" style="font-size:11.0pt;" lang="EN-GB"> SELECT id, status, ST_MakeLine(array_agg(point_geom )) AS mylines</span></div><div id="yui_3_16_0_1_1416636367288_504540" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504539" style="font-size:11.0pt;" lang="EN-GB"> FROM your_table</span></div><div id="yui_3_16_0_1_1416636367288_504541" class="yiv8453726355MsoNormal" style="text-indent:35.4pt;"><span style="font-size:11.0pt;" lang="EN-GB">GROUP BY id, status</span></div><div id="yui_3_16_0_1_1416636367288_504542" class="yiv8453726355MsoNormal" style="text-indent:35.4pt;"><span id="yui_3_16_0_1_1416636367288_504551" style="font-size:11.0pt;" lang="EN-GB">ORDER BY time_field</span></div><div id="yui_3_16_0_1_1416636367288_504543" class="yiv8453726355MsoNormal" style="text-indent:35.4pt;"><span id="yui_3_16_0_1_1416636367288_504544" style="font-size:11.0pt;" lang="EN-GB">)</span></div><div id="yui_3_16_0_1_1416636367288_504545" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504546" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504547" style="font-size:11.0pt;" lang="EN-GB">SELECT id, status, (ST_Dump(mylines)).geom</span></div><div id="yui_3_16_0_1_1416636367288_504548" class="yiv8453726355MsoNormal"><span id="yui_3_16_0_1_1416636367288_504549" style="font-size:11.0pt;" lang="EN-GB">FROM multis</span></div><div class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504654" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" 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></div><div id="yui_3_16_0_1_1416636367288_504655" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">Hugues.</span></div><div id="yui_3_16_0_1_1416636367288_504656" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504700" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504699" class="yiv8453726355MsoNormal" style="text-indent:35.4pt;"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504698" class="yiv8453726355MsoNormal" style="text-indent:35.4pt;"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504697" class="yiv8453726355MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB"> </span></div><div id="yui_3_16_0_1_1416636367288_504658" class="yiv8453726355MsoNormal"><b><span style="font-size:10.0pt;">De :</span></b><span id="yui_3_16_0_1_1416636367288_504657" style="font-size:10.0pt;"> postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-bounces@lists.osgeo.org] <b>De la part de</b> Oliver Burgfeld<br clear="none"><b>Envoyé :</b> mardi 25 novembre 2014 07:09<br clear="none"><b>À :</b> postgis-users@lists.osgeo.org<br clear="none"><b>Objet :</b> [postgis-users] Creating trajectory/lines from millions of points[PostGIS]</span></div><div id="yui_3_16_0_1_1416636367288_504696" class="yiv8453726355MsoNormal"> </div><div class="qtdSeparateBR"><br><br></div><div class="yiv8453726355yqt9846661550" id="yiv8453726355yqt00029"><div id="yui_3_16_0_1_1416636367288_504692"><div id="yui_3_16_0_1_1416636367288_504691"><div id="yui_3_16_0_1_1416636367288_504690">Hi,</div><div>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.</div><div>I have one column representing the "taxi_is_occupied" status with 0 or 1. </div><div>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.</div><div>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. </div><div> </div><div>Thanks a lot</div></div></div></div></div></div></div><br><div class="yqt9846661550" id="yqt46022">_______________________________________________<br clear="none">postgis-users mailing list<br clear="none"><a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none"><a shape="rect" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div><br><br></div> </div> </div> </div></body></html>