[postgis-tickets] r15348 - Get rid of knn tests where multiple records have the same distances

[Regina Obe]

Author: robe
Date: 2017-04-08 05:25:07 -0700 (Sat, 08 Apr 2017)
New Revision: 15348

Modified:
   branches/2.3/regress/knn_recheck.sql
   branches/2.3/regress/knn_recheck_expected
Log:
Get rid of knn tests where multiple records have the same distances
references #3649 for 2.3

Modified: branches/2.3/regress/knn_recheck.sql
===================================================================
--- branches/2.3/regress/knn_recheck.sql	2017-04-07 08:28:07 UTC (rev 15347)
+++ branches/2.3/regress/knn_recheck.sql	2017-04-08 12:25:07 UTC (rev 15348)
@@ -14,7 +14,7 @@
 
 
 INSERT INTO knn_recheck_geom(gid,geom)
-SELECT 600000 + ROW_NUMBER() OVER(ORDER BY gid) AS gid, ST_Translate(ST_Buffer(geom,8,15),100,300) As geom
+SELECT 600000 + ROW_NUMBER() OVER(ORDER BY gid) AS gid, ST_Translate(ST_Buffer(geom,8,15 ),100,300) As geom
 FROM knn_recheck_geom
 WHERE gid IN(1000, 10000, 2000,3000);
 
@@ -37,8 +37,8 @@
 	LEFT JOIN 
 		LATERAL ( SELECT  gid, geom, a.geom <-> g.geom As knn_dist
 			FROM knn_recheck_geom As g WHERE a.gid <> g.gid ORDER BY a.geom <-> g.geom LIMIT 5) As b ON true
-	WHERE a.gid IN(1,500101,500003)
-ORDER BY a.gid, true_rn;
+	WHERE a.gid IN(1,500101)
+ORDER BY a.gid, true_rn, b.gid;
 
 -- create index and repeat
 CREATE INDEX idx_knn_recheck_geom_gist ON knn_recheck_geom USING gist(geom);
@@ -60,8 +60,8 @@
 	LEFT JOIN 
 		LATERAL ( SELECT  gid, geom, a.geom <-> g.geom As knn_dist
 			FROM knn_recheck_geom As g WHERE a.gid <> g.gid ORDER BY a.geom <-> g.geom LIMIT 5) As b ON true
-	WHERE a.gid IN(1,500101,500003)
-ORDER BY a.gid, true_rn;
+	WHERE a.gid IN(1,500101)
+ORDER BY a.gid, true_rn, b.gid;
 
 DROP TABLE knn_recheck_geom;
 
@@ -102,7 +102,7 @@
 			FROM knn_recheck_geog As g WHERE a.gid <> g.gid ORDER BY ST_Distance(a.geog, g.geog, false) LIMIT 5) = ARRAY(SELECT  gid
 			FROM knn_recheck_geog As g WHERE a.gid <> g.gid ORDER BY a.geog <-> g.geog LIMIT 5) As dist_order_agree
 FROM knn_recheck_geog As a 
-	WHERE a.gid IN(500000,500010,1000,2614)
+	WHERE a.gid IN(500000,500010,1000)
 ORDER BY a.gid;
 
 
@@ -121,11 +121,11 @@
 FROM knn_recheck_geog
 ORDER BY 'LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog LIMIT 5;
 
