Did anyone else spot that the person created his target table, created an index on that (empty) table, then inserted all the data in it using St_intersection? He should create the index afterwards.
Sent from BlueMail On 3 Dec. 2018, 08:41, at 08:41, Paul Ramsey <[email protected]> wrote: >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
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
