-
Notifications
You must be signed in to change notification settings - Fork 5
/
s2--0.0.2.sql
200 lines (173 loc) · 7.3 KB
/
s2--0.0.2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
-- Requires plpython3u language to be installed
-- CREATE LANGUAGE plpython3u;
-- Also requires the s2sphere extension to be installed on the system for python3.
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION s2" to load this file. \quit
--
-- Conversion functions
--
-- PostgreSQL doesn't handle unsigned ints, so we have to re-interpret the byte representation
-- of the id.
CREATE OR REPLACE FUNCTION s2_cellid_from_latlng(lat double precision, lng double precision) RETURNS bigint
AS $$
import s2sphere
id = s2sphere.CellId.from_lat_lng(s2sphere.LatLng.from_degrees(lat, lng))
return int.from_bytes(id.id().to_bytes(8, 'big', signed=False), 'big', signed=True)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Convert from token (text) to cellid (long) representation.
CREATE OR REPLACE FUNCTION s2_cellid_from_token(token text) RETURNS bigint
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
return int.from_bytes(id.id().to_bytes(8, 'big', signed=False), 'big', signed=True)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Get the string token for a cellid from the latitude and longitude
CREATE OR REPLACE FUNCTION s2_token_from_latlng(lat double precision, lng double precision) RETURNS text
AS $$
import s2sphere
id = s2sphere.CellId.from_lat_lng(s2sphere.LatLng.from_degrees(lat, lng))
return id.to_token()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Get the string token for a cellid from the raw id
CREATE OR REPLACE FUNCTION s2_token_from_cellid(cellid bigint) RETURNS text
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id.to_token()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Get the latitude and longitude as degrees from a cellid
CREATE OR REPLACE FUNCTION s2_latlng_from_cellid(cellid bigint, OUT lat double precision, OUT lng double precision)
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
latlng = id.to_lat_lng()
return (latlng.lat().degrees, latlng.lng().degrees)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Get the latitude and longitude as degrees from a token
CREATE OR REPLACE FUNCTION s2_latlng_from_token(token text, OUT lat double precision, OUT lng double precision)
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
latlng = id.to_lat_lng()
return (latlng.lat().degrees, latlng.lng().degrees)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
--
-- CellId based functions
--
-- Return whether this is a valid s2 cellid.
CREATE OR REPLACE FUNCTION s2_cellid_is_valid(cellid bigint) RETURNS boolean
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id.is_valid()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_cellid_is_leaf(cellid bigint) RETURNS boolean
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id.is_leaf()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Return the level of the cellid
CREATE OR REPLACE FUNCTION s2_cellid_level(cellid bigint) RETURNS int
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id.level()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Return a cell id at a parent level for the passed in cell id
CREATE OR REPLACE FUNCTION s2_cellid_parent(cellid bigint, parent_level int) RETURNS bigint
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
newid = id.parent(parent_level)
return int.from_bytes(newid.id().to_bytes(8, 'big', signed=False), 'big', signed=True)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_cellid_children(cellid bigint) RETURNS SETOF bigint
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
if not id.is_leaf():
for child in id.children():
yield int.from_bytes(child.id().to_bytes(8, 'big', signed=False), 'big', signed=True)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_cellid_edge_neighbors(cellid bigint) RETURNS SETOF bigint
AS $$
import s2sphere
id = s2sphere.CellId(int.from_bytes(cellid.to_bytes(8, 'big', signed=True), 'big', signed=False))
for neighbor in id.get_edge_neighbors():
yield int.from_bytes(neighbor.id().to_bytes(8, 'big', signed=False), 'big', signed=True)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_cellid_contains(a bigint, b bigint) RETURNS boolean
AS $$
import s2sphere
id_a = s2sphere.CellId(int.from_bytes(a.to_bytes(8, 'big', signed=True), 'big', signed=False))
id_b = s2sphere.CellId(int.from_bytes(b.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id_a.contains(id_b)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_cellid_intersects(a bigint, b bigint) RETURNS boolean
AS $$
import s2sphere
id_a = s2sphere.CellId(int.from_bytes(a.to_bytes(8, 'big', signed=True), 'big', signed=False))
id_b = s2sphere.CellId(int.from_bytes(b.to_bytes(8, 'big', signed=True), 'big', signed=False))
return id_a.intersects(id_b)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
--
-- Token based functions
--
-- Return whether this is a valid s2 cellid.
CREATE OR REPLACE FUNCTION s2_token_is_valid(token text) RETURNS boolean
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
return id.is_valid()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_token_is_leaf(token text) RETURNS boolean
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
return id.is_leaf()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Return the level of the cellid
CREATE OR REPLACE FUNCTION s2_token_level(token text) RETURNS int
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
return id.level()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
-- Return a cell id at a parent level for the passed in cell id
CREATE OR REPLACE FUNCTION s2_token_parent(token text, parent_level int) RETURNS text
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
newid = id.parent(parent_level)
return newid.to_token()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_token_children(token text) RETURNS SETOF text
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
if not id.is_leaf():
for child in id.children():
yield child.to_token()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_token_edge_neighbors(token text) RETURNS SETOF text
AS $$
import s2sphere
id = s2sphere.CellId.from_token(token)
for neighbor in id.get_edge_neighbors():
yield neighbor.to_token()
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_token_contains(a text, b text) RETURNS boolean
AS $$
import s2sphere
id_a = s2sphere.CellId.from_token(a)
id_b = s2sphere.CellId.from_token(b)
return id_a.contains(id_b)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION s2_token_intersects(a text, b text) RETURNS boolean
AS $$
import s2sphere
id_a = s2sphere.CellId.from_token(a)
id_b = s2sphere.CellId.from_token(b)
return id_a.intersects(id_b)
$$ LANGUAGE plpython3u IMMUTABLE STRICT;