<div dir="ltr">You're running an unconstrained join, so if sets a and b are of non-trivial size, it's never going to finish because you'll have billions of results to test. You are also making promiscuous and unnecessary use of CTEs ("WITH" clause). Where you can avoid CTEs, avoid CTEs.<div><br></div><div>SELECT <span style="font-size:12.8px">CASE</span></div><span style="font-size:12.8px"> WHEN ST_CoveredBy(a.geom, b.geom)</span><br style="font-size:12.8px"><span style="font-size:12.8px"> THEN a.geom</span><br style="font-size:12.8px"><span style="font-size:12.8px"> WHEN ST_CoveredBy(b.geom, a.geom)</span><br style="font-size:12.8px"><span style="font-size:12.8px"> THEN b.geom</span><br style="font-size:12.8px"><span style="font-size:12.8px"> ELSE</span><br style="font-size:12.8px"><span style="font-size:12.8px"> ST_Intersection(a.geom, b.geom)</span><br style="font-size:12.8px"><span style="font-size:12.8px"> END as geom</span><div><span style="font-size:12.8px">FROM </span><span style="font-size:12.8px">alappuzhanew1 a</span></div><div><span style="font-size:12.8px">JOIN </span><span style="font-size:12.8px">alappuzhanew2 b</span></div><div><span style="font-size:12.8px">ON ST_Intersects(a.geom, b.geom)</span></div><div><span style="font-size:12.8px">WHERE ST_IsValid(a.geom)</span></div><div><span style="font-size:12.8px">AND ST_IsValid(b.geom)<br>AND a.filename = 'part3'</span></div><div><span style="font-size:12.8px">AND b.filename = 'part4'</span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">This is not 100% the same as your query, because yours includes a scary cast geom::geometry, which I am going to hope is not actually needed. I'm going to assume that (a) your "geom" columns are actually geometry type already and (b) that you already have spatial ("USING GIST (geom)") indexes on those columns.</span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">ATB</span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">P</span></div><div><span style="font-size:12.8px"><br></span></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Mar 30, 2017 at 4:53 AM, Saranya Sari <span dir="ltr"><<a href="mailto:saranyak578@gmail.com" target="_blank">saranyak578@gmail.com</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"><span class="m_-7020864629553712310gmail-gI"><span>Hai,<br>When I run my code, each time the memory reaches its limit and the system gets stuck. There is no output. This is my code. I am using PostGIS from psycopg2.<br><br>iin="""WITH a AS (<br>SELECT * FROM alappuzhanew1<br>WHERE filename = 'part3'<br>AND ST_IsValid(geom::geometry)),<br>b AS (<br>SELECT * FROM alappuzhanew2<br>WHERE filename = 'part4'<br>AND ST_IsValid(geom::geometry))<br>SELECT<br> CASE<br> WHEN ST_CoveredBy(a.geom, b.geom)<br> THEN a.geom<br> WHEN ST_CoveredBy(b.geom, a.geom)<br> THEN b.geom<br> ELSE<br> ST_Intersection(a.geom, b.geom)<br> END as geom<br>FROM a,b ;"""<br>curs.execute(iin)<br><br>Is there any fault with the code. Please help me....<br></span></span></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>