[postgis-users] Intersection of three polygons

Giuseppe Broccolo giuseppe.broccolo at 2ndquadrant.it
Mon Feb 20 15:27:28 PST 2017

Hi Saranya,

2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <saranyak578 at gmail.com>:
>> Hi all,
>> I am new to postgis and I need to find intersection of three polygons.
>> In my table named "mytable" contains large no of polygon coordinates and
>> grouped into three categories by adding a field called "filename".I
>> labelled the category names as part1, part2, part3 in filename . Now I need
>> to find all polygon which spacially intersect in three categories.
>> I write my code as:
>>  SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
>> FROM mytable a INNER JOIN mytable b ON ST_Intersects(a.geom,b.geom) INNER
>> JOIN mytable c ON ST_Intersects(a.geom,c.geom)
>> WHERE ST_Overlaps(a.geom, b.geom) AND ST_Overlaps(b.geom, c.geom) AND
>> ST_Overlaps(a.geom, c.geom) AND ST_isvalid(a.geom)='t' AND
>> ST_isvalid(b.geom)='t' AND ST_isvalid(c.geom)='t' AND a.filename = 'part1'
>>    AND b.filename = 'part2' AND c.filename = 'part3') ;
>> Is it correct?????
In principle, your query is fine, but are you interested to retrieve just
the three intersecting geometries or the resulting intersection of these
(currently, you are selecting the intersection between part1 and part2
geometries)? Moreover, are you interested on overlapping or intersection (I
mean, is it fine if a part1 geometry contains a part2 or part3 ones)? I'm
asking this because the intersects computation is faster than the overlaps


Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo at 2ndQuadrant.it | www.2ndQuadrant.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170221/cf5149d0/attachment.html>

More information about the postgis-users mailing list