I have a table of about 150,000 points in PostGIS with a spatial index and a SRID of 27700 (OS). I want to select the points that fall outside England and Wales. I have a multipolygon table with just one record in it (a dissolved polygon of England and Wales).

I would have thought that the following query would select this quite quickly, but it was still running after about 5 hours (I actually forgot I'd started it and was off doing something else). I've tried a few variations using ST_Within to do it the other way around, but I can't get a result in a reasonable time (I have yet to wait for the query to finish running). Considering the points were generating in pgRouting in about 5 mins, I'm surprised it takes this long to do any analysis of them.

Admittedly I'm using an old PC but still with 2GB of ram I would have thought this was a relatively simple query. Is it likely that it should take this long or is something likely to be wrong. The query I've used is below:

FROM catchment_distance_output, england_wales_os 
WHERE ST_Disjoint (catchment_distance_output.geometry, england_wales_os.geometry);

I imagine I'm doing something obvious, but I'm very new to this and learning as I go.

  • How many vertices do you have in the polygon of england_wales_os.geometry?
    – user30184
    Mar 22, 2015 at 19:16
  • ST_Disjoint is always a full table scan, since it can't make use of an index.
    – Vince
    Mar 22, 2015 at 20:28
  • It could because it means the same as Not intersects.
    – user30184
    Mar 22, 2015 at 20:59

1 Answer 1


Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus:

SELECT pts.*
FROM pts LEFT JOIN polys
ON ST_Contains(polys.geom, pts.geom)
WHERE polys.id IS NULL;

The un-matched rows in a left join are returned as NULL so doing an IS NULL test on a column that you know is declared NOT NULL finds you all the un-matched rows.

  • Thanks for this. I think the main problem with performance was with my polygon geometry - obviously something wrong there as when I created a very simply container for my points (for testing only) the above query returned 11,500 records in 1516ms. However, I also tried my original query it returned the same number of records in 41,906ms - so considerable improvement in performance. Two important lessons learned; use your method for querying and ensure polygon geometries are valid and as simple as possible. Thanks.
    – Steven Lee
    Mar 23, 2015 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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