Mapserver WFS should send maxfeatures to the spatial database

[bartvde]

Mapserver WFS should send the maxfeatures from the WFS request to the underlying spatial database. Now it will retrieve all the rows matching, and get rid of the additional rows, which is quite inefficient for certain situations.

[pramsey]

Patch for PostGIS

[dmorissette]

FYI there is also ticket #2424 about more general support for MAXFEATURES in MapServer (and not just with WMS/WFS) that could benefit from this fix.

[dmorissette]

Paul, Steve, do the changes to the query stuff done at the Code Sprint help this in anyway? Would one of you want to take ownership of this ticket?

[pramsey]

In trunk PostGIS should be respecting maxfeatures now. The sprint change should not alter this logic at all.

[pramsey]

In Oracle it should also be pretty easy,

// Oracle
select col from tbl where rownum<=20;

[dmorissette]

Thanks for the info. From a quick browse of the code it seems that to enable this we'd need to update mapwfs.c to set layer->maxfeatures before calling the query.

[pramsey]

Right, and I guess the key is to not over-ride maxfeatures if it's already set in the map file.

[dmorissette]

Or if maxfeatures is already set then we override it only if the value from the WFS request is lower than the value set in the mapfile?

[pramsey]

Yes, that seems a reasonable interpretation of user intent.

[bartvde]

For Oracle this is an interesting article:

 http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

select *
  from 
(select * 
   from t 
  order by unindexed_column)
 where ROWNUM < :N;

[assefa]

I will look into this one after the 5.6 release.

[assefa]

Added a patch against trunk to set the maxfeatures on the layer object if available in the wfs request. Postgis already uses the maxfeatures to limit the search. Added support for the oracle driver.

[bartvde]

Assefa, did you read the stuff in:

 http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

?

If I understand your patch correctly, it won't work correctly as yet, but I could be wrong.

[assefa]

Bart,

From what I can see rownum<=N should allow to select the first N random records. From the tests I have done (oracle11) by setting the layer's maxfeatures in the map file or from wfs request, this is what is happening.

Is it possible to give it a try?

[bartvde]

Assefa, but did you try combining it with another filter? My experience is that you will get far less records than the maxfeatures, since the rownum is applied too soon, even if the database has more records that match the filter.

[assefa]

This is what I used to test the case with a filter:

FILTER " OGR_FID > 10" #I have 28 features in my layer

DATA "ORA_GEOMETRY FROM POPPLACE_MSAUTOTEST USING VERSION 10g"

if I do not set the MAXFEATURES on the layer, I get 18 records. IfvI set the maxfeatures to 10 , query looks something like this and returns 10 features:

SELECT rownum, OGR_FID, AREA, PERIMETER, POPPLACE_, POPPLACE_I, UNIQUE_KEY, NAME, NAME_E, NAME_F, UNIQUE_K_1, UNIQUE_K_2, REG_CODE, NTS50, LAT, LON, SGC_CODE, CAPITAL, POP_RANGE, ORA_GEOMETRY FROM POPPLACE_MSAUTOTEST WHERE OGR_FID > 10 AND ROWNUM<=10 AND SDO_FILTER( ORA_GEOMETRY, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'

Are there any particular tests I should add to see if there are problems?

[assefa]

patch committed in r9593

[bartvde]

Tested for Oracle and it works fine.