[postgis-users] Oddity in _ST_Expand(geography) ?

Tue Jul 3 11:11:36 PDT 2018

Ug. So the issue with XL is an interesting side-effect of the way
expand is implemented in geography...

The first part to understand is that the bounding box of a geography
is expressed in 3-space. So imagine the world, it's a big sphere. Now,
imagine a line on that world. In 3-space, it's a curved arc. Now wrap
a bounding volume around that arc. That is the extent, the bounding
box, of that arc, as understood by geography. (Now you also know how
we implement a spatial index on geography, it's an r-tree in 3-space)

So, what does _ST_Expand(geography) do? It expands that 3-space
bounding volume, and that is stored in the memory representation of
the geography (lwgeom->bbox). It'll also get stored on the disk
(lwgeom to gserialized). Unfortunately it will *now* be stored in the
canonical text format (hex-encoded EWKB) because that representation
has no space for the bbox. This was never really a problem in geometry
because the bounds were implicit in the object itself. For geography
because we change the box but do not change the underlying coordinates
of the object, that homeomorphism is broken.

When I look at what we do for geometry:

select st_astext(st_expand('LINESTRING(0 0, 1 1)'::geometry, 1));

POLYGON((-1 -1,-1 2,2 2,2 -1,-1 -1))

Huh, we morph a linestring into a polygon, odd, right? But it's a
polygon that represents the bounds.

I start to think that perhaps the existing behavior is broken, and
geography expand should rewrite the object so that it becomes
something that has the 3-space bounds we want. Maybe an appropriately
chosen multipoint, for example. It will be very confusing to people,
though, since they tend to think rectangularly about bounds, even when
working with spherical coordinates, which are very decidedly not
rectangular.

Interested in thoughts,

P

On Mon, Jul 2, 2018 at 10:47 PM, Pavan Deolasee
<pavan.deolasee at gmail.com> wrote:
> Hello,
>
> I came across what looks like a little puzzling behaviour of using
> _ST_Expand on a geography data type. This is with PostGIS version 2.2.7,
> running on PostgreSQL 9.5
>
> The following queries return the same output. Does that mean _ST_Expand() is
> not doing its job correctly or is the out function is somehow broken or is
> this an expected behaviour of _ST_Expand on a geography type?
>
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 0);
>                      _st_expand
> ----------------------------------------------------
>  0101000020E6100000F5F75278D08152C0118DEE20765E4440
> (1 row)
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 100);
>                      _st_expand
> ----------------------------------------------------
>  0101000020E6100000F5F75278D08152C0118DEE20765E4440
> (1 row)
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 10000);
>                      _st_expand
> ----------------------------------------------------
>  0101000020E6100000F5F75278D08152C0118DEE20765E4440
> (1 row)
>
>
> So irrespective of the quantum by which I extend the value, the result
> remains the same.
>
> I started looking at it because of another problem I noticed. See the output
> of the following query:
>
> postgres=# explain analyze select * from test_geog se where
> ST_DWithin(se.shape, ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326),1611 * 1);
>
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> -----------------------------------------------------------------------------------------------------------------------
>  Seq Scan on test_geog se  (cost=0.00..32.20 rows=1 width=60) (actual
> time=0.107..0.221 rows=80 loops=1)
>    Filter: ((shape &&
> '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography) AND
> ('0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography &&
> _st_expand(shape, '1611'::double precision)) AN
> D _st_dwithin(shape,
> '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography,
> '1611'::double precision, true))
>  Planning time: 0.101 ms
>  Execution time: 0.236 ms
> (4 rows)
>
> The query returns 80 rows. But if I simply replace the WHERE  clause with
> the first filter condition shown in the EXPLAIN output above, no rows are
> returned.
>
>
> postgres=# explain analyze select * from test_geog se where (shape &&
> '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography);
>                                               QUERY PLAN
> -------------------------------------------------------------------------------------------------------
>  Seq Scan on test_geog se  (cost=0.00..2.00 rows=1 width=60) (actual
> time=0.040..0.040 rows=0 loops=1)
>    Filter: (shape &&
> '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography)
>    Rows Removed by Filter: 80
>  Planning time: 0.042 ms
>  Execution time: 0.052 ms
> (5 rows)
>
> So quite clearly, the filter condition returns FALSE for all rows in the
> table, even though when the same filter is used internally by the system, it
> returns TRUE. I traced down that to the _ST_Expand() oddity I mentioned
> before.
>
> I must admit I don't know much about PostGIS. I am the primary
> developer/maintainer of the Postgres-XL project and I started looking into
> this because of a XL bug report. XL requires serialisation/deserialisation
> of plans and we do that by using in/out functions provided by PostgreSQL and
> the extensions. But since the string representation of the filter is not
> quite correct, the remote node fails to later apply the filter correctly.
> That's the context for this investigation.
>
> Thanks,
> Pavan
>
> --
>  Pavan Deolasee                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users