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

[PostGIS]

#3127: KNN geog distance doesn't match ST_Distance spheroid but uses sphere
---------------------------+---------------------------
 Reporter:  robe           |      Owner:  robe
     Type:  defect         |     Status:  new
 Priority:  medium         |  Milestone:  PostGIS 2.2.0
Component:  documentation  |    Version:  trunk
 Keywords:                 |
---------------------------+---------------------------
 I'm not sure if this is expected or not, but I was trying to write up
 regress tests for KNN geog and found inconsistencies between what <->
 returns and what ST_Distance geography returns which changes the ranking.
 It seems the <-> is returning the sphere distance instead of spheroid
 (e.g. I get same answer if I do


 {{{

 ST_Distance(geog1,geog2, false) )


 }}}


 If that is the case, and by design, we just need to make a note of it in
 the docs, so people don't falsely report its bugged.

 e.g.:


 {{{
 CREATE TABLE knn_recheck_geog(gid serial primary key, geog geography);
 INSERT INTO knn_recheck_geog(gid,geog)
 SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid,
 ST_Point(x*1.11,y*0.95)::geography As geog
 FROM generate_series(-100,100, 1) AS x CROSS JOIN
 generate_series(-90,90,1) As y;

 SELECT a.gid, b.gid As match, RANK() OVER(PARTITION BY a.gid ORDER BY
 ST_Distance(a.geog, b.geog) ) As true_rn, b.rn  As knn_rn,
 ST_Distance(a.geog, b.geog) As dist_spheroid, ST_Distance(a.geog, b.geog,
 false) As dist_sphere,
 a.geog <-> b.geog As knn_dist
 FROM knn_recheck_geog As a
         LEFT JOIN
                 LATERAL ( SELECT  gid, geog, RANK() OVER(ORDER BY a.geog
 <-> g.geog) As rn
                         FROM knn_recheck_geog As g WHERE a.gid <> g.gid
 ORDER BY 'POINT(-105.45 3.8)'::Geography <-> g.geog LIMIT 5) As b ON true
         WHERE a.gid = 1000
 ORDER BY a.gid, b.rn;


 }}}

 yields:

 {{{
  gid  | match | true_rn | knn_rn |  dist_spheroid   |   dist_sphere    |
 knn_dist
 ------+-------+---------+--------+------------------+------------------+------------------
  1000 |  1001 |       2 |      1 | 105051.447591582 |   105635.3257479 |
 105635.3257479
  1000 |   999 |       1 |      2 | 105049.134514627 |   105635.3257479 |
 105635.3257479
  1000 |   819 |       3 |      3 | 123294.778176043 | 123155.172887459 |
 123155.172887459
  1000 |  1181 |       4 |      4 | 123294.778176044 |  123155.17288746 |
 123155.17288746
  1000 |   820 |       5 |      5 | 161924.039877904 | 162197.178690157 |
 162197.178690157
 (5 rows)

 }}}

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