geography regression failure ST_DWithin/ST_Distance between 2.0.4 and 2.1.2 for dimensional mix of geographies

[robe]

This is unwatered down fresh from garden bot. I'll try to water it down to something we can throw in our tests:

This is running on:

POSTGIS="2.1.2dev r12220" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

SELECT ST_DWithin(geography(foo1.the_geom), geography(foo2.the_geom), 100, false) As result,
   ST_Distance(geography(foo1.the_geom), geography(foo2.the_geom)), _ST_DistanceUncached(geography(foo1.the_geom), geography(foo2.the_geom) )
FROM ((SELECT ST_Collect(ST_SetSRID(ST_MakePoint(i,j,k),4326)) As the_geom FROM generate_series(-10,50,20) As i CROSS JOIN generate_series(40,70, 25) j CROSS JOIN generate_series(1,3) k )) As foo1 
  CROSS JOIN ((SELECT ST_Buffer(ST_SetSRID(ST_Point(i,j),4326), j*0.05) As the_geom FROM (SELECT a*1.11111111 FROM generate_series(-10,50,10) As a) As i(i) CROSS JOIN generate_series(40,70, 20) As j ORDER BY i, i*j, j)) As foo2 LIMIT 2;

 result |   st_distance    | _st_distanceuncached
--------+------------------+----------------------
 f      | 229423.805745173 |     229423.805745173
 f      |  75661.624157771 |                    0

[robe]

this might be the same issue as #2422

[robe]

With this one I am getting mixed results

CREATE TABLE test_geogs(gid integer, geog geography );
INSERT INTO test_geogs(gid, geog)
VALUES ( 1, 'MULTIPOINT Z (-10 40 1,-10 65 1,10 40 1,10 65 1,30 40 1,30 65 1,50 40 1,50 65 1,-10 40 2,-10 65 2,10 40 2,10 65 2,30 40 2,30 65 2,50 40 2,50 65 2,-10 40 3,-10 65 3,10 40 3,10 65 3,30 40 3,30 65 3,50 40 3,50 65 3)'::geography ),
 (2, 'POLYGON((-9.1111111 40,-9.14954053919354 39.6098193559677,-9.26335203497743 39.2346331352698,-9.44817187539491 38.8888595339608,-9.6968975376269 38.5857864376269,-9.99997063396079 38.3370607753949,-10.3457442352698 38.1522409349774,-10.7209304559677 38.0384294391935,-11.1111111 38,-11.5012917440323 38.0384294391935,-11.8764779647302 38.1522409349774,-12.2222515660392 38.3370607753949,-12.5253246623731 38.5857864376269,-12.7740503246051 38.8888595339608,-12.9588701650226 39.2346331352698,-13.0726816608065 39.6098193559677,-13.1111111 40,-13.0726816608065 40.3901806440322,-12.9588701650226 40.7653668647302,-12.7740503246051 41.1111404660392,-12.5253246623731 41.4142135623731,-12.2222515660392 41.6629392246051,-11.8764779647302 41.8477590650226,-11.5012917440323 41.9615705608065,-11.1111111 42,-10.7209304559678 41.9615705608065,-10.3457442352698 41.8477590650226,-9.9999706339608 41.6629392246051,-9.69689753762691 41.4142135623731,-9.44817187539491 41.1111404660392,-9.26335203497743 40.7653668647302,-9.14954053919354 40.3901806440323,-9.1111111 40))'::geography);

SELECT t1.gid As t1_gid, t2.gid As t2_gid
 , ST_DWithin(t1.geog, t2.geog,100)
 , ST_Distance(t1.geog, t2.geog) As dist
 , _ST_DistanceUncached(t1.geog,t2.geog) As dist_cache, _ST_DistanceTree(t1.geog, t2.geog) As dist_tree
FROM test_geogs As t1 CROSS JOIN test_geogs As t2;

When run from pgAdmin the 1 x2 gives a st_dwithin: f and dist ( 75661.624157771)

But 2, 1 gives the right answer of 0

This is running on

SELECT version() || ' ' || postgis_full_version(); 

PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit POSTGIS="2.1.2dev r12220" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

I'm going to test next on my 64-bit. I rarely test on my 32-bit but that's the only one I have 2.0.4 easily setup to compare.

[robe]

First fails on my:

PostgreSQL 9.3rc1, compiled by Visual C++ build 1600, 64-bit POSTGIS="2.1.2dev r12221" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

as well. 2nd also fails on my 64-bit consistently when running n psql and pgAdmin

SELECT t1.gid As t1_gid, t2.gid As t2_gid

 , ST_DWithin(t1.geog, t2.geog,100)
 , ST_Distance(t1.geog, t2.geog) As dist
 , _ST_DistanceUncached(t1.geog,t2.geog) As dist_cache, _ST_DistanceTree(t1.geog
, t2.geog) As dist_tree
FROM test_geogs As t1 CROSS JOIN test_geogs As t2;
 t1_gid | t2_gid | st_dwithin |      dist       | dist_cache | dist_tree
--------+--------+------------+-----------------+------------+-----------
      1 |      1 | t          |               0 |          0 |         0
      1 |      2 | f          | 75661.624157771 |          0 |         0
      2 |      1 | t          |               0 |          0 |         0
      2 |      2 | t          |               0 |          0 |         0
(4 rows)

[robe]

I will note that:

SELECT _ST_DistanceUncached(t1.geog, ST_Boundary(t2.geog::geometry)::geography)
FROM test_geogs As t1 CROSS JOIN test_geogs As t2
where t1.gid = 1 and t2.gid = 2;

----
75661.6241577711

[robe]

pramsey what does this return for you?

with geogp(gid, geog) AS (VALUES (1, 'POINT(-11.1111111 40)'::geography), (1, 'POINT(-11.1111111 40)'::geography))
 , geoglm (gid, geog) AS (VALUES 
      (3, 'LINESTRING M (-10 50 1,50 -10 1)'::geography), 
     (4, 'LINESTRING M (-10 50 2,50 -10 2)'::geography) )
SELECT geogp.gid As p_gid, geoglm.gid As lm_gid,  ST_Distance(geogp.geog, geoglm.geog,false) As dist
 , _ST_DistanceUncached(geogp.geog, geoglm.geog, false) As distunc
 FROM geogp CROSS JOIN geoglm ;

 p_gid | lm_gid |       dist       |     distunc
-------+--------+------------------+-----------------
     1 |      3 |  1010519.2194355 | 1010519.2194355
     1 |      4 | 1011346.61455376 | 1010519.2194355
     1 |      3 | 1011346.61455376 | 1010519.2194355
     1 |      4 | 1011346.61455376 | 1010519.2194355
(4 rows)

This one only seems to be an issue if false is passed in for use_sphere

[pramsey]

I'm not seeing this one much, the differences look to be spheroid-vs-not, basically not respecting the sphere/spheroid flag correctly throughout the logic. Still odder case

with geogp(gid, geog) AS (VALUES (1, 'POINT(-11.1111111 40)'::geography), (1, 'POINT(-11.1111111 40)'::geography))
 , geoglm (gid, geog) AS (VALUES 
      (3, 'LINESTRING M (-10 50 1,50 -10 1)'::geography), 
     (4, 'LINESTRING M (-10 50 2,50 -10 2)'::geography) )
SELECT geogp.gid As p_gid, geoglm.gid As lm_gid,  ST_Distance(geogp.geog, geoglm.geog) As dist, _ST_DistanceTree(geogp.geog, geoglm.geog)
 , _ST_DistanceUncached(geogp.geog, geoglm.geog) As distunc
 FROM geogp CROSS JOIN geoglm ;

tree distance is different from the other two! I'd expect it to be one (sphere) or the other (spheroid) but not a whole new number.

[pramsey]

Back-pedal, it's displaying memory corruption symptoms (dwithin gives wrong answer twice, then starts giving right answer). Only dwithin, oddly, not distance.

[pramsey]

