[UMN_MAPSERVER-USERS] Question re: PostGIS and MapScript getExtent() function

[Paul Ramsey]

Steve,

Try it on a 20M record table in Oracle. Both implementations,  
Oracle's and your uncommented PostGIS one, do the same functional  
thing, which is a full table scan.  It is possible that Oracle is  
slightly smarter about reading the index for the AGGR_MBR case, which  
might make it work better on large tables: one for the testing range.

I think your tuppence might be right on the money:

- Enable ms*GetLayerExtent() to provide the "right" answer in all  
cases, even if it is expensive.
- Add a ms*GetLayerExtentFast() that for the easy data sources just  
calls GetLayerExtent, and for the hard data sources does whatever  
combination of fast hacks and cop-outs are required.

WMS GetCapabilities should call GetLayerExtentFast and mapscript  
should call GetLayerExtent.

Unrelated question for the gurus: does GetLayerExtent respect filters  
for all data sources?

Paul

On 12-Jul-05, at 11:30 AM, Steve Hall wrote:

> Thanks Paul,
>
> This all sounds very reasonable to me....  I'll keep this to myself  
> for now then.
>
> I'm curious about the fact that this is implemented for Oracle  
> which is performing a very similar operation (i.e a spatial  
> aggregate, SDO_AGGR_MBR).  Is Oracle more efficient than PostGIS in  
> this area or is there a difference in approach that i'm missing...?
>
> For my two-pennorth a function like this is very useful, as it  
> allows me to change the filter for a layer, and alter the map  
> extent to match the extent of the "new" layer, without needing to  
> do any "dirty" SQL on the back-end database manually to find the  
> new extent.  Perhaps if it can't / shouldn't be done via these  
> msXXXLayerGetExtent() functions there is another way, or could it  
> be listed as an enhancement?
>
> Thanks again, i'm new to the contributing side of Open Source so  
> forgive my early mistakes!
>
> Steve
>
>
>
> ----- Original Message ----- From: "Paul Ramsey"  
> <pramsey at refractions.net>
> To: "Steve Hall" <steve.hall at mercatorgeosystems.co.uk>
> Cc: <MAPSERVER-USERS at LISTS.UMN.EDU>; <MAPSERVER-DEV at LISTS.UMN.EDU>
> Sent: Tuesday, July 12, 2005 6:09 PM
> Subject: Re: [UMN_MAPSERVER-USERS] Question re: PostGIS and  
> MapScript getExtent() function
>
>
>
>> Steve,
>>
>> If you grep through the source, you'll see the calls, which are   
>> mainly in the WMS code (pulling extents for capabilities).  The   
>> problem is that the return time for the call can be very large  
>> for  very large tables.  Which is not really acceptable for a  
>> capabilities  return.  Maybe it is acceptable for your  
>> application.  This is the  real problem.  We want something with  
>> very low return times (as a  shapefile extents call is) but we  
>> cannot get it with the extent()  function, because that requires a  
>> sequence scan.
>>
>> So basically, we cannot simply stick in your implementation, or  
>> we  are building a big obscure "gotcha" into mapserver. ("Why does  
>> it  take forever to return a capabilities file for my PostGIS  
>> backed WMS? Everything else is fast.")
>>
>> Perhaps the folks on -DEV can return some zen wisdom on the  
>> correct approach to this.
>>
>> Paul
>>
>> On 12-Jul-05, at 9:52 AM, Steve Hall wrote:
>>
>>
>>> Hi Paul,
>>>
>>> Exactly right - it definitely needs reviewing by those that know   
>>> more about MapServer internals than I do.  (which is most of you  
>>> I  expect).
>>>
>>> How is this function used my MapServer itself then?  The context  
>>> in which I am using it is via an explicit $layerObj->getExtent()  
>>> call  from PHP/MapScript, so in this respect the current  
>>> implementation  is not much use, but I suspect there is more to  
>>> this function than  just an implementation for this call.   The  
>>> main issue I had was  that this function worked as I expected for  
>>> Oracle, yet not for  PostGIS, meaning I'd need some ugly code in  
>>> my app to deal with the  inconsistency...
>>>
>>> Anyway, i've attached my version of the function for you to take  
>>> a  look at. My implemenation is basically the version that was   
>>> commented out, with the compilcation errors & warning resolved,   
>>> added support for the FILTER expression and support for either  
>>> BOX  or BOX3D return clauses from PostGIS extent().
>>>
>>> Many thanks,
>>>
>>> Steve
>>>
>>>
>>>
>>> ----- Original Message ----- From: "Paul Ramsey"  
>>> <pramsey at refractions.net>
>>> To: "Steve Hall" <steve.hall at MERCATORGEOSYSTEMS.CO.UK>
>>> Cc: <MAPSERVER-USERS at LISTS.UMN.EDU>
>>> Sent: Tuesday, July 12, 2005 3:49 PM
>>> Subject: Re: [UMN_MAPSERVER-USERS] Question re: PostGIS and   
>>> MapScript getExtent() function
>>>
>>>
>>>
>>>
>>>> msPOSTGISLayerGetExtent() is potentially extremely inefficient,  
>>>> depending on how it is implemented, so I would have to see your   
>>>> patch to judge if it does more harm than good. It may be doing   
>>>> good things for you and bad things for others.  One thing it   
>>>> *could* do without harm is read the wms_extent meta-parameter  
>>>> and  use that if it is provided by the user. There are also a  
>>>> few  version specific fast extent functions available in  
>>>> PostGIS, but  they are not guaranteed to work in all cases, at  
>>>> all times,  correctly.
>>>>
>>>> On 12-Jul-05, at 2:58 AM, Steve Hall wrote:
>>>>
>>>>
>>>>
>>>>> Hi All,
>>>>>
>>>>> I'm using MapServer 4.6.0 with Oracle Spatial and PostGIS data  
>>>>> sources via PHP/MapScript.  Part of my application code looks   
>>>>> like this pseudo-code :
>>>>>
>>>>>     $map->setLayerFilter("ThematicData", "REQUEST_ID=121");
>>>>>     $map->zoomToLayer("ThematicData");
>>>>> Here i'm setting a specific FILTER expression for a layer, and   
>>>>> then zooming to the extent of the (now filtered) layer.  I use   
>>>>> the layerObj->getExtent() and then mapObj->setExtent()  
>>>>> functions  to achieve this behind the scenes.  This works great  
>>>>> for Oracle,  the query is fast and makes for nice, tidy code in  
>>>>> my  application  without the need for Oracle specific SQL  
>>>>> statements  to derive the  new extent.
>>>>>
>>>>> However when I try this on PostGIS I just get a massive   
>>>>> (useless) extent returned.  Digging into the code I can see  
>>>>> that  the msPOSTGISLayerGetExtent() function in mappostgis.c  
>>>>> is  effectively doing nothing although a lot of code is  
>>>>> commented out.
>>>>>
>>>>> Working on my local code I have re-enabled this code, fixed the  
>>>>> compilation errors and changed it so that when a FILTER is   
>>>>> applied  to the layer this is used as part of the SQL call to   
>>>>> PG.  It seems  to work great (from PHP/MapScript) and  
>>>>> performance  is fine and is  really useful for me meaning I  
>>>>> don't need to  calculate the extents  for a map myself -  
>>>>> MapServer can supply  the information in a  generic and  
>>>>> abstract fashion.
>>>>>
>>>>> Ideally I would like to get this code fed back into MapServer,   
>>>>> but  of course only if it's acceptable to you all.  So,   
>>>>> should  I  instead post this to mapserver-dev or can I pass on  
>>>>> the new  code to  someone from here?
>>>>>
>>>>> There may be good, historical, reasons why this wasn't ever  
>>>>> implemented though so please let me know!
>>>>>
>>>>> Many thanks,
>>>>>
>>>>> Steve Hall
>>>>>
>>>>>
>>>>> <msPOSTGISLayerGetExtent.c>
>>>>>
>
>