[postgis-tickets] [PostGIS] #3127: KNN geog distance doesn't match ST_Distance spheroid

[PostGIS]

#3127: KNN geog distance doesn't match ST_Distance spheroid
----------------------+---------------------------
  Reporter:  robe     |      Owner:  robe
      Type:  defect   |     Status:  reopened
  Priority:  medium   |  Milestone:  PostGIS 2.2.0
 Component:  postgis  |    Version:  trunk
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 Disregard my index comment -- not sure if its an issue of concern or not,
 but it seems the extra order by clause prevented use of spatial index so
 getting rid of the addtional order by a.gid seems to allow index to kick
 in.


 {{{
 SELECT a.gid,  ARRAY(SELECT  (gid,a.geog <-> g.geog)
                         FROM knn_recheck_geog_small_2 As g WHERE a.gid <>
 g.gid ORDER BY a.geog <-> g.geog LIMIT 2) As knn ,
         ARRAY(SELECT  (gid, ST_Distance(a.geog, g.geog ) )
                         FROM knn_recheck_geog_small_2 As g WHERE a.gid <>
 g.gid ORDER BY ST_Distance(a.geog, g.geog) LIMIT 2) As dist
 FROM knn_recheck_geog_small_2 As a
 WHERE a.gid = 1000
 ORDER BY a.gid;
 }}}

 but answer with index use is still the same with it thinking these two
 geometries are same distance. Seems like its still using sphere to me at
 r13589


 {{{
 SELECT a.geog  <-> g.geog As knn_dist, ST_Distance(a.geog,g.geog) As dist,
 ST_Distance(a.geog,g.geog, false) As dist_sphere
 FROM (SELECT 'POINT(-105.45 3.8)'::geography) As a(geog) ,
     (VALUES ('POINT(-105.45 2.85)'::geography ) , ( 'POINT(-105.45
 4.75)'::geography) ) g(geog);

     knn_dist    |       dist       |  dist_sphere
 ----------------+------------------+----------------
  105635.3257479 | 105049.134514627 | 105635.3257479
  105635.3257479 | 105051.447591582 | 105635.3257479

 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3127#comment:4>
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.