<div dir="ltr">Hello,<div><br></div><div><div>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</div><div><br></div><div>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?</div><div><br></div><div><div><br></div><div>postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)', 4326), 0);</div><div> _st_expand </div><div>----------------------------------------------------</div><div> 0101000020E6100000F5F75278D08152C0118DEE20765E4440</div><div>(1 row)</div><div><br></div><div>postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)', 4326), 100);</div><div> _st_expand </div><div>----------------------------------------------------</div><div> 0101000020E6100000F5F75278D08152C0118DEE20765E4440</div><div>(1 row)</div><div><br></div><div>postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)', 4326), 10000);</div><div> _st_expand </div><div>----------------------------------------------------</div><div> 0101000020E6100000F5F75278D08152C0118DEE20765E4440</div><div>(1 row)</div></div><div><br></div><div><br></div><div>So irrespective of the quantum by which I extend the value, the result remains the same.</div><div><br></div><div>I started looking at it because of another problem I noticed. See the output of the following query:</div><div><br></div><div><div>postgres=# explain analyze select * from test_geog se where ST_DWithin(se.shape, ST_GeomFromText('POINT(-74.028349 40.737980)', 4326),1611 * 1);</div><div> QUERY PLAN </div><div> </div><div>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div><div>-----------------------------------------------------------------------------------------------------------------------</div><div> 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)</div><div> Filter: ((shape && '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography) AND ('0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography && _st_expand(shape, '1611'::double precision)) AN</div><div>D _st_dwithin(shape, '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography, '1611'::double precision, true))</div><div> Planning time: 0.101 ms</div><div> Execution time: 0.236 ms</div><div>(4 rows)</div><div><br></div><div>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.</div><div><br></div><div><br></div><div>postgres=# explain analyze select * from test_geog se where (shape && '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography);</div><div> QUERY PLAN </div><div>-------------------------------------------------------------------------------------------------------</div><div> 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)</div><div> Filter: (shape && '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography)</div><div> Rows Removed by Filter: 80</div><div> Planning time: 0.042 ms</div><div> Execution time: 0.052 ms</div><div>(5 rows)</div></div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>Thanks,</div><div>Pavan</div><div><br></div>-- <br><div class="gmail-m_-30272790885289949gmail_signature"><div dir="ltr"><div><div dir="ltr"> Pavan Deolasee <a href="http://www.2ndQuadrant.com/" target="_blank">http://www.2ndQuadrant.com/</a><br> PostgreSQL Development, 24x7 Support, Training & Services</div></div></div></div>
</div></div>