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