WG: [mapserver-users] Again: WFS: How to sort the WFS output in MS5.x.x ?

Wed Jan 21 09:46:23 EST 2009

-----Ursprüngliche Nachricht-----
Von: Schönhammer, Herbert
Gesendet: Mittwoch, 21. Januar 2009 13:14
An: 'Steve Lime'
Betreff: AW: [mapserver-users] Again: WFS: How to sort the WFS output in MS5.x.x ?

Hi Steve,

sorry for the delay.

I want a wfs - result in a certain order. So I made a wfs - mapfile. The mapfile uses postgres/postgis.

The DATA-statement in my mapfile is:
====================================
DATA 'the_geom from (SELECT the_geom, fid, str_name, haus_nr, haus_nr_zusatz, str_such_string, ... FROM stgk_hausnummern ORDER BY str_name,haus_nr,haus_nr_zusatz) as foo using unique fid using SRID=31468'

My application generates a wfs-request of this form:
====================================================
....map=/data-umn/wfs/rgb_wfs_adressen.map&REQUEST=getFeature&Typename=adressen&Version=1.0.0&service=WFS&filter=<ogc%3AFilter+xmlns%3Aogc%3D"http%3A%2F%2Fogc.org"+xmlns%3Agml%3D"http%3A%2F%2Fwww.opengis.net%2Fgml"><ogc%3APropertyIsLike+wildCard%3D'*'+singleChar%3D'.'+escape%3D'!'><ogc%3APropertyName>str_such_string<%2Fogc%3APropertyName><ogc%3ALiteral>*STEINWEG*<%2Fogc%3ALiteral><%2Fogc%3APropertyIsLike><%2Fogc%3AFilter>

Generating errors in the request above, I was able to have a look at the sql-statments, which are done by mapserver in postgres:

Mapserver 4.10.x (in combination with postges version 8.2.3)
============================================================

STATEMENT:  DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),fid::text from (SELECT the_geom, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY str_name,haus_nr,haus_nr_zusatz) as foo WHERE (str_such_strings like '%STEINWEG%' escape '!') and (the_geom && setSRID( 'BOX3D(4501274 5424202,4515870 5438587)'::BOX3D,31468) )

I get a xml-document containing all addresses in order by str_name,haus_nr,haus_nr_zusatz !!

Changing the mapfile sql-statement to DATA 'the_geom from (SELECT the_geom, fid, str_name, haus_nr, haus_nr_zusatz, str_such_string, ... FROM stgk_hausnummern ORDER BY haus_nr,haus_nr_zusatz,str_name) as foo using unique fid using SRID=31468'

causes a sql-stament of
STATEMENT:  DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),fid::text from (SELECT the_geom, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY haus_nr,haus_nr_zusatz,str_name) as foo WHERE (str_such_strings like '%STEINWEG%' escape '!') and (the_geom && setSRID( 'BOX3D(4501274 5424202,4515870 5438587)'::BOX3D,31468) )

Now I get a xml-document containing all addresses in order by haus_nr,haus_nr_zusatz,str_name !!

This is, what I expect from mapserver 5.x too.

But I don't get this.

Mapserver 5.x.x (in combination with postges version 8.3.1)
===========================================================

The DATA - statement in the mapfile:
    DATA 'the_geom from (SELECT the_geom, oid, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY str_name, haus_nr,haus_nr_zusatz) as foo using unique oid using SRID=31468'

The sql-statement now looks like this:

STATEMENT:  DECLARE mycursor BINARY CURSOR FOR SELECT "str_such_strings"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text from (SELECT the_geom, oid, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY str_name,haus_nr,haus_nr_zusatz) as foo WHERE the_geom && setSRID('BOX3D(-25000000 -25000000,25000000 25000000)'::BOX3D, 31468 )

I get a xml-document, but the order of the data IS NOT "ORDER BY str_name,haus_nr,haus_nr_zusatz". The records are ordered by "oid" !!!!

Changing the sql-statement in the mapfile for example:
    DATA 'the_geom from (SELECT the_geom, oid, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY  haus_nr,haus_nr_zusatz,str_name) as foo using unique oid using SRID=31468'

