[postgis-users] Improvement suggestion

[Darafei "Komяpa" Praliaskouski]

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 <haynesd2 at gmail.com> 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 <pramsey at cleverelephant.ca>
> 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 <
>> paul.doskabouter at gmail.com> 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
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181218/9c8eb626/attachment.html>