[postgis-users] Creating trajectory/lines from millions ofpoints

[Paragon Corporation]

Oliver,

I'm not sure why your below query would create a single line for each
vehicle when you are also grouping by timedate.  IT would seem it wold
create a line for each routeid, vehicle, timedate which seems wrong too.


Is your timedate of data type date or timestamp / timestamptz?

It would be best to have it as timestamptz then to get the line per day for
each vehicle/route you would do

    SELECT routeid, vehicleid, ST_MakeLine(geom ORDER BY timedate) 
AS mylines
    FROM taxitable
    GROUP BY routeid,vehicleid,timedate::date;



By casting timedate to a date it will then give you a grouping for each day
sorted by timestamp of travel.

If you want travel regardless of route -- then take routeid out of your
select and you should get travel path for each vehicle for each day

    SELECT vehicleid, ST_MakeLine(geom ORDER BY timedate) 
AS mylines
    FROM taxitable
    GROUP BY vehicleid,timedate::date;

Hope that helps,
Regina
http://www.postgis.us
http://postgis.net





-------original --

Just another short question...

I would like to select a specific taxi route and create lines which 
represent this route over a specific time range.


CREATE TABLE taxiroutexy_lines AS WITH multis AS(
    SELECT routeid,vehicleid,timedate,ST_MakeLine(geom ORDER BY timedate) 
AS mylines
    FROM taxitable
    GROUP BY routeid,vehicleid,timedate
    )
SELECT routeid,vehicleid,timedate,(ST_Dump(mylines)).geom
FROM multis;

But this only creates one line for each vehicleid over the whole timedate. 
So let's say I have timestamps from two weeks, then I will have one line 
for vehicle xy from the beginning of the timestamp to the end.
How can I modify this to get one line for each day?