[postgis-users] ST_Intersects

[Birgit Laggner]

Hi Shreyes,

if I understand your question right, it's not much different from what 
you asked before. You would select the intersecting geometries of both 
tables by asking for st_intersects(a.the_geom,b.the_geom)=true, then 
intersect those geometries with st_intersection() and then calculate the 
area of the intersection with st_area(). For the identification of the 
origin of the resulting geometries, you select the id columns of the 
tables plots and parcels, too - so, you would see that plot S01BAT009 
does have intersecting geometries with parcels P1, P2 and P3 (in your 
example). Here an example for such a query:

select
a.plot_id,
b.parcel_id,
st_area(st_intersection(a.the_geom, b.the_geom)) as the_geom
from
plots a
inner join
parcels b
on a.the_geom && b.the_geom
where st_intersects(a.the_geom, b.the_geom);

I don't know exactly what you mean by redundant tuples - perhaps it 
would help, if you would post your query and an example of its result 
here too...

Regards,

Birgit.


Am 05.05.2011 14:35, schrieb shreyes shiv:
> Dear all,
>
> I have two shape files plots and parcels.
>
> the attributes of the "plots" shape file are
> plot_id
> plot_area
> owner_nm
> the_geom
>
> the attributes of the "parcels" shape file are
> parcel_id
> parcel_area
> conversion
> the_geom
>
> when i do the ST_Intersects operation on the shape files it returns a 
> table that contains redundant tuples.
> what i want is the area of the newly formed polygons which are formed 
> after the intersection as an extra column.
>
> This image explains my problem.
> parcelplotresult-1.jpg
>
> The Plot S01BAT009 lying in Parcels P1, P2 and P3, and also occupying 
> the area A1, A2 and A3 in the respective parcels. The white lines are 
> the parcel boundaries. The black lines are the plot boundaries.
> I want the areas A1, A2 and A3.
>
> please help.
>
>
>
> -- 
> shreyes shiv
> email: shivshreyes at gmail.com <mailto:shivshreyes at gmail.com>
> phone: 9557975780
> IIRS(Indian Institute of Remote Sensing)
> No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110505/2ae70fb3/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 14784 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110505/2ae70fb3/attachment.jpe>