[postgis-users] Intersect Query Issue

[Aaron Racicot]

I am not an SQL expert by any means but I think you are looking for
something more like:

SELECT (area(intersection(first_shp.the_geom, second_shp.the_geom)) *
0.0002471) as acres FROM shape_pa as first_shp, shape_incursion_2005 as
second_shp WHERE intersects(first_shp.the_geom, second_shp.the_geom) AND
fisrt_shp.name = 'Caracol'

You want to take the area of the intersection, not just the whole
original shape once you know that it intersects... there is a subtle
difference.  Your original query would return the area of the original
shape and a new geometry that is the intersection... but never the area
of the intersection.  

You are probably going to need to watch out for types here since
intersection of two shapes type POLYGON will not necessarily return
another POLYGON... it might return a geometry collection, multi-polygon
etc.  I am not sure how particular the AREA function is with these
types.  If you know your data and you know that the intersection will
always return type POLYGON then you might be OK.

Hope this helps more than it hurts!

Aaron


+----------------------------------------+
| Aaron Racicot  | aaronr at ecotrust.org   |
| GIS Programmer | 503.467.0759          |
+----------------------------------------+
| e c o t r u s t                        |
| Jean Vollum Natural Capital Center     |
| 721 NW Ninth Avenue                    |
| Suite 200                              |
| Portland, OR 97209                     |
| www.ecotrust.org                       |
+----------------------------------------+


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Jerod Clabaugh
Sent: Wednesday, June 01, 2005 3:02 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Intersect Query Issue

All:

I am trying to get the area as acres from the overlap of 2 polygons. 
I have 2 polygon tables, shape.pa and shape_incursion_2005.  I used 
the query below and got an answer but its not correct.  Its not 
providing the correct area for the overlapping area post-clipping, 
but rather pre-clipping.

SELECT (area(shape_incursion_2005.the_geom)* 0.0002471) as acres, 
intersection(shape_incursion_2005.the_geom, shape_pa.the_geom) FROM 
shape_pa, shape_incursion_2005 WHERE 
intersects(shape_incursion_2005.the_geom, shape_pa.the_geom) AND 
shape_pa.name = 'Caracol'

In ArcGIS, I would select the polygon I wanted to compare 
(shape_pa.name = 'Caracol') and then clip the other table's 
overlapping polygons onto it and then calculate the area...

Any ideas on what I am doing wrong?

Thanks in advance,

Jerod Clabaugh
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users