<div dir="ltr"><div><div><div><div><div>Hai,<br></div>yes. I am interested to retrieve all polygons which intersects any one among three categories.. <br></div>In the above example,<br>1)POLYGON((0 0,0 1,1 1,1 0,0 0)) filename as part1<br>POLYGON((0 0,0 2,2 2,2 0,0 0)) filename as part2<br>POLYGON((0 0,0 3,3 3,3 0,0 0)) filename as part3<br></div>From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 1,1 1,1 0,0 0)),<br><br>2)POLYGON((0 0,0 3,3 3,3 0,0 0)) filename as part1<br>POLYGON((0 0,0 2,2 2,2 0,0 0)) filename as part2<br>POLYGON((0 0,0 3,3 3,3 0,0 0)) filename as part3<br>From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 2,2 2,2 0,0 0)).<br><br>3)POLYGON((0 0,0 3,3 3,3 0,0 0)) filename as part1<br>POLYGON((0 0,0 5,5 5,5 0,0 0)) filename as part2<br>POLYGON((0 0,0 4,4 4,4 0,0 0)) filename as part3<br>From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 3,3 3,3 0,0 0)).<br></div>Other combinations also returns same answer.<br><br><br></div>Thanks<br><div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Feb 21, 2017 at 4:57 AM, Giuseppe Broccolo <span dir="ltr"><<a href="mailto:giuseppe.broccolo@2ndquadrant.it" target="_blank">giuseppe.broccolo@2ndquadrant.it</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Saranya,<br><div><span class=""><div class="gmail_extra"><br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="gmail_extra"><div class="gmail_quote">2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <span dir="ltr"><<a href="mailto:saranyak578@gmail.com" target="_blank">saranyak578@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hi all,<br>I am new to postgis and I need to find intersection of three polygons.<br></div>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. <br></div>I write my code as:<br> SELECT ST_Intersection(a.geom, b.geom) as intersect_ab <br>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)<br>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'<br> AND b.filename = 'part2' AND c.filename = 'part3') ;<br></div>Is it correct?????<br></div></blockquote></div></div></blockquote></div><br></div></span><div class="gmail_extra">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 one.<br><br></div><div class="gmail_extra">Regards,<br></div><div class="gmail_extra">Giuseppe.<span class="HOEnZb"><font color="#888888"><br clear="all"></font></span></div><span class="HOEnZb"><font color="#888888"><div class="gmail_extra"><br>-- <br><div class="m_1643951910362838441gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><span></span>Giuseppe Broccolo - 2ndQuadrant Italy
<br>PostgreSQL & PostGIS Training, Services and Support
<br><a href="mailto:giuseppe.broccolo@2ndQuadrant.it" target="_blank">giuseppe.broccolo@2ndQuadrant.<wbr>it</a> | <a href="http://www.2ndQuadrant.it" target="_blank">www.2ndQuadrant.it</a>
</div></div></div></div>
</div></font></span></div></div>
<br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>