PostGIS ST_Intersects Performance – Tips for Optimization

postgispostgresql

I am selecting a lot of polygons within a gigantic polygon by using the postgis function ST_Intersects().

SELECT 
        * 
FROM 
        data.polygons AS poly, 
        data.gig_polygon AS gig
WHERE
        ST_Intersects(poly.geom, gig.geom)

The gigantic polygon is just a (one row) large sized polygon at the moment. The query is taking a very long time. I was wondering if it would be faster to split the gigantic polygon in multiple smaller polygons and run the query again. I dont want to cancel it without knowing this will be faster. To know the answer to my question I guess you have to understand in what way the ST_Intersects() function works.

Does anybody know that? Or knows another way of speeding up the ST_Intersect() functionality?

Best Answer

It could be faster, you're right but what you really need if you don't already have one is a spatial index. This will then be able to do a first pass of your query quickly by working out which bits of the data the query needs to look it. It will then check these data points for which fall exactly within the polygon.

You can create an index using information here - it also gives an overview of spatial indexing.

The reason it might be faster if you break up the large polygon is that if, say your polygon is long and thin, the bounding box (which the index uses) might not cover much of the actual area of the polygon, so you will get a lot of false positives from the index. Equally if your polygon covers most of your other dataset anyway, the index will not be much use. This might be more appropritate on gis.stackexchange.com

Related Question