[postgis-users] Difficulty for finding Intersection using postGIS

[Saranya Sari]

Thank you...
I actually need to find the regions which occurs in both alappuzha1 and
alappuzha2. Also, there should not be any intersection between the
resulting polygons . That is, i want unique polygons which intersect in
both table.
Is this code is sufficient for this purpose??????

On Thu, Mar 30, 2017 at 7:33 PM, Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> 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.
>
> SELECT CASE
>     WHEN ST_CoveredBy(a.geom, b.geom)
>         THEN a.geom
>     WHEN ST_CoveredBy(b.geom, a.geom)
>         THEN b.geom
>     ELSE
>         ST_Intersection(a.geom, b.geom)
>     END as geom
> FROM alappuzhanew1 a
> JOIN alappuzhanew2 b
> ON ST_Intersects(a.geom, b.geom)
> WHERE ST_IsValid(a.geom)
> AND ST_IsValid(b.geom)
> AND a.filename = 'part3'
> AND b.filename = 'part4'
>
> 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.
>
> ATB
>
> P
>
>
> On Thu, Mar 30, 2017 at 4:53 AM, Saranya Sari <saranyak578 at gmail.com>
> wrote:
>
>> Hai,
>> 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.
>>
>> iin="""WITH a AS (
>> SELECT * FROM alappuzhanew1
>> WHERE filename = 'part3'
>> AND ST_IsValid(geom::geometry)),
>> b AS (
>> SELECT * FROM alappuzhanew2
>> WHERE filename = 'part4'
>> AND ST_IsValid(geom::geometry))
>> SELECT
>>     CASE
>>     WHEN ST_CoveredBy(a.geom, b.geom)
>>         THEN a.geom
>>     WHEN ST_CoveredBy(b.geom, a.geom)
>>         THEN b.geom
>>     ELSE
>>         ST_Intersection(a.geom, b.geom)
>>     END as geom
>> FROM a,b ;"""
>> curs.execute(iin)
>>
>> Is there any fault with the code. Please help me....
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170331/f2b9f6ce/attachment.html>