[postgis-users] Create Linestrings from multiple Points stored in other table

[Nenad Nikolic]

As the subject suggests I need to create Linestrings by connecting Points.I
have one table locations that looks like this:

CREATE TABLE locations
(
userid integer NOT NULL DEFAULT 0,
"time" integer NOT NULL DEFAULT 0,
"timestamp" bigint,
the_geom geometry(Point,4326),
CONSTRAINT locations_pkey PRIMARY KEY (userid , "time" )
)

Basically it's a table with a lot of locations with Point geometries. Field
'time' is relative timestamp, from 0 to 179. So for every "userid" there is
180 entries with 180 locations.


What i need to do here is to create a new table which will have one
Linestring per "userid", which was calculated from Points in locations
table, of course in correct order defined in "time" attribute. So new table
should look like this:

CREATE TABLE trajectories (
userid int NOT NULL default '0',
the_geom geometry(Linestring, 4326),
PRIMARY KEY ( userid )
)

I can't find a way to do that. Is it even possible to connect multiple
points (in this problem 180 of them) to one Linestring with st_makeline?

I tried something like this

SELECT St_MakeLine(the_geom) as the_route, bp.userid
FROM (SELECT userid, the_geom, "time"
           FROM locations
           ORDER BY "time") bp
GROUP BY bp.userid
ORDER BY bp.userid;

But result has geometries field empty...
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120919/0975ba76/attachment.html>