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]
#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
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 :
[email protected]
[mailto:[email protected]] 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
