ST_Intersects

ST_Intersects takes two geometries as input parameters and returns 1 if the given geometries intersect. If the geometries do not intersect, 0 (zero) is returned.

If any of the two geometries is null or is empty, null is returned.

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 geometry to test for intersection with geometry2.
geometry2: A value of one of the seven distinct spatial data types that represents the geometry to test for intersection with geometry1.

Return type

INTEGER

Example

The following statements create and populate the SAMPLE_GEOMETRIES1 and SAMPLE_GEOMETRIES2 tables.


SET CURRENT PATH = CURRENT PATH, db2gse; 

CREATE TABLE sample_geometries1(id SMALLINT, spatial_type varchar(13), 
    geometry ST_GEOMETRY);
CREATE TABLE sample_geometries2(id SMALLINT, spatial_type varchar(13), 
    geometry ST_GEOMETRY);

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
    ( 1, 'ST_Point', ST_GEOMETRY(ST_Point('point(550 150)', 1) ));

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
    (10, 'ST_LineString', ST_GEOMETRY(ST_LineString('linestring(800 800, 
          900 800)', 1)));

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
    (20, 'ST_Polygon', ST_GEOMETRY(ST_Polygon('polygon((500 100, 500 200, 700 200, 
          700 100, 500 100))', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (101, 'ST_Point', ST_GEOMETRY(ST_Point('point(550 150)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (102, 'ST_Point', ST_GEOMETRY(ST_Point('point(650 200)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (103, 'ST_Point', ST_GEOMETRY(ST_Point('point(800 800)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (110, 'ST_LineString', ST_GEOMETRY(ST_LineString('linestring(850 250, 
           850 850)', 1)));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (120, 'ST_Polygon', ST_GEOMETRY(ST_Polygon('polygon((650 50, 650 150, 800 150, 
           800 50, 650 50))', 1)));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (121, 'ST_Polygon', ST_GEOMETRY(ST_Polygon('polygon((20 20, 20 40, 40 40, 
           40 20, 20 20))', 1) ));

The following SELECT statement determines whether the various geometries in the SAMPLE_GEOMETRIES1 and SAMPLE_GEOMETRIES2 tables intersect.


SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg2_id, sg2.spatial_type AS sg2_type,
         CASE ST_Intersects(sg1.geometry, sg2.geometry)
            WHEN 0 THEN 'Geometries do not intersect'
            WHEN 1 THEN 'Geometries intersect'
         END AS intersects
FROM     sample_geometries1 sg1, sample_geometries2 sg2
ORDER BY sg1.id;

Results:


SG1_ID SG1_TYPE      SG2_ID SG2_TYPE      INTERSECTS
------ ------------- ------ ------------- ---------------------------
     1 ST_Point         101 ST_Point      Geometries intersect
     1 ST_Point         102 ST_Point      Geometries do not intersect
     1 ST_Point         103 ST_Point      Geometries do not intersect
     1 ST_Point         110 ST_LineString Geometries do not intersect
     1 ST_Point         120 ST_Polygon    Geometries do not intersect
     1 ST_Point         121 ST_Polygon    Geometries do not intersect
    10 ST_LineString    101 ST_Point      Geometries do not intersect
    10 ST_LineString    102 ST_Point      Geometries do not intersect
    10 ST_LineString    103 ST_Point      Geometries intersect
    10 ST_LineString    110 ST_LineString Geometries intersect
    10 ST_LineString    120 ST_Polygon    Geometries do not intersect
    10 ST_LineString    121 ST_Polygon    Geometries do not intersect
    20 ST_Polygon       101 ST_Point      Geometries intersect
    20 ST_Polygon       102 ST_Point      Geometries intersect
    20 ST_Polygon       103 ST_Point      Geometries do not intersect
    20 ST_Polygon       110 ST_LineString Geometries do not intersect
    20 ST_Polygon       120 ST_Polygon    Geometries intersect
    20 ST_Polygon       121 ST_Polygon    Geometries do not intersect