-----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:[email protected]]
>Gesendet: Dienstag, 16. Dezember 2008 22:54
>An: Mapserver-users ([email protected]);
>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
><d1982c7c00c1de409dde5f9ab94c9e6aac269a4...@mail-back01.regensburg.de>,
>Schönhammer, Herbert <[email protected]> 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
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
