Difficulty for finding Intersection using postGIS

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Difficulty for finding Intersection using postGIS

Saranya Sari
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Difficulty for finding Intersection using postGIS

Paul Ramsey-3
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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users