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
