[postgis-devel] ST_Intersection optimisation?

[George MacKerron]

I just came across this Stack Exchange exchange from last year:
http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis

In it, an st_intersection query is sped up by an order of magnitude by following Paul Ramsey's advice not to call st_intersection on two geometries where one is within the other, but instead to just return the inner geometry.

So I wondered if it would be a good idea to have st_intersection do this automatically? 

For example, by renaming the current st_intersection as _ st_intersection, and making a new st_intersection that does something like this:

CASE
WHEN ST_Within(a, b) THEN a
WHEN ST_Within(b, a) THEN b  
ELSE _ST_Intersection(a, b)
END

Admittedly this would add some overhead to queries in which most geometries do need intersecting, but the performance gains in the example linked above make it look as though it could be worth it?

I guess one could extend this, if the st_intersection logic doesn't do so already, to an st_intersects check too:

CASE
WHEN NOT ST_Intersects(a, b) THEN ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')
WHEN ST_Within(a, b) THEN a
WHEN ST_Within(b, a) THEN b  
ELSE _ST_Intersection(a, b)
END

All the best,
George

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4361 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20130730/305b3f03/attachment.bin>