[mapserver-users] WMS Feature Order Problem
umberto.nicoletti at gmail.com
Mon Apr 28 02:53:39 PDT 2014
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
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,
> web: http://geographika.co.uk
> twitter: @geographika
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the mapserver-users