[postgis-tickets] [PostGIS] #2556: geography ST_Intersects results depending on insert order

[PostGIS]

#2556: geography ST_Intersects results depending on insert order
--------------------------------------------------+-------------------------
 Reporter:  gekorob                               |       Owner:  pramsey      
     Type:  defect                                |      Status:  new          
 Priority:  high                                  |   Milestone:  PostGIS 2.1.2
Component:  postgis                               |     Version:  2.1.x        
 Keywords:  st_intersects intersection geography  |  
--------------------------------------------------+-------------------------

Comment(by robe):

 Very interesting.  I took the sample test given in
 http://lists.osgeo.org/pipermail/postgis-users/2014-January/038619.html
 and simplified it a bit and complicated it.  As the original example above
 its the cached_dist that is screwed up.  I think the cache may acutally be
 right but how the distance is being computed from it.  In this case they
 are all wrong.  The first is right of course because on first call, it
 can't use the cache.  I also think this issue might be different from the
 one I have in #2422 (sinc eit that case the distance tree as I recall
 doesn't agree with distance uncache)

 Here is full example:


 {{{
 CREATE TABLE test_cache (id serial, condition_geo geography);
 INSERT INTO test_cache (condition_geo)
  SELECT  ST_Buffer(ST_MakePoint(20.0,30.0)::geography,10.0)
   FROM generate_series(1,2) i;

 SELECT id , r, ST_Distance(condition_geo,geog) As  cached_dist
  , _ST_DistanceUncached(condition_geo,geog) As uncached_dist,
 _ST_DistanceTree(condition_geo,geog)
  from test_cache CROSS JOIN
    (SELECT r, ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), r) As geog
 FROM generate_series(1,14,2) As r) As foo
 ;


 }}}


 output is:


 {{{
  id | r  | cached_dist | uncached_dist | _st_distancetree
 ----+----+-------------+---------------+------------------
   1 |  1 |           0 |             0 |                0
   2 |  1 |           0 |             0 |                0
   1 |  3 |           0 |             0 |                0
   2 |  3 |           0 |             0 |                0
   1 |  5 |           0 |             0 |                0
   2 |  5 |           0 |             0 |                0
   1 |  7 |           0 |             0 |                0
   2 |  7 |           0 |             0 |                0
   1 |  9 |           0 |             0 |                0
   2 |  9 |           0 |             0 |                0
   1 | 11 | 0.995469035 |             0 |                0
   2 | 11 | 0.995469035 |             0 |                0
   1 | 13 |   2.9864071 |             0 |                0
   2 | 13 |   2.9864071 |             0 |                0
 }}}


 Not how both 1 and 2 give the same wrong answer using cache (they are in
 fact the same geography so the order dependence thing I think in this case
 is an artifact that the cache is not used for the first record).  things
 go wrong right around 10.4 radius.


 {{{
 SELECT id , r, ST_Distance(condition_geo,geog) As  cached_dist
  , _ST_DistanceUncached(condition_geo,geog) As uncached_dist,
 _ST_DistanceTree(condition_geo,geog)
  from test_cache CROSS JOIN
    (SELECT 10*(1 + i*0.01) As r, ST_Buffer(ST_GeogFromText('POINT(20.0
 30.0)'), 10*(1 + i*0.01)) As geog FROM generate_series(0,5,1) As i) As foo
    WHERE id = 1;

  id |   r   | cached_dist | uncached_dist | _st_distancetree
 ----+-------+-------------+---------------+------------------
   1 | 10.00 |           0 |             0 |                0
   1 | 10.10 |           0 |             0 |                0
   1 | 10.20 |           0 |             0 |                0
   1 | 10.30 |           0 |             0 |                0
   1 | 10.40 | 0.398187614 |             0 |                0
   1 | 10.50 | 0.497734518 |             0 |                0
 (6 rows)
 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2556#comment:6>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.