[mapserver-users] WMS Feature Order Problem

[geographika]

Thanks Umberto,

I have an index set up on the TimeStamp field, but looking at the query 
plans it won't affect performance as SQL Server still has to get all the 
records in the table to order them, before applying WHERE clauses in the 
outer query.

What would be ideal is a LAYER property such as ORDER which would allow 
a field to be set to order records for rendering (similar to FILTER 
which is added to the outer query).
I'll have to look at how Postgres handles subqueries to see if it is 
also affected by the same issues.

Note SQL Server simply (and silently) ignores the ORDER BY clause when 
using SELECT TOP 100 PERCENT.
This introduced hard to find logical errors in my application which were 
only spotted by someone who knew the data well, so I'd suggest anyone 
who relies on features being rendered in a specific order to 
double-check their apps!


On 28/04/2014 11:53, Umberto Nicoletti wrote:
> If you don't have an index yet on TimeStamp, you could try creating 
> one with an ORDER clause and see if this improves performance (with a 
> properly created index you can avoid running separate order operation 
> for every query):
>
> http://technet.microsoft.com/en-us/library/ms181154(v=sql.105).aspx 
> <http://technet.microsoft.com/en-us/library/ms181154%28v=sql.105%29.aspx>
>
> HTH,
> Umberto
>
>
>
> On Mon, Apr 28, 2014 at 10:50 AM, geographika <geographika at gmail.com 
> <mailto:geographika at gmail.com>> wrote:
>
>     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
>
>
>
>     _______________________________________________
>     mapserver-users mailing list
>     mapserver-users at lists.osgeo.org
>     <mailto:mapserver-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20140429/e916ea6d/attachment.html>