1--
2-- POLYGON
3--
4-- polygon logic
5--
6
7CREATE TABLE POLYGON_TBL(f1 polygon);
8
9
10INSERT INTO POLYGON_TBL(f1) VALUES ('(2.0,0.0),(2.0,4.0),(0.0,0.0)');
11
12INSERT INTO POLYGON_TBL(f1) VALUES ('(3.0,1.0),(3.0,3.0),(1.0,0.0)');
13
14INSERT INTO POLYGON_TBL(f1) VALUES ('(1,2),(3,4),(5,6),(7,8)');
15INSERT INTO POLYGON_TBL(f1) VALUES ('(7,8),(5,6),(3,4),(1,2)'); -- Reverse
16INSERT INTO POLYGON_TBL(f1) VALUES ('(1,2),(7,8),(5,6),(3,-4)');
17
18-- degenerate polygons
19INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0)');
20
21INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,1.0),(0.0,1.0)');
22
23-- bad polygon input strings
24INSERT INTO POLYGON_TBL(f1) VALUES ('0.0');
25
26INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0 0.0');
27
28INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2)');
29
30INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2,3');
31
32INSERT INTO POLYGON_TBL(f1) VALUES ('asdf');
33
34
35SELECT '' AS four, * FROM POLYGON_TBL;
36
37--
38-- Test the SP-GiST index
39--
40
41CREATE TABLE quad_poly_tbl (id int, p polygon);
42
43INSERT INTO quad_poly_tbl
44	SELECT (x - 1) * 100 + y, polygon(circle(point(x * 10, y * 10), 1 + (x + y) % 10))
45	FROM generate_series(1, 100) x,
46		 generate_series(1, 100) y;
47
48INSERT INTO quad_poly_tbl
49	SELECT i, polygon '((200, 300),(210, 310),(230, 290))'
50	FROM generate_series(10001, 11000) AS i;
51
52INSERT INTO quad_poly_tbl
53	VALUES
54		(11001, NULL),
55		(11002, NULL),
56		(11003, NULL);
57
58CREATE INDEX quad_poly_tbl_idx ON quad_poly_tbl USING spgist(p);
59
60-- get reference results for ORDER BY distance from seq scan
61SET enable_seqscan = ON;
62SET enable_indexscan = OFF;
63SET enable_bitmapscan = OFF;
64
65CREATE TEMP TABLE quad_poly_tbl_ord_seq2 AS
66SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
67FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
68
69-- check results from index scan
70SET enable_seqscan = OFF;
71SET enable_indexscan = OFF;
72SET enable_bitmapscan = ON;
73
74EXPLAIN (COSTS OFF)
75SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))';
76SELECT count(*) FROM quad_poly_tbl WHERE p << polygon '((300,300),(400,600),(600,500),(700,200))';
77
78EXPLAIN (COSTS OFF)
79SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))';
80SELECT count(*) FROM quad_poly_tbl WHERE p &< polygon '((300,300),(400,600),(600,500),(700,200))';
81
82EXPLAIN (COSTS OFF)
83SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))';
84SELECT count(*) FROM quad_poly_tbl WHERE p && polygon '((300,300),(400,600),(600,500),(700,200))';
85
86EXPLAIN (COSTS OFF)
87SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))';
88SELECT count(*) FROM quad_poly_tbl WHERE p &> polygon '((300,300),(400,600),(600,500),(700,200))';
89
90EXPLAIN (COSTS OFF)
91SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))';
92SELECT count(*) FROM quad_poly_tbl WHERE p >> polygon '((300,300),(400,600),(600,500),(700,200))';
93
94EXPLAIN (COSTS OFF)
95SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))';
96SELECT count(*) FROM quad_poly_tbl WHERE p <<| polygon '((300,300),(400,600),(600,500),(700,200))';
97
98EXPLAIN (COSTS OFF)
99SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))';
100SELECT count(*) FROM quad_poly_tbl WHERE p &<| polygon '((300,300),(400,600),(600,500),(700,200))';
101
102EXPLAIN (COSTS OFF)
103SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))';
104SELECT count(*) FROM quad_poly_tbl WHERE p |&> polygon '((300,300),(400,600),(600,500),(700,200))';
105
106EXPLAIN (COSTS OFF)
107SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))';
108SELECT count(*) FROM quad_poly_tbl WHERE p |>> polygon '((300,300),(400,600),(600,500),(700,200))';
109
110EXPLAIN (COSTS OFF)
111SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
112SELECT count(*) FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
113
114EXPLAIN (COSTS OFF)
115SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))';
116SELECT count(*) FROM quad_poly_tbl WHERE p @> polygon '((340,550),(343,552),(341,553))';
117
118EXPLAIN (COSTS OFF)
119SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))';
120SELECT count(*) FROM quad_poly_tbl WHERE p ~= polygon '((200, 300),(210, 310),(230, 290))';
121
122-- test ORDER BY distance
123SET enable_indexscan = ON;
124SET enable_bitmapscan = OFF;
125
126EXPLAIN (COSTS OFF)
127SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
128FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
129
130CREATE TEMP TABLE quad_poly_tbl_ord_idx2 AS
131SELECT rank() OVER (ORDER BY p <-> point '123,456') n, p <-> point '123,456' dist, id
132FROM quad_poly_tbl WHERE p <@ polygon '((300,300),(400,600),(600,500),(700,200))';
133
134SELECT *
135FROM quad_poly_tbl_ord_seq2 seq FULL JOIN quad_poly_tbl_ord_idx2 idx
136	ON seq.n = idx.n AND seq.id = idx.id AND
137		(seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
138WHERE seq.id IS NULL OR idx.id IS NULL;
139
140RESET enable_seqscan;
141RESET enable_indexscan;
142RESET enable_bitmapscan;
143