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