<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 11/25/2014 11:48 AM, Oliver Burgfeld
wrote:<br>
</div>
<blockquote
cite="mid:270e3db9-cd90-46db-8de0-556554d22492@googlegroups.com"
type="cite">
<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
cite="mid:270e3db9-cd90-46db-8de0-556554d22492@googlegroups.com"
type="cite">
<div dir="ltr"> <br>
Am Dienstag, 25. November 2014 17:39:21 UTC+1 schrieb Brent
Wood:
<blockquote class="gmail_quote" 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 face="Arial" size="2"> <b><span
style="font-weight:bold">From:</span></b>
Oliver Burgfeld <<a moz-do-not-send="true"
href="javascript:" target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">oliver....@gmail.com</a>><br>
<b><span style="font-weight:bold">To:</span></b> <a
moz-do-not-send="true" href="javascript:"
target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">postgi...@googlegroups.com</a> <br>
<b><span style="font-weight:bold">Cc:</span></b> <a
moz-do-not-send="true" href="javascript:"
target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">pcr...@pcreso.com</a>; <a
moz-do-not-send="true" href="javascript:"
target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">postgi...@lists.osgeo.org</a>; <a
moz-do-not-send="true" href="javascript:"
target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">postgi...@lists.osgeo.org</a>; <a
moz-do-not-send="true" href="javascript:"
target="_blank"
gdf-obfuscated-mailto="uCjnawqMU8EJ"
onmousedown="this.href='javascript:';return
true;" onclick="this.href='javascript:';return
true;">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 clear="none">
<br clear="none">
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
clear="none">
<br clear="none">
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
clear="none">
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
clear="none">
<br clear="none">
For example:<br clear="none">
<br clear="none">
vehicleid | status | time_start | time_end<br
clear="none">
<br clear="none">
<br clear="none">
I hope its understandable and not to mixed
up...<br clear="none">
<br clear="none">
Thanks!<br clear="none">
<br clear="none">
<br clear="none">
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
clear="none">
</div>
ST_MakeLine is already an aggregate,
and you may want to enforce the order
inside the aggregate (see at the end).<br
clear="none">
</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 clear="none">
</div>
<div>This comes very handy when you want
to extrat parts of the lines.<br
clear="none">
</div>
<div><br clear="none">
</div>
<div><br clear="none">
</div>
So for instance for the first
proposition :<br clear="none">
<br clear="none">
<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
clear="none">
<br clear="none">
</div>
Cheers,<br clear="none">
Rémi-c<br clear="none">
<div>
<div><br clear="none">
</div>
</div>
</div>
<div><br clear="none">
<div>2014-11-25 9:53 GMT+01:00 Brent Wood
<span dir="ltr"><<a
moz-do-not-send="true"
rel="nofollow" shape="rect">pcr...@pcreso.com</a>></span>:<br
clear="none">
<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 clear="none">
</div>
<div dir="ltr"><br clear="none">
</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 clear="none">
<span style="font-size:11.0pt"
lang="EN-GB"></span></div>
<div><br clear="none">
<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
clear="none">
</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
moz-do-not-send="true"
rel="nofollow"
shape="rect">hugues....@irstea.fr</a>><br
clear="none">
<b><span
style="font-weight:bold">To:</span></b>
PostGIS Users Discussion
<<a
moz-do-not-send="true"
rel="nofollow"
shape="rect">postgi...@lists.osgeo.org</a>
> <br clear="none">
<b><span
style="font-weight:bold">Sent:</span></b>
Tuesday, November 25, 2014
8:13 PM<br clear="none">
<b><span
style="font-weight:bold">Subject:</span></b>
Re: [postgis-users]
Creating trajectory/lines
from millions of
points[PostGIS]<br
clear="none">
</font> </div>
<div>
<div>
<div><br clear="none">
<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 moz-do-not-send="true" rel="nofollow"
shape="rect">postgis-us...@lists.
osgeo.org</a>
[mailto:<a
moz-do-not-send="true"
rel="nofollow"
shape="rect">postgis-us...@
lists.osgeo.org</a>] <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> <a
moz-do-not-send="true" rel="nofollow" shape="rect">postgi...@lists.osgeo.org</a><br
clear="none">
<b>Objet :</b>
[postgis-users]
Creating
trajectory/lines
from millions
of
points[PostGIS]</span></div>
<div> </div>
<div><br
clear="none">
<br clear="none">
</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
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 clear="none">
</div>
</div>
<div>______________________________
_________________<br
clear="none">
postgis-users mailing list<br
clear="none">
<a moz-do-not-send="true"
rel="nofollow"
shape="rect">postgi...@lists.osgeo.org</a><br
clear="none">
<a moz-do-not-send="true"
rel="nofollow"
shape="rect"
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></div>
<br clear="none">
<br clear="none">
</div>
</div>
</div>
</div>
</div>
<br clear="none">
______________________________
_________________<br clear="none">
postgis-users mailing list<br
clear="none">
<a moz-do-not-send="true"
rel="nofollow" shape="rect">postgi...@lists.osgeo.org</a>
<div><br>
<br>
</div>
<div><br clear="none">
<a moz-do-not-send="true"
rel="nofollow" shape="rect"
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><br clear="none">
</div>
</blockquote>
</div>
<div><br clear="none">
</div>
</div>
<div>
</div>
</blockquote>
</div>
</div>
</div>
<br>
<br>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">--
[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</pre>
</body>
</html>