ST_Union

Definition

ST_Union returns an ST_Geometry object that is the combination of two source objects.

Syntax

sde.st_union (g1 sde.st_geometry, g2 sde.st_geometry)

Return type

ST_Geometry

Example

The sensitive_areas table stores the IDs of threatened institutions in addition to the zone column, which stores the institutions' ST_Polygon geometries.

The hazardous_sites table stores the identity of the sites in the id column, while the actual geographic location of each site is stored in the location point column.

Oracle

CREATE TABLE sensitive_areas (id integer, 
                              zone sde.st_geometry); 

CREATE TABLE hazardous_sites (id integer, 
                              location sde.st_geometry);


INSERT INTO SENSITIVE_AREAS VALUES (
1,
sde.st_polygon ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);

INSERT INTO SENSITIVE_AREAS VALUES (
2,
sde.st_polygon ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);

INSERT INTO SENSITIVE_AREASs VALUES (
3,
sde.st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);

INSERT INTO HAZARDOUS_SITES VALUES (
4,
sde.st_point ('point (60 60)', 0)
);

INSERT INTO HAZARDOUS_SITES VALUES (
5,
sde.st_point ('point (30 30)', 0)
);

The ST_Buffer function generates a buffer surrounding the hazardous waste site locations. The ST_Union function generates polygons from the union of the buffered hazardous waste sites and sensitive area polygons. The ST_Area function returns the area of these polygons.

Oracle

SELECT sa.id SA_ID, hs.id HS_ID,
sde.st_area (sde.st_union (sde.st_buffer (hs.location, .01), sa.zone)) UNION_AREA
FROM HAZARDOUS_SITES hs, SENSITIVE_AREAS sa;

     SA_ID  HS_ID     UNION_AREA

         1    4       100.000314
         2    4       400.000314
         3    4       400.000235
         1    5       100.000235
         2    5       400.000235
         3    5       400.000314

PostgreSQL

SELECT sa.id AS SA_ID, hs.id AS HS_ID,
sde.st_area (sde.st_union (sde.st_buffer (hs.location, .01), sa.zone)) AS UNION_AREA
FROM hazardous_sites hs, sensitive_areas sa;

     sa_id  hs_id   union_area

         1    4       100.000314
         2    4       400.000314
         3    4       400.000235
         1    5       100.000235
         2    5       400.000235
         3    5       400.000314