[postgis-users] Improvement suggestion

[Paul Ramsey]

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