1-- 2-- POINT 3-- 4CREATE TABLE POINT_TBL(f1 point); 5INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); 6INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); 7INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); 8INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); 9INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); 10-- bad format points 11INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); 12ERROR: invalid input syntax for type point: "asdfasdf" 13LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); 14 ^ 15INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); 16INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); 17ERROR: invalid input syntax for type point: "(10.0 10.0)" 18LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); 19 ^ 20INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); 21ERROR: invalid input syntax for type point: "(10.0,10.0" 22LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); 23 ^ 24SELECT '' AS six, * FROM POINT_TBL; 25 six | f1 26-----+------------ 27 | (0,0) 28 | (-10,0) 29 | (-3,4) 30 | (5.1,34.5) 31 | (-5,-12) 32 | (10,10) 33(6 rows) 34 35-- left of 36SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)'; 37 three | f1 38-------+---------- 39 | (-10,0) 40 | (-3,4) 41 | (-5,-12) 42(3 rows) 43 44-- right of 45SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1; 46 three | f1 47-------+---------- 48 | (-10,0) 49 | (-3,4) 50 | (-5,-12) 51(3 rows) 52 53-- above 54SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >^ p.f1; 55 one | f1 56-----+---------- 57 | (-5,-12) 58(1 row) 59 60-- below 61SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 <^ '(0.0, 0.0)'; 62 one | f1 63-----+---------- 64 | (-5,-12) 65(1 row) 66 67-- equal 68SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)'; 69 one | f1 70-----+------------ 71 | (5.1,34.5) 72(1 row) 73 74-- point in box 75SELECT '' AS three, p.* FROM POINT_TBL p 76 WHERE p.f1 <@ box '(0,0,100,100)'; 77 three | f1 78-------+------------ 79 | (0,0) 80 | (5.1,34.5) 81 | (10,10) 82(3 rows) 83 84SELECT '' AS three, p.* FROM POINT_TBL p 85 WHERE box '(0,0,100,100)' @> p.f1; 86 three | f1 87-------+------------ 88 | (0,0) 89 | (5.1,34.5) 90 | (10,10) 91(3 rows) 92 93SELECT '' AS three, p.* FROM POINT_TBL p 94 WHERE not p.f1 <@ box '(0,0,100,100)'; 95 three | f1 96-------+---------- 97 | (-10,0) 98 | (-3,4) 99 | (-5,-12) 100(3 rows) 101 102SELECT '' AS two, p.* FROM POINT_TBL p 103 WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]'; 104 two | f1 105-----+--------- 106 | (0,0) 107 | (-10,0) 108(2 rows) 109 110SELECT '' AS three, p.* FROM POINT_TBL p 111 WHERE not box '(0,0,100,100)' @> p.f1; 112 three | f1 113-------+---------- 114 | (-10,0) 115 | (-3,4) 116 | (-5,-12) 117(3 rows) 118 119SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist 120 FROM POINT_TBL p 121 ORDER BY dist; 122 six | f1 | dist 123-----+------------+------------------ 124 | (0,0) | 0 125 | (-3,4) | 5 126 | (-10,0) | 10 127 | (-5,-12) | 13 128 | (10,10) | 14.142135623731 129 | (5.1,34.5) | 34.8749193547455 130(6 rows) 131 132SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist 133 FROM POINT_TBL p1, POINT_TBL p2 134 ORDER BY dist, p1.f1[0], p2.f1[0]; 135 thirtysix | point1 | point2 | dist 136-----------+------------+------------+------------------ 137 | (-10,0) | (-10,0) | 0 138 | (-5,-12) | (-5,-12) | 0 139 | (-3,4) | (-3,4) | 0 140 | (0,0) | (0,0) | 0 141 | (5.1,34.5) | (5.1,34.5) | 0 142 | (10,10) | (10,10) | 0 143 | (-3,4) | (0,0) | 5 144 | (0,0) | (-3,4) | 5 145 | (-10,0) | (-3,4) | 8.06225774829855 146 | (-3,4) | (-10,0) | 8.06225774829855 147 | (-10,0) | (0,0) | 10 148 | (0,0) | (-10,0) | 10 149 | (-10,0) | (-5,-12) | 13 150 | (-5,-12) | (-10,0) | 13 151 | (-5,-12) | (0,0) | 13 152 | (0,0) | (-5,-12) | 13 153 | (0,0) | (10,10) | 14.142135623731 154 | (10,10) | (0,0) | 14.142135623731 155 | (-3,4) | (10,10) | 14.3178210632764 156 | (10,10) | (-3,4) | 14.3178210632764 157 | (-5,-12) | (-3,4) | 16.1245154965971 158 | (-3,4) | (-5,-12) | 16.1245154965971 159 | (-10,0) | (10,10) | 22.3606797749979 160 | (10,10) | (-10,0) | 22.3606797749979 161 | (5.1,34.5) | (10,10) | 24.9851956166046 162 | (10,10) | (5.1,34.5) | 24.9851956166046 163 | (-5,-12) | (10,10) | 26.6270539113887 164 | (10,10) | (-5,-12) | 26.6270539113887 165 | (-3,4) | (5.1,34.5) | 31.5572495632937 166 | (5.1,34.5) | (-3,4) | 31.5572495632937 167 | (0,0) | (5.1,34.5) | 34.8749193547455 168 | (5.1,34.5) | (0,0) | 34.8749193547455 169 | (-10,0) | (5.1,34.5) | 37.6597928831267 170 | (5.1,34.5) | (-10,0) | 37.6597928831267 171 | (-5,-12) | (5.1,34.5) | 47.5842410888311 172 | (5.1,34.5) | (-5,-12) | 47.5842410888311 173(36 rows) 174 175SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 176 FROM POINT_TBL p1, POINT_TBL p2 177 WHERE (p1.f1 <-> p2.f1) > 3; 178 thirty | point1 | point2 179--------+------------+------------ 180 | (0,0) | (-10,0) 181 | (0,0) | (-3,4) 182 | (0,0) | (5.1,34.5) 183 | (0,0) | (-5,-12) 184 | (0,0) | (10,10) 185 | (-10,0) | (0,0) 186 | (-10,0) | (-3,4) 187 | (-10,0) | (5.1,34.5) 188 | (-10,0) | (-5,-12) 189 | (-10,0) | (10,10) 190 | (-3,4) | (0,0) 191 | (-3,4) | (-10,0) 192 | (-3,4) | (5.1,34.5) 193 | (-3,4) | (-5,-12) 194 | (-3,4) | (10,10) 195 | (5.1,34.5) | (0,0) 196 | (5.1,34.5) | (-10,0) 197 | (5.1,34.5) | (-3,4) 198 | (5.1,34.5) | (-5,-12) 199 | (5.1,34.5) | (10,10) 200 | (-5,-12) | (0,0) 201 | (-5,-12) | (-10,0) 202 | (-5,-12) | (-3,4) 203 | (-5,-12) | (5.1,34.5) 204 | (-5,-12) | (10,10) 205 | (10,10) | (0,0) 206 | (10,10) | (-10,0) 207 | (10,10) | (-3,4) 208 | (10,10) | (5.1,34.5) 209 | (10,10) | (-5,-12) 210(30 rows) 211 212-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 213SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance 214 FROM POINT_TBL p1, POINT_TBL p2 215 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 216 ORDER BY distance, p1.f1[0], p2.f1[0]; 217 fifteen | point1 | point2 | distance 218---------+------------+------------+------------------ 219 | (-3,4) | (0,0) | 5 220 | (-10,0) | (-3,4) | 8.06225774829855 221 | (-10,0) | (0,0) | 10 222 | (-10,0) | (-5,-12) | 13 223 | (-5,-12) | (0,0) | 13 224 | (0,0) | (10,10) | 14.142135623731 225 | (-3,4) | (10,10) | 14.3178210632764 226 | (-5,-12) | (-3,4) | 16.1245154965971 227 | (-10,0) | (10,10) | 22.3606797749979 228 | (5.1,34.5) | (10,10) | 24.9851956166046 229 | (-5,-12) | (10,10) | 26.6270539113887 230 | (-3,4) | (5.1,34.5) | 31.5572495632937 231 | (0,0) | (5.1,34.5) | 34.8749193547455 232 | (-10,0) | (5.1,34.5) | 37.6597928831267 233 | (-5,-12) | (5.1,34.5) | 47.5842410888311 234(15 rows) 235 236-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 237SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance 238 FROM POINT_TBL p1, POINT_TBL p2 239 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 >^ p2.f1 240 ORDER BY distance; 241 three | point1 | point2 | distance 242-------+------------+----------+------------------ 243 | (-3,4) | (0,0) | 5 244 | (-10,0) | (-5,-12) | 13 245 | (5.1,34.5) | (10,10) | 24.9851956166046 246(3 rows) 247 248-- Test that GiST indexes provide same behavior as sequential scan 249CREATE TEMP TABLE point_gist_tbl(f1 point); 250INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000); 251CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1); 252INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)'); 253SET enable_seqscan TO true; 254SET enable_indexscan TO false; 255SET enable_bitmapscan TO false; 256SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; 257 count 258------- 259 1002 260(1 row) 261 262SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; 263 count 264------- 265 1 266(1 row) 267 268SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; 269 count 270------- 271 1 272(1 row) 273 274SET enable_seqscan TO false; 275SET enable_indexscan TO true; 276SET enable_bitmapscan TO true; 277SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; 278 count 279------- 280 1002 281(1 row) 282 283SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; 284 count 285------- 286 1 287(1 row) 288 289SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; 290 count 291------- 292 1 293(1 row) 294 295RESET enable_seqscan; 296RESET enable_indexscan; 297RESET enable_bitmapscan; 298