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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
