polygon.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 187 行

--
-- POLYGON
--
-- polygon logic
--
-- 3	      o
--	      |
-- 2	    + |
--	   /  |
-- 1	  # o +
--       /    |
-- 0	#-----o-+
--
--	0 1 2 3 4
--
CREATE TABLE POLYGON_TBL(f1 polygon);
INSERT INTO POLYGON_TBL(f1) VALUES ('(2.0,0.0),(2.0,4.0),(0.0,0.0)');
INSERT INTO POLYGON_TBL(f1) VALUES ('(3.0,1.0),(3.0,3.0),(1.0,0.0)');
-- degenerate polygons 
INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0)');
INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,1.0),(0.0,1.0)');
-- bad polygon input strings 
INSERT INTO POLYGON_TBL(f1) VALUES ('0.0');
ERROR:  invalid input syntax for type polygon: "0.0"
INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0 0.0');
ERROR:  invalid input syntax for type polygon: "(0.0 0.0"
INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2)');
ERROR:  invalid input syntax for type polygon: "(0,1,2)"
INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2,3');
ERROR:  invalid input syntax for type polygon: "(0,1,2,3"
INSERT INTO POLYGON_TBL(f1) VALUES ('asdf');
ERROR:  invalid input syntax for type polygon: "asdf"
SELECT '' AS four, * FROM POLYGON_TBL;
 four |         f1          
------+---------------------
      | ((2,0),(2,4),(0,0))
      | ((3,1),(3,3),(1,0))
      | ((0,0))
      | ((0,1),(0,1))
(4 rows)

-- overlap 
SELECT '' AS three, p.*
   FROM POLYGON_TBL p
   WHERE p.f1 && '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 three |         f1          
-------+---------------------
       | ((2,0),(2,4),(0,0))
       | ((3,1),(3,3),(1,0))
(2 rows)

-- left overlap 
SELECT '' AS four, p.* 
   FROM POLYGON_TBL p
   WHERE p.f1 &< '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 four |         f1          
------+---------------------
      | ((2,0),(2,4),(0,0))
      | ((3,1),(3,3),(1,0))
      | ((0,0))
      | ((0,1),(0,1))
(4 rows)

-- right overlap 
SELECT '' AS two, p.* 
   FROM POLYGON_TBL p
   WHERE p.f1 &> '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 two |         f1          
-----+---------------------
     | ((3,1),(3,3),(1,0))
(1 row)

-- left of 
SELECT '' AS one, p.*
   FROM POLYGON_TBL p
   WHERE p.f1 << '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 one |      f1       
-----+---------------
     | ((0,0))
     | ((0,1),(0,1))
(2 rows)

-- right of 
SELECT '' AS zero, p.*
   FROM POLYGON_TBL p
   WHERE p.f1 >> '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 zero | f1 
------+----
(0 rows)

-- contained 
SELECT '' AS one, p.* 
   FROM POLYGON_TBL p
   WHERE p.f1 <@ polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 one |         f1          
-----+---------------------
     | ((3,1),(3,3),(1,0))
(1 row)

-- same 
SELECT '' AS one, p.*
   FROM POLYGON_TBL p
   WHERE p.f1 ~= polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 one |         f1          
-----+---------------------
     | ((3,1),(3,3),(1,0))
(1 row)

-- contains 
SELECT '' AS one, p.*
   FROM POLYGON_TBL p
   WHERE p.f1 @> polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)';
 one |         f1          
-----+---------------------
     | ((3,1),(3,3),(1,0))
(1 row)

--
-- polygon logic
--
-- 3	      o
--	      |
-- 2	    + |
--	   /  |
-- 1	  / o +
--       /    |
-- 0	+-----o-+
--
--	0 1 2 3 4
--
-- left of 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' << polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- left overlap 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' << polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS true;
 true 
------
 f
(1 row)

-- right overlap 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' &> polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- right of 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' >> polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- contained in 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' <@ polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- contains 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' @> polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- same 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' ~= polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS false;
 false 
-------
 f
(1 row)

-- overlap 
SELECT polygon '(2.0,0.0),(2.0,4.0),(0.0,0.0)' && polygon '(3.0,1.0),(3.0,3.0),(1.0,0.0)' AS true;
 true 
------
 t
(1 row)