ST_Union — Returns a geometry that represents the point set union of the Geometries.
geometry ST_Union(
geometry set g1field)
;
geometry ST_Union(
geometry g1, geometry g2)
;
geometry ST_Union(
geometry[] g1_array)
;
Output type can be a MULTI* , single geometry, or Geometry Collection. Comes in 2 variants. Variant 1 unions 2 geometries resulting in a new geomety with no intersecting regions. Variant 2 is an aggregate function that takes a set of geometries and unions them into a single ST_Geometry resulting in no intersecting regions.
Aggregate version: This function returns a MULTI geometry or NON-MULTI geometry from a set of geometries. The ST_Union() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operates on rows of data, in the same way the SUM() and AVG() functions do.
Non-Aggregate version: This function returns a geometry being a union of two input geometries. Output type can be a MULTI* ,NON-MULTI or GEOMETRYCOLLECTION.
ST_Collect and ST_Union are often interchangeable. ST_Union is in general orders of magnitude slower than ST_Collect because it tries to dissolve boundaries and reorder geometries to ensure that a constructed Multi* doesn't have intersecting regions. |
Performed by the GEOS module.
NOTE: this function was formerly called GeomUnion(), which was renamed from "Union" because UNION is an SQL reserved word.
Availability: 1.4.0 - ST_Union was enhanced. ST_Union(geomarray) was introduced and also faster aggregate collection in PostgreSQL. If you are using GEOS 3.1.0+ ST_Union will use the faster Cascaded Union algorithm described in http://blog.cleverelephant.ca/2009/01/must-faster-unions-in-postgis-14.html
This method implements the OpenGIS Simple Features Implementation Specification for SQL: OGC SPEC s2.1.1.3
This method implements the SQL/MM specification: SQL-MM 3: 5.1.19
Aggregate version is not explicitly defined in OGC SPEC. |
This function supports 3d and will not drop the z-index, but sometimes does something goofy with the z-index (elevation) when polygons are involved.
Aggregate example
SELECT stusps, ST_Multi(ST_Union(f.the_geom)) as singlegeom FROM sometable As f GROUP BY stusps
Non-Aggregate example
SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'), ST_GeomFromText('POINT(-2 3)') ) ) st_astext ---------- MULTIPOINT(-2 3,1 2) SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'), ST_GeomFromText('POINT(1 2)') ) ); st_astext ---------- POINT(1 2) --3d example - sort of supports 3d (and with mixed dimensions!) SELECT ST_AsEWKT(st_union(the_geom)) FROM (SELECT ST_GeomFromEWKT('POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3, -7 4.2))') as the_geom UNION ALL SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom UNION ALL SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom UNION ALL SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo; st_asewkt --------- GEOMETRYCOLLECTION(POINT(-2 3 1),LINESTRING(5 5 5,10 10 10),POLYGON((-7 4.2 5,-7.1 4.2 5,-7.1 4.3 5,-7 4.2 5))); --3d example not mixing dimensions SELECT ST_AsEWKT(st_union(the_geom)) FROM (SELECT ST_GeomFromEWKT('POLYGON((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2, -7 4.2 2))') as the_geom UNION ALL SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom UNION ALL SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom UNION ALL SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo; st_asewkt --------- GEOMETRYCOLLECTION(POINT(-2 3 1),LINESTRING(5 5 5,10 10 10),POLYGON((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2,-7 4.2 2))) --Examples using new Array construct SELECT ST_Union(ARRAY(SELECT the_geom FROM sometable)); SELECT ST_AsText(ST_Union(ARRAY[ST_GeomFromText('LINESTRING(1 2, 3 4)'), ST_GeomFromText('LINESTRING(3 4, 4 5)')])) As wktunion; --wktunion--- MULTILINESTRING((3 4,4 5),(1 2,3 4))