-SELECT '#3g' As t, a.gid,  ARRAY(SELECT  gid
+SELECT '#3g' As t, a.gid,  ARRAY(SELECT  g.gid
 			FROM knn_recheck_geog As g WHERE a.gid <> g.gid ORDER BY ST_Distance(a.geog, g.geog, false) LIMIT 5) = ARRAY(SELECT  gid
 			FROM knn_recheck_geog As g WHERE a.gid <> g.gid ORDER BY a.geog <-> g.geog LIMIT 5) As dist_order_agree
 FROM knn_recheck_geog As a 
-	WHERE a.gid IN(500000,500010,1000,2614)
+	WHERE a.gid IN(500000,500010,1000)
 ORDER BY a.gid;
 
 DROP TABLE knn_recheck_geog;
@@ -189,8 +189,8 @@
 	LEFT JOIN 
 		LATERAL ( SELECT  gid, geom, a.geom <<->> g.geom As knn_dist
 			FROM knn_recheck_geom_nd As g WHERE a.gid <> g.gid ORDER BY a.geom <<->> g.geom LIMIT 5) As b ON true
-	WHERE a.gid IN(1,500003,600001)
-ORDER BY a.gid, true_rn;
+	WHERE a.gid IN(1,600001)
+ORDER BY a.gid, true_rn, b.gid;
 
 -- create index and repeat
 CREATE INDEX idx_knn_recheck_geom_nd_gist ON knn_recheck_geom_nd USING gist(geom gist_geometry_ops_nd);
@@ -214,8 +214,8 @@
 	LEFT JOIN 
 		LATERAL ( SELECT  gid, geom, a.geom <<->> g.geom As knn_dist
 			FROM knn_recheck_geom_nd As g WHERE a.gid <> g.gid ORDER BY a.geom <<->> g.geom LIMIT 5) As b ON true
-	WHERE a.gid IN(1,500003,600001)
-ORDER BY a.gid, true_rn;
+	WHERE a.gid IN(1,600001)
+ORDER BY a.gid, true_rn, b.gid;
 
 
 DROP TABLE knn_recheck_geom_nd;

Modified: branches/2.3/regress/knn_recheck_expected
===================================================================
--- branches/2.3/regress/knn_recheck_expected	2017-04-07 08:28:07 UTC (rev 15347)
+++ branches/2.3/regress/knn_recheck_expected	2017-04-08 12:25:07 UTC (rev 15348)
@@ -13,11 +13,6 @@
 #3|1|147|9.6598|9.6598
 #3|1|291|10.8780|10.8780
 #3|1|292|13.4929|13.4929
-#3|500003|500004|1447.7424|1447.7424
-#3|500003|500002|1447.7424|1447.7424
-#3|500003|500001|3423.4486|3423.4486
-#3|500003|500005|3423.4486|3423.4486
-#3|500003|22837|5123.7770|5123.7770
 #3|500101|500000|0.0000|0.0000
 #3|500101|600004|971.4947|971.4947
 #3|500101|600001|1106.0791|1106.0791
@@ -38,11 +33,6 @@
 #3|1|147|9.6598|9.6598
 #3|1|291|10.8780|10.8780
 #3|1|292|13.4929|13.4929
-#3|500003|500004|1447.7424|1447.7424
-#3|500003|500002|1447.7424|1447.7424
-#3|500003|500001|3423.4486|3423.4486
-#3|500003|500005|3423.4486|3423.4486
-#3|500003|22837|5123.7770|5123.7770
 #3|500101|500000|0.0000|0.0000
 #3|500101|600004|971.4947|971.4947
 #3|500101|600001|1106.0791|1106.0791
@@ -59,7 +49,6 @@
 #2g|30695|21264.3654|21264.3654
 #2g|30512|25313.2118|25313.2118
 #3g|1000|t
-#3g|2614|t
 #3g|500000|t
 #1g|500000|0.0000|0.0000
 #1g|600003|69974.6935|69974.6935
@@ -72,7 +61,6 @@
 #2g|30695|21264.3654|21264.3654
 #2g|30512|25313.2118|25313.2118
 #3g|1000|t
-#3g|2614|t
 #3g|500000|t
 #1nd-3|290|260.6797|260.6797
 #1nd-3|287|264.3000|264.3000
@@ -89,11 +77,6 @@
 #3nd-3|1|294|9.6598|9.6598
 #3nd-3|1|582|10.8780|10.8780
 #3nd-3|1|583|13.4929|13.4929
-#3nd-3|500003|500004|1448.3262|1448.3262
-#3nd-3|500003|500002|1448.3262|1448.3262
-#3nd-3|500003|500001|3424.9088|3424.9088
-#3nd-3|500003|500005|3424.9088|3424.9088
-#3nd-3|500003|45674|5153.7747|5153.7747
 #3nd-3|600001|600002|0.0000|0.0000
 #3nd-3|600001|9752|54.2730|54.2730
 #3nd-3|600001|9461|54.3900|54.3900
@@ -114,11 +97,6 @@
 #3nd-3|1|294|9.6598|9.6598
 #3nd-3|1|582|10.8780|10.8780
 #3nd-3|1|583|13.4929|13.4929
-#3nd-3|500003|500004|1448.3262|1448.3262
-#3nd-3|500003|500002|1448.3262|1448.3262
-#3nd-3|500003|500001|3424.9088|3424.9088
-#3nd-3|500003|500005|3424.9088|3424.9088
-#3nd-3|500003|45674|5153.7747|5153.7747
 #3nd-3|600001|600002|0.0000|0.0000
 #3nd-3|600001|9752|54.2730|54.2730
 #3nd-3|600001|9461|54.3900|54.3900