[postgis-users] ST_Distance provides unexpected results when using Geography type

[Wayne Rowcliffe]

Hi,

I've attached a file containing sample queries that reproduce what I am
seeing.

Basically, we have an application that shows data on the globe. We are
filtering the results to those within the current window viewport. What I
am seeing is that depending on where the map is, I'll either get results or
I won't, even when there should be data visible.

While investigating, I switched to calling ST_Distance and noticed that
when using geometry the results were always accurate. However, when using
geography (like we do in our application), we will sometimes get the
correct result, and other times it will say a point within the bounding
geometry is actually hundreds of thousands of meters away.

This is my version string using the query on the postgis webpage:
PostgreSQL 10.9 (Ubuntu 10.9-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="100" GEOS="3.5.0-CAPI-1.9.0
r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released
2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY
RASTER

The closest bug I could find was this:
https://trac.osgeo.org/postgis/ticket/4480
It seems potentially different though since the bounding area of my query
is less than 180 degrees.

*Wayne Rowcliffe*
Software Engineer, Priority5 Holdings
E-mail: wayne at priority5.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200305/6776dff6/attachment.html>
-------------- next part --------------

aims=# select
aims-#    ST_Distance(
aims(#       ST_geographyFromText(
aims(#          'SRID=4326;POLYGON ((-167.51953125 8.841651120809145, 1.23046875 8.841651120809145, 1.23046875 63.35212928507874, -167.51953125 63.35212928507874, -167.51953125 8.841651120809145))'
aims(#       ),
aims(#       ST_geographyFromText('SRID=4326;POINT(-93.6401158882678 42.0192299264859)')
aims(#    );
   st_distance
-----------------
 1683966.5677263
(1 row)

aims=#
aims=# select
aims-#    ST_Distance(
aims(#       ST_geographyFromText(
aims(#          'SRID=4326;POLYGON ((-203.466796875 4.12728532324537, -34.71679687500001 4.12728532324537, -34.71679687500001 61.14323525084058, -203.466796875 61.14323525084058, -203.466796875 4.12728532324537))'
aims(#       ),
aims(#       ST_geographyFromText('SRID=4326;POINT(-93.6401158882678 42.0192299264859)')
aims(#    );
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
 st_distance
-------------
           0
(1 row)

aims=#
aims=# select
aims-#    ST_Distance(
aims(#       ST_geometryFromText(
aims(#          'SRID=4326;POLYGON ((-167.51953125 8.841651120809145, 1.23046875 8.841651120809145, 1.23046875 63.35212928507874, -167.51953125 63.35212928507874, -167.51953125 8.841651120809145))'
aims(#       ),
aims(#       ST_geometryFromText('SRID=4326;POINT(-93.6401158882678 42.0192299264859)')
aims(#    );
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
 st_distance
-------------
           0
(1 row)

aims=#
aims=# select
aims-#    ST_Distance(
aims(#       ST_geometryFromText(
aims(#          'SRID=4326;POLYGON ((-203.466796875 4.12728532324537, -34.71679687500001 4.12728532324537, -34.71679687500001 61.14323525084058, -203.466796875 61.14323525084058, -203.466796875 4.12728532324537))'
aims(#       ),
aims(#       ST_geometryFromText('SRID=4326;POINT(-93.6401158882678 42.0192299264859)')
aims(#    );
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
 st_distance
-------------
           0
(1 row)