[postgis-users] Improvement suggestion

[Paul van der Linden]

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