[postgis-users] help with sql - calc area of polygons within map view

[Dylan Beaudette]

On Tuesday 23 September 2008, Jennifer Strahan wrote:
> Hi Everyone,
>
> I'm hoping someone can offer guidance on writing an sql statement.
>
> I'm working on a Google maps interface to display protected lands in
> California.  As the user moves the map, I'd like to calculate the total
> area of protected lands within the current map view.  I'm able to get
> the map bounds as lat/lon and pass that to the script.  I'm not sure how
> to do processing though.  Do I need to use ST_Union to get the union of
> the map frame and the polygons, then get the area of that?  Any examples
> on how to write the SQL would be very helpful.  By the way I have
> multiple geometry columns in my table, one that is CA teale albers
> (the_geom_ca_albers), and one that is web mercator (the_geom_google).
>
> Thanks,
> Jennifer

I would approach the problem like this:

1. get bbox corners
2. generate a postgis bbox from those corners
3. project the bbox geom to your albers projection
4. perform an intersection between your bbox and your features
5. compute the areas from the result


Here is an approach in PHP / fake SQL

$bbox_geom = "ST_Transform(ST_SetSRID(ST_MakeBox2D( ST_MakePoint($xmin, 
$ymin), ST_MakePoint($xmax, $ymax) ), $transform_from_SRID), 9001)" ;

select sum(ST_Area(ST_Intersection(wkb_geometry, $bbox_geom )
from feature_table
where ST_Intersects(feature_table.geometry, $bbox_geom)
group by something
...

Cheers,

-- 
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341