Question re: PostGIS and MapScript getExtent() function
Steve Hall
steve.hall at MERCATORGEOSYSTEMS.CO.UK
Tue Jul 12 14:52:04 EDT 2005
In my experience with Oracle it too can be pretty slow getting an MBR on a
table that big ;-)
I don't know much about other data sources, but Oracle's spatial metadata
registry (USER_SDO_GEOM_METADATA) does maintain layer extent data in the
DIMINFO column so could be retrieved without needing to perform a spatial
aggregate, and hence would be a suitable candidate for the *Fast function
you descibe here.
Like you say PostGIS also has a couple of choices in this area.
I guess by definition the *Fast functions would just ignore, (or fail?) if a
filter was specified...? Or perhaps if a filter is specified, the *Fast
functions could call the non-Fast functions to get the answer in the
(blind?) hope that the filter makes them sufficiently performant.
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>; <MAPSERVER-DEV at LISTS.UMN.EDU>
Sent: Tuesday, July 12, 2005 7:37 PM
Subject: Re: [UMN_MAPSERVER-USERS] Question re: PostGIS and MapScript
getExtent() function
> 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>
>>>>>>
>>
>>
More information about the mapserver-users
mailing list