17 -- boxes are specified by two points, given by four floats x1,y1,x2,y2
18 CREATE TABLE BOX_TBL (f1 box);
19 INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)');
20 INSERT 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
23 INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)');
24 INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)');
25 -- badly formatted box inputs
26 INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)');
27 ERROR: invalid input syntax for type box: "(2.3, 4.5)"
28 INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad');
29 ERROR: invalid input syntax for type box: "asdfasdf(ad"
30 SELECT '' AS four, * FROM BOX_TBL;
32 ------+---------------------
39 SELECT '' AS four, b.*, area(b.f1) as barea
42 ------+---------------------+-------
45 | (2.5,3.5),(2.5,2.5) | 0
50 SELECT '' AS three, b.f1
52 WHERE b.f1 && box '(2.5,2.5,1.0,1.0)';
54 -------+---------------------
60 -- left-or-overlap (x only)
61 SELECT '' AS two, b1.*
63 WHERE b1.f1 &< box '(2.0,2.0,2.5,2.5)';
65 -----+---------------------
70 -- right-or-overlap (x only)
71 SELECT '' AS two, b1.*
73 WHERE b1.f1 &> box '(2.0,2.0,2.5,2.5)';
75 -----+---------------------
81 SELECT '' AS two, b.f1
83 WHERE b.f1 << box '(3.0,3.0,5.0,5.0)';
85 -----+---------------------
91 SELECT '' AS four, b.f1
93 WHERE b.f1 <= box '(3.0,3.0,5.0,5.0)';
95 ------+---------------------
103 SELECT '' AS two, b.f1
105 WHERE b.f1 < box '(3.0,3.0,5.0,5.0)';
107 -----+---------------------
108 | (2.5,3.5),(2.5,2.5)
113 SELECT '' AS two, b.f1
115 WHERE b.f1 = box '(3.0,3.0,5.0,5.0)';
123 SELECT '' AS two, b.f1
124 FROM BOX_TBL b -- zero area
125 WHERE b.f1 > box '(3.5,3.0,4.5,3.0)';
133 SELECT '' AS four, b.f1
134 FROM BOX_TBL b -- zero area
135 WHERE b.f1 >= box '(3.5,3.0,4.5,3.0)';
137 ------+---------------------
140 | (2.5,3.5),(2.5,2.5)
145 SELECT '' AS two, b.f1
147 WHERE box '(3.0,3.0,5.0,5.0)' >> b.f1;
149 -----+---------------------
151 | (2.5,3.5),(2.5,2.5)
155 SELECT '' AS three, b.f1
157 WHERE b.f1 <@ box '(0,0,3,3)';
159 -------+-------------
166 SELECT '' AS three, b.f1
168 WHERE box '(0,0,3,3)' @> b.f1;
170 -------+-------------
177 SELECT '' AS one, b.f1
179 WHERE box '(1,1,3,3)' ~= b.f1;
185 -- center of box, left unary operator
186 SELECT '' AS four, @@(b1.f1) AS p
197 SELECT '' AS one, b1.*, b2.*
198 FROM BOX_TBL b1, BOX_TBL b2
199 WHERE b1.f1 @> b2.f1 and not b1.f1 ~= b2.f1;
201 -----+-------------+-------------
202 | (3,3),(1,1) | (3,3),(3,3)
205 SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL;
206 four | height | width
207 ------+--------+-------