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
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
