-- -- POINT -- -- avoid bit-exact output here because operations may not be bit-exact. SET extra_float_digits = 0; CREATE TABLE POINT_TBL(f1 point); INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); INSERT INTO POINT_TBL(f1) VALUES ('(1e-300,-1e-300)'); -- To underflow INSERT INTO POINT_TBL(f1) VALUES ('(1e+300,Inf)'); -- To overflow INSERT INTO POINT_TBL(f1) VALUES ('(Inf,1e+300)'); -- Transposed INSERT INTO POINT_TBL(f1) VALUES (' ( Nan , NaN ) '); -- bad format points INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); ERROR: invalid input syntax for type point: "asdfasdf" LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); ^ INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); ERROR: invalid input syntax for type point: "(10.0 10.0)" LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); ^ INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x'); ERROR: invalid input syntax for type point: "(10.0, 10.0) x" LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x'); ^ INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); ERROR: invalid input syntax for type point: "(10.0,10.0" LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); ^ INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)'); -- Out of range ERROR: "1e+500" is out of range for type double precision LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)'); ^ SELECT * FROM POINT_TBL; f1 ------------------- (0,0) (-10,0) (-3,4) (5.1,34.5) (-5,-12) (1e-300,-1e-300) (1e+300,Infinity) (Infinity,1e+300) (NaN,NaN) (10,10) (10 rows) -- left of SELECT p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)'; f1 ---------- (-10,0) (-3,4) (-5,-12) (3 rows) -- right of SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1; f1 ---------- (-10,0) (-3,4) (-5,-12) (3 rows) -- above SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' |>> p.f1; f1 ---------- (-5,-12) (1 row) -- below SELECT p.* FROM POINT_TBL p WHERE p.f1 <<| '(0.0, 0.0)'; f1 ---------- (-5,-12) (1 row) -- equal SELECT p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)'; f1 ------------ (5.1,34.5) (1 row) -- point in box SELECT p.* FROM POINT_TBL p WHERE p.f1 <@ box '(0,0,100,100)'; f1 ------------ (0,0) (5.1,34.5) (10,10) (3 rows) SELECT p.* FROM POINT_TBL p WHERE box '(0,0,100,100)' @> p.f1; f1 ------------ (0,0) (5.1,34.5) (10,10) (3 rows) SELECT p.* FROM POINT_TBL p WHERE not p.f1 <@ box '(0,0,100,100)'; f1 ------------------- (-10,0) (-3,4) (-5,-12) (1e-300,-1e-300) (1e+300,Infinity) (Infinity,1e+300) (NaN,NaN) (7 rows) SELECT p.* FROM POINT_TBL p WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]'; f1 ------------------ (0,0) (-10,0) (1e-300,-1e-300) (3 rows) SELECT p.* FROM POINT_TBL p WHERE not box '(0,0,100,100)' @> p.f1; f1 ------------------- (-10,0) (-3,4) (-5,-12) (1e-300,-1e-300) (1e+300,Infinity) (Infinity,1e+300) (NaN,NaN) (7 rows) SELECT p.f1, p.f1 <-> point '(0,0)' AS dist FROM POINT_TBL p ORDER BY dist; f1 | dist -------------------+---------------------- (0,0) | 0 (1e-300,-1e-300) | 1.4142135623731e-300 (-3,4) | 5 (-10,0) | 10 (-5,-12) | 13 (10,10) | 14.142135623731 (5.1,34.5) | 34.8749193547455 (1e+300,Infinity) | Infinity (Infinity,1e+300) | Infinity (NaN,NaN) | NaN (10 rows) SELECT p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist FROM POINT_TBL p1, POINT_TBL p2 ORDER BY dist, p1.f1[0], p2.f1[0]; point1 | point2 | dist -------------------+-------------------+---------------------- (-10,0) | (-10,0) | 0 (-5,-12) | (-5,-12) | 0 (-3,4) | (-3,4) | 0 (0,0) | (0,0) | 0 (1e-300,-1e-300) | (1e-300,-1e-300) | 0 (5.1,34.5) | (5.1,34.5) | 0 (10,10) | (10,10) | 0 (0,0) | (1e-300,-1e-300) | 1.4142135623731e-300 (1e-300,-1e-300) | (0,0) | 1.4142135623731e-300 (-3,4) | (0,0) | 5 (-3,4) | (1e-300,-1e-300) | 5 (0,0) | (-3,4) | 5 (1e-300,-1e-300) | (-3,4) | 5 (-10,0) | (-3,4) | 8.06225774829855 (-3,4) | (-10,0) | 8.06225774829855 (-10,0) | (0,0) | 10 (-10,0) | (1e-300,-1e-300) | 10 (0,0) | (-10,0) | 10 (1e-300,-1e-300) | (-10,0) | 10 (-10,0) | (-5,-12) | 13 (-5,-12) | (-10,0) | 13 (-5,-12) | (0,0) | 13 (-5,-12) | (1e-300,-1e-300) | 13 (0,0) | (-5,-12) | 13 (1e-300,-1e-300) | (-5,-12) | 13 (0,0) | (10,10) | 14.142135623731 (1e-300,-1e-300) | (10,10) | 14.142135623731 (10,10) | (0,0) | 14.142135623731 (10,10) | (1e-300,-1e-300) | 14.142135623731 (-3,4) | (10,10) | 14.3178210632764 (10,10) | (-3,4) | 14.3178210632764 (-5,-12) | (-3,4) | 16.1245154965971 (-3,4) | (-5,-12) | 16.1245154965971 (-10,0) | (10,10) | 22.3606797749979 (10,10) | (-10,0) | 22.3606797749979 (5.1,34.5) | (10,10) | 24.9851956166046 (10,10) | (5.1,34.5) | 24.9851956166046 (-5,-12) | (10,10) | 26.6270539113887 (10,10) | (-5,-12) | 26.6270539113887 (-3,4) | (5.1,34.5) | 31.5572495632937 (5.1,34.5) | (-3,4) | 31.5572495632937 (0,0) | (5.1,34.5) | 34.8749193547455 (1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455 (5.1,34.5) | (0,0) | 34.8749193547455 (5.1,34.5) | (1e-300,-1e-300) | 34.8749193547455 (-10,0) | (5.1,34.5) | 37.6597928831267 (5.1,34.5) | (-10,0) | 37.6597928831267 (-5,-12) | (5.1,34.5) | 47.5842410888311 (5.1,34.5) | (-5,-12) | 47.5842410888311 (-10,0) | (1e+300,Infinity) | Infinity (-10,0) | (Infinity,1e+300) | Infinity (-5,-12) | (1e+300,Infinity) | Infinity (-5,-12) | (Infinity,1e+300) | Infinity (-3,4) | (1e+300,Infinity) | Infinity (-3,4) | (Infinity,1e+300) | Infinity (0,0) | (1e+300,Infinity) | Infinity (0,0) | (Infinity,1e+300) | Infinity (1e-300,-1e-300) | (1e+300,Infinity) | Infinity (1e-300,-1e-300) | (Infinity,1e+300) | Infinity (5.1,34.5) | (1e+300,Infinity) | Infinity (5.1,34.5) | (Infinity,1e+300) | Infinity (10,10) | (1e+300,Infinity) | Infinity (10,10) | (Infinity,1e+300) | Infinity (1e+300,Infinity) | (-10,0) | Infinity (1e+300,Infinity) | (-5,-12) | Infinity (1e+300,Infinity) | (-3,4) | Infinity (1e+300,Infinity) | (0,0) | Infinity (1e+300,Infinity) | (1e-300,-1e-300) | Infinity (1e+300,Infinity) | (5.1,34.5) | Infinity (1e+300,Infinity) | (10,10) | Infinity (1e+300,Infinity) | (Infinity,1e+300) | Infinity (Infinity,1e+300) | (-10,0) | Infinity (Infinity,1e+300) | (-5,-12) | Infinity (Infinity,1e+300) | (-3,4) | Infinity (Infinity,1e+300) | (0,0) | Infinity (Infinity,1e+300) | (1e-300,-1e-300) | Infinity (Infinity,1e+300) | (5.1,34.5) | Infinity (Infinity,1e+300) | (10,10) | Infinity (Infinity,1e+300) | (1e+300,Infinity) | Infinity (-10,0) | (NaN,NaN) | NaN (-5,-12) | (NaN,NaN) | NaN (-3,4) | (NaN,NaN) | NaN (0,0) | (NaN,NaN) | NaN (1e-300,-1e-300) | (NaN,NaN) | NaN (5.1,34.5) | (NaN,NaN) | NaN (10,10) | (NaN,NaN) | NaN (1e+300,Infinity) | (1e+300,Infinity) | NaN (1e+300,Infinity) | (NaN,NaN) | NaN (Infinity,1e+300) | (Infinity,1e+300) | NaN (Infinity,1e+300) | (NaN,NaN) | NaN (NaN,NaN) | (-10,0) | NaN (NaN,NaN) | (-5,-12) | NaN (NaN,NaN) | (-3,4) | NaN (NaN,NaN) | (0,0) | NaN (NaN,NaN) | (1e-300,-1e-300) | NaN (NaN,NaN) | (5.1,34.5) | NaN (NaN,NaN) | (10,10) | NaN (NaN,NaN) | (1e+300,Infinity) | NaN (NaN,NaN) | (Infinity,1e+300) | NaN (NaN,NaN) | (NaN,NaN) | NaN (100 rows) SELECT p1.f1 AS point1, p2.f1 AS point2 FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3; point1 | point2 -------------------+------------------- (0,0) | (-10,0) (0,0) | (-3,4) (0,0) | (5.1,34.5) (0,0) | (-5,-12) (0,0) | (1e+300,Infinity) (0,0) | (Infinity,1e+300) (0,0) | (NaN,NaN) (0,0) | (10,10) (-10,0) | (0,0) (-10,0) | (-3,4) (-10,0) | (5.1,34.5) (-10,0) | (-5,-12) (-10,0) | (1e-300,-1e-300) (-10,0) | (1e+300,Infinity) (-10,0) | (Infinity,1e+300) (-10,0) | (NaN,NaN) (-10,0) | (10,10) (-3,4) | (0,0) (-3,4) | (-10,0) (-3,4) | (5.1,34.5) (-3,4) | (-5,-12) (-3,4) | (1e-300,-1e-300) (-3,4) | (1e+300,Infinity) (-3,4) | (Infinity,1e+300) (-3,4) | (NaN,NaN) (-3,4) | (10,10) (5.1,34.5) | (0,0) (5.1,34.5) | (-10,0) (5.1,34.5) | (-3,4) (5.1,34.5) | (-5,-12) (5.1,34.5) | (1e-300,-1e-300) (5.1,34.5) | (1e+300,Infinity) (5.1,34.5) | (Infinity,1e+300) (5.1,34.5) | (NaN,NaN) (5.1,34.5) | (10,10) (-5,-12) | (0,0) (-5,-12) | (-10,0) (-5,-12) | (-3,4) (-5,-12) | (5.1,34.5) (-5,-12) | (1e-300,-1e-300) (-5,-12) | (1e+300,Infinity) (-5,-12) | (Infinity,1e+300) (-5,-12) | (NaN,NaN) (-5,-12) | (10,10) (1e-300,-1e-300) | (-10,0) (1e-300,-1e-300) | (-3,4) (1e-300,-1e-300) | (5.1,34.5) (1e-300,-1e-300) | (-5,-12) (1e-300,-1e-300) | (1e+300,Infinity) (1e-300,-1e-300) | (Infinity,1e+300) (1e-300,-1e-300) | (NaN,NaN) (1e-300,-1e-300) | (10,10) (1e+300,Infinity) | (0,0) (1e+300,Infinity) | (-10,0) (1e+300,Infinity) | (-3,4) (1e+300,Infinity) | (5.1,34.5) (1e+300,Infinity) | (-5,-12) (1e+300,Infinity) | (1e-300,-1e-300) (1e+300,Infinity) | (1e+300,Infinity) (1e+300,Infinity) | (Infinity,1e+300) (1e+300,Infinity) | (NaN,NaN) (1e+300,Infinity) | (10,10) (Infinity,1e+300) | (0,0) (Infinity,1e+300) | (-10,0) (Infinity,1e+300) | (-3,4) (Infinity,1e+300) | (5.1,34.5) (Infinity,1e+300) | (-5,-12) (Infinity,1e+300) | (1e-300,-1e-300) (Infinity,1e+300) | (1e+300,Infinity) (Infinity,1e+300) | (Infinity,1e+300) (Infinity,1e+300) | (NaN,NaN) (Infinity,1e+300) | (10,10) (NaN,NaN) | (0,0) (NaN,NaN) | (-10,0) (NaN,NaN) | (-3,4) (NaN,NaN) | (5.1,34.5) (NaN,NaN) | (-5,-12) (NaN,NaN) | (1e-300,-1e-300) (NaN,NaN) | (1e+300,Infinity) (NaN,NaN) | (Infinity,1e+300) (NaN,NaN) | (NaN,NaN) (NaN,NaN) | (10,10) (10,10) | (0,0) (10,10) | (-10,0) (10,10) | (-3,4) (10,10) | (5.1,34.5) (10,10) | (-5,-12) (10,10) | (1e-300,-1e-300) (10,10) | (1e+300,Infinity) (10,10) | (Infinity,1e+300) (10,10) | (NaN,NaN) (91 rows) -- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 SELECT p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 ORDER BY distance, p1.f1[0], p2.f1[0]; point1 | point2 | distance -------------------+-------------------+------------------ (-3,4) | (0,0) | 5 (-3,4) | (1e-300,-1e-300) | 5 (-10,0) | (-3,4) | 8.06225774829855 (-10,0) | (0,0) | 10 (-10,0) | (1e-300,-1e-300) | 10 (-10,0) | (-5,-12) | 13 (-5,-12) | (0,0) | 13 (-5,-12) | (1e-300,-1e-300) | 13 (0,0) | (10,10) | 14.142135623731 (1e-300,-1e-300) | (10,10) | 14.142135623731 (-3,4) | (10,10) | 14.3178210632764 (-5,-12) | (-3,4) | 16.1245154965971 (-10,0) | (10,10) | 22.3606797749979 (5.1,34.5) | (10,10) | 24.9851956166046 (-5,-12) | (10,10) | 26.6270539113887 (-3,4) | (5.1,34.5) | 31.5572495632937 (0,0) | (5.1,34.5) | 34.8749193547455 (1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455 (-10,0) | (5.1,34.5) | 37.6597928831267 (-5,-12) | (5.1,34.5) | 47.5842410888311 (-10,0) | (1e+300,Infinity) | Infinity (-10,0) | (Infinity,1e+300) | Infinity (-5,-12) | (1e+300,Infinity) | Infinity (-5,-12) | (Infinity,1e+300) | Infinity (-3,4) | (1e+300,Infinity) | Infinity (-3,4) | (Infinity,1e+300) | Infinity (0,0) | (1e+300,Infinity) | Infinity (0,0) | (Infinity,1e+300) | Infinity (1e-300,-1e-300) | (1e+300,Infinity) | Infinity (1e-300,-1e-300) | (Infinity,1e+300) | Infinity (5.1,34.5) | (1e+300,Infinity) | Infinity (5.1,34.5) | (Infinity,1e+300) | Infinity (10,10) | (1e+300,Infinity) | Infinity (10,10) | (Infinity,1e+300) | Infinity (1e+300,Infinity) | (Infinity,1e+300) | Infinity (35 rows) -- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 SELECT p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 |>> p2.f1 ORDER BY distance; point1 | point2 | distance -------------------+-------------------+------------------ (-3,4) | (0,0) | 5 (-3,4) | (1e-300,-1e-300) | 5 (-10,0) | (-5,-12) | 13 (5.1,34.5) | (10,10) | 24.9851956166046 (1e+300,Infinity) | (Infinity,1e+300) | Infinity (5 rows) -- Test that GiST indexes provide same behavior as sequential scan CREATE TEMP TABLE point_gist_tbl(f1 point); INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000); CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1); INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)'); SET enable_seqscan TO true; SET enable_indexscan TO false; SET enable_bitmapscan TO false; SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; count ------- 1002 (1 row) SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; count ------- 1 (1 row) SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; count ------- 1 (1 row) SET enable_seqscan TO false; SET enable_indexscan TO true; SET enable_bitmapscan TO true; SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; count ------- 1002 (1 row) SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; count ------- 1 (1 row) SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; count ------- 1 (1 row) RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan;