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

Pavan Deolasee pavan.deolasee at gmail.com
Mon Jul 2 22:47:05 PDT 2018


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180703/69a30078/attachment.html>


More information about the postgis-users mailing list