Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
By 'lines' do you mean database records/features, because you only mention point and polygon geometry? Have you tried ST_Intersection? –  MappaGnosis Feb 21 '13 at 16:10
    
Yes, it is the number of records/features... –  Onesime Feb 21 '13 at 16:19

1 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)
 );
share|improve this answer
    
Hi "HeyOverThere", Thank you for your Query, I tried the first one (the one with the "ST_Difference"), but it returned with a wrong result. The one with ST_Disjoint run until this weekend, and the result was good, it delete all the occurences outside the polygon as I wanted. So thank you very much for your help!!! Cheers. –  Onesime Feb 25 '13 at 10:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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