[GIS] Query to find the Intersection coordinates of multiple Polyon

aggregationintersectionpostgis

I am using the query below to find out the intersection coordinates of 2 Polygons and it's working fine, but how can I use or modify the query to find the intersection area coordinates for multiple (or N number of) polygons?

SELECT ST_AsText(
        ST_INTERSECTION(
                       ST_GeomFromText('POLYGON((1.25 9.5,1.375 18.25,7.25 17.875,7.375 9.5,1.25 9.5))'),
                       ST_GeomFromText('POLYGON((1.25 9.5,1.375 18.25,7.25 17.875,7.375 9.5,1.25 9.5))')
                )
        );

What other alternative do I have?

Best Answer

As katahdin wrote, ST_Intersection works only with two geometries. I don't know a variant of it what works on arrays. If you have your geometries in one table, you can write a function that will do the job, like this:

create or replace function ST_IntersectionArray(geoms geometry[]) returns geometry as $$
declare
   i integer;
   tmpGeom geometry;
begin
    tmpGeom := geoms[1];
    FOR i IN 1..array_length(geoms,1) LOOP
      tmpGeom:= ST_Intersection(tmpGeom,geoms[i]);
    END LOOP;
    return tmpGeom;
end;
$$
LANGUAGE plpgsql;

Example call:

SELECT ST_AsText(ST_IntersectionArray(ARRAY(select geom from test_geom)));
Related Question