My fault, you also need to select these in the final select, try:
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_tableGROUP BY id, status) SELECT
id, status, time_start, time_end, (ST_Dump(mylines)).geom
Cheers,
Brent
From: Oliver Burgfeld <[email protected]>
To: [email protected]
Cc: [email protected]; [email protected];
[email protected]; [email protected]
Sent: Wednesday, November 26, 2014 5:48 AM
Subject: Re: [postgis-users] Creating trajectory/lines from millions of
points[PostGIS]
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.
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_tableGROUP BY id, status) SELECT
id, status, (ST_Dump(mylines)).geomFROM multisBrent Wood
From: Oliver Burgfeld <[email protected]>
To: [email protected]
Cc: [email protected]; [email protected]; [email protected];
[email protected]
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_tableGROUP BY id,
status) SELECT id, status, (ST_Dump(mylines)).geomFROM multis
Cheers,
Rémi-c
2014-11-25 9:53 GMT+01:00 Brent Wood <[email protected]>:
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_tableGROUP BY id, statusORDER BY time_field)
SELECT id, status, (ST_Dump(mylines)).geomFROM multis;
Cheers, Brent Wood
From: Hugues François <[email protected]>
To: PostGIS Users Discussion <[email protected] >
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_tableGROUP BY id, statusORDER BY
time_field) SELECT id, status, (ST_Dump(mylines)).geomFROM 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
À : [email protected]
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 user... I do not know
exactly how to use it to get the results I need. Thanks a lot
______________________________ _________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users
______________________________ _________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
