Hi, These are all hacks with different meanings. ST_CollectionExtract removes sliver parts that became lines, and ST_Multi just transforms a GEOMETRYCOLLECTION into MULTI*. Basically ST_Multi is most of time used with badly-written software that does not expect GEOMETRYCOLLECTION at all, or wants everything to be of the same type (so, a MULTIPOLYGON). ST_CollectionExtract performs one kind of clean up. For other clean ups, like dissolving overlapping polygons, you may need different hacks - maybe ST_UnaryUnion. If your data does not contain that kind of problems or your further processing is robust to them, you can just omit it.
On Thu, Dec 13, 2018 at 6:38 PM David Haynes <[email protected]> wrote: > 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 -- Darafei Praliaskouski Support me: http://patreon.com/komzpa -- Darafei Praliaskouski Support me: http://patreon.com/komzpa
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
