one pass query and OGC filter query

[assefa]

e-mail discussion of the issue:

I am running into issues with some OGC Filters. For example some complex filters can use 2 or more bbox in the filter (select soem shapes in this region or select some shapes in another region). Basically this should translate into 2 separate queries and the results should be combined at the end and returned to the user. In mapogcfilter.c, there is code to do separate queries and combine the result and set the layer's resultcache (doing AND, OR, several queries ...). At the end of all the layer's result cache has shape id's that can be used to retrieve the shapes.

This logic unfortunately does not really work with one pass query and

the use of LayerResultGetShape. Initially I was using LayerGetShape and mapgml.c (outputting the gml features) was also using LayerGetShape . For these complex filters, I would like to be able to go back to using LayerGetShape. I could for example set some kind of flag at the layer level that would tell mapgml.c (msGMLWriteWFSQuery) to either use msLayerResultsGetShape (which would be the case by default) or msLayerGetShape is that flag is set to true.

It seems to be localized for this need and would like to have your input on this.

[assefa]

e-mail from SteveL:

The problem is that for some drivers (most of the database-base ones) once you do a query you don't get information back that would allow you to use msLayerGetShape() even if you wanted to. Being able to merge queries (XOR) seems to be something at least a few folks were doing routinely, nobody caught that fact although it's noted in the RFC.

A couple of ideas off the top of my head:

1) One I thought about recently was this. In pre-5.6 versions what killed the database drivers where the tons of individual id=xxx SQL statements that msLayerGetShape() created. In 5.6 we reference the result set generated by the query to speed things up. What if instead we did something like this:

1) do a query in msQueryBy...() and tag each result shape with the global index value as we did pre-5.6 2a) now in the presentation step (GML creation, templates, query map) we do a "select * from ..." which sets up all features as result set (like we have in 5.6) 2b) you use msLayerGetShape() to retrieve features by index but not via individual SQL calls but by addressing the results in 2a with the indexes gathered in step 1

While I like this approach what I don't know is how realistic steps 2a and 2b are. What's the penalty for 2a? This is a hybrid approach to 5.4 and 5.6. It's like running msLayerWhichShapes() with an extent that selects all of them and using msLayerGetShape() to randomly access that result set (instead of msLayerNextShape()). This would allow us to open/close layers in query operations which simplifies life too.

2) Consider capturing two index values at query time: a global index suitable for use with msLayerGetShape() and one relevant only to the result set and useful with msLayerResultsGetShape(). Feels like a kludge.

[assefa]

I did not know what 'kludge' meant; wikepedia defines it as a clumsy or inelegant, yet effective :) I am all for kludge.

Seriously I am not sure all that is involved in the different drivers to be able to capture a global index. By looking for example in the oracle driver, if a unique id is defined as part of the data statement, the global index is the value of that unique id; and we can only do GetShape if a unique id exists on the data statement. In this particular work GetShape would already work using the shape->index returned right now by the queries.

[sdlime]

Using the index returned by queries msLayerGetShape() will NOT work for Oracle Spatial and PostGIS (and some OGR). In those cases the query results must persist for msLayerResultsGetShape() to work since the index value returned as part of the queries is specific to the query results (in the db). I curious Paul and Jim's take on the first idea - best of both worlds if performance doesn't suffer too much.

I think a global index IS available for PostGIS, not sure about others.

Steve

[assefa]

from Jim:

1) I can't guarantee that the database will give me the same rows in the same order twice times.

2) If the ResultCache object was expanded, then a cursor relative index and a primary key could both be returned. And whoever is writing the mapscript could choose. For awhile (it may still be there) I was returning shapeindex as the cursor index and tileindex as the primary key (mostly for debugging purposes). This would be easier if the ResultCache was an opaque object that was passed into a layer method rather than passing shapeindex and tileindex around all the time.

3) Personally, I would really like the drivers to be structure the code in a why that is typical in ruby so that the information is only needs to be fetched once.

datasource = layer.data datasource.open

datasource.find( < some query > ).each do |result|

layer->draw_feature(result) if(something) layer->process_template(result) if(something else)

end

Then if people want to implement their own queries or do merges, there could be a Datasource "driver" do to just that (on top of data returned from other Datasources).

I have yet to figure out how this fits into C and how it would fit into mapserver without breaking just about everything. In particular, the templating system and probably the OGC services need information about the entirety of the query up front that would not be available using this method.

[bartvde]

Assefa, I just wanted to report another potential issue with this approach in mapogcfilter.c. If people have defined an ORDER BY in their data statament or in their database view, they will loose this because of the processing done in mapogcfilter.c. At least that's what we have seen.

[sdlime]

Replying to bartvde:

Bart, by approach are you talking about the result set combination or something else? Steve

[bartvde]

Replying to sdlime:

Replying to bartvde:

Bart, by approach are you talking about the result set combination or something else? Steve

Hi Steve, yes the result set combination approach.

[sdlime]

An argument in favor of a new filterObj/msLayerWhichShapeFiltered() approach as suggested by Paul...

Steve

[assefa]

I attached a patch against the 5.6 branch. Thanks to Paul,Steve,Jim.

Initial tests I have done with it are against Postgis,Oracle, shapefile.

The tests were done using BODEM50 dataset provided by Bart. The query I used involved a filter encoding with 2 bbox (with an OR):

