4

How can I delete all points (rows) in one table that fall outside a polygon stored in a different table? Assume the tables follow the common convention of storing the geometry in a column named geom.

2
  • By 'lines' do you mean database records/features, because you only mention point and polygon geometry? Have you tried ST_Intersection? Commented Feb 21, 2013 at 16:10
  • Yes, it is the number of records/features...
    – Onesime
    Commented Feb 21, 2013 at 16:19

1 Answer 1

8

You can use a SELECT subquery in a DELETE query. So your query would look something like:

DELETE FROM points_table WHERE 
points_table.id IN (
    SELECT a.id FROM
      points_table a, polygon_table b
      WHERE NOT ST_Intersects(a.geom, b.geom)
 );

id is the primary key, or some other column with a unique constraint in your points table.

Alternatively, you can use ST_Disjoint to find points that do not intersect the polygon, but PostGIS cannot make use of spatial indexes to determine whether geometries are disjoint. NOT ST_Intersects will be more efficient.

Second Edit: If your polygon layer contains more than one feature you'll need to union them together. Otherwise the above query will delete all your points, as each point will fall outside some polygon (unless you're dealing with an unusual dataset with overlapping polygons). That changes the query to this:

DELETE FROM points_table WHERE 
points_table.id IN (
    SELECT a.id FROM
      points_table a, (
           SELECT ST_Union(geom) As geom FROM polygon_table
      ) b
      WHERE NOT ST_Intersects(a.geom, b.geom)
 );
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.