Try the solution outlined here: https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden < [email protected]> wrote: > As I am working with large polygons, I'm always struggling with > performance, and trying to find ways to improve them. > F.e. I have lots of queries like: > SELECT ST_Intersection(table1.geom,table2.geom) > FROM table1 > JOIN table2 on ST_Intersects(table1.geom,table2.geom) > > In case of large polygons this is sometimes a bottleneck, and I have the > following suggestion: > Create a function which returns the relation between 2 polygons (within, > intersects or disjunct) so that I can do the following: > > SELECT > CASE > WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN > ST_Intersection(table1.geom,table2.geom) > ELSE table1.geom > END > FROM table1 > JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within) > > or (because ST_Relate is calculated twice in previous query): > > SELECT > CASE > WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom) > ELSE t1geom > END > FROM ( > SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS > t1geom,table2.geom AS t2geom FROM table1 > JOIN table2 on table1.geom && table2.geom > ) AS allpolies > WHERE relate IN (intersects,within) > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
