ST_Intersection takes two geometry objects and returns the intersection set as a geometry object.
Syntax
db2gse.ST_Intersection(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)
Return type
db2gse.ST_Geometry
Examples
The fire marshall must obtain the areas of the hospitals, schools, and nursing homes that are intersected by the radius of a possible hazardous waste contamination.
The sensitive areas are stored in the table SENSITIVE_AREAS that is created with the following CREATE TABLE statement. The ZONE column is defined as a polygon that stores the outline of each of the sensitive areas.
CREATE TABLE SENSITIVE_AREAS (id integer,
name varchar(128),
size float,
type varchar(10),
zone db2gse.ST_Polygon);
The hazardous sites are stored in the HAZARDOUS_SITES table that is created with the following CREATE TABLE statement. The LOCATION column, defined as a point, stores a location that is the geographic center of each hazardous site.
CREATE TABLE HAZARDOUS_SITES (site_id integer,
name varchar(128),
location db2gse.ST_Point);
The buffer function generates a five-mile buffer that surrounds the hazardous waste site locations. The ST_Intersection function generates polygons from the intersection of the buffered hazardous waste site polygons and the sensitive areas. The ST_Area function returns the intersection polygon's area, which is summarized for each hazardous site by the SUM function. The GROUP BY clause directs the query to aggregate the intersected areas by the hazardous waste site_ID.
SELECT hs.name,SUM(db2gse.ST_Area(db2gse.ST_Intersection (sa.zone, db2gse.ST_buffer hs.location,(5 * 5280)))) FROM SENSITIVE_AREAS sa, HAZARDOUS_SITES hs GROUP BY hs.site_id;
In Figure 35, the circles represent the buffered polygons that surround the hazardous waste sites. The intersection of these buffer polygons with the sensitive area polygons produces three other polygons. The hospital in the upper left hand corner is intersected twice, while the school in the lower right hand corner is intersected only once.
|