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