[postgis-devel] ORDER BY semantic on geometries

[Paul Ramsey]

On Wed, Sep 13, 2017 at 8:32 AM, Sandro Santilli <strk at kbt.io> wrote:

> 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...
>

The old operation wasn't indexed, so I'm surprised that "ORDER BY
ST_XMin(geom)" isn't doing the right thing, no slower than the old one.
Using a > didn't provide any index support unless an index was actually
*built* and nobody in their right mind builds a btree.


> 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}
>

That is worthy of examination. Do you have any SQL  not tied to the
topology system?

P




>
> 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;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20170913/967fef70/attachment.html>