ST_Union function and ST_Union aggregation function

The ST_Union function takes two geometries as input parameters and returns the geometry that is the union of the specified geometries. The resulting geometry is represented in the spatial reference system of the first geometry. Another form of this function is the ST_Union aggregate function. Use the ST_Union aggregate function to aggregate a set of geometries from a column in a table to single geometry by constructing the union.

ST_Union function
ST_Union aggregation function

ST_Union function

Both geometries must be of the same dimension. If any of the two specified geometries is null, null is returned.

If the second geometry is not represented in the same spatial reference system as the first geometry and uses the same underlying datum, it will be converted to the other spatial reference system.

The resulting geometry is represented in the most appropriate spatial type. If it can be represented as a point, linestring, or polygon, then one of those types is used. Otherwise, the multipoint, multilinestring, or multipolygon type is used.

Syntax

Parameters

geometry1: A value of type ST_Geometry or one of its subtypes that is combined with geometry2.
geometry2: A value of type ST_Geometry or one of its subtypes that is combined with geometry1.

Return type

ST_Geometry

Examples

Example 1

The following SQL statements create and populate the SAMPLE_GEOMS table.


CREATE TABLE sample_geoms (id INTEGER, geometry, ST_Geometry)

INSERT INTO sample_geoms
  VALUES (1, ST_Geometry( 'polygon 
         ((10 10, 10 20, 20 20, 20 10, 10 10) )', 0))

INSERT INTO sample_geoms
  VALUES (2, ST_Geometry( 'polygon 
         ((30 30, 30 50, 50 50, 50 30, 30 30) )', 0))

INSERT INTO sample_geoms
  VALUES (3, ST_Geometry( 'polygon 
         ((40 40, 40 60, 60 60, 60 40, 40 40) )', 0))

INSERT INTO sample_geoms
  VALUES (4, ST_Geometry('linestring (70 70, 80 80)', 0))

INSERT INTO sample_geoms
  VALUES (5, ST_Geometry('linestring (80 80, 100 70)', 0))

In the following examples, the results have been reformatted for readability. Your results will vary according to your display.

Example 2

This example finds the union of two disjoint polygons.


SELECT a.id as id1, b.id as id2, CAST ( ST_AsText( ST_Union( a.geometry, b.geometry) )
    AS VARCHAR (180) ) UNION
  FROM sample_geoms a, sample_geoms b
  WHERE a.id = 1 AND b.id = 2

Results:


ID1  ID2  UNION
---- ---- -------------------------------------------------------
   1    2 MULTIPOLYGON (((10 10, 20 10, 20 20, 10 20, 10 10)),
                          ((30 30, 50 30, 50 50, 30 50, 30 30)))

Example 3

This example finds the union of two intersecting polygons.


SELECT a.id as id1, b.id as id2, CAST ( ST_AsText( ST_Union(a.geometry, b.geometry))
    AS VARCHAR (180)) UNION
  FROM sample_geoms a, sample_geoms b
  WHERE a.id = 2 AND b.id = 3

Results:


ID1  ID2  UNION
---- ---- ----------------------------------------------------
   2    3 POLYGON ((30 30, 50 30, 50 40, 60 40, 60 60, 40 60, 
                      40 50, 30 50, 30 30))

Example 4

Find the union of two linestrings.


SELECT a.id as id1, b.id as id2, CAST ( ST_AsText( ST_Union( a.geometry, b.geometry) )
    AS VARCHAR (180) ) UNION
  FROM sample_geoms a, sample_geoms b
  WHERE a.id = 4 AND b.id = 5

Results:


ID1  ID2  UNION
---- ---- ---------------------------------------------------------------
   4    5 MULTILINESTRING ((70 70, 80 80),(80 80, 100 70))

ST_Union aggregation function

ST_Union aggregate function returns a geometry or null depending upon the selected set of geometries.

If all of the geometries to be combined in the union are null, then null is returned. If each of the geometries to be combined in the union are either null or are empty, then an empty geometry of type ST_Point is returned.

Prerequisites

Spatial Analytics must be enabled.

Syntax

Parameter

geometry: A column that has a type of ST_Geometry or one of its subtypes and contains the set of geometries for which the union is to be computed.

Return type

ST_Geometry

Restrictions

For the ST_Union aggregation function, all the geometries in the set must belong to the same geometry type, otherwise an incompatible geometries error will be reported.
For the ST_Union aggregation function, all the geometries in the set must have compatible spatial reference systems, otherwise an error will be reported. In case the spatial reference system (SRS) is compatible, but not the same, the SRS of the resulting geometry will be the same as the SRS of the first geometry in the set (geometries with a different SRS are converted into the first SRS).

Examples

Example 1:

This example illustrates how a union aggregate can be used to combine a set of points into multipoints. Several points are added to the SAMPLE_POINTS table. The ST_Union aggregation function is used to construct the union of the points:


CREATE TABLE sample_points (id INTEGER, geometry ST_Point)

INSERT INTO sample_points VALUES (1, ST_Point (2, 3, 1) )
INSERT INTO sample_points VALUES (2, ST_Point (4, 5, 1) )
INSERT INTO sample_points VALUES (3, ST_Point (13, 15, 1) )
INSERT INTO sample_pointsVALUES (4, ST_Point (12, 5, 1) )
INSERT INTO sample_pointsVALUES (5, ST_Point (23, 2, 1) )
INSERT INTO sample_points VALUES (6, ST_Point (11, 4, 1) )

SELECT SUBSTR (ST_AsText( ST_UNION(geometry) ) ,1, 200) AS POINT_AGGREGATE FROM sample_points

Result:


POINT_AGGREGATE
----------------------------------------------------------
MULTIPOINT ( 2.00000000 3.00000000, 4.00000000 5.00000000, 11.00000000 4.00000000, 12.00000000 5.00000000, 13.00000000 15.00000000, 23.00000000 2.00000000)

  1 record(s) selected.

Example 2:

The following example shows the ST_Union function used in a group-by query to compute the union of all the geometries in the respective groups:


CREATE TABLE sample_geometries(id int, region int, geometry st_geometry)

INSERT INTO sample_geometries values (1, 1, st_point(1,1))
INSERT INTO sample_geometries values (2, 1, st_point(2,2))
INSERT INTO sample_geometries values (3, 2, st_linestring('linestring(0 0 , 2 2)'))
INSERT INTO sample_geometries values (4, 2, st_linestring('linestring (1 1, 3 3)'))
INSERT INTO sample_geometries values (5, 3, st_point(3,3))
INSERT INTO sample_geometries values (6, 2, st_linestring('linestring(0 1 , 2 1)'))

SELECT region ,SUBSTR(ST_ASTEXT(ST_UNION(geometry)),1,200) AS "Union_of_Geos" from sample_geometries WHERE id < 5 GROUP BY region

Result:


REGION      UNION_OF_GEOS                                                                                                                                       
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------
          1 MULTIPOINT (1.000000000 1.000000000, 2.000000000 2.000000000)                                                                                       
          2 MULTILINESTRING ((2.000000000 2.000000000, 3.000000000 3.000000000),(1.000000000 1.000000000, 2.000000000 2.000000000),(0.000000000 0.000000000, 1.000000000 1.000000000))

  2 record(s) selected.