[postgis-users] Improvement suggestion

[David Haynes]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181213/5393c14c/attachment.html>