changes also the sql-statement which is generated by mapserver
STATEMENT:  DECLARE mycursor BINARY CURSOR FOR SELECT "str_such_strings"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text from (SELECT the_geom, oid, fid, str_name, str_schluessel, str_such_string, h_nummer, orientation, haus_nr, haus_nr_zusatz FROM stgk_hausnummern ORDER BY haus_nr,haus_nr_zusatz, str_name) as foo WHERE the_geom && setSRID('BOX3D(-25000000 -25000000,25000000 25000000)'::BOX3D, 31468 )

But (unfortenately) this has no effect, the result is also ordered by "oid".

Changing the sort-order
=======================
Changing the order is only possible by using another " using unique " - clause: for exampe fid is another unique identifier.
So "using unique fid" changes the order, but the order of the result is now "fid".

Question
========
How may I determine the sort order of the result using mapserver 5.x.x ?
This must be possible for a combination of several attributes, even if this combination is not unique !

Regards
Herbert

>-----Ursprüngliche Nachricht-----
>Von: Steve Lime [mailto:Steve.Lime at dnr.state.mn.us]
>Gesendet: Dienstag, 16. Dezember 2008 22:54
>An: Mapserver-users (mapserver-users at lists.osgeo.org);
>Schönhammer, Herbert
>Betreff: Re: [mapserver-users] Again: WFS: How to sort the WFS
>output in MS5.x.x ?
>
>
>The presentation side of the WFS code doesn't do any sorting so that's
>not the culprit. Looks like you're
>using PostGIS. Is there any way you can capture the SQL actually being
>generated by MapServer and
>executed by PostGIS? That would help us track this down.
>
>Steve
>
>>>> On 12/16/2008 at 4:13 AM, in message
><D1982C7C00C1DE409DDE5F9AB94C9E6AAC269A4107 at mail-back01.Regensburg.de>,
>Schönhammer, Herbert <Schoenhammer.Herbert at Regensburg.de> wrote:
>> Hi list,
>>
>> I have posted this item some days ago, but I didn't get some help. So
>I want
>> to repeat my question:
>>
>> Until now I've been using mapserver 4.10.2 for generating wfs (for
>use with
>> wfs-gazetteers). I determined the order of the wfs-output with the
>ORDER BY
>> clause of the DATA-statement.
>>
>> Example:
>> DATA 'the_geom from (SELECT the_geom, fid, str_name, haus_nr,
>> haus_nr_zusatz, str_such_string, ... FROM stgk_hausnummern ORDER BY
>> str_name,haus_nr,haus_nr_zusatz) as foo using unique fid using
>SRID=31468'
>>
>> Changing the ORDER BY - clause caused changing the sort order of the
>output.
>> ==============================================================
>>
>> Using mapserver 5.2.0 and mapserver 5.2.1 the ORDER BY - clause has
>obviously
>> no effect. The wfs - output is always sorted by sort order given from
>
>> attribute fid. (could be another attribute like oid, but it is alwas
>used the
>> attribute after USING UNIQUE ..)
>>
>> Is it possible to change the sort order in mapserver 5.x.x too?
>> Has anybody a tipp for me, how this could be done ?
>>
>> Thanks
>> Schoenhammer Herbert
>>
>> P.S.
>> The generated urls are looking like:
>>
>http://myserver/cgi-bin/getows?map=/data-umn/wfs/rgb_wfs_adress
en.map&REQUEST=g

>
etFeature&Typename=adressen&Version=1.0.0&service=WFS&filter=%3Cogc:Filter+xmlns:
>
ogc=%27http://ogc.org%27+xmlns:gml=%27http://www.opengis.net/gml%27%3E%3Cogc:
>
PropertyIsLike+wildCard=%27*%27+singleChar=%27.%27+escape=%27!%27%3E%3Cogc:Pr
>
opertyName%3Estr_such_string%3C/ogc:PropertyName%3E%3Cogc:Literal%3E*MARTIN*%
> 3C/ogc:Literal%3E%3C/ogc:PropertyIsLike%3E%3C/ogc:Filter%3E