1-- 2-- BOX 3-- 4-- 5-- box logic 6-- o 7-- 3 o--|X 8-- | o| 9-- 2 +-+-+ | 10-- | | | | 11-- 1 | o-+-o 12-- | | 13-- 0 +---+ 14-- 15-- 0 1 2 3 16-- 17-- boxes are specified by two points, given by four floats x1,y1,x2,y2 18CREATE TABLE BOX_TBL (f1 box); 19INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)'); 20INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)'); 21-- degenerate cases where the box is a line or a point 22-- note that lines and points boxes all have zero area 23INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)'); 24INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)'); 25-- badly formatted box inputs 26INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)'); 27ERROR: invalid input syntax for type box: "(2.3, 4.5)" 28LINE 1: INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)'); 29 ^ 30INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad'); 31ERROR: invalid input syntax for type box: "asdfasdf(ad" 32LINE 1: INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad'); 33 ^ 34SELECT '' AS four, * FROM BOX_TBL; 35 four | f1 36------+--------------------- 37 | (2,2),(0,0) 38 | (3,3),(1,1) 39 | (2.5,3.5),(2.5,2.5) 40 | (3,3),(3,3) 41(4 rows) 42 43SELECT '' AS four, b.*, area(b.f1) as barea 44 FROM BOX_TBL b; 45 four | f1 | barea 46------+---------------------+------- 47 | (2,2),(0,0) | 4 48 | (3,3),(1,1) | 4 49 | (2.5,3.5),(2.5,2.5) | 0 50 | (3,3),(3,3) | 0 51(4 rows) 52 53-- overlap 54SELECT '' AS three, b.f1 55 FROM BOX_TBL b 56 WHERE b.f1 && box '(2.5,2.5,1.0,1.0)'; 57 three | f1 58-------+--------------------- 59 | (2,2),(0,0) 60 | (3,3),(1,1) 61 | (2.5,3.5),(2.5,2.5) 62(3 rows) 63 64-- left-or-overlap (x only) 65SELECT '' AS two, b1.* 66 FROM BOX_TBL b1 67 WHERE b1.f1 &< box '(2.0,2.0,2.5,2.5)'; 68 two | f1 69-----+--------------------- 70 | (2,2),(0,0) 71 | (2.5,3.5),(2.5,2.5) 72(2 rows) 73 74-- right-or-overlap (x only) 75SELECT '' AS two, b1.* 76 FROM BOX_TBL b1 77 WHERE b1.f1 &> box '(2.0,2.0,2.5,2.5)'; 78 two | f1 79-----+--------------------- 80 | (2.5,3.5),(2.5,2.5) 81 | (3,3),(3,3) 82(2 rows) 83 84-- left of 85SELECT '' AS two, b.f1 86 FROM BOX_TBL b 87 WHERE b.f1 << box '(3.0,3.0,5.0,5.0)'; 88 two | f1 89-----+--------------------- 90 | (2,2),(0,0) 91 | (2.5,3.5),(2.5,2.5) 92(2 rows) 93 94-- area <= 95SELECT '' AS four, b.f1 96 FROM BOX_TBL b 97 WHERE b.f1 <= box '(3.0,3.0,5.0,5.0)'; 98 four | f1 99------+--------------------- 100 | (2,2),(0,0) 101 | (3,3),(1,1) 102 | (2.5,3.5),(2.5,2.5) 103 | (3,3),(3,3) 104(4 rows) 105 106-- area < 107SELECT '' AS two, b.f1 108 FROM BOX_TBL b 109 WHERE b.f1 < box '(3.0,3.0,5.0,5.0)'; 110 two | f1 111-----+--------------------- 112 | (2.5,3.5),(2.5,2.5) 113 | (3,3),(3,3) 114(2 rows) 115 116-- area = 117SELECT '' AS two, b.f1 118 FROM BOX_TBL b 119 WHERE b.f1 = box '(3.0,3.0,5.0,5.0)'; 120 two | f1 121-----+------------- 122 | (2,2),(0,0) 123 | (3,3),(1,1) 124(2 rows) 125 126-- area > 127SELECT '' AS two, b.f1 128 FROM BOX_TBL b -- zero area 129 WHERE b.f1 > box '(3.5,3.0,4.5,3.0)'; 130 two | f1 131-----+------------- 132 | (2,2),(0,0) 133 | (3,3),(1,1) 134(2 rows) 135 136-- area >= 137SELECT '' AS four, b.f1 138 FROM BOX_TBL b -- zero area 139 WHERE b.f1 >= box '(3.5,3.0,4.5,3.0)'; 140 four | f1 141------+--------------------- 142 | (2,2),(0,0) 143 | (3,3),(1,1) 144 | (2.5,3.5),(2.5,2.5) 145 | (3,3),(3,3) 146(4 rows) 147 148-- right of 149SELECT '' AS two, b.f1 150 FROM BOX_TBL b 151 WHERE box '(3.0,3.0,5.0,5.0)' >> b.f1; 152 two | f1 153-----+--------------------- 154 | (2,2),(0,0) 155 | (2.5,3.5),(2.5,2.5) 156(2 rows) 157 158-- contained in 159SELECT '' AS three, b.f1 160 FROM BOX_TBL b 161 WHERE b.f1 <@ box '(0,0,3,3)'; 162 three | f1 163-------+------------- 164 | (2,2),(0,0) 165 | (3,3),(1,1) 166 | (3,3),(3,3) 167(3 rows) 168 169-- contains 170SELECT '' AS three, b.f1 171 FROM BOX_TBL b 172 WHERE box '(0,0,3,3)' @> b.f1; 173 three | f1 174-------+------------- 175 | (2,2),(0,0) 176 | (3,3),(1,1) 177 | (3,3),(3,3) 178(3 rows) 179 180-- box equality 181SELECT '' AS one, b.f1 182 FROM BOX_TBL b 183 WHERE box '(1,1,3,3)' ~= b.f1; 184 one | f1 185-----+------------- 186 | (3,3),(1,1) 187(1 row) 188 189-- center of box, left unary operator 190SELECT '' AS four, @@(b1.f1) AS p 191 FROM BOX_TBL b1; 192 four | p 193------+--------- 194 | (1,1) 195 | (2,2) 196 | (2.5,3) 197 | (3,3) 198(4 rows) 199 200-- wholly-contained 201SELECT '' AS one, b1.*, b2.* 202 FROM BOX_TBL b1, BOX_TBL b2 203 WHERE b1.f1 @> b2.f1 and not b1.f1 ~= b2.f1; 204 one | f1 | f1 205-----+-------------+------------- 206 | (3,3),(1,1) | (3,3),(3,3) 207(1 row) 208 209SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL; 210 four | height | width 211------+--------+------- 212 | 2 | 2 213 | 2 | 2 214 | 1 | 0 215 | 0 | 0 216(4 rows) 217 218-- 219-- Test the SP-GiST index 220-- 221CREATE TEMPORARY TABLE box_temp (f1 box); 222INSERT INTO box_temp 223 SELECT box(point(i, i), point(i * 2, i * 2)) 224 FROM generate_series(1, 50) AS i; 225CREATE INDEX box_spgist ON box_temp USING spgist (f1); 226INSERT INTO box_temp 227 VALUES (NULL), 228 ('(0,0)(0,100)'), 229 ('(-3,4.3333333333)(40,1)'), 230 ('(0,100)(0,infinity)'), 231 ('(-infinity,0)(0,infinity)'), 232 ('(-infinity,-infinity)(infinity,infinity)'); 233SET enable_seqscan = false; 234SELECT * FROM box_temp WHERE f1 << '(10,20),(30,40)'; 235 f1 236---------------------------- 237 (2,2),(1,1) 238 (4,4),(2,2) 239 (6,6),(3,3) 240 (8,8),(4,4) 241 (0,100),(0,0) 242 (0,Infinity),(0,100) 243 (0,Infinity),(-Infinity,0) 244(7 rows) 245 246EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 << '(10,20),(30,40)'; 247 QUERY PLAN 248---------------------------------------------- 249 Index Only Scan using box_spgist on box_temp 250 Index Cond: (f1 << '(30,40),(10,20)'::box) 251(2 rows) 252 253SELECT * FROM box_temp WHERE f1 &< '(10,4.333334),(5,100)'; 254 f1 255---------------------------- 256 (2,2),(1,1) 257 (4,4),(2,2) 258 (6,6),(3,3) 259 (8,8),(4,4) 260 (10,10),(5,5) 261 (0,100),(0,0) 262 (0,Infinity),(0,100) 263 (0,Infinity),(-Infinity,0) 264(8 rows) 265 266EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &< '(10,4.333334),(5,100)'; 267 QUERY PLAN 268---------------------------------------------------- 269 Index Only Scan using box_spgist on box_temp 270 Index Cond: (f1 &< '(10,100),(5,4.333334)'::box) 271(2 rows) 272 273SELECT * FROM box_temp WHERE f1 && '(15,20),(25,30)'; 274 f1 275------------------------------------------- 276 (20,20),(10,10) 277 (22,22),(11,11) 278 (24,24),(12,12) 279 (26,26),(13,13) 280 (28,28),(14,14) 281 (30,30),(15,15) 282 (32,32),(16,16) 283 (34,34),(17,17) 284 (36,36),(18,18) 285 (38,38),(19,19) 286 (40,40),(20,20) 287 (42,42),(21,21) 288 (44,44),(22,22) 289 (46,46),(23,23) 290 (48,48),(24,24) 291 (50,50),(25,25) 292 (Infinity,Infinity),(-Infinity,-Infinity) 293(17 rows) 294 295EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 && '(15,20),(25,30)'; 296 QUERY PLAN 297---------------------------------------------- 298 Index Only Scan using box_spgist on box_temp 299 Index Cond: (f1 && '(25,30),(15,20)'::box) 300(2 rows) 301 302SELECT * FROM box_temp WHERE f1 &> '(40,30),(45,50)'; 303 f1 304------------------- 305 (80,80),(40,40) 306 (82,82),(41,41) 307 (84,84),(42,42) 308 (86,86),(43,43) 309 (88,88),(44,44) 310 (90,90),(45,45) 311 (92,92),(46,46) 312 (94,94),(47,47) 313 (96,96),(48,48) 314 (98,98),(49,49) 315 (100,100),(50,50) 316(11 rows) 317 318EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &> '(40,30),(45,50)'; 319 QUERY PLAN 320---------------------------------------------- 321 Index Only Scan using box_spgist on box_temp 322 Index Cond: (f1 &> '(45,50),(40,30)'::box) 323(2 rows) 324 325SELECT * FROM box_temp WHERE f1 >> '(30,40),(40,30)'; 326 f1 327------------------- 328 (82,82),(41,41) 329 (84,84),(42,42) 330 (86,86),(43,43) 331 (88,88),(44,44) 332 (90,90),(45,45) 333 (92,92),(46,46) 334 (94,94),(47,47) 335 (96,96),(48,48) 336 (98,98),(49,49) 337 (100,100),(50,50) 338(10 rows) 339 340EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 >> '(30,40),(40,30)'; 341 QUERY PLAN 342---------------------------------------------- 343 Index Only Scan using box_spgist on box_temp 344 Index Cond: (f1 >> '(40,40),(30,30)'::box) 345(2 rows) 346 347SELECT * FROM box_temp WHERE f1 <<| '(10,4.33334),(5,100)'; 348 f1 349-------------------------- 350 (2,2),(1,1) 351 (4,4),(2,2) 352 (40,4.3333333333),(-3,1) 353(3 rows) 354 355EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 <<| '(10,4.33334),(5,100)'; 356 QUERY PLAN 357---------------------------------------------------- 358 Index Only Scan using box_spgist on box_temp 359 Index Cond: (f1 <<| '(10,100),(5,4.33334)'::box) 360(2 rows) 361 362SELECT * FROM box_temp WHERE f1 &<| '(10,4.3333334),(5,1)'; 363 f1 364-------------------------- 365 (2,2),(1,1) 366 (4,4),(2,2) 367 (40,4.3333333333),(-3,1) 368(3 rows) 369 370EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &<| '(10,4.3333334),(5,1)'; 371 QUERY PLAN 372---------------------------------------------------- 373 Index Only Scan using box_spgist on box_temp 374 Index Cond: (f1 &<| '(10,4.3333334),(5,1)'::box) 375(2 rows) 376 377SELECT * FROM box_temp WHERE f1 |&> '(49.99,49.99),(49.99,49.99)'; 378 f1 379---------------------- 380 (100,100),(50,50) 381 (0,Infinity),(0,100) 382(2 rows) 383 384EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 |&> '(49.99,49.99),(49.99,49.99)'; 385 QUERY PLAN 386----------------------------------------------------------- 387 Index Only Scan using box_spgist on box_temp 388 Index Cond: (f1 |&> '(49.99,49.99),(49.99,49.99)'::box) 389(2 rows) 390 391SELECT * FROM box_temp WHERE f1 |>> '(37,38),(39,40)'; 392 f1 393---------------------- 394 (82,82),(41,41) 395 (84,84),(42,42) 396 (86,86),(43,43) 397 (88,88),(44,44) 398 (90,90),(45,45) 399 (92,92),(46,46) 400 (94,94),(47,47) 401 (96,96),(48,48) 402 (98,98),(49,49) 403 (100,100),(50,50) 404 (0,Infinity),(0,100) 405(11 rows) 406 407EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 |>> '(37,38),(39,40)'; 408 QUERY PLAN 409----------------------------------------------- 410 Index Only Scan using box_spgist on box_temp 411 Index Cond: (f1 |>> '(39,40),(37,38)'::box) 412(2 rows) 413 414SELECT * FROM box_temp WHERE f1 @> '(10,11),(15,16)'; 415 f1 416------------------------------------------- 417 (16,16),(8,8) 418 (18,18),(9,9) 419 (20,20),(10,10) 420 (Infinity,Infinity),(-Infinity,-Infinity) 421(4 rows) 422 423EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 @> '(10,11),(15,15)'; 424 QUERY PLAN 425---------------------------------------------- 426 Index Only Scan using box_spgist on box_temp 427 Index Cond: (f1 @> '(15,15),(10,11)'::box) 428(2 rows) 429 430SELECT * FROM box_temp WHERE f1 <@ '(10,15),(30,35)'; 431 f1 432----------------- 433 (30,30),(15,15) 434(1 row) 435 436EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 <@ '(10,15),(30,35)'; 437 QUERY PLAN 438---------------------------------------------- 439 Index Only Scan using box_spgist on box_temp 440 Index Cond: (f1 <@ '(30,35),(10,15)'::box) 441(2 rows) 442 443SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; 444 f1 445----------------- 446 (40,40),(20,20) 447(1 row) 448 449EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; 450 QUERY PLAN 451---------------------------------------------- 452 Index Only Scan using box_spgist on box_temp 453 Index Cond: (f1 ~= '(40,40),(20,20)'::box) 454(2 rows) 455 456RESET enable_seqscan; 457DROP INDEX box_spgist; 458-- 459-- Test the SP-GiST index on the larger volume of data 460-- 461CREATE TABLE quad_box_tbl (b box); 462INSERT INTO quad_box_tbl 463 SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) 464 FROM generate_series(1, 100) x, 465 generate_series(1, 100) y; 466-- insert repeating data to test allTheSame 467INSERT INTO quad_box_tbl 468 SELECT '((200, 300),(210, 310))' 469 FROM generate_series(1, 1000); 470INSERT INTO quad_box_tbl 471 VALUES 472 (NULL), 473 (NULL), 474 ('((-infinity,-infinity),(infinity,infinity))'), 475 ('((-infinity,100),(-infinity,500))'), 476 ('((-infinity,-infinity),(700,infinity))'); 477CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); 478SET enable_seqscan = OFF; 479SET enable_indexscan = ON; 480SET enable_bitmapscan = ON; 481SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))'; 482 count 483------- 484 901 485(1 row) 486 487SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))'; 488 count 489------- 490 3901 491(1 row) 492 493SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))'; 494 count 495------- 496 1653 497(1 row) 498 499SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))'; 500 count 501------- 502 10100 503(1 row) 504 505SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; 506 count 507------- 508 7000 509(1 row) 510 511SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; 512 count 513------- 514 7000 515(1 row) 516 517SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))'; 518 count 519------- 520 1900 521(1 row) 522 523SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))'; 524 count 525------- 526 5901 527(1 row) 528 529SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))'; 530 count 531------- 532 9100 533(1 row) 534 535SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))'; 536 count 537------- 538 5000 539(1 row) 540 541SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))'; 542 count 543------- 544 1003 545(1 row) 546 547SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))'; 548 count 549------- 550 1600 551(1 row) 552 553SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))'; 554 count 555------- 556 1 557(1 row) 558 559RESET enable_seqscan; 560RESET enable_indexscan; 561RESET enable_bitmapscan; 562