<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">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. <br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jan 10, 2019, at 8:06 AM, David M. Kaplan <<a href="mailto:david.kaplan@ird.fr" class="">david.kaplan@ird.fr</a>> wrote:</div><br class="Apple-interchange-newline"><div class="">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" class="">
<div text="#000000" bgcolor="#FFFFFF" class="">
<br class="">
<div class="moz-cite-prefix">On 10/01/2019 12:26, David M. Kaplan
wrote:<br class="">
</div>
<blockquote type="cite" cite="mid:af61a3b7-db42-7012-1f03-9198f2ec4108@ird.fr" class="">
<pre class="">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.</pre>
</blockquote>
<br class="">
Thanks for the responses. I tried with _ST_DistanceTree and
_ST_DistanceUnCached<br class="">
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?<br class="">
<br class="">
Also, how should I interpret that this error only occurs when one of
the geometries is a multipolygon?<br class="">
<br class="">
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"...<br class="">
<br class="">
Cheers,<br class="">
David<br class="">
<br class="">
<blockquote type="cite" cite="mid:af61a3b7-db42-7012-1f03-9198f2ec4108@ird.fr" class="">
<pre class="">
On Thu, Jan 10, 2019 at 7:00 AM Raúl Marín Rodríguez
<<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" class="">rmrodriguez at carto.com</a>> wrote:
><i class="">
</i>><i class=""> Hi,
</i>><i class="">
</i>><i class=""> In my case I get dist_geography between gid 1 and gid 3 0 in both queries.
</i>><i class="">
</i>><i class=""> This looks like a possible issue around the geos cache, something similar
</i>><i class=""> to <a href="https://trac.osgeo.org/postgis/ticket/4269." class="">https://trac.osgeo.org/postgis/ticket/4269.</a> Can you run them with
</i>><i class=""> EXPLAIN ANALYZE?
</i>><i class="">
</i>><i class=""> --
</i>><i class=""> Raúl Marín Rodríguez
</i>><i class=""> <a href="http://carto.com" class="">carto.com</a>
</i>><i class=""> _______________________________________________
</i>><i class=""> postgis-users mailing list
</i>><i class=""> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" class="">postgis-users at lists.osgeo.org</a>
</i>><i class=""> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</i></pre>
</blockquote>
<br class="">
<blockquote type="cite" cite="mid:af61a3b7-db42-7012-1f03-9198f2ec4108@ird.fr" class="">Hi,
<br class="">
<br class="">
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.
<br class="">
<br class="">
The table data for a set of polygons that generate the error can
be downloaded here:
<br class="">
<br class="">
<a class="moz-txt-link-freetext" href="http://www.davidmkaplan.fr/bad_geography_dist_calcs.csv">http://www.davidmkaplan.fr/bad_geography_dist_calcs.csv</a>
<br class="">
<br class="">
Once you have that data, the code to generate the error is:
<br class="">
<br class="">
CREATE TEMP TABLE tt (gid int PRIMARY KEY, geom geometry);
<br class="">
<br class="">
\copy tt FROM 'bad_geography_dist_calcs.csv' WITH (FORMAT
'csv',HEADER TRUE)
<br class="">
<br class="">
-- Incorrect dist_geography for t1.gid=1 and t2.gid=3
<br class="">
SELECT t1.gid AS gid1, t2.gid AS gid2,
<br class="">
ST_Distance(t1.geom,t2.geom) AS dist_lonlat,
<br class="">
ST_Distance(ST_Transform(t1.geom,26918),
<br class="">
ST_Transform(t2.geom,26918)) AS dist_utm,
<br class="">
ST_Distance(t1.geom::geography,t2.geom::geography) AS
dist_geography,
<br class="">
ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography)
AS dist_geography_dump
<br class="">
FROM tt t1 JOIN tt t2 ON t1.gid<t2.gid
<br class="">
WHERE t1.gid=1
<br class="">
;
<br class="">
<br class="">
-- No error just by specifying t2.gid=3 in the WHERE clause
<br class="">
SELECT t1.gid AS gid1, t2.gid AS gid2,
<br class="">
ST_Distance(t1.geom,t2.geom) AS dist_lonlat,
<br class="">
ST_Distance(ST_Transform(t1.geom,26918),
<br class="">
ST_Transform(t2.geom,26918)) AS dist_utm,
<br class="">
ST_Distance(t1.geom::geography,t2.geom::geography) AS
dist_geography,
<br class="">
ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography)
AS dist_geography_dump
<br class="">
FROM tt t1 JOIN tt t2 ON t1.gid<t2.gid
<br class="">
WHERE t1.gid=1 AND t2.gid=3
<br class="">
;
<br class="">
<br class="">
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.
<br class="">
<br class="">
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...
<br class="">
<br class="">
The specifics of my installation are:
<br class="">
<br class="">
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
<br class="">
<br class="">
psql 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
<br class="">
<br class="">
Ubuntu 18.04.1 LTS
<br class="">
<br class="">
I have also tested this with postgis 2.5.0 r16836 and there the
incorrect 0 is in both queries!?!?!?
<br class="">
<br class="">
Thanks,
<br class="">
David
<br class="">
<br class="">
<br class="">
</blockquote>
<br class="">
<pre class="moz-signature" cols="72">--
**********************************
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: <a class="moz-txt-link-abbreviated" href="mailto:david.kaplan@ird.fr">david.kaplan@ird.fr</a>
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95
<a class="moz-txt-link-freetext" href="http://www.umr-marbec.fr/kaplan-david.html">http://www.umr-marbec.fr/kaplan-david.html</a>
<a class="moz-txt-link-freetext" href="http://www.davidmkaplan.fr/">http://www.davidmkaplan.fr/</a>
**********************************
</pre>
</div>
_______________________________________________<br class="">postgis-users mailing list<br class=""><a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</div></blockquote></div><br class=""></body></html>