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

Thu Jan 10 05:50:26 PST 2019

Glad to hear that you seem to have found the issue. If there are any 
other tests I can do to try to diagnose the problem, let me know.

Cheers,
David

:
> You’ve found a bug, it’s in PostGIS (geodetic code is in postgis 
> native), and the extra detail that you can get it to manifest with 
> MULTIPOLYGON but the same rings in POLYGON don’t manifest is an 
> excellent extra detail to help in fixing it.
> On 10/01/2019 12:26, David M. Kaplan wrote:
>> Comparing the results of _ST_DistanceTree and _ST_DistanceUnCached is
>> a good way to tease out whether there are differences between the
>> implementations without having to fight the caching machinery along
>> the way.
>>
>> P.
>
> Thanks for the responses. I tried with _ST_DistanceTree and 
> _ST_DistanceUnCached
> and as predicted, tree gives 0 and uncached gives the correct answer. 
> What is the appropriate work around to avoid this issue? Who should I 
> report this problem to? The maintainers of libgeos?
>
> Also, how should I interpret that this error only occurs when one of 
> the geometries is a multipolygon?
>
> In response to Raúl, I found that both queries give 0 for postgis 
> 2.5.0 r16836, but only the multiline query gives zero for 
> POSTGIS="2.4.3 r16312"...
>
> Cheers,
> David
>
>> On Thu, Jan 10, 2019 at 7:00 AM Raúl Marín Rodríguez
>> <rmrodriguez at carto.com  <https://lists.osgeo.org/mailman/listinfo/postgis-users>> wrote:
>> >//>/Hi, />//>/In my case I get dist_geography between gid 1 and gid 3 0 in both 
>> queries. />//>/This looks like a possible issue around the geos cache, something 
>> similar />/to https://trac.osgeo.org/postgis/ticket/4269. Can you run them with />/EXPLAIN ANALYZE? />//>/-- />/Raúl Marín Rodríguez />/carto.com />/_______________________________________________ />/postgis-users mailing list />/postgis-users at lists.osgeo.org 
>> <https://lists.osgeo.org/mailman/listinfo/postgis-users> />/https://lists.osgeo.org/mailman/listinfo/postgis-users /
>
>> 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/
> **********************************

-- 
**********************************
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/
**********************************

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190110/805c0a1a/attachment.html>