[postgis-users] Creating trajectory/lines from millions of points[PostGIS]

[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 <pcreso 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.francois at irstea.fr>
> *To:* PostGIS Users Discussion <postgis-users 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-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *De la part de* Oliver Burgfeld
> *Envoyé :* mardi 25 novembre 2014 07:09
> *À :* postgis-users 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
> postgis-users at lists.osgeo.org
> 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/c44e7564/attachment.html>