ST_Intersection takes two ST_Geometry objects and returns the intersection set as an ST_Geometry object.

Oracle

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

PostgreSQL

st_intersection (g1 st_geometry, g2 st_geometry)

ST_Geometry

The fire marshal must obtain the areas of the hospitals, schools, and nursing homes intersected by the radius of a possible hazardous waste contamination.

The hospitals, schools, and nursing homes are stored in the sensitive_areas table that is created with the CREATE TABLE statement that follows. The zone column, defined as a polygon, stores the outline of each of the sensitive areas.

The hazardous sites are stored in the hazardous_sites table created with the CREATE TABLE statement that follows. The location column, defined as a point, stores a location that is the geographic center of each hazardous site.

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_areas 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)
);

PostgreSQL

CREATE TABLE sensitive_areas (id integer, 
                               zone st_geometry); 

CREATE TABLE hazardous_sites (id integer, 
                              location st_geometry); 

INSERT INTO sensitive_areas VALUES (
1,
st_polygon ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);

INSERT INTO sensitive_areas VALUES (
2,
st_polygon ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);

INSERT INTO sensitive_areas VALUES (
3,
st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);

INSERT INTO hazardous_sites VALUES (
4,
st_point ('point (60 60)', 0)
);

INSERT INTO hazardous_sites VALUES (
5,
st_point ('point (30 30)', 0)
);

The ST_Buffer function generates a buffer surrounding the hazardous waste site locations. The ST_Intersection function generates polygons from the intersection of the buffered hazardous waste sites and the sensitive areas.

Oracle

SELECT sa.id, sde.st_astext (sde.st_intersection (sde.st_buffer (hs.location, .1), sa.zone)) Intersection
FROM sensitive_areas sa, hazardous_sites hs
WHERE hs.id = 5
AND sde.st_astext (sde.st_intersection (sde.st_buffer (hs.location, .1), sa.zone)) 
NOT LIKE '%EMPTY%';

  ID  INTERSECTION

  1   POLYGON  ((29.90000000 30.00000000, 30.00000000 30.00000000, 30.00000000 30.100

  2   POLYGON  ((30.00000000 30.00000000, 30.10000000 30.00000000, 30.09978589 30.006

PostgreSQL

SELECT sa.id, st_astext (st_intersection (st_buffer (hs.location, .1), sa.zone)) AS Intersection
FROM sensitive_areas sa, hazardous_sites hs
WHERE hs.id = 5
AND st_astext (st_intersection (st_buffer (hs.location, .1), sa.zone))::varchar  NOT LIKE '%EMPTY%';

  id  intersection

  1   POLYGON  ((29.90000000 30.00000000, 30.00000000 30.00000000, 30.00000000 30.100

  2   POLYGON  ((30.00000000 30.00000000, 30.10000000 30.00000000, 30.09978589 30.006