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

Thu Jan 10 05:14:53 PST 2019

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 Jan 10, 2019, at 8:06 AM, David M. Kaplan <david.kaplan at ird.fr> wrote:
> 
> 
> 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. <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 <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 <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 <mailto: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.umr-marbec.fr/kaplan-david.html>
> http://www.davidmkaplan.fr/ <http://www.davidmkaplan.fr/>
> **********************************
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

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