point.out

PostgreSQL7.4.6 for Linux

--
-- POINT
--
CREATE TABLE POINT_TBL(f1 point);
INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)');
INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)');
-- bad format points 
INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf');
ERROR:  invalid input syntax for type point: "asdfasdf"
INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0');
INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)');
ERROR:  invalid input syntax for type point: "(10.0 10.0)"
INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0');
ERROR:  invalid input syntax for type point: "(10.0,10.0"
SELECT '' AS six, POINT_TBL.*;
 six |     f1     
-----+------------
     | (0,0)
     | (-10,0)
     | (-3,4)
     | (5.1,34.5)
     | (-5,-12)
     | (10,10)
(6 rows)

-- left of 
SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)';
 three |    f1    
-------+----------
       | (-10,0)
       | (-3,4)
       | (-5,-12)
(3 rows)

-- right of 
SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1;
 three |    f1    
-------+----------
       | (-10,0)
       | (-3,4)
       | (-5,-12)
(3 rows)

-- above 
SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >^ p.f1;
 one |    f1    
-----+----------
     | (-5,-12)
(1 row)

-- below 
SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 <^ '(0.0, 0.0)';
 one |    f1    
-----+----------
     | (-5,-12)
(1 row)

-- equal 
SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)';
 one |     f1     
-----+------------
     | (5.1,34.5)
(1 row)

-- point in box 
SELECT '' AS three, p.* FROM POINT_TBL p
   WHERE p.f1 @ box '(0,0,100,100)';
 three |     f1     
-------+------------
       | (0,0)
       | (5.1,34.5)
       | (10,10)
(3 rows)

SELECT '' AS three, p.* FROM POINT_TBL p
   WHERE not p.f1 @ box '(0,0,100,100)';
 three |    f1    
-------+----------
       | (-10,0)
       | (-3,4)
       | (-5,-12)
(3 rows)

SELECT '' AS two, p.* FROM POINT_TBL p
   WHERE p.f1 @ path '[(0,0),(-10,0),(-10,10)]';
 two |   f1    
-----+---------
     | (0,0)
     | (-10,0)
(2 rows)

SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist
   FROM POINT_TBL p
   ORDER BY dist;
 six |     f1     |       dist       
-----+------------+------------------
     | (0,0)      |                0
     | (-3,4)     |                5
     | (-10,0)    |               10
     | (-5,-12)   |               13
     | (10,10)    |  14.142135623731
     | (5.1,34.5) | 34.8749193547455
(6 rows)

SET geqo TO 'off';
SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist
   FROM POINT_TBL p1, POINT_TBL p2
   ORDER BY dist, point1 using <<, point2 using <<;
 thirtysix |   point1   |   point2   |       dist       
-----------+------------+------------+------------------
           | (-10,0)    | (-10,0)    |                0
           | (-5,-12)   | (-5,-12)   |                0
           | (-3,4)     | (-3,4)     |                0
           | (0,0)      | (0,0)      |                0
           | (5.1,34.5) | (5.1,34.5) |                0
           | (10,10)    | (10,10)    |                0
           | (-3,4)     | (0,0)      |                5
           | (0,0)      | (-3,4)     |                5
           | (-10,0)    | (-3,4)     | 8.06225774829855
           | (-3,4)     | (-10,0)    | 8.06225774829855
           | (-10,0)    | (0,0)      |               10
           | (0,0)      | (-10,0)    |               10
           | (-10,0)    | (-5,-12)   |               13
           | (-5,-12)   | (-10,0)    |               13
           | (-5,-12)   | (0,0)      |               13
           | (0,0)      | (-5,-12)   |               13
           | (0,0)      | (10,10)    |  14.142135623731
           | (10,10)    | (0,0)      |  14.142135623731
           | (-3,4)     | (10,10)    | 14.3178210632764
           | (10,10)    | (-3,4)     | 14.3178210632764
           | (-5,-12)   | (-3,4)     | 16.1245154965971
           | (-3,4)     | (-5,-12)   | 16.1245154965971
           | (-10,0)    | (10,10)    | 22.3606797749979
           | (10,10)    | (-10,0)    | 22.3606797749979
           | (5.1,34.5) | (10,10)    | 24.9851956166046
           | (10,10)    | (5.1,34.5) | 24.9851956166046
           | (-5,-12)   | (10,10)    | 26.6270539113887
           | (10,10)    | (-5,-12)   | 26.6270539113887
           | (-3,4)     | (5.1,34.5) | 31.5572495632937
           | (5.1,34.5) | (-3,4)     | 31.5572495632937
           | (0,0)      | (5.1,34.5) | 34.8749193547455
           | (5.1,34.5) | (0,0)      | 34.8749193547455
           | (-10,0)    | (5.1,34.5) | 37.6597928831267
           | (5.1,34.5) | (-10,0)    | 37.6597928831267
           | (-5,-12)   | (5.1,34.5) | 47.5842410888311
           | (5.1,34.5) | (-5,-12)   | 47.5842410888311
