[postgis-users] Difficulty for finding Intersection using postGIS

[Paul Ramsey]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170330/d878810c/attachment.html>