[Mapserver-users] PHP Mapsctipt, PostGIS and extent of required features - a faster method?

[Ellis Robin Bundaberg]

Thanks to Robert Crossley - he gave this advice:

"I've worked a bit with Oracle and SQLServer, and invariably 
performance is related to either indices or order of queries.  You could 
perhaps try selecting the subset first and doing the extents on the 
subset.  If the slowness is in the extent function, then then perhaps you 
could put a procedure in there to calculate the extents for each object 
and store them with the record?"

To that end I am now looping through each of the unique identifiers and running the 'select extent' query for each, placing the returned values into PHP arrays. I then take the min or max values from the relevant arrays to get the 'bounding box' required for all of the selected features. This runs very rapidly. I just need to get the arrays to handle 'negative' latitude values correctly in the min and max functions and everything should be fine.

Thanks again Robert

Rob


-----Original Message-----
From: Ellis Robin (Bundaberg) 
Sent: Wednesday, 3 December 2003 9:50 AM
To: MapSever Users (E-mail)
Subject: [Mapserver-users] PHP Mapsctipt, PostGIS and extent of required
features - a faster method?


Another message along the 'zoom to selected features' theme, but what I'm after is some hints that will hopefully speed my process up.

The concept of my system is that a user can input 1 or more unique identifiers for some cadastral polygon data we have stored in Postges/PostGIS format, with 1620320 records. The identifiers are placed into a string that is used firstly for applying a filter to the cadastre layer, and secondly to get the extent of the required features. The string (or 'filterexpression') would look something like:

fieldname = 'identifier1' or fieldname = 'identifier2' etc...

In PHP mapscript I use the following to apply the filter to my cadastre layer:

$zoomdcdblayer = $map->getLayerByName("Selected DCDB");
$thefilterexpression = $thefilterexpression." or lotplan = '".$thedcdbparcel."'";
$zoomdcdblayer->setfilter($thefilterexpression2);

This part works great, the filter is applied and displayed correctly in the blink of an eye. The next step is getting the extent of the required features so that view can be zoomed to the appropriate extent. I'm currently acheiving this like:

$extquery = "SELECT extent(geo_value) FROM sedcdb where $thefilterexpression";
$res = pg_query($connection, $extquery) or die("Error in query: $query. " . pg_last_error($connection));
$row = pg_fetch_row($res, $i);

$swlong = substr($row[0], 6, 10);
$swlat = substr($row[0], 17, 10);
$nelong = substr($row[0], 30, 10);
$nelat = substr($row[0], 41, 10);

$map->setextent($swlong,$swlat,$nelong,$nelat);

Which works well, however the time it takes to process the extent increases dramatically when more than one unique identifier is supplied. That is, the results for a single polygon are processed in around 1 second, two polygons in around 20 - 25 seconds, and so on. I do have an index created for the unique identifier field. The time lag appears associed with the 'select extent' query - maybe I could be structuring the details of this query in a smarter way, or maybe there is just a better way to go about it. We used to have a similar system utilising querymap and mapext=shapes, however this was outside of the mapscript realm and only worked for a single feature. I'm not sure mapscript and Postgis allow a simple replication of this.

Does anybody have any suggestions as to how the speed of processing may be improved?

Thanks

Rob


Robin Ellis
Department of Natural Resources and Mines
PO Box 1167
Bundaberg QLD 4670
ph +61 7 4131 5771
fax +61 7 4131 5823
Robin.Ellis at nrm.qld.gov.au




************************************************************************
The information in this e-mail together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.
Any form of review, disclosure, modification, distribution
and/or publication of this e-mail message is prohibited.  
If you have received this message in error, you are asked to
inform the sender as quickly as possible and delete this message
and any copies of this message from your computer and/or your
computer system network.  
************************************************************************


_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users