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