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