<div dir="ltr">Hi and thanks, that's exactly what I was looking for.<br><br>But nevertheless it seems not to work as it's supposed to, I think.<br><br>If I just run the first query to detect when the status changes, I get a really strange result.<br>I have at least the same amount of rows as in my original table.<br>But the status does not change at every point of course, so the result has to be lower. As I have only very basic knowledge of SQL, I really don't know how to handle this strange behaviour. <br><br>Or did I miss a thing here?<br><br><br>Am Montag, 1. Dezember 2014 10:50:09 UTC+1 schrieb toni hernández:<blockquote class="gmail_quote" style="margin: 0;margin-left: 0.8ex;border-left: 1px #ccc solid;padding-left: 1ex;">
  
  
    Hi,<br>
    <br>
    Maybe this is already solved but anyway....<br>
    <br>
    As the "taxi_is_occupied" field  is boolean (or binary), if you
    group by this field you will get only two multilinestrings for each
    vehicle. One multilinestring when "taxi_is_occupied" is true, and
    one multilinestring when is false.<br>
    <br>
    If you want to get as many geometries as clients a taxi has done, 
    then you can use the window functions "LAG". With this function you
    can detect when the taxi changes from free to occupied and viceversa<br>
    <br>
    This sentence detects when taxi changes status:<br>
    select geom, time, status, case when (status <> lag(status,1)
    over(order by time)) then 1 else 0 end as canvi from table<br>
    <br>
    From there you can count the number of times the "taxi_is_occupied"
    has changed. <br>
    <br>
    select geom, time, status, sum(canvi) over (order by time) as
    track_num from<br>
    (<br>
    select geom, time, status, case when (status <> lag(status,1)
    over(order by time)) then 1 else 0 end as canvi from table<br>
    ) as foo<br>
    <br>
    <br>
    And finally , you can use the previous SELECT statement to create
    all tracks for all taxis<br>
    <br>
    select vehicleid, st_makeline(geom), track_num, status<br>
    from<br>
    (<br>
    select vehicleid,geom, time, status, sum(canvi) over (order by time)
    as track_num from<br>
    (<br>
    select vehicleid, geom, time, status, case when (status <>
    lag(status,1) over(order by time)) then 1 else 0 end as canvi from
    table<br>
    ) as foo<br>
    ) as fooo<br>
    group by track_num, status, vehicleid<br>
    order by track_num<br>
    <br>
    <br>
    <br>
    <div>On 25/11/2014 19:16, Roxanne
      Reid-Bennett wrote:<br>
    </div>
    <blockquote>
      
      <div>On 11/25/2014 11:48 AM, Oliver
        Burgfeld wrote:<br>
      </div>
      <blockquote>
        <div dir="ltr">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.<br>
          <br>
        </div>
      </blockquote>
      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, </span><span style="font-size:11pt" lang="EN-GB"><span style="font-size:11pt" lang="EN-GB">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).   </span>
        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>
      </span>
      <blockquote>
        <div dir="ltr"> <br>
          Am Dienstag, 25. November 2014 17:39:21 UTC+1 schrieb Brent
          Wood:
          <blockquote style="margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-left:1ex">
            <div>
              <div style="color:#000;background-color:#fff;font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                <div dir="ltr">as in my previous reply, I figured that
                  would be useful...<br>
                </div>
                <div><span style="font-size:11pt" lang="EN-GB">WITH
                    multis AS (</span></div>
                <div><span style="font-size:11pt" lang="EN-GB">               

                    SELECT id, status,<b> min(timestamp) as time_start,
                      max(timestamp) as time_end, </b><span>ST_MakeLine(

                      point_</span><span>geom ORDER BY timestamp</span><span>)
                    </span>AS mylines</span></div>
                <div><span style="font-size:11pt" lang="EN-GB">               

                    FROM your_table</span></div>
                <div style="text-indent:35.4pt"><span style="font-size:11pt" lang="EN-GB">GROUP BY id,
                    status</span></div>
                <div style="text-indent:35.4pt"><span style="font-size:11pt" lang="EN-GB">)</span></div>
                <div><span style="font-size:11pt" lang="EN-GB"> </span></div>
                <div><span style="font-size:11pt" lang="EN-GB">SELECT
                    id, status, (ST_Dump(mylines)).geom</span></div>
                <span style="font-size:11pt" lang="EN-GB">FROM multis</span>Brent

                Wood<br>
                <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                  <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                    <div dir="ltr">
                      <hr size="1"> <font size="2" face="Arial"> <b><span style="font-weight:bold">From:</span></b>
                        Oliver Burgfeld <<a>oliver....@gmail.com</a>><br>
                        <b><span style="font-weight:bold">To:</span></b>
                        <a>postgi...@googlegroups.com</a> <br>
                        <b><span style="font-weight:bold">Cc:</span></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><span style="font-weight:bold">Sent:</span></b>
                        Wednesday, November 26, 2014 5:10 AM<br>
                        <b><span style="font-weight:bold">Subject:</span></b>
                        Re: [postgis-users] Creating trajectory/lines
                        from millions of points[PostGIS]<br>
                      </font> </div>
                    <div><br>
                      <div>
                        <div>
                          <div dir="ltr">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:
                            <blockquote style="margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-left:1ex">
                              <div dir="ltr">
                                <div>
                                  <div>
                                    <div>Hey, a small correction :<br>
                                    </div>
                                    ST_MakeLine is already an aggregate,
                                    and you may want to enforce the
                                    order inside the aggregate (see at
                                    the end).<br>
                                  </div>
                                  <div>Another interesting point is the
                                    possiblity to pu somehting in the M
                                    value of each point of the line, for
                                    instance the time.<br>
                                  </div>
                                  <div>This comes very handy when you
                                    want to extrat parts of the lines.<br>
                                  </div>
                                  <div><br>
                                  </div>
                                  <div><br>
                                  </div>
                                  So for instance for the first
                                  proposition :<br>
                                  <br>
                                  <div><span style="font-size:11pt" lang="EN-GB">WITH multis AS (</span></div>
                                  <div><span style="font-size:11pt" lang="EN-GB">               
                                      SELECT id, status,<b> ST_MakeLine(
                                        point_</b><b>geom ORDER BY
                                        time_field) </b>AS mylines</span></div>
                                  <div><span style="font-size:11pt" lang="EN-GB">                FROM
                                      your_table</span></div>
                                  <div style="text-indent:35.4pt"><span style="font-size:11pt" lang="EN-GB">GROUP BY id, status</span></div>
                                  <div style="text-indent:35.4pt"><span style="font-size:11pt" lang="EN-GB">)</span></div>
                                  <div><span style="font-size:11pt" lang="EN-GB"> </span></div>
                                  <div><span style="font-size:11pt" lang="EN-GB">SELECT id, status,
                                      (ST_Dump(mylines)).geom</span></div>
                                  <span style="font-size:11pt" lang="EN-GB">FROM multis</span><br>
                                  <br>
                                </div>
                                Cheers,<br>
                                Rémi-c<br>
                                <div>
                                  <div><br>
                                  </div>
                                </div>
                              </div>
                              <div><br>
                                <div>2014-11-25 9:53 GMT+01:00 Brent
                                  Wood <span dir="ltr"><<a rel="nofollow" shape="rect">pcr...@pcreso.com</a>></span>:<br>
                                  <blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
                                    <div>
                                      <div style="color:#000;background-color:#fff;font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                                        <div><span></span></div>
                                        <div dir="ltr">or automatically
                                          get the start & end times
                                          for each trackline in the
                                          record like this:<br>
                                        </div>
                                        <div dir="ltr"><br>
                                        </div>
                                        <div><span style="font-size:11.0pt" lang="EN-GB">WITH multis AS
                                            (</span></div>
                                        <div dir="ltr"><span 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>
                                        <span></span>
                                        <div><span style="font-size:11.0pt" lang="EN-GB">               
                                            FROM your_table</span></div>
                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">GROUP BY id,
                                            status</span></div>
                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">ORDER BY
                                            time_field</span></div>
                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">)</span></div>
                                        <div><span style="font-size:11.0pt" lang="EN-GB">  </span></div>
                                        <div><span style="font-size:11.0pt" lang="EN-GB">SELECT id,
                                            status,
                                            (ST_Dump(mylines)).geom</span></div>
                                        <div><span style="font-size:11.0pt" lang="EN-GB">FROM multis;</span></div>
                                        <div><br>
                                          <span style="font-size:11.0pt" lang="EN-GB"></span></div>
                                        <div><br>
                                          <span style="font-size:11.0pt" lang="EN-GB"></span></div>
                                        <div dir="ltr"><span style="font-size:11.0pt" lang="EN-GB">Cheers,</span></div>
                                        <div dir="ltr"><span style="font-size:11.0pt" lang="EN-GB">   Brent Wood<br>
                                          </span></div>
                                        <div dir="ltr"> </div>
                                        <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                                          <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px">
                                            <div dir="ltr">
                                              <hr size="1"> <font face="Arial"> <b><span style="font-weight:bold">From:</span></b> Hugues François <<a rel="nofollow" shape="rect">hugues....@irstea.fr</a>><br>
                                                <b><span style="font-weight:bold">To:</span></b>
                                                PostGIS Users Discussion
                                                <<a rel="nofollow" shape="rect">postgi...@lists.osgeo.org</a>
                                                > <br>
                                                <b><span style="font-weight:bold">Sent:</span></b>
                                                Tuesday, November 25,
                                                2014 8:13 PM<br>
                                                <b><span style="font-weight:bold">Subject:</span></b>
                                                Re: [postgis-users]
                                                Creating
                                                trajectory/lines from
                                                millions of
                                                points[PostGIS]<br>
                                              </font> </div>
                                            <div>
                                              <div>
                                                <div><br>
                                                  <div>
                                                    <div>
                                                      <div>
                                                        <div><span style="font-size:11.0pt">Hello,</span></div>
                                                        <div><span style="font-size:11.0pt">
                                                           </span></div>
                                                        <div><span 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><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">WITH

                                                          multis AS (</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">               

                                                          SELECT id,
                                                          status,
                                                          ST_MakeLine(array_agg(point_
                                                          geom )) AS
                                                          mylines</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">               

                                                          FROM
                                                          your_table</span></div>
                                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">GROUP BY id, status</span></div>
                                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">ORDER BY time_field</span></div>
                                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">)</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">SELECT

                                                          id, status,
                                                          (ST_Dump(mylines)).geom</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">FROM

                                                          multis</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><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><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">Hugues.</span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">  </span></div>
                                                        <div style="text-indent:35.4pt"><span style="font-size:11.0pt" lang="EN-GB">  </span></div>
                                                        <div><span style="font-size:11.0pt" lang="EN-GB">
                                                           </span></div>
                                                        <div><b><span style="font-size:10.0pt">De :</span></b><span style="font-size:10.0pt"> <a rel="nofollow" shape="rect">postgis-us...@lists.

                                                          osgeo.org</a>
                                                          [mailto:<a rel="nofollow" shape="rect">postgis-us...@
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 rel="nofollow" shape="rect">postgi...@lists.osgeo.org</a><br>
                                                          <b>Objet :</b>
                                                          [postgis-users]

                                                          Creating
                                                          trajectory/lines
                                                          from millions
                                                          of
                                                          points[PostGIS]</span></div>
                                                        <div>  </div>
                                                        <div><br>
                                                          <br>
                                                        </div>
                                                        <div>
                                                          <div>
                                                          <div>
                                                          <div>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
                                                       </div></div></div></div></div></div></div></div></div></div></div></div></div></div></blockquote></div></div></blockquote></div></div></div></div></div></div></div></div></blockquote></div></blockquote></blockquote>...</blockquote></div>