[mapserver-dev] incorrect layer drawing order issue

[geographika]

Hi Thomas,

Issue created at: https://github.com/mapserver/mapserver/issues/5041
Also my temporary workaround for the MSSQL driver at: 
https://github.com/mapserver/mapserver/issues/5008
Regards,

Seth

On 25/11/2014 11:56, thomas bonfort wrote:
> Seth,
> Please open an issue for this. I'd imagine (for postgis) that the
> syntax would become
>
> DATA "the geom from mytable using unique id using srid=xxxx order by my_attr"
> or
> DATA "the geom from (select id,my_attr,geom from mytable where xxxx)
> as subq using unique id using srid=xxxx order by my_attr"
>
> I can handle the postgis case, others will need to help out for mssql
> and oracle.
>
> --
> thomas
>
> On 25 November 2014 at 11:50, geographika <geographika at gmail.com> wrote:
>> 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
>>>
>> _______________________________________________
>> mapserver-dev mailing list
>> mapserver-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-dev