ST_Difference

ST_Difference takes two geometries as input parameters and returns the part of the first geometry that does not intersect with the second geometry.

Both geometries must be of the same dimension. If either geometry is null, null is returned. If the first geometry is empty, an empty geometry of type ST_Point is returned. If the second geometry is empty, then the first geometry is returned unchanged.

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

Syntax

Parameter

geometry1: A value of one of the seven distinct spatial data types that represents the first geometry to use to compute the difference to geometry2.
geometry2: A value of one of the seven distinct spatial data types that represents the second geometry that is used to compute the difference to geometry1.

Return type

db2gse.ST_Geometry

The dimension of the returned geometry is the same as that of the input geometries.

Examples

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

Example 1

The following example creates and populates the SAMPLE_GEOMETRIES table.


SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
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(75 75, 90 90)' ,0))

Example 2

This example finds the difference between two disjoint polygons.


SELECT a.id, b.id, CAST(ST_AsText(ST_Difference(a.geometry, b.geometry))
   as VARCHAR(200))  Difference
FROM sample_geoms a, sample_geoms b
WHERE a.id = 1 and b.id = 2

Results:


ID        ID      DIFFERENCE
-------- -------- ----------------------------------------------------
       1        2 POLYGON (( 10.00000000 10.00000000, 20.00000000 
                    10.00000000, 20.00000000 20.00000000,
                    10.00000000 20.00000000, 10.00000000 10.00000000))

Example 3

This example finds the difference between two intersecting polygons.


SELECT a.id, b.id, CAST(ST_AsText(ST_Difference(a.geometry, b.geometry)) 
   as VARCHAR(200))  Difference
FROM sample_geoms a, sample_geoms b
WHERE a.id = 2 and b.id = 3

Results:


ID       ID       DIFFERENCE
-------- -------- ---------------------------------------------------
       2        3 POLYGON (( 30.00000000 30.00000000, 50.00000000 
                    30.00000000, 50.00000000 40.00000000, 40.00000000 
                    40.00000000, 40.00000000 50.00000000, 30.00000000
                    50.00000000, 30.00000000 30.00000000))

Example 4

This example finds the difference between two overlapping linestrings.


SELECT a.id, b.id, CAST(ST_AsText(ST_Difference(a.geometry, b.geometry)) 
   as VARCHAR(100))  Difference
FROM sample_geoms a, sample_geoms b
WHERE a.id = 4 and b.id = 5

Results:


ID       ID       DIFFERENCE
-------- -------- --------------------------------------------------
       4        5 LINESTRING ( 70.00000000 70.00000000, 75.00000000 
                    75.00000000)