​http://127.0.0.1/cgi-bin/mapserv.exe?map=f:/msapps/tmp/bug2799/bodemkaart_assefa.map&service=WFS&version=1.1.0&Request=GetFeature&typename=AAA35&Filter=<Filter><Or><BBOX><PropertyName>geometry</PropertyName><Envelope srsName='EPSG:28992'><lowerCorner>124222.609041 479955.38295137</lowerCorner><upperCorner>132851.41190592 483688.11435559</upperCorner></Envelope></BBOX><BBOX><PropertyName>geometry</PropertyName><Envelope srsName='EPSG:28992'><lowerCorner>247268 602563</lowerCorner><upperCorner>252296 608479</upperCorner></Envelope></BBOX></Or></Filter>

Bart,

I would appreciate any help on testing issues you had with the filter encoding and the oracle driver with this patch. If possible, use the BODEM data/map so I can also test it using the same data. (my data statement of the layer looks like: DATA "ORA_GEOMETRY FROM BODEM50 USING UNIQUE OBJECTID")

[bartvde]

Thanks Assefa, can you confirm no changes are necessary for maporaclespatial.c, since it is not in the patch zip?

[assefa]

Bart,

I have ckecked the maporaclespatial.c and the logs that I am having (using UNIQUE OBJECTID). The individual queries coming from OCGFilter (query by rect) would end up being translated into an sql like this:

SELECT OBJECTID, OGR_FID, OBJECTID, VOOR, LETTER, CYFER, KALK, ACHTER, SCHOP, HEL, GWT, CODE, OMSCHR, EERSTE_BOD, EERSTE_GWT, KLEUR_BOD, KLEUR_GWT, AREA, PERIMETER, ORA_GEOMETRY FROM BODEM50 WHERE SDO_FILTER( ORA_GEOMETRY, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'

The shapes returned in mapserver will have a unique shapeid that represents the unique id value from the DB

The final request merging the individual queries (and used to get the shapes when writing the GML) would end up using these unique shape id's and do a call to msOracleSpatialLayerGetShape and the sql will look like:

SELECT OGR_FID, OBJECTID, VOOR, LETTER, CYFER, KALK, ACHTER, SCHOP, HEL, GWT, CODE, OMSCHR, EERSTE_BOD, EERSTE_GWT, KLEUR_BOD, KLEUR_GWT, AREA, PERIMETER, ORA_GEOMETRY FROM BODEM50 WHERE OBJECTID = 73

From what I can see with the test data, this works.

Let me know.

[assefa]

Bart did few tests and discovered one issue that I can reproduce her. I will chat with Steve and add a patch to fix it. I will update this bug with the patch. If all goes well after that, I will commit the patchs tt 5.6 branch as well as trunk

Paul, I will need your help to get the same patch of mapostgis.c for trunk. You can either commit it or send me the patch and I will do the same tests I did initially and commit. Thanks

[assefa]

patch against 5.6 branch

[assefa]

Bart, I attached a new version of the patch with the fixes. With that fix I was able to execute your test case with the sld and filter encoding and get a image returned.

[bartvde]

Hi Assefa, I still can't get an Intersects filter with a polygon to work, I get an empty image back. This is the request I am using:

​http://145.50.148.45:8081/cgi-bin/mapserv-220210?map=/data/OGC_UMN_services/bodemkaart_assefa.map&LAYERS=AAA35&FORMAT=image%2Fpng&TRANSPARENT=TRUE&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&STYLES=default&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&SRS=EPSG%3A28992&SLD=http%3A%2F%2Ftest.intranet.rijkswaterstaat.nl%2Fapps%2Fgeoservices%2Fms_tmp%2Fsld%2FsldxztY5e&BBOX=128815.47018799,450843.66392138,157684.52981201,469156.33607862&WIDTH=1031&HEIGHT=654

And this is the SLD:

<sld:StyledLayerDescriptor xmlns:sld="http://www.opengis.net/sld" version="1.0.0" xsi:schemaLocation="http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ogc="http://www.opengis.net/ogc" xmlns:gml="http://www.opengis.net/gml"><sld:NamedLayer><sld:Name>AAA35</sld:Name><sld:UserStyle><sld:Name>default</sld:Name><sld:FeatureTypeStyle><sld:Rule><sld:Name>default</sld:Name><ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:Intersects><ogc:PropertyName>geometry</ogc:PropertyName><gml:Polygon xmlns:gml="http://www.opengis.net/gml" srsName="EPSG:28992"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">136851.76515607,461358.04984652 141751.94501466,463402.12487325 146792.13001206,459537.9830419 139427.8597103,456009.85354372 137803.80010003,458697.952209 142003.95426453,459341.97584756 136851.76515607,461358.04984652</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon></ogc:Intersects></ogc:Filter><sld:MaxScaleDenominator>100000</sld:MaxScaleDenominator><sld:PolygonSymbolizer><sld:Fill><sld:CssParameter name="fill">#FF0000</sld:CssParameter></sld:Fill></sld:PolygonSymbolizer></sld:Rule></sld:FeatureTypeStyle></sld:UserStyle></sld:NamedLayer></sld:StyledLayerDescriptor>

[assefa]

Bart,

I entered a bug on this #3346 and fixed it in the branch. All patches are now committed in the 5.6 branch (r9877)

I will also upgrade trunk with the patches.

[assefa]

the revision in 5.6 branch is r9887

[dmorissette]

r9877 was committed in tags/rel5-6-1 by accident. I reverted the tags/rel5-6-1 commit in r9902.

[assefa]

committed also in trunk r9931