4

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?

3 Answers 3

6

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)));
1

If your polygons are in two separate tables, say poly1, and poly2, then

SELECT As_Text(St_Intersection(poly1.geom, poly2.geom))
FROM poly1, poly2;

If all the polygons are in one table, polys, then, assuming you have a primary key 'id':

SELECT As_Text(ST_Intersection(p1.geom, p2.geom))
FROM polys AS p1, polys AS p2
WHERE p1.id <> p2.id
1
  • 3
    p1.id <> p2.id will get you doubles: (a,b) and (b,a). It's better to use > or < instead of <> May 11, 2013 at 21:12
1

The ST_Intersect function works with only two geometries at a time. It is a binary relation only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.