Deleting values from an ST_Geometry spatial column

The SQL DELETE statement removes rows of data from a specified table or view.

Steps:
  1. The following are example queries that delete data for each supported database:
    • This example deletes values from a geodatabase in Oracle using the spatial type based on a spatial filter:
      DELETE FROM sensitive_areas WHERE names
      (SELECT sa.names 
      FROM sensitive_areas sa, hazardous_sites hs
      WHERE sde.st_overlaps (sa.zone, sde.st_buffer (hs.location,.01)) = 1);
    • This example deletes values from a geodatabase in PostgreSQL using the spatial type based on a spatial filter:
      DELETE FROM sensitive_areas 
      WHERE names EXISTS (SELECT sa.names
      FROM sensitive_areas sa, hazardous_sites hs
      WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) = 't');
    • In IBM DB2, use the following statement:
      DELETE FROM sensitive_areas 
      WHERE names (SELECT sa.names 
      FROM sensitive_areas sa, hazardous_sites hs 
      WHERE db2gse.st_overlaps (sa.zone, db2gse.st_buffer (hs.location,.01)) = 1);
    • Informix does not support modifying a table or view used in a subquery. Therefore, break the query into two parts: select into a temporary table and delete using that temporary table.
      SELECT sa.name FROM
      sensitive_areas sa, hazardous_sites hs
      WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) 
      INTO TEMP tempTable;
      
      DELETE FROM sensitive_areas WHERE name IN (select name from tempTable);

2/5/2013