From f476059c75f90292f62423c991efd31ebf7db6c0 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Thu, 4 Jun 2015 08:14:59 +0000 Subject: [PATCH] rework the knn recheck tests for goemetry, geography, add some nd tests for 3d. still more tests to add. git-svn-id: http://svn.osgeo.org/postgis/trunk@13615 b70326c6-7e19-0410-871a-916f4a2858ee --- regress/knn_recheck.sql | 140 +++++++++++++++++++++-------- regress/knn_recheck_expected | 167 ++++++++++++++++++----------------- 2 files changed, 189 insertions(+), 118 deletions(-) diff --git a/regress/knn_recheck.sql b/regress/knn_recheck.sql index 1f50c696e..c4320d82f 100644 --- a/regress/knn_recheck.sql +++ b/regress/knn_recheck.sql @@ -1,65 +1,74 @@ -- 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.777) As geom -FROM generate_series(-100,100, 1) AS x CROSS JOIN generate_series(-300,10000,10) As y; +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, 'LINESTRING(100 300, -10 700, 400 123, -300 10000)'::geometry; - +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 500001, 'POLYGON((100 3000, -10 700, 400 123, 405 124, 100 3000))'::geometry; +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_Buffer(geom,1000,2) 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, 40000); +WHERE gid IN(1000, 10000, 2000,3000); -- without index order should match st_distance order -- -- point check -SELECT gid, ST_Distance( 'POINT(200 1000)'::geometry, geom)::numeric(10,2) + +SELECT '#1' As t, gid, ST_Distance( 'POINT(-305 998.5)'::geometry, geom)::numeric(10,2) FROM knn_recheck_geom -ORDER BY 'POINT(200 1000)'::geometry <-> geom LIMIT 5; +ORDER BY 'POINT(-305 998.5)'::geometry <-> geom LIMIT 5; -- linestring check -SELECT gid, ST_Distance( 'LINESTRING(200 100, -10 600)'::geometry, geom)::numeric(10,2) +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 'LINESTRING(200 100, -10 600)'::geometry <-> geom LIMIT 5; +ORDER BY 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::geometry <-> geom LIMIT 5; -- lateral check before index -SELECT a.gid, b.gid As match, RANK() OVER(PARTITION BY a.gid ORDER BY ST_Distance(a.geom, b.geom) ) As true_rn, b.rn As knn_rn +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, RANK() OVER(ORDER BY a.geom <-> g.geom) As rn + 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(50000,50001,70000,61000) -ORDER BY a.gid, b.rn; + WHERE a.gid IN(1,500101,500003) +ORDER BY a.gid, true_rn; -- 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; --- point check after index -SELECT gid, ST_Distance( 'POINT(200 1000)'::geometry, geom)::numeric(10,2) +SELECT '#1' As t, gid, ST_Distance( 'POINT(-305 998.5)'::geometry, geom)::numeric(10,2) FROM knn_recheck_geom -ORDER BY 'POINT(200 1000)'::geometry <-> geom LIMIT 5; +ORDER BY 'POINT(-305 998.5)'::geometry <-> geom LIMIT 5; --- lateral check after index - currently is wrong -SELECT a.gid, b.gid As match, RANK() OVER(PARTITION BY a.gid ORDER BY ST_Distance(a.geom, b.geom) ) As true_rn, b.rn As knn_rn +-- 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, RANK() OVER(ORDER BY a.geom <-> g.geom) As rn + 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(50000,50001,70000,61000) -ORDER BY a.gid, b.rn; + WHERE a.gid IN(1,500101,500003) +ORDER BY a.gid, true_rn; DROP TABLE knn_recheck_geom; -- geography tests DELETE FROM spatial_ref_sys where srid = 4326; -INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","proj4text") VALUES (4326,'EPSG',4326,'+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '); +INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","proj4text") + VALUES (4326,'EPSG',4326,'+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs '); -- create table CREATE TABLE knn_recheck_geog(gid serial primary key, geog geography); INSERT INTO knn_recheck_geog(gid,geog) @@ -67,7 +76,7 @@ SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*1.11,y*0.95)::geograph 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, -11 65, 5 10, -70 60)'::geography; +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; @@ -78,16 +87,18 @@ FROM knn_recheck_geog WHERE gid IN(1000, 10000, 2000, 2614, 40000); -SELECT gid, RANK() OVER(ORDER BY ST_Distance( 'POINT(95 10)'::geography, geog,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; +ORDER BY 'POINT(-95 -10)'::geography <-> geog LIMIT 5; -SELECT gid, RANK() OVER(ORDER BY ST_Distance( 'POINT(-95 -10)'::geography, geog, false) ) +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 'POINT(-95 -10)'::geography <-> geog LIMIT 5; +ORDER BY 'LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog LIMIT 5; -- lateral check before index -SELECT a.gid, ARRAY(SELECT gid +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 @@ -97,18 +108,20 @@ 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 gid +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; +ORDER BY 'POINT(-95 -10)'::geography <-> geog LIMIT 5; -SELECT gid +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 'POINT(-95 -10)'::geography <-> geog LIMIT 5; +ORDER BY 'LINESTRING(75 10, 75 12, 80 20)'::geography <-> geog LIMIT 5; --- check after index -set enable_seqscan = false; --sometimes doesn't want to use index -SELECT a.gid, ARRAY(SELECT gid +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 @@ -122,3 +135,56 @@ DROP TABLE knn_recheck_geog; -- DELETE FROM spatial_ref_sys WHERE srid = 4326; +--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) 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; + +-- TODO: add index tests + +DROP TABLE knn_recheck_geom_nd; + diff --git a/regress/knn_recheck_expected b/regress/knn_recheck_expected index 5c6a4a98a..1eee3f700 100644 --- a/regress/knn_recheck_expected +++ b/regress/knn_recheck_expected @@ -1,81 +1,86 @@ -500001|0.00 -206360|122.32 -206359|122.42 -206361|122.72 -206358|123.01 -500000|0.00 -134128|0.04 -199066|0.04 -155774|0.04 -112482|0.04 -50000|48969|1|1 -50000|51031|2|2 -50000|47938|3|3 -50000|52062|4|4 -50000|46907|5|5 -50001|48970|1|1 -50001|51032|2|2 -50001|47939|3|3 -50001|52063|4|4 -50001|46908|5|5 -61000|62031|1|1 -61000|59969|1|1 -61000|63062|3|3 -61000|58938|4|4 -61000|64093|5|5 -70000|600001|1|1 -70000|600002|1|1 -70000|600004|1|1 -70000|71031|4|4 -70000|68969|5|5 -500001|0.00 -206360|122.32 -206359|122.42 -206361|122.72 -206358|123.01 -50000|48969|1|1 -50000|51031|2|2 -50000|47938|3|3 -50000|52062|4|4 -50000|46907|5|5 -50001|48970|1|1 -50001|51032|2|2 -50001|47939|3|3 -50001|52063|4|4 -50001|46908|5|5 -61000|62031|1|1 -61000|59969|1|1 -61000|63062|3|3 -61000|58938|4|4 -61000|64093|5|5 -70000|600001|1|1 -70000|600002|1|1 -70000|600004|1|1 -70000|71031|4|4 -70000|68969|5|5 -33768|1 -33767|2 -33587|3 -33586|4 -33769|5 -500000|1 -600003|2 -2614|3 -2615|4 -2795|5 -1000|t -2614|t -500000|t -33768 -33767 -33587 -33586 -33769 -500000 -600003 -2614 -2615 -2795 -1000|t -2614|t -500000|t +#1|145|254.76 +#1|144|258.46 +#1|290|259.62 +#1|143|262.35 +#1|289|263.26 +#2|3084|0.0094 +#2|2497|0.0256 +#2|3671|0.0444 +#2|1910|0.0607 +#2|4258|0.0794 +#3|1|146|5.4390|5.4390 +#3|1|2|7.9830|7.9830 +#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 +#3|500101|600002|1210.3577|1210.3577 +#3|500101|12905|1239.5484|1239.5484 +#1|145|254.76 +#1|144|258.46 +#1|290|259.62 +#1|143|262.35 +#1|289|263.26 +#2|3084|0.0094 +#2|2497|0.0256 +#2|3671|0.0444 +#2|1910|0.0607 +#2|4258|0.0794 +#3|1|146|5.4390|5.4390 +#3|1|2|7.9830|7.9830 +#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 +#3|500101|600002|1210.3577|1210.3577 +#3|500101|12905|1239.5484|1239.5484 +#1g|500000|0.0000|0.0000 +#1g|600003|69974.6935|69974.6935 +#1g|2614|70976.1794|70976.1794 +#1g|2615|75048.8551|75048.8551 +#1g|2795|86965.9298|86965.9298 +#2g|31244|4189.9122|4189.9122 +#2g|31061|6756.6424|6756.6424 +#2g|30878|15161.4706|15161.4706 +#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 +#1g|2614|70976.1794|70976.1794 +#1g|2615|75048.8551|75048.8551 +#1g|2795|86965.9298|86965.9298 +#2g|31244|4189.9122|4189.9122 +#2g|31061|6756.6424|6756.6424 +#2g|30878|15161.4706|15161.4706 +#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 +#1nd-3|579|265.4356|265.4356 +#1nd-3|285|268.1092|268.1092 +#1nd-3|578|268.9919|268.9919 +#2nd-3|9749|3.7272|3.7272 +#2nd-3|600001|3.9451|3.9451 +#2nd-3|600002|3.9950|3.9950 +#2nd-3|9748|4.1707|4.1707 +#2nd-3|10040|6.1761|6.1761 -- 2.40.0