point.sql 1.79 KB
Newer Older
Marc G. Fournier's avatar
Marc G. Fournier committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
--  ************testing built-in type 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');

INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0');

INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)');

INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0');


25
SELECT '' AS six, POINT_TBL.*;
Marc G. Fournier's avatar
Marc G. Fournier committed
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

-- left of 
SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 !< '(0.0, 0.0)';

-- right of 
SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !> p.f1;

-- above 
SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !^ p.f1;

-- below 
SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 !| '(0.0, 0.0)';

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

-- point in box 
43
SELECT '' AS three, p.* FROM POINT_TBL p
Marc G. Fournier's avatar
Marc G. Fournier committed
44 45 46 47 48 49 50 51
   WHERE p.f1 ===> '(0,0,100,100)';

SELECT '' AS three, p.* FROM POINT_TBL p
   WHERE not on_pb(p.f1,'(0,0,100,100)'::box);

SELECT '' AS two, p.* FROM POINT_TBL p
   WHERE on_ppath(p.f1,'(0,3,0,0,-10,0,-10,10)'::path);

52
SELECT '' AS six, p.f1, p.f1 <===> '(0,0)'::point AS dist FROM POINT_TBL p;
Marc G. Fournier's avatar
Marc G. Fournier committed
53

54
SELECT '' AS thirtysix, p1.f1, p2.f1, p1.f1 <===> p2.f1 AS dist
Marc G. Fournier's avatar
Marc G. Fournier committed
55 56
   FROM POINT_TBL p1, POINT_TBL p2;

57
SELECT '' AS thirty, p1.f1, p2.f1
Marc G. Fournier's avatar
Marc G. Fournier committed
58 59 60
   FROM POINT_TBL p1, POINT_TBL p2
   WHERE (p1.f1 <===> p2.f1) > 3;

61
SELECT '' AS fifteen, p1.f1, p2.f1
Marc G. Fournier's avatar
Marc G. Fournier committed
62 63 64 65
   FROM POINT_TBL p1, POINT_TBL p2
   WHERE (p1.f1 <===> p2.f1) > 3 and 
	p1.f1 !< p2.f1;

66
SELECT '' AS three, p1.f1, p2.f1 
Marc G. Fournier's avatar
Marc G. Fournier committed
67 68 69 70 71
   FROM POINT_TBL p1, POINT_TBL p2 
   WHERE (p1.f1 <===> p2.f1) > 3 and 
	p1.f1 !< p2.f1 and
	p1.f1 !^ p2.f1;