4

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?

migrated from stackoverflow.com Oct 9 '15 at 16:25

This question came from our site for professional and enthusiast programmers.

  • To me your query looks like a massive join on a geometry field rather than comparison against a single Gigantic polygon. Such joins are unfortunately rather slow since there is a lot of calculations involved. Are you quite sure there souldn't be a filter on gig_polygon? – e4c5 Oct 7 '15 at 0:02
  • The other thing that slows down Intersects() is if the polygons are complex (i.e., have many vertices). Even with indexes, ST_Intersects() is slow when comparing polygons where one or more of them has a large number of vertices; I routinely check ST_Intersects() between rectangular cadastre polygons (with 4 vertices) against zoning polygons (with sometimes upwards of 40,000 vertices per zoning polygon, and over a thousand polygons in the state). That's the nature of the comparison being performed, unfortunately. – GT. May 20 '16 at 6:07
8

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

2

Yes, it will be faster, I've done a similar task not so long ago and realized PostGIS works better with lots of small features rather than few big ones. You can read more on my blog.

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.