Name
ST_Collect — Returns a geometry from the collection of others geometries.
Synopsis
PostgreSQL
geometry ST_Collect(bytea GeomA, bytea GeomB);
geometry ST_Collect(bytea[]
Geometry_Array);
geometry ST_Collect(bytea
Aggregate);
H2
geometry ST_Collect(bytea GeomA, bytea GeomB);
geometry ST_Collect(bytea[]
Geometry_Array);
geometry ST_CollectAgg(bytea
Aggregate);
Description
Returns a geometry from the collection of others geometries. The resulting geometry can be GEOMETRYCOLLECTION or MULTI* type.
This function has three versions:
Collects 2 geometries.
Collect an array of geometries.
Collect an aggregate. An aggregate function computes a single result from multiple input rows.
Coordinate Dimensions
| 2D | 3D | M |
|---|---|---|
 | | |
Spatial Standards Support
| OGC SFS for SQL. 1.1 (1999) | OGC SFS for SQL. 1.1.0 (2005) | OGC SFS for SQL. 1.2.0 (2006) | SQL-MM Part 3 |
|---|---|---|---|
| - | - | - | - |
Examples
PostgreSQL
ST_Collect(geomA,geomB)
SELECT ST_AsEWKT(ST_Collect(ST_GeomFromText('POINT(5 5 10 8)'),
ST_GeomFromText('LINESTRING(0 0 10 8,1 1 1 1)')));
--Result
GEOMETRYCOLLECTION (POINT (5 5 10 8), LINESTRING (0 0 10 8, 1 1 1 1))ST_Collect(geomArray)
SELECT AsEWKT(ST_Collect(Array[ST_MakePoint(0, 0), ST_MakePoint(10, 10),ST_MakePoint(15, 10)]));
asewkt
--------------------------------------
MULTIPOINT ((0 0), (10 10), (15 10))
(1 row)ST_Collect(Aggregate)
This example creates a collection of points from a set of single points.
The single points are stored in the gps_points table.
The INSERT INTO statements that follow insert each one of the points geometries.
CREATE TABLE "gps_points"(id serial PRIMARY KEY);
SELECT AddGeometryColumn ('gps_points','geom',-1,'POINT',2);
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(0 0)'));
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(10 10)'));
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(15 15)'));
SELECT asewkt(ST_Collect("gps_points"."geom")) as measures from "gps_points";
SELECT DropGeometryTable ('gps_points');
--Result
measures
--------------------------------------
MULTIPOINT ((0 0), (10 10), (15 15))
H2
ST_Collect(geomA,geomB)
SELECT ST_AsEWKT(ST_Collect(ST_GeomFromText('POINT(5 5 10 8)'),ST_GeomFromText('POINT(0 0 10 8)')));
--Result
MULTIPOINT ((5 5 10 8), (0 0 10 8))
ST_Collect(geomArray)
-- (1st Version)
SELECT ST_AsText(ST_Collect(st_accumagg(geom))) as mycollection from (SELECT array_get(
(
st_geomfromtext('POINT(0 0)'),
st_geomfromtext('POINT(10 10)'),
st_geomfromtext('POINT(15 15)')
),x)::binary as geom from (select x from system_range(1,3) as foo) as tabla);
--Result
MULTIPOINT ((0 0), (10 10), (15 15))
--(2nd Version)
SELECT ST_AsText(ST_Collect(st_accumagg(geom))) as myline
from
(SELECT ST_geomfromtext('LINESTRING(0 0,10 10)') as geom
union
SELECT ST_geomfromtext('LINESTRING(10 10,15 10)')
union
SELECT ST_geomfromtext('LINESTRING(15 10,20 20)')
) as geom;
--Result
MULTILINESTRING ((10 10, 15 10), (15 10, 20 20), (0 0, 10 10))ST_Collect(Aggregate)
CREATE TABLE "gps_points"(id serial PRIMARY KEY);
SELECT AddGeometryColumn ('gps_points','geom',-1,'POINT',2);
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(0 0)'));
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(10 10)'));
INSERT INTO "gps_points" ("geom") VALUES (ST_GeomfromEWKT('SRID=-1;POINT(15 15)'));
SELECT asewkt(ST_CollectAgg("gps_points"."geom")) as measures from "gps_points";
SELECT DropGeometryTable ('gps_points');
--Result
MEASURES
MULTIPOINT ((0 0), (10 10), (15 15))