[mapserver-dev] incorrect layer drawing order issue

Tue Nov 25 02:56:43 PST 2014

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