1--
2-- POINT
3--
4
5CREATE TABLE POINT_TBL(f1 point);
6
7INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)');
8
9INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)');
10
11INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)');
12
13INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)');
14
15INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)');
16
17-- bad format points
18INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf');
19
20INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0');
21
22INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)');
23
24INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0');
25
26
27SELECT '' AS six, * FROM POINT_TBL;
28
29-- left of
30SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)';
31
32-- right of
33SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1;
34
35-- above
36SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >^ p.f1;
37
38-- below
39SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 <^ '(0.0, 0.0)';
40
41-- equal
42SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)';
43
44-- point in box
45SELECT '' AS three, p.* FROM POINT_TBL p
46   WHERE p.f1 <@ box '(0,0,100,100)';
47
48SELECT '' AS three, p.* FROM POINT_TBL p
49   WHERE box '(0,0,100,100)' @> p.f1;
50
51SELECT '' AS three, p.* FROM POINT_TBL p
52   WHERE not p.f1 <@ box '(0,0,100,100)';
53
54SELECT '' AS two, p.* FROM POINT_TBL p
55   WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]';
56
57SELECT '' AS three, p.* FROM POINT_TBL p
58   WHERE not box '(0,0,100,100)' @> p.f1;
59
60SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist
61   FROM POINT_TBL p
62   ORDER BY dist;
63
64SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist
65   FROM POINT_TBL p1, POINT_TBL p2
66   ORDER BY dist, p1.f1[0], p2.f1[0];
67
68SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2
69   FROM POINT_TBL p1, POINT_TBL p2
70   WHERE (p1.f1 <-> p2.f1) > 3;
71
72-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
73SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance
74   FROM POINT_TBL p1, POINT_TBL p2
75   WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1
76   ORDER BY distance, p1.f1[0], p2.f1[0];
77
78-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
79SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance
80   FROM POINT_TBL p1, POINT_TBL p2
81   WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 >^ p2.f1
82   ORDER BY distance;
83
84-- Test that GiST indexes provide same behavior as sequential scan
85CREATE TEMP TABLE point_gist_tbl(f1 point);
86INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000);
87CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1);
88INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)');
89SET enable_seqscan TO true;
90SET enable_indexscan TO false;
91SET enable_bitmapscan TO false;
92SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point;
93SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box;
94SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point;
95SET enable_seqscan TO false;
96SET enable_indexscan TO true;
97SET enable_bitmapscan TO true;
98SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point;
99SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box;
100SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point;
101RESET enable_seqscan;
102RESET enable_indexscan;
103RESET enable_bitmapscan;
104