[mapserver-users] WMS Feature Order Problem

[Umberto Nicoletti]

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

HTH,
Umberto



On Mon, Apr 28, 2014 at 10:50 AM, geographika <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
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20140428/7ecd2bab/attachment.html>