[postgis-users] Creating trajectory/lines from millions of points[PostGIS]
Rémi Cura
remi.cura at gmail.com
Tue Nov 25 08:50:41 PST 2014
Hey,
you have to try a little ;-)
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,time_start, time_end, (ST_Dump(mylines)).geom
FROM multis
Cheers,
Rémi-C
2014-11-25 17:48 GMT+01:00 Oliver Burgfeld <oliver.burgfeld at gmail.com>:
> 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_table
>> GROUP BY id, status
>> )
>>
>> SELECT id, status, (ST_Dump(mylines)).geom
>> FROM multisBrent Wood
>> ------------------------------
>> *From:* Oliver Burgfeld <oliver.... at gmail.com>
>> *To:* postgi... at googlegroups.com
>> *Cc:* pcr... at pcreso.com; postgi... at lists.osgeo.org;
>> postgi... at lists.osgeo.org; remi... at 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... at 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.... at irstea.fr>
>> *To:* PostGIS Users Discussion <postgi... at 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... at lists. osgeo.org [mailto:postgis-us...@
>> lists.osgeo.org] *De la part de* Oliver Burgfeld
>> *Envoyé :* mardi 25 novembre 2014 07:09
>> *À :* postgi... at 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 user... I do not know
>> exactly how to use it to get the results I need.
>>
>> Thanks a lot
>>
>> ______________________________ _________________
>> postgis-users mailing list
>> postgi... at lists.osgeo.org
>> http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users
>> <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>>
>>
>>
>> ______________________________ _________________
>> postgis-users mailing list
>> postgi... at lists.osgeo.org
>>
>>
>>
>> http://lists.osgeo.org/cgi- bin/mailman/listinfo/postgis- users
>> <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>>
>>
>>
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141125/654198ef/attachment.html>
More information about the postgis-users
mailing list