1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
-- create table
CREATE TABLE knn_recheck_geom(gid serial primary key, geom geometry);
INSERT INTO knn_recheck_geom(gid,geom)
SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*0.777,y*0.887) As geom
FROM generate_series(-100,1000, 7) AS x CROSS JOIN generate_series(-300,1000,9) As y;
INSERT INTO knn_recheck_geom(gid, geom)
SELECT 500000 + i, ST_Translate('LINESTRING(-100 300, 500 700, 400 123, 500 10000, 1 1)'::geometry, i*2000,0)
FROM generate_series(0,10) i;
INSERT INTO knn_recheck_geom(gid, geom)
SELECT 500100 + i, ST_Translate('POLYGON((100 800, 100 700, 400 123, 405 124, 100 800))'::geometry,0,i*2000)
FROM generate_series(0,3) i;
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
FROM knn_recheck_geom
WHERE gid IN(1000, 10000, 2000,3000);
-- without index order should match st_distance order --
-- point check
SELECT '#1' As t, gid, ST_Distance( 'POINT(-305 998.5)'::geometry, geom)::numeric(10,2)
FROM knn_recheck_geom
ORDER BY 'POINT(-305 998.5)'::geometry <-> geom LIMIT 5;
-- linestring check
SELECT '#2' As t, gid, ST_Distance( 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::geometry, geom)::numeric(12,4)
FROM knn_recheck_geom
ORDER BY 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::geometry <-> geom LIMIT 5;
-- lateral check before index
SELECT '#3' As t, a.gid, b.gid As match, ST_Distance(a.geom, b.geom)::numeric(15,4) As true_rn, b.knn_dist::numeric(15,4)
FROM knn_recheck_geom As a
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)
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);
vacuum analyze knn_recheck_geom;
set enable_seqscan = false;
SELECT '#1' As t, gid, ST_Distance( 'POINT(-305 998.5)'::geometry, geom)::numeric(10,2)
FROM knn_recheck_geom
ORDER BY 'POINT(-305 998.5)'::geometry <-> geom LIMIT 5;
-- linestring check
SELECT '#2' As t, gid, ST_Distance( 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::geometry, geom)::numeric(12,4)
FROM knn_recheck_geom
ORDER BY 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::geometry <-> geom LIMIT 5;
-- lateral check before index
SELECT '#3' As t, a.gid, b.gid As match, ST_Distance(a.geom, b.geom)::numeric(15,4) As true_rn, b.knn_dist::numeric(15,4)
FROM knn_recheck_geom As a
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)
ORDER BY a.gid, true_rn, b.gid;
DROP TABLE knn_recheck_geom;
-- create table
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;
INSERT INTO knn_recheck_geog(gid, geog)
SELECT 500000, 'LINESTRING(-95 -10, -93 -10.5, -90 -10.6, -95 -10.5, -95 -10)'::geography;
INSERT INTO knn_recheck_geog(gid, geog)
SELECT 500001, 'POLYGON((-95 10, -95.6 10.5, -95.9 10.75, -95 10))'::geography;
INSERT INTO knn_recheck_geog(gid,geog)
SELECT 600000 + ROW_NUMBER() OVER(ORDER BY gid) AS gid, ST_Buffer(geog,1000) As geog
FROM knn_recheck_geog
WHERE gid IN(1000, 10000, 2000, 2614, 40000);
SELECT '#1g' As t, gid, ST_Distance( 'POINT(-95 -10)'::geography, geog, false)::numeric(12,4) ,
('POINT(-95 -10)'::geography <-> geog )::numeric(12,4)
FROM knn_recheck_geog
ORDER BY 'POINT(-95 -10)'::geography <-> geog LIMIT 5;
SELECT '#2g' As t, gid, ST_Distance( 'LINESTRING(75 10, 75 12, 80 20)'::geography, geog, false)::numeric(12,4),
('LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog)::numeric(12,4) As knn_dist
FROM knn_recheck_geog
ORDER BY 'LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog LIMIT 5;
-- lateral check before index
SELECT '#3g' As t, a.gid, ARRAY(SELECT 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)
ORDER BY a.gid;
-- create index and repeat
CREATE INDEX idx_knn_recheck_geog_gist ON knn_recheck_geog USING gist(geog);
vacuum analyze knn_recheck_geog;
set enable_seqscan = false;
SELECT '#1g' As t, gid, ST_Distance( 'POINT(-95 -10)'::geography, geog, false)::numeric(12,4) ,
('POINT(-95 -10)'::geography <-> geog )::numeric(12,4)
FROM knn_recheck_geog
ORDER BY 'POINT(-95 -10)'::geography <-> geog LIMIT 5;
SELECT '#2g' As t, gid, ST_Distance( 'LINESTRING(75 10, 75 12, 80 20)'::geography, geog, false)::numeric(12,4),
('LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog)::numeric(12,4) As knn_dist
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 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)
ORDER BY a.gid;
DROP TABLE knn_recheck_geog;
--now the nd operator tests
-- create table and load
CREATE TABLE knn_recheck_geom_nd(gid serial primary key, geom geometry);
INSERT INTO knn_recheck_geom_nd(gid,geom)
SELECT ROW_NUMBER() OVER(ORDER BY x,y,z) AS gid, ST_MakePoint(x*0.777,y*0.887,z*1.05) As geom
FROM generate_series(-100,1000, 7) AS x ,
generate_series(-300,1000,9) As y,
generate_series(1005,10000,5555) As z ;
-- 3d lines
INSERT INTO knn_recheck_geom_nd(gid, geom)
SELECT 500000 + i, ST_Translate('LINESTRING(-100 300 500, 500 700 600, 400 123 0, 500 10000 -1234, 1 1 5000)'::geometry, i*2000,0)
FROM generate_series(0,10) i;
-- 3d polygons
INSERT INTO knn_recheck_geom_nd(gid, geom)
SELECT 500100 + i, ST_Translate('POLYGON((100 800 5678, 100 700 5678, 400 123 5678, 405 124 5678, 100 800 5678))'::geometry,0,i*2000)
FROM generate_series(0,3) i;
-- polyhedral surface --
INSERT INTO knn_recheck_geom_nd(gid,geom)
SELECT 600000 + row_number() over(), ST_Translate(the_geom,100, 450,1000) As the_geom
FROM (VALUES ( ST_GeomFromText(
'PolyhedralSurface(
((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1))
)') ) ,
( ST_GeomFromText(
'PolyhedralSurface(
((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)) )') ) )
As foo(the_geom) ;
-- without index order should match st_3ddistance order --
-- point check
SELECT '#1nd-3' As t, gid, ST_3DDistance( 'POINT(-305 998.5 1000)'::geometry, geom)::numeric(12,4) As dist3d,
('POINT(-305 998.5 1000)'::geometry <<->> geom)::numeric(12,4) As dist_knn
FROM knn_recheck_geom_nd
ORDER BY 'POINT(-305 998.5 1000)'::geometry <<->> geom LIMIT 5;
-- linestring check
SELECT '#2nd-3' As t, gid, ST_3DDistance( 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry::geometry, geom)::numeric(12,4),
('MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom)::numeric(12,4) As knn_dist
FROM knn_recheck_geom_nd
ORDER BY 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom LIMIT 5;
-- lateral test
SELECT '#3nd-3' As t, a.gid, b.gid As match, ST_3DDistance(a.geom, b.geom)::numeric(15,4) As true_rn, b.knn_dist::numeric(15,4)
FROM knn_recheck_geom_nd As a
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,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);
vacuum analyze knn_recheck_geom_nd;
set enable_seqscan = false;
-- point check
SELECT '#1nd-3' As t, gid, ST_3DDistance( 'POINT(-305 998.5 1000)'::geometry, geom)::numeric(12,4) As dist3d,
('POINT(-305 998.5 1000)'::geometry <<->> geom)::numeric(12,4) As dist_knn
FROM knn_recheck_geom_nd
ORDER BY 'POINT(-305 998.5 1000)'::geometry <<->> geom LIMIT 5;
-- linestring check
SELECT '#2nd-3' As t, gid, ST_3DDistance( 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry::geometry, geom)::numeric(12,4),
('MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom)::numeric(12,4) As knn_dist
FROM knn_recheck_geom_nd
ORDER BY 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom LIMIT 5;
-- lateral test
SELECT '#3nd-3' As t, a.gid, b.gid As match, ST_3DDistance(a.geom, b.geom)::numeric(15,4) As true_rn, b.knn_dist::numeric(15,4)
FROM knn_recheck_geom_nd As a
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,600001)
ORDER BY a.gid, true_rn, b.gid;
DROP TABLE knn_recheck_geom_nd;
-- #3573
SELECT '#3573', 'POINT M (0 0 13)'::geometry <<->> 'LINESTRING M (0 0 5, 0 1 6)'::geometry;
-- #3418
CREATE TABLE test_wo (geo geometry);
INSERT INTO test_wo VALUES
('0101000020E61000007D91D0967329E4BF6631B1F9B8D64A40'::geometry),
('0101000020E6100000E2AFC91AF510C1BFCDCCCCCCCCAC4A40'::geometry);
CREATE INDEX ON TEST_WO USING GIST (GEO);
analyze test_wo;
SET enable_seqscan = false;
SELECT '#3418' As ticket, ('0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry <-> geo)::numeric(14,7), ST_Distance('0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry, geo)::numeric(14,7)
FROM test_wo ORDER BY geo <->
('0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry);
DROP TABLE test_wo;
set enable_seqscan to default;
-- #5782
CREATE TABLE t5782 ( l text, g geometry );
INSERT INTO t5782 VALUES
('A', 'LINESTRING(18.00678831099686 69.0404811833497,18.006784630996860 69.04045431334970,18.00677727099686 69.0404005833497)'),
('B', 'LINESTRING(18.00677727099686 69.0404005833497,18.006780950996863 69.04042744334969,18.00678831099686 69.0404811833497)');
CREATE INDEX ON t5782 USING GIST(g);
ANALYZE t5782;
SET enable_seqscan = false;
SELECT '#5782', l FROM t5782 ORDER BY g <-> 'POINT(18.006691126034692 69.04048768506776)'::geometry;
DROP TABLE t5782;
SET enable_seqscan to default;
|
