Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am using the below Query to find out the intersection coordinates of 2 Polygon and its working fine,but how to 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))') ) );

Plz let me know if any other alternative to this.

share|improve this question

3 Answers 3

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)));
share|improve this answer

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
share|improve this answer
    
p1.id <> p2.id will get you doubles: (a,b) and (b,a). It's better to use > or < instead of <> –  Jakub Kania May 11 '13 at 21:12

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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