ST_SymmetricDiff

Definition

ST_SymmetricDiff takes two geometry objects and returns a geometry object composed of the parts of the source objects that are not common to both.

Syntax

Oracle and PostgreSQL

sde.st_symmetricdiff (geometry1 sde.st_geometry, geometry2 sde.st_geometry)

SQLite

st_symmetricdiff (geometry1 geometryblob, geometry2 geometryblob)

Return type

Oracle and PostgreSQL

ST_Geometry

SQLite

Geometryblob

Example

For a special report, the county supervisor must determine the watershed and hazardous plumes radii areas that aren't intersected.

The watershed table contains an id column, a column to store the watershed name (wname), and a shape column, which stores the watershed area geometry.

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

The ST_Buffer function generates a buffer surrounding the hazardous waste site points. The ST_SymmetricDiff function returns the polygons of the buffered hazardous waste sites and the watersheds that don't intersect.

The symmetric difference of the hazardous waste sites and the watershed results in the subtraction of the intersected areas.

Oracle

CREATE TABLE watershed (
 id integer,
 wname varchar(40) 
 shape sde.st_geometry
); 

CREATE TABLE plumes (
 id integer,
 site sde.st_geometry
);

INSERT INTO WATERSHED (ID, WNAME, SHAPE) VALUES (
 1,
 'Big River'
 sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO WATERSHED (ID, WNAME, SHAPE) VALUES (
 2,
 'Lost Creek'
 sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO WATERSHED (ID, WNAME, SHAPE) VALUES (
 3,
 'Szymborska Stream',
 sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO PLUMES (ID, SITE) VALUES (
 20,
 sde.st_geometry ('point (60 60)', 4326)
);

INSERT INTO PLUMES (ID, SITE) VALUES (
 21,
 sde.st_geometry ('point (30 30)', 4326)
);

SELECT ws.id WS_ID,
 sde.st_area (sde.st_symmetricdiff (sde.st_buffer (p.site, .1), ws.shape)) AREA_NO_INT
 FROM plumes p, SENSITIVE_AREAS ws
 WHERE p.id = 20;

     SA_ID     AREA_NO_INT

         1     100.031393
         2     400.031393
         3     400.015697

PostgreSQL

CREATE TABLE watershed (
 id serial,
 wname varchar(40) 
 shape sde.st_geometry
); 

CREATE TABLE plumes (
 id serial,
 site sde.st_geometry
);

INSERT INTO watershed (wname, shape) VALUES (
 'Big River'
 sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO watershed (wname, shape) VALUES (
 'Lost Creek'
 sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO watershed (wname, shape) VALUES (
 'Szymborska Stream',
 sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO plumes (site) VALUES (
 20,
 sde.st_geometry ('point (60 60)', 4326)
);

INSERT INTO plumes (site) VALUES (
 21,
 sde.st_geometry ('point (30 30)', 4326)
);

SELECT ws.id AS WS_ID,
 sde.st_area (sde.st_symmetricdiff (sde.st_buffer (p.site, .1), ws.shape)) AS "no intersection"
 FROM plumes p, watershed ws
 WHERE p.id = 20;

    ws_id     no intersection

         1    100.031393502001
         2    400.031393502001
         3    400.01569751

SQLite

CREATE TABLE watershed (
 id integer primary key autoincrement not null,
 wname text(40)
);

SELECT AddGeometryColumn(
 NULL,
 'watershed',
 'shape',
 4326,
 'polygon',
 'xy',
 'null'
);

CREATE TABLE plumes (
 id integer primary key autoincrement not null
);

SELECT AddGeometryColumn(
 NULL,
 'plumes',
 'site',
 4326,
 'point',
 'xy',
 'null'
);

INSERT INTO watershed (wname, shape) VALUES (
 'Big River',
 st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO watershed (wname, shape) VALUES (
 'Lost Creek',
 st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO watershed (wname, shape) VALUES (
 'Szymborska Stream',
 st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO plumes (site) VALUES (
 st_geometry ('point (60 60)', 4326)
);

INSERT INTO plumes (site) VALUES (
 st_geometry ('point (30 30)', 4326)
);

SELECT ws.id AS WS_ID,
 st_area (st_symmetricdiff (st_buffer (p.site, .1), ws.shape)) AS "no intersection"
 FROM plumes p, watershed ws
 WHERE p.id = 2;

    WS_ID     no intersection

         1    400.031393502001
         2    100.031393502001
         3    400.01569751