Opened 7 years ago

Last modified 7 years ago

#3127 closed defect

KNN geog distance doesn't match ST_Distance spheroid but uses sphere — at Initial Version

Reported by: robe Owned by: robe
Priority: medium Milestone: PostGIS 2.2.0
Component: documentation Version: master
Keywords: Cc:

Description

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)

Change History (0)

Note: See TracTickets for help on using tickets.