[mapserver-users] WMS Feature Order Problem

[geographika]

Hi list,

I recently came across a problem with the order features are rendered by 
Mapserver. I am displaying road surveys done at various times, and have 
a WMS layer that displays the latest survey results. Rather than trying 
to do complicated segmentation, I simply display features ordered by 
survey date, with the most recent records on top.

Initially my layer DATA clause was similar to the following:

   SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from mydata) as 
tbl USING UNIQUE ID USING SRID=29902 ORDER BY [TimeStamp] DESC

I hadn't realised that anything outside after the USING... statement was 
ignored, so the records were displayed unordered.

I then tried using:

   SELECT ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from mydata 
ORDER BY [TimeStamp] DESC) as tbl USING UNIQUE ID USING SRID=29902

However when the data for the WMS is prepared this SQL becomes a 
subquery, and a WHERE clause is added to get the data for the requested 
extent.
SQL Server doesn't allow ORDER BY in a subquery, so an error is thrown:

     "The ORDER BY clause is invalid in views, inline functions, derived 
tables, subqueries, and common table expressions, unless TOP or FOR XML 
is also specified."

My current workaround is to change the SQL to:

   SELECT TOP 1000000 ID, GEOM, Rating, YEAR([TimeStamp]) AS [YEAR] from 
mydata ORDER BY [TimeStamp] DESC) as tbl USING UNIQUE ID USING SRID=29902

However this causes slow queries as all data needs to be fetched in 
order before filtering.

So my question is is there a way to order records using another LAYER 
property (similar to the FILTER property), so ordering is done only on 
the records returned for rendering?

Thanks for any advice,

Seth

--
web:http://geographika.co.uk
twitter: @geographika


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20140428/6c38931f/attachment.htm>