5

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:

SELECT pk 
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 '15 at 19:16
  • ST_Disjoint is always a full table scan, since it can't make use of an index. – Vince Mar 22 '15 at 20:28
  • It could because it means the same as Not intersects. – user30184 Mar 22 '15 at 20:59
12

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 '15 at 22:04

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.