convert list of points to line or polygon

Question

I have a list of Points in a PostGIS table. The rows can be grouped by a date

something like:

myTable
 id INT
 date DATE
 lat NUMBER
 lng NUMBER
 geom GEOMETRY(POINT)

now I need a table where all points of one day are collected in a new geometry (sorted by id)

targetTable
 id2 INT
 date DATE
 geom2 GEOMETRY(POLYGON)

how can I do this with PostGIS or GeoKettle, I cannot use any other client since this transformation has to be done in batch?

Answer 1

use ST_MakeLine function in postgis. info here...

ST_MakeLine — Creates a Linestring from point or line geometries.

Example:

SELECT gps.gps_track, ST_MakeLine(gps.the_geom) As newgeom
    FROM gps_points As gps
    GROUP BY gps.gps_track;

i hope it helps you...