Using this simple case to compare 2.0 and 2.1, and 2.0 is stably correct and 2.1 is variably correct and incorrect.

SELECT t1.gid As t1_gid, t2.gid As t2_gid
 , ST_DWithin(t1.geog, t2.geog,100)
 , ST_Distance(t1.geog, t2.geog) As dist
FROM test_geogs As t1 CROSS JOIN test_geogs As t2;

[robe]

Same bugginess with PostGIS 2.1.1 release as seen in 2.1.2dev. I guess I didn't test that one before we released. Sorry about that. I'll next test 2.1.0.

[robe]

Hmm haven't gotten 2.1.0 to give an unstable answer for below even after 10 attempts PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

SELECT t1.gid As t1_gid, t2.gid As t2_gid
 , ST_DWithin(t1.geog, t2.geog,100)
 , ST_Distance(t1.geog, t2.geog) As dist
FROM test_geogs As t1 CROSS JOIN test_geogs As t2;

 t1_gid | t2_gid | st_dwithin | dist
--------+--------+------------+------
      1 |      1 | t          |    0
      1 |      2 | t          |    0
      2 |      1 | t          |    0
      2 |      2 | t          |    0

I do however get the same screwy answers for:

with geogp(gid, geog) AS (VALUES (1, 'POINT(-11.1111111 40)'::geography), (1, 'POINT(-11.1111111 40)'::geography))
 , geoglm (gid, geog) AS (VALUES 
      (3, 'LINESTRING M (-10 50 1,50 -10 1)'::geography), 
     (4, 'LINESTRING M (-10 50 2,50 -10 2)'::geography) )
SELECT geogp.gid As p_gid, geoglm.gid As lm_gid,  ST_Distance(geogp.geog, geoglm.geog,false) As dist
 , _ST_DistanceUncached(geogp.geog, geoglm.geog, false) As distunc
 FROM geogp CROSS JOIN geoglm ;

 p_gid | lm_gid |       dist       |     distunc
-------+--------+------------------+-----------------
     1 |      3 |  1010519.2194355 | 1010519.2194355
     1 |      4 | 1011346.61455377 | 1010519.2194355
     1 |      3 | 1011346.61455377 | 1010519.2194355
     1 |      4 | 1011346.61455377 | 1010519.2194355

[pramsey]

The second case appears distinct from the first, because it's consistent, and the flipping is just the cache in operation. The core problem can be seen by setting both tree and brute force algorithms into sphere mode

WITH 
geogp(gid, geog) AS (VALUES 
 (1, 'POINT(-11.1111111 40)'::geography), 
 (1, 'POINT(-11.1111111 40)'::geography)), 
geogl (gid, geog) AS (VALUES 
  (3, 'LINESTRING (-10 50,50 -10)'::geography), 
  (4, 'LINESTRING (-10 50,50 -10)'::geography))
  
SELECT 
  geogp.gid AS p_gid,  
  geogl.gid AS l_gid,  
  ST_Distance(geogp.geog, geogl.geog, false) AS dist, 
  _ST_DistanceUncached(geogp.geog, geogl.geog, false) AS distunc,
  _ST_DistanceTree(geogp.geog, geogl.geog) AS disttree
FROM geogp CROSS JOIN geogl;

 p_gid | l_gid |       dist       |     distunc     |     disttree     
-------+-------+------------------+-----------------+------------------
     1 |     3 |  1010519.2194355 | 1010519.2194355 | 1011346.61455376
     1 |     4 | 1011346.61455376 | 1010519.2194355 | 1011346.61455376
     1 |     3 | 1011346.61455376 | 1010519.2194355 | 1011346.61455376
     1 |     4 | 1011346.61455376 | 1010519.2194355 | 1011346.61455376

They just plain disagree on the answer here (though they agree when in spheroid mode). The flip/flopping problem has to be something else (worse), I think.

[pramsey]

I fixed the sphere regression in trunk r12305 and 2.1 r12304, still testing the flip/flop

[pramsey]

Nope, flip/flop is still there, it was a different issue.

[pramsey]

I believe the fixes in #2675 address the last problem here.