[postgis-users] very strange bug generating incorrect ST_Distance calculations

[David M. Kaplan]

Hi,

I have found what seems to be an extremely bizarre bug. I am not sure if 
it is a postgis issue or postgresql issue, but it leads 
ST_Distance(geography(Polygon,4326),geography(MultiPolygon,4326)) to 
return 0 (zero) when the true answer is not zero under certain peculiar 
situations.

The table data for a set of polygons that generate the error can be 
downloaded here:

http://www.davidmkaplan.fr/bad_geography_dist_calcs.csv

Once you have that data, the code to generate the error is:

CREATE TEMP TABLE tt (gid int PRIMARY KEY, geom geometry);

\copy tt FROM 'bad_geography_dist_calcs.csv' WITH (FORMAT 'csv',HEADER TRUE)

-- Incorrect dist_geography for t1.gid=1 and t2.gid=3
SELECT t1.gid AS gid1, t2.gid AS gid2,
        ST_Distance(t1.geom,t2.geom) AS dist_lonlat,
        ST_Distance(ST_Transform(t1.geom,26918),
                    ST_Transform(t2.geom,26918)) AS dist_utm,
        ST_Distance(t1.geom::geography,t2.geom::geography) AS 
dist_geography,
ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography) 
AS dist_geography_dump
FROM tt t1 JOIN tt t2 ON t1.gid<t2.gid
WHERE t1.gid=1
;

-- No error just by specifying t2.gid=3 in the WHERE clause
SELECT t1.gid AS gid1, t2.gid AS gid2,
        ST_Distance(t1.geom,t2.geom) AS dist_lonlat,
        ST_Distance(ST_Transform(t1.geom,26918),
                    ST_Transform(t2.geom,26918)) AS dist_utm,
        ST_Distance(t1.geom::geography,t2.geom::geography) AS 
dist_geography,
ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography) 
AS dist_geography_dump
FROM tt t1 JOIN tt t2 ON t1.gid<t2.gid
WHERE t1.gid=1 AND t2.gid=3
;

Hopefully, if I am not crazy, you will have a 0 value of dist_geography 
in the first query for t2.gid=3, but not in the second.

Does anyone have any idea what could be causing this? To be honest, I am 
not really certain that I know where to start do diagnose the problem...

The specifics of my installation are:

POSTGIS="2.4.3 r16312" PGSQL="100" GEOS="3.7.0-CAPI-1.11.0 673b9939" 
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.3.2, released 2018/09/21" 
LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER

psql 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))

Ubuntu 18.04.1 LTS

I have also tested this with postgis 2.5.0 r16836 and there the 
incorrect 0 is in both queries!?!?!?

Thanks,
David


-- 
**********************************
David M. Kaplan
Charge de Recherche 1

Institut de Recherche pour le Developpement (IRD)
UMR MARBEC (IRD/Ifremer/CNRS/UMII)
av. Jean Monnet
CS 30171
34203 Sete cedex
France

Email: david.kaplan at ird.fr
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95

http://www.umr-marbec.fr/kaplan-david.html
http://www.davidmkaplan.fr/
**********************************