ST_INTERSECTION scalar function

Edit online

The ST_INTERSECTION function takes two geometry objects as input parameters and returns the geometry that is the intersection of the two specified geometries.

The intersection is the common part of the first geometry and the second geometry.

If possible, the specific type of the returned geometry will be ST_POINT, ST_LINESTRING, or ST_POLYGON. For example, the intersection of a point and a polygon is either empty or a single point, represented as ST_POINT.

If geometry1 or geometry2 is null, the result is the null value. If geometry1 or geometry2 is empty, an empty geometry is returned.

geometry1: A value of type ST_GEOMETRY or one of its subtypes that represents the first geometry to compute the intersection with geometry2.

geometry2: A value of type ST_GEOMETRY or one of its subtypes that represents the second geometry to compute the intersection with geometry1.

The result of the function is ST_GEOMETRY.

The dimension of the returned geometry is that of the input with the lower dimension.

Example

Find the intersection of two geometries.

CREATE TABLE sample_geometries (id INTEGER, geometry QSYS2.ST_GEOMETRY);

INSERT INTO sample_geometries VALUES
  (2, QSYS2.ST_POLYGON('polygon((20 30, 30 30, 30 40, 20 40, 20 30))')),
  (3, QSYS2.ST_POLYGON('polygon((40 40, 40 60, 60 60, 60 40, 40 40))')),
  (4, QSYS2.ST_LINESTRING('linestring(60 60, 70 70)')),
  (5, QSYS2.ST_LINESTRING('linestring(30 30, 60 60)'));

CREATE VARIABLE my_linestring QSYS2.ST_LINESTRING;
SET my_linestring = QSYS2.ST_LINESTRING('linestring(30 30, 60 60)');

SELECT id, QSYS2.ST_ASTEXT(QSYS2.ST_INTERSECTION(my_linestring, geometry)) AS intersection
  FROM sample_geometries;

Results:


ID       INTERSECTION
------   ------------------------------------------------
    2    LINESTRING (30.0 30.0, 30.0 30.0)
    3    LINESTRING (40.0 44.898573, 60.0 60.0)
    4    POINT (60.0 60.0)
    5    LINESTRING (30.0 30.0, 60.0 60.0)