1-- 2-- BOX 3-- 4 5-- 6-- box logic 7-- o 8-- 3 o--|X 9-- | o| 10-- 2 +-+-+ | 11-- | | | | 12-- 1 | o-+-o 13-- | | 14-- 0 +---+ 15-- 16-- 0 1 2 3 17-- 18 19-- boxes are specified by two points, given by four floats x1,y1,x2,y2 20 21 22CREATE TABLE BOX_TBL (f1 box); 23 24INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)'); 25 26INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)'); 27 28INSERT INTO BOX_TBL (f1) VALUES ('((-8, 2), (-2, -10))'); 29 30 31-- degenerate cases where the box is a line or a point 32-- note that lines and points boxes all have zero area 33INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)'); 34 35INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)'); 36 37-- badly formatted box inputs 38INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)'); 39 40INSERT INTO BOX_TBL (f1) VALUES ('[1, 2, 3, 4)'); 41 42INSERT INTO BOX_TBL (f1) VALUES ('(1, 2, 3, 4]'); 43 44INSERT INTO BOX_TBL (f1) VALUES ('(1, 2, 3, 4) x'); 45 46INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad'); 47 48 49SELECT '' AS four, * FROM BOX_TBL; 50 51SELECT '' AS four, b.*, area(b.f1) as barea 52 FROM BOX_TBL b; 53 54-- overlap 55SELECT '' AS three, b.f1 56 FROM BOX_TBL b 57 WHERE b.f1 && box '(2.5,2.5,1.0,1.0)'; 58 59-- left-or-overlap (x only) 60SELECT '' AS two, b1.* 61 FROM BOX_TBL b1 62 WHERE b1.f1 &< box '(2.0,2.0,2.5,2.5)'; 63 64-- right-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 69-- left of 70SELECT '' AS two, b.f1 71 FROM BOX_TBL b 72 WHERE b.f1 << box '(3.0,3.0,5.0,5.0)'; 73 74-- area <= 75SELECT '' AS four, b.f1 76 FROM BOX_TBL b 77 WHERE b.f1 <= box '(3.0,3.0,5.0,5.0)'; 78 79-- area < 80SELECT '' AS two, b.f1 81 FROM BOX_TBL b 82 WHERE b.f1 < box '(3.0,3.0,5.0,5.0)'; 83 84-- area = 85SELECT '' AS two, b.f1 86 FROM BOX_TBL b 87 WHERE b.f1 = box '(3.0,3.0,5.0,5.0)'; 88 89-- area > 90SELECT '' AS two, b.f1 91 FROM BOX_TBL b -- zero area 92 WHERE b.f1 > box '(3.5,3.0,4.5,3.0)'; 93 94-- area >= 95SELECT '' AS four, b.f1 96 FROM BOX_TBL b -- zero area 97 WHERE b.f1 >= box '(3.5,3.0,4.5,3.0)'; 98 99-- right of 100SELECT '' AS two, b.f1 101 FROM BOX_TBL b 102 WHERE box '(3.0,3.0,5.0,5.0)' >> b.f1; 103 104-- contained in 105SELECT '' AS three, b.f1 106 FROM BOX_TBL b 107 WHERE b.f1 <@ box '(0,0,3,3)'; 108 109-- contains 110SELECT '' AS three, b.f1 111 FROM BOX_TBL b 112 WHERE box '(0,0,3,3)' @> b.f1; 113 114-- box equality 115SELECT '' AS one, b.f1 116 FROM BOX_TBL b 117 WHERE box '(1,1,3,3)' ~= b.f1; 118 119-- center of box, left unary operator 120SELECT '' AS four, @@(b1.f1) AS p 121 FROM BOX_TBL b1; 122 123-- wholly-contained 124SELECT '' AS one, b1.*, b2.* 125 FROM BOX_TBL b1, BOX_TBL b2 126 WHERE b1.f1 @> b2.f1 and not b1.f1 ~= b2.f1; 127 128SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL; 129 130-- 131-- Test the SP-GiST index 132-- 133 134CREATE TEMPORARY TABLE box_temp (f1 box); 135 136INSERT INTO box_temp 137 SELECT box(point(i, i), point(i * 2, i * 2)) 138 FROM generate_series(1, 50) AS i; 139 140CREATE INDEX box_spgist ON box_temp USING spgist (f1); 141 142INSERT INTO box_temp 143 VALUES (NULL), 144 ('(0,0)(0,100)'), 145 ('(-3,4.3333333333)(40,1)'), 146 ('(0,100)(0,infinity)'), 147 ('(-infinity,0)(0,infinity)'), 148 ('(-infinity,-infinity)(infinity,infinity)'); 149 150SET enable_seqscan = false; 151 152SELECT * FROM box_temp WHERE f1 << '(10,20),(30,40)'; 153EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 << '(10,20),(30,40)'; 154 155SELECT * FROM box_temp WHERE f1 &< '(10,4.333334),(5,100)'; 156EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &< '(10,4.333334),(5,100)'; 157 158SELECT * FROM box_temp WHERE f1 && '(15,20),(25,30)'; 159EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 && '(15,20),(25,30)'; 160 161SELECT * FROM box_temp WHERE f1 &> '(40,30),(45,50)'; 162EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &> '(40,30),(45,50)'; 163 164SELECT * FROM box_temp WHERE f1 >> '(30,40),(40,30)'; 165EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 >> '(30,40),(40,30)'; 166 167SELECT * FROM box_temp WHERE f1 <<| '(10,4.33334),(5,100)'; 168EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 <<| '(10,4.33334),(5,100)'; 169 170SELECT * FROM box_temp WHERE f1 &<| '(10,4.3333334),(5,1)'; 171EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 &<| '(10,4.3333334),(5,1)'; 172 173SELECT * FROM box_temp WHERE f1 |&> '(49.99,49.99),(49.99,49.99)'; 174EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 |&> '(49.99,49.99),(49.99,49.99)'; 175 176SELECT * FROM box_temp WHERE f1 |>> '(37,38),(39,40)'; 177EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 |>> '(37,38),(39,40)'; 178 179SELECT * FROM box_temp WHERE f1 @> '(10,11),(15,16)'; 180EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 @> '(10,11),(15,15)'; 181 182SELECT * FROM box_temp WHERE f1 <@ '(10,15),(30,35)'; 183EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 <@ '(10,15),(30,35)'; 184 185SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; 186EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)'; 187 188RESET enable_seqscan; 189 190DROP INDEX box_spgist; 191 192-- 193-- Test the SP-GiST index on the larger volume of data 194-- 195CREATE TABLE quad_box_tbl (id int, b box); 196 197INSERT INTO quad_box_tbl 198 SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5)) 199 FROM generate_series(1, 100) x, 200 generate_series(1, 100) y; 201 202-- insert repeating data to test allTheSame 203INSERT INTO quad_box_tbl 204 SELECT i, '((200, 300),(210, 310))' 205 FROM generate_series(10001, 11000) AS i; 206 207INSERT INTO quad_box_tbl 208VALUES 209 (11001, NULL), 210 (11002, NULL), 211 (11003, '((-infinity,-infinity),(infinity,infinity))'), 212 (11004, '((-infinity,100),(-infinity,500))'), 213 (11005, '((-infinity,-infinity),(700,infinity))'); 214 215CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b); 216 217-- get reference results for ORDER BY distance from seq scan 218SET enable_seqscan = ON; 219SET enable_indexscan = OFF; 220SET enable_bitmapscan = OFF; 221 222CREATE TABLE quad_box_tbl_ord_seq1 AS 223SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 224FROM quad_box_tbl; 225 226CREATE TABLE quad_box_tbl_ord_seq2 AS 227SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 228FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; 229 230SET enable_seqscan = OFF; 231SET enable_indexscan = ON; 232SET enable_bitmapscan = ON; 233 234SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))'; 235SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))'; 236SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))'; 237SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))'; 238SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; 239SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))'; 240SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))'; 241SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))'; 242SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))'; 243SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))'; 244SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))'; 245SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))'; 246SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))'; 247 248-- test ORDER BY distance 249SET enable_indexscan = ON; 250SET enable_bitmapscan = OFF; 251 252EXPLAIN (COSTS OFF) 253SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 254FROM quad_box_tbl; 255 256CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS 257SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 258FROM quad_box_tbl; 259 260SELECT * 261FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx 262 ON seq.n = idx.n AND seq.id = idx.id AND 263 (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) 264WHERE seq.id IS NULL OR idx.id IS NULL; 265 266 267EXPLAIN (COSTS OFF) 268SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 269FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; 270 271CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS 272SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id 273FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; 274 275SELECT * 276FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx 277 ON seq.n = idx.n AND seq.id = idx.id AND 278 (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL) 279WHERE seq.id IS NULL OR idx.id IS NULL; 280 281RESET enable_seqscan; 282RESET enable_indexscan; 283RESET enable_bitmapscan; 284