dimanche 22 mars 2015

PostGis - ST_Within or ST_Disjoint performance issues


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.





Aucun commentaire:

Enregistrer un commentaire