Intersection of three polygons

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

Intersection of three polygons

Saranya Sari
Hi all,
I am new to postgis and I need to find intersection of three polygons.
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.
I write my code as:
 SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
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)
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'
   AND b.filename = 'part2' AND c.filename = 'part3') ;
Is it correct?????

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

Re: Intersection of three polygons

Rémi Cura
Hey,
sorry to say it's very difficult to understand what you want to do.
Please either do a schema or a precise description (with math: you have setA, set B, setC , and you want to find ...).
Cheers,
Rémi-C

2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <[hidden email]>:
Hi all,
I am new to postgis and I need to find intersection of three polygons.
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.
I write my code as:
 SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
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)
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'
   AND b.filename = 'part2' AND c.filename = 'part3') ;
Is it correct?????

_______________________________________________
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: Intersection of three polygons

Saranya Sari
For example:
POLYGON((0 0,0 1,1 1,1 0,0 0))   filename as part1
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part1
POLYGON((0 0,0 2,2 2,2 0,0 0))   filename as part2
POLYGON((0 0,0 5,5 5,5 0,0 0))   filename as part2
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part3
POLYGON((0 0,0 4,4 4,4 0,0 0))   filename as part3

Now I need to find regions which are common in all categories.
that is I expect the result as:
POLYGON((0 0,0 1,1 1,1 0,0 0))
POLYGON((0 0,0 2,2 2,2 0,0 0))
POLYGON((0 0,0 3,3 3,3 0,0 0))



On Mon, Feb 20, 2017 at 3:12 PM, Rémi Cura <[hidden email]> wrote:
Hey,
sorry to say it's very difficult to understand what you want to do.
Please either do a schema or a precise description (with math: you have setA, set B, setC , and you want to find ...).
Cheers,
Rémi-C

2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <[hidden email]>:
Hi all,
I am new to postgis and I need to find intersection of three polygons.
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.
I write my code as:
 SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
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)
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'
   AND b.filename = 'part2' AND c.filename = 'part3') ;
Is it correct?????

_______________________________________________
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
Reply | Threaded
Open this post in threaded view
|

Re: Intersection of three polygons

Giuseppe Broccolo
In reply to this post by Rémi Cura
Hi Saranya,

2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <[hidden email]>:
Hi all,
I am new to postgis and I need to find intersection of three polygons.
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.
I write my code as:
 SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
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)
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'
   AND b.filename = 'part2' AND c.filename = 'part3') ;
Is it correct?????

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.

Regards,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
[hidden email] | www.2ndQuadrant.it

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

Re: Intersection of three polygons

Saranya Sari
Hai,
yes. I am interested to retrieve all polygons which intersects any one among three categories..
In the above example,
1)POLYGON((0 0,0 1,1 1,1 0,0 0))   filename as part1
POLYGON((0 0,0 2,2 2,2 0,0 0))   filename as part2
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 1,1 1,1 0,0 0)),

2)POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part1
POLYGON((0 0,0 2,2 2,2 0,0 0))   filename as part2
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 2,2 2,2 0,0 0)).

3)POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part1
POLYGON((0 0,0 5,5 5,5 0,0 0))   filename as part2
POLYGON((0 0,0 4,4 4,4 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 3,3 3,3 0,0 0)).
Other combinations also returns same answer.


Thanks


On Tue, Feb 21, 2017 at 4:57 AM, Giuseppe Broccolo <[hidden email]> wrote:
Hi Saranya,

2017-02-20 5:57 GMT+01:00 Saranya Kuniyil <[hidden email]>:
Hi all,
I am new to postgis and I need to find intersection of three polygons.
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.
I write my code as:
 SELECT ST_Intersection(a.geom, b.geom) as intersect_ab
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)
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'
   AND b.filename = 'part2' AND c.filename = 'part3') ;
Is it correct?????

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.

Regards,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
[hidden email] | www.2ndQuadrant.it

_______________________________________________
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: Intersection of three polygons

Giuseppe Broccolo
Hi Saranya,

2017-02-21 10:51 GMT+01:00 Saranya Kuniyil <[hidden email]>:
Hai,
yes. I am interested to retrieve all polygons which intersects any one among three categories..
In the above example,
1)POLYGON((0 0,0 1,1 1,1 0,0 0))   filename as part1
POLYGON((0 0,0 2,2 2,2 0,0 0))   filename as part2
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 1,1 1,1 0,0 0)),

2)POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part1
POLYGON((0 0,0 2,2 2,2 0,0 0))   filename as part2
POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 2,2 2,2 0,0 0)).

3)POLYGON((0 0,0 3,3 3,3 0,0 0))   filename as part1
POLYGON((0 0,0 5,5 5,5 0,0 0))   filename as part2
POLYGON((0 0,0 4,4 4,4 0,0 0))   filename as part3
From these polygons I need to get Intersecting polygon as POLYGON((0 0,0 3,3 3,3 0,0 0)).
Other combinations also returns same answer.

So, it could be possible that some intersecting geometries (properly referred to different categories) are
actually one contained into the other, and so on. You could benefit of the technique reported in this interesting
article::

to speed up the intersection computation (that is quite expensive in time). I'm asking this because I see you were
filtering on overlapping (not just intersecting) geometries through the condition on ST_Overlaps. This one is more
restrictive than the ST_Intersects one, so consider to use one or the other basing on what you really need to filter.

Hope this can help.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
[hidden email] | www.2ndQuadrant.it

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