Query spatial data

A common task in a GIS application is to retrieve the visible subset of spatial data for display in a window.

You can define a polygon that represents the boundary of the window and then run the SE_EnvelopesIntersect() function to find all spatial objects that overlap this window. The following statement returns the objects that overlap the polygon that is defined by the ST_PolyFromText() function:

SELECT name, type, zone FROM sensitive_areas
   WHERE SE_EnvelopesIntersect(zone, 
      ST_PolyFromText('polygon((20000 20000,60000 20000,
60000 60000,20000 60000,20000 20000))', 5));

You can include spatial columns in the WHERE clause of queries to qualify the result set. The spatial column does not need to be in the result set. For example, the following SQL statement retrieves each sensitive area with its nearby hazardous waste site if the sensitive area is within five miles of a hazardous site. The ST_Buffer() function generates a circular polygon that represents the 5-mile radius around each hazardous location. The ST_Polygon geometry that is returned by the ST_Buffer() function becomes the argument of the ST_Overlaps() function, which returns t (TRUE) if the zone ST_Polygon of the sensitive_areas table overlaps the ST_Polygon generated by the ST_Buffer() function:

SELECT sa.name sensitive_area, hs.name hazardous_site
   FROM sensitive_areas sa, hazardous_sites hs
   WHERE ST_Overlaps(sa.zone, ST_Buffer(hs.location, 26400));


sensitive_area  Summerhill Elementary School
hazardous_site  Landmark Industrial

sensitive_area  Johnson County Hospital
hazardous_site  Landmark Industrial