(36 rows)

SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2
   FROM POINT_TBL p1, POINT_TBL p2
   WHERE (p1.f1 <-> p2.f1) > 3;
 thirty |   point1   |   point2   
--------+------------+------------
        | (0,0)      | (-10,0)
        | (0,0)      | (-3,4)
        | (0,0)      | (5.1,34.5)
        | (0,0)      | (-5,-12)
        | (0,0)      | (10,10)
        | (-10,0)    | (0,0)
        | (-10,0)    | (-3,4)
        | (-10,0)    | (5.1,34.5)
        | (-10,0)    | (-5,-12)
        | (-10,0)    | (10,10)
        | (-3,4)     | (0,0)
        | (-3,4)     | (-10,0)
        | (-3,4)     | (5.1,34.5)
        | (-3,4)     | (-5,-12)
        | (-3,4)     | (10,10)
        | (5.1,34.5) | (0,0)
        | (5.1,34.5) | (-10,0)
        | (5.1,34.5) | (-3,4)
        | (5.1,34.5) | (-5,-12)
        | (5.1,34.5) | (10,10)
        | (-5,-12)   | (0,0)
        | (-5,-12)   | (-10,0)
        | (-5,-12)   | (-3,4)
        | (-5,-12)   | (5.1,34.5)
        | (-5,-12)   | (10,10)
        | (10,10)    | (0,0)
        | (10,10)    | (-10,0)
        | (10,10)    | (-3,4)
        | (10,10)    | (5.1,34.5)
        | (10,10)    | (-5,-12)
(30 rows)

-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance
   FROM POINT_TBL p1, POINT_TBL p2
   WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1
   ORDER BY distance, point1 using <<, point2 using <<;
 fifteen |   point1   |   point2   |     distance     
---------+------------+------------+------------------
         | (-3,4)     | (0,0)      |                5
         | (-10,0)    | (-3,4)     | 8.06225774829855
         | (-10,0)    | (0,0)      |               10
         | (-10,0)    | (-5,-12)   |               13
         | (-5,-12)   | (0,0)      |               13
         | (0,0)      | (10,10)    |  14.142135623731
         | (-3,4)     | (10,10)    | 14.3178210632764
         | (-5,-12)   | (-3,4)     | 16.1245154965971
         | (-10,0)    | (10,10)    | 22.3606797749979
         | (5.1,34.5) | (10,10)    | 24.9851956166046
         | (-5,-12)   | (10,10)    | 26.6270539113887
         | (-3,4)     | (5.1,34.5) | 31.5572495632937
         | (0,0)      | (5.1,34.5) | 34.8749193547455
         | (-10,0)    | (5.1,34.5) | 37.6597928831267
         | (-5,-12)   | (5.1,34.5) | 47.5842410888311
(15 rows)

-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance
   FROM POINT_TBL p1, POINT_TBL p2 
   WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 >^ p2.f1
   ORDER BY distance;
 three |   point1   |  point2  |     distance     
-------+------------+----------+------------------
       | (-3,4)     | (0,0)    |                5
       | (-10,0)    | (-5,-12) |               13
       | (5.1,34.5) | (10,10)  | 24.9851956166046
(3 rows)

RESET geqo;