1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
--
-- BOX
--
--
-- box logic
-- o
-- 3 o--|X
-- | o|
-- 2 +-+-+ |
-- | | | |
-- 1 | o-+-o
-- | |
-- 0 +---+
--
-- 0 1 2 3
--
-- boxes are specified by two points, given by four floats x1,y1,x2,y2
CREATE TABLE BOX_TBL (f1 box);
INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)');
INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)');
-- degenerate cases where the box is a line or a point
-- note that lines and points boxes all have zero area
INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)');
INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)');
-- badly formatted box inputs
INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)');
INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad');
SELECT '' AS four, * FROM BOX_TBL;
SELECT '' AS four, b.*, area(b.f1) as barea
FROM BOX_TBL b;
-- overlap
SELECT '' AS three, b.f1
FROM BOX_TBL b
WHERE b.f1 && box '(2.5,2.5,1.0,1.0)';
-- left-or-overlap (x only)
SELECT '' AS two, b1.*
FROM BOX_TBL b1
WHERE b1.f1 &< box '(2.0,2.0,2.5,2.5)';
-- right-or-overlap (x only)
SELECT '' AS two, b1.*
FROM BOX_TBL b1
WHERE b1.f1 &> box '(2.0,2.0,2.5,2.5)';
-- left of
SELECT '' AS two, b.f1
FROM BOX_TBL b
WHERE b.f1 << box '(3.0,3.0,5.0,5.0)';
-- area <=
SELECT '' AS four, b.f1
FROM BOX_TBL b
WHERE b.f1 <= box '(3.0,3.0,5.0,5.0)';
-- area <
SELECT '' AS two, b.f1
FROM BOX_TBL b
WHERE b.f1 < box '(3.0,3.0,5.0,5.0)';
-- area =
SELECT '' AS two, b.f1
FROM BOX_TBL b
WHERE b.f1 = box '(3.0,3.0,5.0,5.0)';
-- area >
SELECT '' AS two, b.f1
FROM BOX_TBL b -- zero area
WHERE b.f1 > box '(3.5,3.0,4.5,3.0)';
-- area >=
SELECT '' AS four, b.f1
FROM BOX_TBL b -- zero area
WHERE b.f1 >= box '(3.5,3.0,4.5,3.0)';
-- right of
SELECT '' AS two, b.f1
FROM BOX_TBL b
WHERE box '(3.0,3.0,5.0,5.0)' >> b.f1;
-- contained in
SELECT '' AS three, b.f1
FROM BOX_TBL b
WHERE b.f1 <@ box '(0,0,3,3)';
-- contains
SELECT '' AS three, b.f1
FROM BOX_TBL b
WHERE box '(0,0,3,3)' @> b.f1;
-- box equality
SELECT '' AS one, b.f1
FROM BOX_TBL b
WHERE box '(1,1,3,3)' ~= b.f1;
-- center of box, left unary operator
SELECT '' AS four, @@(b1.f1) AS p
FROM BOX_TBL b1;
-- wholly-contained
SELECT '' AS one, b1.*, b2.*
FROM BOX_TBL b1, BOX_TBL b2
WHERE b1.f1 @> b2.f1 and not b1.f1 ~= b2.f1;
SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL;
|
