1-- 2-- POINT 3-- 4 5CREATE TABLE POINT_TBL(f1 point); 6 7INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); 8 9INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); 10 11INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); 12 13INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); 14 15INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); 16 17-- bad format points 18INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); 19 20INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); 21 22INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); 23 24INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); 25 26 27SELECT '' AS six, * FROM POINT_TBL; 28 29-- left of 30SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)'; 31 32-- right of 33SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1; 34 35-- above 36SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >^ p.f1; 37 38-- below 39SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 <^ '(0.0, 0.0)'; 40 41-- equal 42SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)'; 43 44-- point in box 45SELECT '' AS three, p.* FROM POINT_TBL p 46 WHERE p.f1 <@ box '(0,0,100,100)'; 47 48SELECT '' AS three, p.* FROM POINT_TBL p 49 WHERE box '(0,0,100,100)' @> p.f1; 50 51SELECT '' AS three, p.* FROM POINT_TBL p 52 WHERE not p.f1 <@ box '(0,0,100,100)'; 53 54SELECT '' AS two, p.* FROM POINT_TBL p 55 WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]'; 56 57SELECT '' AS three, p.* FROM POINT_TBL p 58 WHERE not box '(0,0,100,100)' @> p.f1; 59 60SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist 61 FROM POINT_TBL p 62 ORDER BY dist; 63 64SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist 65 FROM POINT_TBL p1, POINT_TBL p2 66 ORDER BY dist, p1.f1[0], p2.f1[0]; 67 68SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 69 FROM POINT_TBL p1, POINT_TBL p2 70 WHERE (p1.f1 <-> p2.f1) > 3; 71 72-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 73SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance 74 FROM POINT_TBL p1, POINT_TBL p2 75 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 76 ORDER BY distance, p1.f1[0], p2.f1[0]; 77 78-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 79SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance 80 FROM POINT_TBL p1, POINT_TBL p2 81 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 >^ p2.f1 82 ORDER BY distance; 83 84-- Test that GiST indexes provide same behavior as sequential scan 85CREATE TEMP TABLE point_gist_tbl(f1 point); 86INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000); 87CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1); 88INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)'); 89SET enable_seqscan TO true; 90SET enable_indexscan TO false; 91SET enable_bitmapscan TO false; 92SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; 93SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; 94SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; 95SET enable_seqscan TO false; 96SET enable_indexscan TO true; 97SET enable_bitmapscan TO true; 98SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; 99SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; 100SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; 101RESET enable_seqscan; 102RESET enable_indexscan; 103RESET enable_bitmapscan; 104