ST_Collect

Syntax

ST_Collect(pgeo);

pgeo

The input point geometry.

Type: VARCHAR(ANY)

start

The start of the group.

Type: INT4

end

The end of the group.

Type: INT4

Returns

A value of type ST_GEOMETRY(ANY) that contains the multipoint geometry.

Examples

CREATE TABLE points (PointID INTEGER, the_geom ST_GEOMETRY(200)); 

INSERT INTO points VALUES (1, inza..ST_WKTToSQL('Point (0 0)')); 
INSERT INTO points VALUES (2, inza..ST_WKTToSQL('Point (22 0)'));
INSERT INTO points VALUES (3, inza..ST_WKTToSQL('Point (33 33)'));
INSERT INTO points VALUES (4, inza..ST_WKTToSQL('Point (44 44)'));

SELECT inza..ST_AsText(inza..ST_Collect(the_geom)) from (SELECT the_geom from points order by PointID LIMIT 9999999) points;

ST_ASTEXT
------------------------------------
MULTIPOINT (0 0, 22 0, 33 33, 44 44)
(1 row)

CREATE TABLE trip_points (trip_id INT, geom VARCHAR(200), timestamp INTEGER);

INSERT INTO trip_points VALUES (100, inza..ST_WKTToSQL('Point (100 100)'), 120212);
INSERT INTO trip_points VALUES (100, inza..ST_WKTToSQL('Point (200 200)'), 120312);
INSERT INTO trip_points VALUES (100, inza..ST_WKTToSQL('Point (300 300)'), 120412);
INSERT INTO trip_points VALUES (100, inza..ST_WKTToSQL('Point (400 400)'), 120512);
INSERT INTO trip_points VALUES (200, inza..ST_WKTToSQL('Point (200 200)'), 120212);
INSERT INTO trip_points VALUES (200, inza..ST_WKTToSQL('Point (300 300)'), 120312);
INSERT INTO trip_points VALUES (200, inza..ST_WKTToSQL('Point (100 100)'), 120412);
INSERT INTO trip_points VALUES (200, inza..ST_WKTToSQL('Point (400 400)'), 120512);
INSERT INTO trip_points VALUES (300, inza..ST_WKTToSQL('Point (400 400)'), 120212);
INSERT INTO trip_points VALUES (300, inza..ST_WKTToSQL('Point (300 300)'), 120312);
INSERT INTO trip_points VALUES (300, inza..ST_WKTToSQL('Point (200 200)'), 120412);
INSERT INTO trip_points VALUES (300, inza..ST_WKTToSQL('Point (100 100)'), 120512);

select trip_id, inza..st_astext(tf.multipoint) from
(select inza..st_astext(geo), geo, trip_id, lag(0,1,1)
over (partition by trip_id order by timestamp) as
begin_part, lead(0,1,1) over(partition by trip_id order
by timestamp) as end_part from trip_points) as foo, table
with final(inza..st_collect(geo, begin_part, end_part))
tf order by trip_id;

TRIP_ID | ST_ASTEXT
--------+-------------------------------------------------
100     | MULTIPOINT (100 100, 200 200, 300 300, 400 400)
200     | MULTIPOINT (200 200, 300 300, 100 100, 400 400)
300     | MULTIPOINT (400 400, 300 300, 200 200, 100 100)
(3 rows)