[postgis-devel] ORDER BY semantic on geometries

[Sandro Santilli]

On Wed, Sep 13, 2017 at 05:04:51AM -0700, Paul Ramsey wrote:
> On Wed, Sep 13, 2017 at 12:32 AM, Sandro Santilli <strk at kbt.io> wrote:

> There's not going to be a great eyeball test of order by, it's a "sort of"
> morton curve, due to the fact that negative numbers are in their own domain
> well above the positive numbers. Also note the spatial ordering is on the
> bbox centers, so each of your elements is quite similar, and in fact the
> "near close to near" principal has been carried out in the ordering.

Then we're now (after the change) missing an operator capable
of returning geometries in the order you'll meet their bounding boxes
while walking from bottom-left to top-right...

I'm trying to use ORDER BY geom <#> ST_MakePoint(-aLot, -aLot) but
it seems to fail in weird way when "aLot" is > 1e18 !

Look:

  SELECT array_agg(ST_Xmin(geom))
  FROM ( select geom from city_data.edge_data
  ORDER BY geom <#> ST_MakePoint(-1e15, 0) ) foo;

  {3,4,9,9,9,9,9,9,17,21,21,21,21,21,25,35,35,35,35,35,36,41,47,47}

vs:

  SELECT array_agg(ST_Xmin(geom))
  FROM ( select geom from city_data.edge_data
  ORDER BY geom <#> ST_MakePoint(-1e18, 0) ) foo;

  {3,17,36,35,21,4,9,25,9,21,21,41,9,9,47,35,21,35,35,47,9,21,35,9}

Ideas why ?

--strk;