I have 150 million points in a point table and would like to find the few points lying outside a given polygon geometry. I know that 99.9% of the points are within the polygon geometry. I am interested in finding the few points which lie outside the polygon.
My present best query using indexed PostGIS tables takes about 30 minutes to complete. Is there a way to optimize the following query knowing that most of the points are within the polygon (border)?
SELECT COUNT(*)
FROM italy_points pt
JOIN borders poly
ON ST_WITHIN (pt.the_geom, poly.geom)
WHERE poly.iso3 = 'ITA';
The polygon is basically the admin 0 border of Italy. Vertices - 405,000. Parts - 510. The envelope is much larger than the polygon (The polygon covers 24% of the envelope)
GROUP BY
the primary key of the points. (PostgreSQL conveniently allows you to reference any columns in theSELECT
clause that come from a table where the primary key is included in theGROUP BY
clause.)ST_Within
already does a boundary box check that enables to use of the index. (Almost all of PostGIS's functions include this optimization.) If it's still slow, then clearly the problem is with the complexity of the polygon.ST_Intersects
, sinceST_Within
would not reliably match internal boundary conditions.