Delete values from an ST_Geometry spatial column

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

1. Open an SQL editor and connect to your database.

   Connect using an account that has privileges to delete from the table.

2. The following are example queries that delete data for each supported database:
   • This example deletes values from a table in Oracle 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 table in PostgreSQL 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');
     

   • This example deletes values from a table in a SQLite database based on a spatial filter:

     DELETE FROM sensitive_areas WHERE names
     (SELECT sa.names 
      FROM sensitive_areas sa, hazardous_sites hs
      WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) = 1);
     

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