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

[Bruce Rindahl]

Sorry
I missed sending this to the entire group:

Assuming you send the request in the form:
http://myserver.com/getarea.php?xmin=-100&ymin=40&xmax=-99&ymax=41

getarea.php is:

<?php
header("Content-Type: text/xml");

$username = 'xxx';
$dbGeomName = 'XXX';
$password = 'yyy';
$hostname = 'mylhost';

//get URL parameters
$xmin = intval($_GET['xmin']);
$xmax = intval($_GET['xmax']);
$ymin = intval($_GET['ymin']);
$ymax = intval($_GET['ymax']);

//I assume since you are using Google Maps
$srid = 4326;

$tablename = 'whateverMyTableIs';

//intersection polygon to clip out data where useful
$intersectPolygon = 'GeometryFromText(\'POLYGON(('.$xmin.' 
'.$ymin.','.$xmax.' '.$ymin.','.$xmax.' '.$ymax.','.$xmin.' 
'.$ymax.','.$xmin.' '.$ymin.'))\','.$srid.')';

//connect to db
$my_pg_connect = pg_Connect('host='.$hostname.' dbname='.$dbGeomName.' 
user='.$username.' password='.$password) or die ('Can\'t connect to 
database '.$dbGeomName);

   $mySQL= 'SELECT 
area(ST_intersection(the_geom_ca_albers,transform('$intersectPolygon.',find_srid(\'\',\''.$tablename.'\',\'the_geom_ca_albers\')))) 
AS area FROM '.$tablename;

   //execute sql command
   $my_result_set = pg_Exec($my_pg_connect, $mySQL) or die 
(pg_ErrorMessage());

   //get number of rows retrieved
   $numRecs = pg_NumRows($my_result_set);

   $resultArray = pg_Fetch_Array($my_result_set, 0);
    print  $resultArray['area']);

//close db connection
pg_Close($my_pg_connect);
?>

This should return the area within your viewport in the map units of 
your table (the_geom_ca_albers).
I didn't exactly test this - I took a small section of some existing 
code.  Let me know if there are issues.

For a nice tutorial of what is going on here see:
http://www.carto.net/papers/svg/postgis_geturl_xmlhttprequest/

Bruce




Jennifer Strahan wrote:
> Yes, PHP.
>
> Thanks,
> Jennifer
>
> Bruce Rindahl wrote:
>> How are you connecting with the database? PHP?
>> Bruce
>>
>> 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
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>