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

[Nash, Edward]

I've been working on trunk, so it's based on the current filter code. I looked at the RFC as I started this, but to my mind starting from the parsed FilterEncodingNode rather than the common filter string representation seems better, as otherwise the driver is going to have to get involved in parsing, or at least calling some common function to do the parsing. That's why I wasn't sure how compatible my path of least resistance approach is with the plans for filters. No worries that it's compatible with their current state though.

My addition therefore works with the individual spatial FilterEncodingNode and hooks into the function that turns the whole FilterEncodingNode tree into a SQL query string - the non-spatial stuff is of course already there. I can't see a reason why more or less the same mechanism couldn't be used as part of native DB support for common query expressions though.

Since the feedback is positive I'll open a ticket on Monday and attach a patch against trunk/HEAD for what I've done so far and then we can look at how to integrate it.

Regards,

Edward

----- Originalnachricht -----
Von: Lime, Steve D (DNR) <steve.lime at state.mn.us>
An: Nash, Edward; mapserver-dev at lists.osgeo.org <mapserver-dev at lists.osgeo.org>
Gesendet: Fri Apr 01 15:48:53 2011
Betreff: RE: [mapserver-dev] Improve spatial query performance on DB backends by using native spatial filters

Hi Edward: Which version have you been looking at? The approach you describe is at least partially implemented in 6.0. The internal filter/expression syntax has been expanded to cover the OGC filter specification, at least most of it. We've also built capabilities to translate between the two. The internal representation is more SQL-like- a string. That string is processed into a series of tokens that could be processed by MapServer proper (using bison) or by the driver. There is a vtable function that allows the driver to say whether or not they support this common expression/filter format. That's all in place now.

What's not in place is any driver specific use of the abstracted common expression/filter format. That's the big todo. My thought is that a driver could examine the common expression/filter and even conditionally say yes/no to supporting it. That way we could implement high-value use cases first and edge cases later.

I guess I'd recommend looking at RFC 64 and the 6.0 code base. There are some test cases for this in msautotest/query so you can see the new syntax. It does seem to work well and I agree there is certainly room to improve by handing tasks off to drivers that can handle things more efficiently. I'm happy to work with you (and others) on this...

Steve

-----Original Message-----
From: mapserver-dev-bounces at lists.osgeo.org [mailto:mapserver-dev-bounces at lists.osgeo.org] On Behalf Of Nash, Edward
Sent: Friday, April 01, 2011 12:59 AM
To: mapserver-dev at lists.osgeo.org
Subject: [mapserver-dev] Improve spatial query performance on DB backends by using native spatial filters

(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