[postgis-devel] [PostGIS] #1958: ST_Intersects(geography) returns incorrect result when called multiple times

[PostGIS]

#1958: ST_Intersects(geography) returns incorrect result when called multiple
times
---------------------------+------------------------------------------------
 Reporter:  realityexists  |       Owner:  pramsey      
     Type:  defect         |      Status:  new          
 Priority:  medium         |   Milestone:  PostGIS 2.1.0
Component:  postgis        |     Version:  2.0.x        
 Keywords:                 |  
---------------------------+------------------------------------------------

Comment(by robe):

 ah nevermind what I said.  I think I see what Paul is saying.  the
 ST_Distance and ST_Intersects use the tree cache (if more than one row in
 a query, which it determines when it sees the second row to switch to
 cache mode) and since the first tree answer is wrong, subsequent answers
 are wrong.  Note the ST_DistanceUncached gives the same answer for both
 calls.


 {{{
 WITH segment_line AS
 (
         SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667
 42.13668)'::geography AS line
         UNION ALL
         SELECT 1 AS id, 'LINESTRING(22.88333 41.96669,24.876111
 42.053364)'::geography AS line
         UNION ALL
         SELECT 3 AS id,  'LINESTRING(22.88333 41.96667,21.32667
 42.13668)'::geography AS line

 )
 , borders AS
 (
         SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389
 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
 )
 SELECT id, ST_Intersects(line, border), ST_Distance(line,border)
 , _ST_DistanceTree(line,border), _ST_DistanceUncached(line,border)
 FROM segment_line, borders;
 }}}


 {{{
  id | st_intersects |   st_distance    | _st_distancetree |
 _st_distanceuncached

 ----+---------------+------------------+------------------+---------------------
   2 | t             |                0 |   452.5945844566 |
 0
   1 | f             | 452.774393150775 | 452.774393150775 |
 452.774393150789
   3 | f             |   452.5945844566 |   452.5945844566 |
 0
 }}}


 I can jury rig it to give the right answer by switching the order of your
 rows


 {{{

 WITH segment_line AS
 (
         SELECT 2 AS id, 'LINESTRING(22.88333 41.96667,21.32667
 42.13667)'::geography AS line
         UNION ALL
         SELECT 1 AS id, 'LINESTRING(22.88333 41.96667,24.876111
 42.053361)'::geography AS lin
 )
 , borders AS
 (
         SELECT 'POLYGON((22.94472 41.34667,22.87528 41.99028,22.87389
 41.98472,22.87472 41.98333,22.94472 41.34667))'::geography AS border
 )
 SELECT id, ST_Intersects(line, border)
 FROM segment_line, borders
 }}}

 {{{
  id | st_intersects
 ----+---------------
   2 | t
   1 | f
 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1958#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.