[mapserver-dev] incorrect layer drawing order issue

[geographika]

Hi list / Jeff,

I posted a question at 
http://dba.stackexchange.com/questions/82930/database-implementations-of-order-by-in-a-subquery 
to check database implementations of ORDER BY in a subquery. From the 
answer there, and various pages on SQL specs it seems as though the 
ORDER BY mechanism used in the DATA clauses for a MapServer layer are in 
no way guaranteed to return the same order of records each time. This 
could produce all sorts of hard to spot errors if a newer version of 
Postgres changes their implementation.
Can anyone confirm that Postgres, Oracle and other drivers simply wrap 
the DATA statement with an outer query with the bounding box for map 
tile requests?
It should probably be opened in GitHub as a serious bug/issue, unless 
I'm overlooking something.

Regards,

Seth

On 10/11/2014 18:43, Jeff McKenna wrote:
> The Basemaps sub-project also heavily relies on "ORDER BY" for the 
> rendering order of features (in my case within Postgres, and using 
> large OpenStreetMap datasets), so I am curiously following this 
> discussion too :)
>
> -jeff
>
>
>
> On 2014-11-10 12:44 PM, Lime, Steve D (MNIT) wrote:
>> I would love to see some sort of sorting capability but at least for
>> shapefiles I've always relied on a presort (using sortshp utility) for
>> performance reasons. I think if something more configurable was needed
>> then one would simply need to OGR access as a wrapper for shapefiles.
>> Maybe the various driver owners could weigh in regarding the SORTBY 
>> option.
>>
>> Steve
>>
>> ------------------------------------------------------------------------
>> *From:* mapserver-dev-bounces at lists.osgeo.org
>> [mapserver-dev-bounces at lists.osgeo.org] on behalf of Tamas Szekeres
>> [szekerest at gmail.com]
>> *Sent:* Sunday, November 09, 2014 3:21 PM
>> *To:* geographika at gmail.com
>> *Cc:* mapserver-dev at lists.osgeo.org
>> *Subject:* Re: [mapserver-dev] incorrect layer drawing order issue
>>
>> Yes that would be the simplest approach in short term.
>> But I think it would also be reasonable to get it configurable for all
>> supported drivers, through mapfile and mapscript.
>>
>> Best regards,
>>
>> Tamas
>>
>>
>>
>> 2014-11-09 20:49 GMT+01:00 geographika <geographika at gmail.com
>> <mailto:geographika at gmail.com>>:
>>
>>     Would a SORTBY clause be added as a keyword in the DATA statement?
>>     E.g. DATA "ogr_geometry from rivers USING UNIQUE ogr_fid USING
>>     SRID=4326 USING SORTBY my_sort_fieldname"
>>
>>
>>     On 07/11/2014 12:49, Tamas Szekeres wrote:
>>>     We could also extend the DATA section with a SORTBY clause for the
>>>     mssql driver.
>>>     Adding this feature to MSSQL (as per RFC 105) would anyway be
>>>     fairly easy.
>>>
>>>     Tamas
>>>
>>>
>>>
>>>
>>>     2014-11-07 11:24 GMT+01:00 geographika <geographika at gmail.com
>>>     <mailto:geographika at gmail.com>>:
>>>
>>>         Hi list,
>>>
>>>         I have come across an issue that is critical to the project I
>>>         am working on and wondered if it applies to all database
>>>         drivers or just the SQL Server 2008 one.
>>>
>>>         I have logged the issue on GitHub at:
>>>         https://github.com/mapserver/mapserver/issues/5008
>>>
>>>         I will try some tests over the weekend to see if the Postgres
>>>         driver works the same way, but there seems to be no way of
>>>         guaranteeing feature display order - even if all documentation
>>>         suggests it can be achieved in a database layer using "ORDER 
>>> BY"
>>>         Whilst for a low number of records the order seems ordered,
>>>         with 150+ features I get different images returned each time
>>>         by my WMS service. No use of clustered indexes, partition
>>>         statements etc. can get around this as SQL result sets can
>>>         only be guaranteed with an ORDER BY in the outermost statement.
>>>
>>>         I note that in
>>> http://mapserver.org/fr/development/rfc/ms-rfc-105.html there
>>>         is a new function msLayerBuildSQLOrderBy() that allows sorting
>>>         to be done outside of the layer's DATA statement for WFS
>>>         requests with a SORTBY parameter. Maybe this could also be
>>>         used by a new LAYER "SORT" config keyword?
>>>
>>>         I see the above is only implemented for a few drivers. The
>>>         client would be willing to fund adding this to the SQL Server
>>>         driver if there are any core devs interested in doing this.
>>>
>>>         Regards,
>>>
>>>         Seth
>>>
>>>         --
>>>         web:http://geographika.co.uk
>>>         twitter: @geographika
>>>
>>>
>>>
>>
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-dev
>