[mapserver-dev] Improve spatial query performance on DB backends by using native spatial filters

Fri Apr 1 09:22:56 EDT 2011

Hi there,

  Thanks for sharing this. Good first post :)
Somme comments regarding this: I think RCF 64 
(http://mapserver.org/development/rfc/ms-rfc-64.html) laid the ground on 
how the query/spatial queries should be handled by the different drivers.
Looking into MapServer 6.0, the OGC filters are parsed and transformed 
into a "common" expression syntax as described in the RFC. The ideal 
situation is that this expression would be set on the layer's filter 
parameter and "understood" by each driver and converted into native code 
and used during the query. Right now non of the DB dirvers does that 
nativly. There is code at the OGCFilter level to transform some of these 
expressions into native sql calls, but It is limited and does not 
include the spatial filters. We want to ideally get rid of that.
I agree that doing all OGC filer queries nativley even for Postgis would 
be a great improvement.  Other drivers can be done as needed.  If you 
are willing to open a bug and work toward a patch to do this, It would 
be great. Please add me (id: assefa) in cc on the bug and I can 
contribute/input on this if needed.

best regards,

On 01/04/2011 1:58 AM, Nash, Edward wrote:
> (waves in greeting on first posting to list)
>
> This is something which is probably too late for the 6.0 release, but which I think would be nice to include in the next release if possible. As a mapserver-dev newbie then I'm not quite sure how to get it adopted since it involves a slightly (but not very) significant change, e.g. adding a function to the vtable, and if I understand correctly the whole filter stuff is currently a bit in flux and so my approach could totally conflict with future plans. I didn't want to just put a ticket on trac or reopen an old one (e.g. #1292) in case this really needs an RFC or similar.
>
> The problem:
> The current performance of the MapServer WFS is poor where there is a spatial filter set: a test DWithin 50m query on our data in a PostGIS backend took ~2½ minutes to retrieve a handful of records. This is because the spatial filter is not added to the SQL query: if the filter contains a spatial part (other than simple BBOX) then only the general layer filter is applied to retrieve basically all records from the DB and the filtering is done with help from GEOS. If there is no spatial filter then it is in total run in the SQL query and performance is good.
>
> The approach:
> It is not quite straightforward to set the spatial filter for all spatial DBs as they all use different syntax/functions, so a slight abstraction is needed. I chose to add a (char *) function to the vtable to convert each spatial filter node to the appropriate SQL syntax. All spatial DB backends can then implement this function their own way and bootstrap the vtable accordingly. Layers not supporting this just use a default function which gives an appropriate "unsupported" error.
>
> In the mapogcfilter functions where the filter is setup and applied then backends supporting this function can reate the SQL for the whole filter (currently in the switch statement there is a nice "TODO" for spatial nodes (: - with my approach I just add the call to the vtable function) and apply it natively. This also requires that at the branching point where it is tested whether the filter contains a spatial bit (and processing is therefore to be done internally) then the logic needs to be changed to "is non-spatial or backend does the filter natively".
>
> What's done:
> I've implemented and tested the above approach for PostGIS only: I don't have a handy SDE, Oracle or MSSQL2008 backend to develop against, but adding support for these only requires implementing the vtable function and adding them to the list of backends supporting native spatial filters - I would hope that there is a developer out there that would be interested in doing this. Support for these other backends is however completely independent: there is no negative effect for them (and absolutely no changes required in their drivers), just positive ones for the drivers supporting this approach.
>
> Result:
> My DWithin query now runs in a couple of seconds (order of magnitude improvement!).
>
> If this approach sounds good then I'll open up a ticket and hang a patch against HEAD onto it. If it conflicts with something that was already planned then let me know and I'll see whether I can adjust what I've got to work with what's already planned.
>
> Regards,
>
> Edward Nash
>
> --
>
> Edward Nash
> E-Lösungen und Geoinformation
> E-Mail  E.Nash at dvz-mv.de
> Mobil +49 170 454 7434
> Internet: www.dvz-mv.de
> _____________________________________
> DVZ Datenverarbeitungszentrum
> Mecklenburg-Vorpommern GmbH
> Lübecker Str. 283 - 19059 Schwerin
> Sitz der Gesellschaft: Schwerin
> Eintrag im Handelsregister: HRB 187/ Amtsgericht Schwerin
> Geschäftsführer:  Hubert Ludwig -
> Aufsichtsratsvorsitzender: Dr. Jost Mediger
>
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-dev
>

-- 
----------------------------------------------------------------
Assefa Yewondwossen
Software Analyst

Email: yassefa at dmsolutions.ca
http://www.dmsolutions.ca/

Phone: (613) 565-5056 (ext 14)
Fax:   (613) 565-0925
----------------------------------------------------------------