[postgis-devel] ORDER BY semantic on geometries

[Sandro Santilli]

On Tue, Sep 12, 2017 at 04:13:31PM -0700, Regina Obe wrote:
> Author: robe
> Date: 2017-09-12 16:13:31 -0700 (Tue, 12 Sep 2017)
> New Revision: 15703

[...]

> +  - B-Tree index operators < = > changed to provide better spatial locality on sorting 
> +    and have expected behavior on GROUP BY.
> +    If you have btree index for geometry or geography, you need to REINDEX it, or review
> +    if it was created by accident and needs to be replaced with GiST index.
> +    If your code relies on old left-to-right box compare ordering, 
> +    update it to use << >> operators

As also written in the ticket, << and >> operators don't provide
left-to-right box ordering.

Is the new operator working as expected ? A test should be added.
I tried this:

  with inp(g) as ( VALUES
    ( 'LINESTRING(0 0, 10 0)'::geometry ),
    ( 'LINESTRING(2 0, 12 0)'::geometry ),
    ( 'LINESTRING(10 0, 0 0)'::geometry ),
    ( 'LINESTRING(2 1, 12 -1)'::geometry )
  )
  select 'order1', ST_AsText(g)
  FROM inp ORDER BY g;

And obtained this:

  order1|LINESTRING(0 0,10 0)
  order1|LINESTRING(10 0,0 0)
  order1|LINESTRING(2 1,12 -1)
  order1|LINESTRING(2 0,12 0)

--strk;