Spatial SQL queries on tables with an ST_Geometry column

You can execute SQL queries on tables that contain ST_Geometry columns to return information about the table itself, to spatially compare the features in one table with features in another table, or to generate new geometry from input tables.

Queries on an ST_Geometry column using a spatial index

The following table contains example spatial queries using a spatial index for geodatabases that use the ST_Geometry type.

DB2

db2 => SELECT sa.name "Sensitive Areas", hs.name "Hazardous Sites" 

FROM sensitive_areas sa, hazardous_sites hs 

WHERE db2gse.st_overlaps (sa.zone, db2gse.st_buffer (hs.location,.01)) = 1

Informix

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, .01)) = 't';

Oracle

SELECT sa.name "Sensitive Areas", hs.name "Hazardous Sites" 

FROM sensitive_areas sa, hazardous_sites hs 

WHERE sde.st_overlaps (sa.zone, sde.st_buffer(hs.location,.01)) = 1;

PostgreSQL

SELECT sa.name AS sensitive_area, hs.name AS hazardous_sites

FROM sensitive_areas sa, hazardous_sites hs 

WHERE st_overlaps(sa.zone, st_buffer(hs.location,.01) = 't');

SQL spatial query

Related Topics

6/19/2015