[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Creating trajectory/lines from millions of points[PostGIS]
From: toni hernández <toni () sigte ! udg ! edu>
Date: 2014-12-02 10:23:57
Message-ID: 547D933D.4080505 () sigte ! udg ! edu
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I am going to change the query to CTE structure to make it more
readable. To know more about CTE take a look at
http://www.postgresql.org/docs/8.4/static/queries-with.html
The CTE query is then like:
with
--first part
status_flag as(
select vehicle_id, geom, time, taxi_is_occupied, case when
(taxi_is_occupied <> lag(taxi_is_occupied,1) over(order by time)) then 1
else 0 end as taxi_status_change from gpx
)
,
--second part
track_group as (
select vehicle_id,geom, time, taxi_is_occupied, sum(taxi_status_change)
over (order by time) as track_num from status_flag
)
--third part
select vehicle_id, st_makeline(geom), track_num, taxi_is_occupied from
track_group
group by track_num,taxi_is_occupied, vehicle_id order by track_num
Yes. With the first part of the query (status_flag) you get the same
amount of raws but with an extra column "taxi_status_change".
This extra column detects when a change in the taxi_is_occupied value
occurs.
The second part (track_group), keeps the extra column but changes its
value. The new value makes it possible to group (in the third part of
the query) all the consecutive points with the same taxi_is_occupied value.
On 02/12/2014 08:59, Oliver Burgfeld wrote:
> Hi and thanks, that's exactly what I was looking for.
>
> But nevertheless it seems not to work as it's supposed to, I think.
>
> If I just run the first query to detect when the status changes, I get
> a really strange result.
> I have at least the same amount of rows as in my original table.
> 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.
>
> Or did I miss a thing here?
>
>
> Am Montag, 1. Dezember 2014 10:50:09 UTC+1 schrieb toni hernández:
>
> Hi,
>
> Maybe this is already solved but anyway....
>
> 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.
>
> 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
>
> This sentence detects when taxi changes status:
> select geom, time, status, case when (status <> lag(status,1)
> over(order by time)) then 1 else 0 end as canvi from table
>
> From there you can count the number of times the
> "taxi_is_occupied" has changed.
>
> select geom, time, status, sum(canvi) over (order by time) as
> track_num from
> (
> select geom, time, status, case when (status <> lag(status,1)
> over(order by time)) then 1 else 0 end as canvi from table
> ) as foo
>
>
> And finally , you can use the previous SELECT statement to create
> all tracks for all taxis
>
> select vehicleid, st_makeline(geom), track_num, status
> from
> (
> select vehicleid,geom, time, status, sum(canvi) over (order by
> time) as track_num from
> (
> select vehicleid, geom, time, status, case when (status <>
> lag(status,1) over(order by time)) then 1 else 0 end as canvi from
> table
> ) as foo
> ) as fooo
> group by track_num, status, vehicleid
> order by track_num
>
>
>
> On 25/11/2014 19:16, Roxanne Reid-Bennett wrote:
>
> On 11/25/2014 11:48 AM, Oliver Burgfeld wrote:
>
> I also tried that and it works but it does not give me
> those two columns in my new table. There are only id and
> status inside.
>
> From the quer below change
> SELECT id, status, (ST_Dump(mylines)).geom
> to
>
> SELECT id, status, (ST_Dump(mylines)).geom, time_start, time_end
>
> Something to keep an eye out for... Depending upon your
> version of PostGIS and the underlying libraries, because we
> were working with an older version of the underlying
> libraries, I don't know if this is still a potential issue.
> We ran into issues with "stacked" points (2 GPS points with
> the same coordinates), and GPS drift causing issues with the
> linestrings being pretty goofy (jagged points in the
> linestring that were clearly NOT what the vehicle did). We
> wrote cleanup scripts to take care of those issues (that are
> still in place). I've not taken time to revisit the library
> routines to see if they now handle those conditions cleanly.
>
> Roxanne
>
>
> Am Dienstag, 25. November 2014 17:39:21 UTC+1 schrieb
> Brent Wood:
>
> as in my previous reply, I figured that would be useful...
> WITH multis AS (
> SELECT id, status,*min(timestamp) as time_start,
> max(timestamp) as time_end, *ST_MakeLine( point_geom
> ORDER BY timestamp) AS mylines
> FROM your_table
> GROUP BY id, status
> )
> SELECT id, status, (ST_Dump(mylines)).geom
> FROM multisBrent Wood
> ------------------------------------------------------------------------
> *From:* Oliver Burgfeld <oliver....@gmail.com>
> *To:* postgi...@googlegroups.com
> *Cc:* pcr...@pcreso.com; postgi...@lists.osgeo.org;
> postgi...@lists.osgeo.org; remi...@gmail.com
> *Sent:* Wednesday, November 26, 2014 5:10 AM
> *Subject:* Re: [postgis-users] Creating
> trajectory/lines from millions of points[PostGIS]
>
> Thank you and all the others who were answering :)
>
> I tried that and it seems that its working.
> Nevertheless I only tried it with a small part of my
> data (round about 1 million rows out of ~500 million)
> but if it's working now, it should also work with the
> whole dataset.
>
> Is there a way to also include the time_field into the
> result? I created a new table with this statement
> given but there are only two columns (vehicleid and
> status) included.
> I know thats logical because I only included those two
> into my select clause but it would be great to not
> only order by time but also have a time column in my
> table.
>
> For example:
>
> vehicleid | status | time_start | time_end
>
>
> I hope its understandable and not to mixed up...
>
> Thanks!
>
>
> Am Dienstag, 25. November 2014 16:06:33 UTC+1 schrieb
> Rémi Cura:
>
> Hey, a small correction :
> ST_MakeLine is already an aggregate, and you may
> want to enforce the order inside the aggregate
> (see at the end).
> Another interesting point is the possiblity to pu
> somehting in the M value of each point of the
> line, for instance the time.
> This comes very handy when you want to extrat
> parts of the lines.
>
>
> So for instance for the first proposition :
>
> WITH multis AS (
> SELECT id, status,*ST_MakeLine( point_**geom ORDER
> BY time_field) *AS mylines
> FROM your_table
> GROUP BY id, status
> )
> SELECT id, status, (ST_Dump(mylines)).geom
> FROM multis
>
> Cheers,
> Rémi-c
>
>
> 2014-11-25 9:53 GMT+01:00 Brent Wood
> <pcr...@pcreso.com>:
>
> or automatically get the start & end times for
> each trackline in the record like this:
>
> WITH multis AS (
> SELECT id, min(time_field) AS time_start,
> max(time_field) as time_end, status,
> ST_MakeLine(array_agg(point_ geom )) AS mylines
> FROM your_table
> GROUP BY id, status
> ORDER BY time_field
> )
> SELECT id, status, (ST_Dump(mylines)).geom
> FROM multis;
>
>
> Cheers,
> Brent Wood
> ------------------------------------------------------------------------
> *From:* Hugues François <hugues....@irstea.fr>
> *To:* PostGIS Users Discussion
> <postgi...@lists.osgeo.org >
> *Sent:* Tuesday, November 25, 2014 8:13 PM
> *Subject:* Re: [postgis-users] Creating
> trajectory/lines from millions of points[PostGIS]
>
> Hello,
> In your case I would have try to make
> multilines for each taxi and each status (i.e.
> two multi by taxi) and then dump them into
> simple linestrings. All in a query that may
> look like this assuming you have a taxi id field:
> WITH multis AS (
> SELECT id, status,
> ST_MakeLine(array_agg(point_ geom )) AS mylines
> FROM your_table
> GROUP BY id, status
> ORDER BY time_field
> )
> SELECT id, status, (ST_Dump(mylines)).geom
> FROM multis
> You may want to add a time reference to your
> lines. To do this, you can add an extraction
> from your timestamp field (e.g. day or month)
> and add it into the WITH and to the group by
> clause.
> Hugues.
> *De :*postgis-us...@lists. osgeo.org
> [mailto:postgis-us...@ lists.osgeo.org] *De la
> part de* Oliver Burgfeld
> *Envoyé :* mardi 25 novembre 2014 07:09
> *À :* postgi...@lists.osgeo.org
> *Objet :* [postgis-users] Creating
> trajectory/lines from millions of points[PostGIS]
>
>
> Hi,
> I have millions of points in a PostGIS
> database containing taxi gps tracks. Now I
> want to create lines from these points by
> vehicleid and ordered by timestamp. But, and
> that's my problem right now, at first I want
> to include every column of my point table into
> the "line table" and I also need to intersect
> those lines at specific points.
> I have one column representing the
> "taxi_is_occupied" status with 0 or 1.
> What I want now is to create lines which are
> divided every time this status changes. In the
> end I need lines which show the path of every
> taxi over time, divided every time the status
> of the car changes so that I can query all
> lines where the taxi is occupied, for example.
> What do I have to use therefore? I know that
> there is the ST_MakeLines tool existing in
> PostGIS, but as I am a new PostGIS
>
> ...
>
[Attachment #5 (text/html)]
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
I am going to change the query to CTE structure to make it more
readable. To know more about CTE take a look at
<a class="moz-txt-link-freetext" \
href="http://www.postgresql.org/docs/8.4/static/queries-with.html">http://www.postgresql.org/docs/8.4/static/queries-with.html</a><br>
<br>
The CTE query is then like:<br>
<br>
with<br>
--first part<br>
status_flag as(<br>
select vehicle_id, geom, time, taxi_is_occupied, case when
(taxi_is_occupied <> lag(taxi_is_occupied,1) over(order by
time)) then 1 else 0 end as taxi_status_change from gpx<br>
)<br>
,<br>
--second part <br>
track_group as (<br>
select vehicle_id,geom, time, taxi_is_occupied,
sum(taxi_status_change) over (order by time) as track_num from
status_flag<br>
)<br>
<br>
--third part<br>
select vehicle_id, st_makeline(geom), track_num, taxi_is_occupied
from track_group<br>
group by track_num,taxi_is_occupied, vehicle_id order by track_num<br>
<br>
<br>
<br>
Yes. With the first part of the query (status_flag) you get the same
amount of raws but with an extra column "taxi_status_change".<br>
This extra column detects when a change in the taxi_is_occupied
value occurs.<br>
<br>
<br>
The second part (track_group), keeps the extra column but changes
its value. The new value makes it possible to group (in the third
part of the query) all the consecutive points with the same
taxi_is_occupied value.<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">On 02/12/2014 08:59, Oliver Burgfeld
wrote:<br>
</div>
<blockquote
cite="mid:351b9ebd-8fe4-499f-92b5-d8c0dbbe8c36@googlegroups.com"
type="cite">
<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 face="Arial" size="2">
<b><span style="font-weight:bold">From:</span></b>
Oliver Burgfeld <<a
\
moz-do-not-send="true">oliver....@gmail.com</a>><br>
<b><span style="font-weight:bold">To:</span></b>
<a \
moz-do-not-send="true">postgi...@googlegroups.com</a> <br>
<b><span style="font-weight:bold">Cc:</span></b>
<a moz-do-not-send="true">pcr...@pcreso.com</a>;
<a \
moz-do-not-send="true">postgi...@lists.osgeo.org</a>;
<a \
moz-do-not-send="true">postgi...@lists.osgeo.org</a>;
<a moz-do-not-send="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>
<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
moz-do-not-send="true"
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
moz-do-not-send="true"
rel="nofollow"
\
shape="rect">hugues....@irstea.fr</a>><br> <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>
<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
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>
<b>Envoyé :</b>
mardi 25
novembre 2014
07:09<br>
<b>À :</b> <a
moz-do-not-send="true" 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>
</blockquote>
<br>
</body>
</html>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic