Question about the solution that was posted below. I have always used ST_CollectionExtract( ST_MakeValid(geom), 3 ) and in the post it uses st_multi(st_makevalid(geom))
Which is preferred. On the website for ST_CollectionExtract() there is a new warning. When specifying 3 == POLYGON a multipolygon is returned even when the edges are shared. This results in an invalid multipolygon for many cases such as applying this function on an ST_Split <https://postgis.net/docs/ST_Split.html> result. Try the solution outlined here: https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis On Sun, Dec 2, 2018 at 3:40 PM 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
