For deleting polygonal features in one layer (~1000000 features) outside polygon feature of another layer (one feature) in PostGIS I use query:

DELETE FROM layer WHERE layer.id IN (SELECT a.id FROM layer a, boundary b WHERE ST_Disjoint(a.geom, b.geom));

Now the process takes more than 6 hours. How can I speed up the process of deleteng big count features one layer outside feature in another layer?

  • there is only one row in the table boundary ?
    – Istopopoki
    Oct 27 '15 at 15:54
  • 1
    The first rule of using DELETE on large tables is "Don't do it". The second rule (for experts only) is "Don't do it yet." Yet I wonder if your problem isn't the spatial query. Please edit the question to specify the number of rows in the boundary table, and the time it takes to populate a temp table with a CREATE TABLE foo AS with your subselect query. It would also be useful to know how many layer features are not within boundary feature(s).
    – Vince
    Oct 27 '15 at 15:58
  • @Istopopoki, yes, only one row Oct 27 '15 at 17:07
  • can you try the same query (just the select part) using ST_DWithin > 0 instead ? If it is faster you could then delete the id "NOT IN". Have also a look at this question.
    – Istopopoki
    Oct 27 '15 at 17:38
  • @Istopopoki, thanks for the advice, I tried ST DWithin, it faster for 7 seconds) Oct 27 '15 at 17:58

I initially used wrong way... thanks @Vince for the advice. Now I try query like

CREATE TABLE layer_new AS SELECT a.* FROM layer a, boundary b WHERE ST_Intersects(a.geom, b.geom);

that works fine. 232527 rows from 680000 changed on 111 seconds.

  • There's nothing wrong with using a DELETE at all. I think the difference is that the second query uses ST_Intersects, which can take advantage of a spatial index, while the first uses ST_Disjoint, which cannot.
    – dbaston
    Oct 28 '15 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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