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

[Paul Ramsey]

Your query looks fine. You know the geometry field is empty how? Did
you run select st_npoints(the_geom), or ST_GeometryType(the_geom) from
it?

P

On Wed, Sep 19, 2012 at 10:57 AM, Nenad Nikolic <nnikolic88 at gmail.com> wrote:
> 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...
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>