1--
2--  Test earthdistance extension
3--
4-- In this file we also do some testing of extension create/drop scenarios.
5-- That's really exercising the core database's dependency logic, so ideally
6-- we'd do it in the core regression tests, but we can't for lack of suitable
7-- guaranteed-available extensions.  earthdistance is a good test case because
8-- it has a dependency on the cube extension.
9--
10
11CREATE EXTENSION earthdistance;  -- fail, must install cube first
12CREATE EXTENSION cube;
13CREATE EXTENSION earthdistance;
14
15--
16-- The radius of the Earth we are using.
17--
18
19SELECT earth()::numeric(20,5);
20
21--
22-- Convert straight line distances to great circle distances.
23--
24SELECT (pi()*earth())::numeric(20,5);
25SELECT sec_to_gc(0)::numeric(20,5);
26SELECT sec_to_gc(2*earth())::numeric(20,5);
27SELECT sec_to_gc(10*earth())::numeric(20,5);
28SELECT sec_to_gc(-earth())::numeric(20,5);
29SELECT sec_to_gc(1000)::numeric(20,5);
30SELECT sec_to_gc(10000)::numeric(20,5);
31SELECT sec_to_gc(100000)::numeric(20,5);
32SELECT sec_to_gc(1000000)::numeric(20,5);
33
34--
35-- Convert great circle distances to straight line distances.
36--
37
38SELECT gc_to_sec(0)::numeric(20,5);
39SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
40SELECT gc_to_sec(10*earth())::numeric(20,5);
41SELECT gc_to_sec(pi()*earth())::numeric(20,5);
42SELECT gc_to_sec(-1000)::numeric(20,5);
43SELECT gc_to_sec(1000)::numeric(20,5);
44SELECT gc_to_sec(10000)::numeric(20,5);
45SELECT gc_to_sec(100000)::numeric(20,5);
46SELECT gc_to_sec(1000000)::numeric(20,5);
47
48--
49-- Set coordinates using latitude and longitude.
50-- Extract each coordinate separately so we can round them.
51--
52
53SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
54 cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
55 cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
56SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
57 cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
58 cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
59SELECT cube_ll_coord(ll_to_earth(180,180),1)::numeric(20,5),
60 cube_ll_coord(ll_to_earth(180,180),2)::numeric(20,5),
61 cube_ll_coord(ll_to_earth(180,180),3)::numeric(20,5);
62SELECT cube_ll_coord(ll_to_earth(180,360),1)::numeric(20,5),
63 cube_ll_coord(ll_to_earth(180,360),2)::numeric(20,5),
64 cube_ll_coord(ll_to_earth(180,360),3)::numeric(20,5);
65SELECT cube_ll_coord(ll_to_earth(-180,-360),1)::numeric(20,5),
66 cube_ll_coord(ll_to_earth(-180,-360),2)::numeric(20,5),
67 cube_ll_coord(ll_to_earth(-180,-360),3)::numeric(20,5);
68SELECT cube_ll_coord(ll_to_earth(0,180),1)::numeric(20,5),
69 cube_ll_coord(ll_to_earth(0,180),2)::numeric(20,5),
70 cube_ll_coord(ll_to_earth(0,180),3)::numeric(20,5);
71SELECT cube_ll_coord(ll_to_earth(0,-180),1)::numeric(20,5),
72 cube_ll_coord(ll_to_earth(0,-180),2)::numeric(20,5),
73 cube_ll_coord(ll_to_earth(0,-180),3)::numeric(20,5);
74SELECT cube_ll_coord(ll_to_earth(90,0),1)::numeric(20,5),
75 cube_ll_coord(ll_to_earth(90,0),2)::numeric(20,5),
76 cube_ll_coord(ll_to_earth(90,0),3)::numeric(20,5);
77SELECT cube_ll_coord(ll_to_earth(90,180),1)::numeric(20,5),
78 cube_ll_coord(ll_to_earth(90,180),2)::numeric(20,5),
79 cube_ll_coord(ll_to_earth(90,180),3)::numeric(20,5);
80SELECT cube_ll_coord(ll_to_earth(-90,0),1)::numeric(20,5),
81 cube_ll_coord(ll_to_earth(-90,0),2)::numeric(20,5),
82 cube_ll_coord(ll_to_earth(-90,0),3)::numeric(20,5);
83SELECT cube_ll_coord(ll_to_earth(-90,180),1)::numeric(20,5),
84 cube_ll_coord(ll_to_earth(-90,180),2)::numeric(20,5),
85 cube_ll_coord(ll_to_earth(-90,180),3)::numeric(20,5);
86
87--
88-- Test getting the latitude of a location.
89--
90
91SELECT latitude(ll_to_earth(0,0))::numeric(20,10);
92SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
93SELECT latitude(ll_to_earth(90,0))::numeric(20,10);
94SELECT latitude(ll_to_earth(-45,0))::numeric(20,10);
95SELECT latitude(ll_to_earth(-90,0))::numeric(20,10);
96SELECT latitude(ll_to_earth(0,90))::numeric(20,10);
97SELECT latitude(ll_to_earth(45,90))::numeric(20,10);
98SELECT latitude(ll_to_earth(90,90))::numeric(20,10);
99SELECT latitude(ll_to_earth(-45,90))::numeric(20,10);
100SELECT latitude(ll_to_earth(-90,90))::numeric(20,10);
101SELECT latitude(ll_to_earth(0,180))::numeric(20,10);
102SELECT latitude(ll_to_earth(45,180))::numeric(20,10);
103SELECT latitude(ll_to_earth(90,180))::numeric(20,10);
104SELECT latitude(ll_to_earth(-45,180))::numeric(20,10);
105SELECT latitude(ll_to_earth(-90,180))::numeric(20,10);
106SELECT latitude(ll_to_earth(0,-90))::numeric(20,10);
107SELECT latitude(ll_to_earth(45,-90))::numeric(20,10);
108SELECT latitude(ll_to_earth(90,-90))::numeric(20,10);
109SELECT latitude(ll_to_earth(-45,-90))::numeric(20,10);
110SELECT latitude(ll_to_earth(-90,-90))::numeric(20,10);
111
112--
113-- Test getting the longitude of a location.
114--
115
116SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
117SELECT longitude(ll_to_earth(45,0))::numeric(20,10);
118SELECT longitude(ll_to_earth(90,0))::numeric(20,10);
119SELECT longitude(ll_to_earth(-45,0))::numeric(20,10);
120SELECT longitude(ll_to_earth(-90,0))::numeric(20,10);
121SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
122SELECT longitude(ll_to_earth(45,90))::numeric(20,10);
123SELECT longitude(ll_to_earth(90,90))::numeric(20,10);
124SELECT longitude(ll_to_earth(-45,90))::numeric(20,10);
125SELECT longitude(ll_to_earth(-90,90))::numeric(20,10);
126SELECT longitude(ll_to_earth(0,180))::numeric(20,10);
127SELECT longitude(ll_to_earth(45,180))::numeric(20,10);
128SELECT longitude(ll_to_earth(90,180))::numeric(20,10);
129SELECT longitude(ll_to_earth(-45,180))::numeric(20,10);
130SELECT longitude(ll_to_earth(-90,180))::numeric(20,10);
131SELECT longitude(ll_to_earth(0,-90))::numeric(20,10);
132SELECT longitude(ll_to_earth(45,-90))::numeric(20,10);
133SELECT longitude(ll_to_earth(90,-90))::numeric(20,10);
134SELECT longitude(ll_to_earth(-45,-90))::numeric(20,10);
135SELECT longitude(ll_to_earth(-90,-90))::numeric(20,10);
136
137--
138-- For the distance tests the following is some real life data.
139--
140-- Chicago has a latitude of 41.8 and a longitude of 87.6.
141-- Albuquerque has a latitude of 35.1 and a longitude of 106.7.
142-- (Note that latitude and longitude are specified differently
143-- in the cube based functions than for the point based functions.)
144--
145
146--
147-- Test getting the distance between two points using earth_distance.
148--
149
150SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
151SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
152SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(90,0))::numeric(20,5);
153SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,90))::numeric(20,5);
154SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))::numeric(20,5);
155SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(1,0))::numeric(20,5);
156SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(30,1))::numeric(20,5);
157SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(31,0))::numeric(20,5);
158SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(60,1))::numeric(20,5);
159SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(61,0))::numeric(20,5);
160SELECT earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))::numeric(20,5);
161SELECT (earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))*
162      100./2.54/12./5280.)::numeric(20,5);
163
164--
165-- Test getting the distance between two points using geo_distance.
166--
167
168SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5);
169SELECT geo_distance('(0,0)'::point,'(180,0)'::point)::numeric(20,5);
170SELECT geo_distance('(0,0)'::point,'(0,90)'::point)::numeric(20,5);
171SELECT geo_distance('(0,0)'::point,'(90,0)'::point)::numeric(20,5);
172SELECT geo_distance('(0,0)'::point,'(1,0)'::point)::numeric(20,5);
173SELECT geo_distance('(0,0)'::point,'(0,1)'::point)::numeric(20,5);
174SELECT geo_distance('(0,30)'::point,'(1,30)'::point)::numeric(20,5);
175SELECT geo_distance('(0,30)'::point,'(0,31)'::point)::numeric(20,5);
176SELECT geo_distance('(0,60)'::point,'(1,60)'::point)::numeric(20,5);
177SELECT geo_distance('(0,60)'::point,'(0,61)'::point)::numeric(20,5);
178SELECT geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)::numeric(20,5);
179SELECT (geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
180
181--
182-- Test getting the distance between two points using the <@> operator.
183--
184
185SELECT ('(0,0)'::point <@> '(0,0)'::point)::numeric(20,5);
186SELECT ('(0,0)'::point <@> '(180,0)'::point)::numeric(20,5);
187SELECT ('(0,0)'::point <@> '(0,90)'::point)::numeric(20,5);
188SELECT ('(0,0)'::point <@> '(90,0)'::point)::numeric(20,5);
189SELECT ('(0,0)'::point <@> '(1,0)'::point)::numeric(20,5);
190SELECT ('(0,0)'::point <@> '(0,1)'::point)::numeric(20,5);
191SELECT ('(0,30)'::point <@> '(1,30)'::point)::numeric(20,5);
192SELECT ('(0,30)'::point <@> '(0,31)'::point)::numeric(20,5);
193SELECT ('(0,60)'::point <@> '(1,60)'::point)::numeric(20,5);
194SELECT ('(0,60)'::point <@> '(0,61)'::point)::numeric(20,5);
195SELECT ('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)::numeric(20,5);
196SELECT (('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
197
198--
199-- Test getting a bounding box around points.
200--
201
202SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
203       cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
204       cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
205       cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
206       cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
207       cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
208SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
209       cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
210       cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5),
211       cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
212       cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
213       cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5);
214SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
215       cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
216       cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5),
217       cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
218       cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
219       cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5);
220
221--
222-- Test for points that should be in bounding boxes.
223--
224
225SELECT earth_box(ll_to_earth(0,0),
226       earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001) @>
227       ll_to_earth(0,1);
228SELECT earth_box(ll_to_earth(0,0),
229       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001) @>
230       ll_to_earth(0,0.1);
231SELECT earth_box(ll_to_earth(0,0),
232       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*1.00001) @>
233       ll_to_earth(0,0.01);
234SELECT earth_box(ll_to_earth(0,0),
235       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*1.00001) @>
236       ll_to_earth(0,0.001);
237SELECT earth_box(ll_to_earth(0,0),
238       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*1.00001) @>
239       ll_to_earth(0,0.0001);
240SELECT earth_box(ll_to_earth(0,0),
241       earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*1.00001) @>
242       ll_to_earth(0.0001,0.0001);
243SELECT earth_box(ll_to_earth(45,45),
244       earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*1.00001) @>
245       ll_to_earth(45.0001,45.0001);
246SELECT earth_box(ll_to_earth(90,180),
247       earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*1.00001) @>
248       ll_to_earth(90.0001,180.0001);
249
250--
251-- Test for points that shouldn't be in bounding boxes. Note that we need
252-- to make points way outside, since some points close may be in the box
253-- but further away than the distance we are testing.
254--
255
256SELECT earth_box(ll_to_earth(0,0),
257       earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @>
258       ll_to_earth(0,1);
259SELECT earth_box(ll_to_earth(0,0),
260       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @>
261       ll_to_earth(0,0.1);
262SELECT earth_box(ll_to_earth(0,0),
263       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*.57735) @>
264       ll_to_earth(0,0.01);
265SELECT earth_box(ll_to_earth(0,0),
266       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*.57735) @>
267       ll_to_earth(0,0.001);
268SELECT earth_box(ll_to_earth(0,0),
269       earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*.57735) @>
270       ll_to_earth(0,0.0001);
271SELECT earth_box(ll_to_earth(0,0),
272       earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*.57735) @>
273       ll_to_earth(0.0001,0.0001);
274SELECT earth_box(ll_to_earth(45,45),
275       earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*.57735) @>
276       ll_to_earth(45.0001,45.0001);
277SELECT earth_box(ll_to_earth(90,180),
278       earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*.57735) @>
279       ll_to_earth(90.0001,180.0001);
280
281--
282-- Test the recommended constraints.
283--
284
285SELECT cube_is_point(ll_to_earth(0,0));
286SELECT cube_dim(ll_to_earth(0,0)) <= 3;
287SELECT abs(cube_distance(ll_to_earth(0,0), '(0)'::cube) / earth() - 1) <
288       '10e-12'::float8;
289SELECT cube_is_point(ll_to_earth(30,60));
290SELECT cube_dim(ll_to_earth(30,60)) <= 3;
291SELECT abs(cube_distance(ll_to_earth(30,60), '(0)'::cube) / earth() - 1) <
292       '10e-12'::float8;
293SELECT cube_is_point(ll_to_earth(60,90));
294SELECT cube_dim(ll_to_earth(60,90)) <= 3;
295SELECT abs(cube_distance(ll_to_earth(60,90), '(0)'::cube) / earth() - 1) <
296       '10e-12'::float8;
297SELECT cube_is_point(ll_to_earth(-30,-90));
298SELECT cube_dim(ll_to_earth(-30,-90)) <= 3;
299SELECT abs(cube_distance(ll_to_earth(-30,-90), '(0)'::cube) / earth() - 1) <
300       '10e-12'::float8;
301
302--
303-- Now we are going to test extension create/drop scenarios.
304--
305
306-- list what's installed
307\dT
308
309drop extension cube;  -- fail, earthdistance requires it
310
311drop extension earthdistance;
312
313drop type cube;  -- fail, extension cube requires it
314
315-- list what's installed
316\dT
317
318create table foo (f1 cube, f2 int);
319
320drop extension cube;  -- fail, foo.f1 requires it
321
322drop table foo;
323
324drop extension cube;
325
326-- list what's installed
327\dT
328\df
329\do
330
331create schema c;
332
333create extension cube with schema c;
334
335-- list what's installed
336\dT public.*
337\df public.*
338\do public.*
339\dT c.*
340
341create table foo (f1 c.cube, f2 int);
342
343drop extension cube;  -- fail, foo.f1 requires it
344
345drop schema c;  -- fail, cube requires it
346
347drop extension cube cascade;
348
349\d foo
350
351-- list what's installed
352\dT public.*
353\df public.*
354\do public.*
355\dT c.*
356\df c.*
357\do c.*
358
359drop schema c;
360