Delete points outside a polygon with PostGis

Question

I have to use PostGis because I have a very large database (more than 7 000 000 lines), but I am not used to work with PostGis; and since days, I would like to do that: Select and then delete all points which are geographically outside from a polygon (the points are in a difference table than the polygon table).

I read many forums etc, but I don't find what I want. Did somebody have an advice or an example command which I can use? I tried to do it through QGis, but the treatment is too large and I have to wait a long time to finally obtain an error message!

Cheers

Answer 1

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 ST_Disjoint(a.geom, b.geom)
 );

Hopefully you have a primary key, or at least a unique ID field in your points table.

Edit: Sorry, used ST_Difference first, that returns geometry. Here you want ST_Disjoint which returns True for all records from a that do not intersect with b.

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. 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 ST_Disjoint(a.geom, b.geom)
 );