从 ST_Geometry 空间列中删除值

SQL DELETE 语句可从指定的表或视图中删除数行数据。

1. 打开 SQL 编辑器并连接到数据库。

   使用具有在表中执行删除操作所需权限的帐户连接。

2. 以下查询示例用于删除每个受支持数据库的数据：
   • 本示例中，将在 Oracle 中基于空间过滤器从表中删除值：

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

   • 本示例中，将在 PostgreSQL 中基于空间过滤器从表中删除值：

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

   • 本示例中，将在 SQLite 数据库中基于空间过滤器从表中删除值：

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

   • 在 IBM DB2 中，使用以下语句：

     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 不支持修改在子查询中使用的表或视图。因此，将查询分为两部分：选择一个临时表，然后用该临时表执行删除操作。

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