box.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- 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)');
ERROR:  invalid input syntax for type box: "(2.3, 4.5)"
INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad');
ERROR:  invalid input syntax for type box: "asdfasdf(ad"
SELECT '' AS four, * FROM BOX_TBL;
 four |         f1          
------+---------------------
      | (2,2),(0,0)
      | (3,3),(1,1)
      | (2.5,3.5),(2.5,2.5)
      | (3,3),(3,3)
(4 rows)

SELECT '' AS four, b.*, area(b.f1) as barea
   FROM BOX_TBL b;
 four |         f1          | barea 
------+---------------------+-------
      | (2,2),(0,0)         |     4
      | (3,3),(1,1)         |     4
      | (2.5,3.5),(2.5,2.5) |     0
      | (3,3),(3,3)         |     0
(4 rows)

-- overlap 
SELECT '' AS three, b.f1
   FROM BOX_TBL b  
   WHERE b.f1 && box '(2.5,2.5,1.0,1.0)';
 three |         f1          
-------+---------------------
       | (2,2),(0,0)
       | (3,3),(1,1)
       | (2.5,3.5),(2.5,2.5)
(3 rows)

-- 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)';
 two |         f1          
-----+---------------------
     | (2,2),(0,0)
     | (2.5,3.5),(2.5,2.5)
(2 rows)

-- 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)';
 two |         f1          
-----+---------------------
     | (2.5,3.5),(2.5,2.5)
     | (3,3),(3,3)
(2 rows)

-- left of 
SELECT '' AS two, b.f1
   FROM BOX_TBL b
   WHERE b.f1 << box '(3.0,3.0,5.0,5.0)';
 two |         f1          
-----+---------------------
     | (2,2),(0,0)
     | (2.5,3.5),(2.5,2.5)
(2 rows)

-- area <= 
SELECT '' AS four, b.f1
   FROM BOX_TBL b
   WHERE b.f1 <= box '(3.0,3.0,5.0,5.0)';
 four |         f1          
------+---------------------
      | (2,2),(0,0)
      | (3,3),(1,1)
      | (2.5,3.5),(2.5,2.5)
      | (3,3),(3,3)
(4 rows)

-- area < 
SELECT '' AS two, b.f1
   FROM BOX_TBL b
   WHERE b.f1 < box '(3.0,3.0,5.0,5.0)';
 two |         f1          
-----+---------------------
     | (2.5,3.5),(2.5,2.5)
     | (3,3),(3,3)
(2 rows)

-- area = 
SELECT '' AS two, b.f1
   FROM BOX_TBL b
   WHERE b.f1 = box '(3.0,3.0,5.0,5.0)';
 two |     f1      
-----+-------------
     | (2,2),(0,0)
     | (3,3),(1,1)
(2 rows)

-- area > 
SELECT '' AS two, b.f1
   FROM BOX_TBL b				-- zero area 
   WHERE b.f1 > box '(3.5,3.0,4.5,3.0)';	
 two |     f1      
-----+-------------
     | (2,2),(0,0)
     | (3,3),(1,1)
(2 rows)

-- area >= 
SELECT '' AS four, b.f1
   FROM BOX_TBL b				-- zero area 
   WHERE b.f1 >= box '(3.5,3.0,4.5,3.0)';
 four |         f1          
------+---------------------
      | (2,2),(0,0)
      | (3,3),(1,1)
      | (2.5,3.5),(2.5,2.5)
      | (3,3),(3,3)
(4 rows)

-- right of 
SELECT '' AS two, b.f1
   FROM BOX_TBL b
   WHERE box '(3.0,3.0,5.0,5.0)' >> b.f1;
 two |         f1          
-----+---------------------
     | (2,2),(0,0)
     | (2.5,3.5),(2.5,2.5)
(2 rows)

-- contained in 
SELECT '' AS three, b.f1
   FROM BOX_TBL b
   WHERE b.f1 @ box '(0,0,3,3)';
 three |     f1      
-------+-------------
       | (2,2),(0,0)
       | (3,3),(1,1)
       | (3,3),(3,3)
(3 rows)

-- contains 
SELECT '' AS three, b.f1
   FROM BOX_TBL b
   WHERE box '(0,0,3,3)' ~ b.f1;
 three |     f1      
-------+-------------
       | (2,2),(0,0)
       | (3,3),(1,1)
       | (3,3),(3,3)
(3 rows)

-- box equality 
SELECT '' AS one, b.f1
   FROM BOX_TBL b
   WHERE box '(1,1,3,3)' ~= b.f1;
 one |     f1      
-----+-------------
     | (3,3),(1,1)
(1 row)

-- center of box, left unary operator 
SELECT '' AS four, @@(b1.f1) AS p
   FROM BOX_TBL b1;
 four |    p    
------+---------
      | (1,1)
      | (2,2)
      | (2.5,3)
      | (3,3)
(4 rows)

-- 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;
 one |     f1      |     f1      
-----+-------------+-------------
     | (3,3),(1,1) | (3,3),(3,3)
(1 row)

SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL;
 four | height | width 
------+--------+-------
      |      2 |     2
      |      2 |     2
      |      1 |     0
      |      0 |     0
(4 rows)