2
-- Test earth distance functions
5
-- first, define the datatype. Turn off echoing so that expected file
6
-- does not depend on contents of earthdistance.sql or cube.sql.
9
psql:../cube/cube.sql:10: NOTICE: type "cube" is not yet defined
10
DETAIL: Creating a shell type definition.
11
psql:../cube/cube.sql:15: NOTICE: argument type cube is only a shell
13
-- The radius of the Earth we are using.
15
SELECT earth()::numeric(20,5);
22
-- Convert straight line distances to great circle distances.
24
SELECT (pi()*earth())::numeric(20,5);
30
SELECT sec_to_gc(0)::numeric(20,5);
36
SELECT sec_to_gc(2*earth())::numeric(20,5);
42
SELECT sec_to_gc(10*earth())::numeric(20,5);
48
SELECT sec_to_gc(-earth())::numeric(20,5);
54
SELECT sec_to_gc(1000)::numeric(20,5);
60
SELECT sec_to_gc(10000)::numeric(20,5);
66
SELECT sec_to_gc(100000)::numeric(20,5);
72
SELECT sec_to_gc(1000000)::numeric(20,5);
79
-- Convert great circle distances to straight line distances.
81
SELECT gc_to_sec(0)::numeric(20,5);
87
SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
93
SELECT gc_to_sec(10*earth())::numeric(20,5);
99
SELECT gc_to_sec(pi()*earth())::numeric(20,5);
105
SELECT gc_to_sec(-1000)::numeric(20,5);
111
SELECT gc_to_sec(1000)::numeric(20,5);
117
SELECT gc_to_sec(10000)::numeric(20,5);
123
SELECT gc_to_sec(100000)::numeric(20,5);
129
SELECT gc_to_sec(1000000)::numeric(20,5);
136
-- Set coordinates using latitude and longitude.
137
-- Extract each coordinate separately so we can round them.
139
SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
140
cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
141
cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
142
cube_ll_coord | cube_ll_coord | cube_ll_coord
143
---------------+---------------+---------------
144
6378168.00000 | 0.00000 | 0.00000
147
SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
148
cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
149
cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
150
cube_ll_coord | cube_ll_coord | cube_ll_coord
151
---------------+---------------+---------------
152
6378168.00000 | 0.00000 | 0.00000
155
SELECT cube_ll_coord(ll_to_earth(180,180),1)::numeric(20,5),
156
cube_ll_coord(ll_to_earth(180,180),2)::numeric(20,5),
157
cube_ll_coord(ll_to_earth(180,180),3)::numeric(20,5);
158
cube_ll_coord | cube_ll_coord | cube_ll_coord
159
---------------+---------------+---------------
160
6378168.00000 | 0.00000 | 0.00000
163
SELECT cube_ll_coord(ll_to_earth(180,360),1)::numeric(20,5),
164
cube_ll_coord(ll_to_earth(180,360),2)::numeric(20,5),
165
cube_ll_coord(ll_to_earth(180,360),3)::numeric(20,5);
166
cube_ll_coord | cube_ll_coord | cube_ll_coord
167
----------------+---------------+---------------
168
-6378168.00000 | 0.00000 | 0.00000
171
SELECT cube_ll_coord(ll_to_earth(-180,-360),1)::numeric(20,5),
172
cube_ll_coord(ll_to_earth(-180,-360),2)::numeric(20,5),
173
cube_ll_coord(ll_to_earth(-180,-360),3)::numeric(20,5);
174
cube_ll_coord | cube_ll_coord | cube_ll_coord
175
----------------+---------------+---------------
176
-6378168.00000 | 0.00000 | 0.00000
179
SELECT cube_ll_coord(ll_to_earth(0,180),1)::numeric(20,5),
180
cube_ll_coord(ll_to_earth(0,180),2)::numeric(20,5),
181
cube_ll_coord(ll_to_earth(0,180),3)::numeric(20,5);
182
cube_ll_coord | cube_ll_coord | cube_ll_coord
183
----------------+---------------+---------------
184
-6378168.00000 | 0.00000 | 0.00000
187
SELECT cube_ll_coord(ll_to_earth(0,-180),1)::numeric(20,5),
188
cube_ll_coord(ll_to_earth(0,-180),2)::numeric(20,5),
189
cube_ll_coord(ll_to_earth(0,-180),3)::numeric(20,5);
190
cube_ll_coord | cube_ll_coord | cube_ll_coord
191
----------------+---------------+---------------
192
-6378168.00000 | 0.00000 | 0.00000
195
SELECT cube_ll_coord(ll_to_earth(90,0),1)::numeric(20,5),
196
cube_ll_coord(ll_to_earth(90,0),2)::numeric(20,5),
197
cube_ll_coord(ll_to_earth(90,0),3)::numeric(20,5);
198
cube_ll_coord | cube_ll_coord | cube_ll_coord
199
---------------+---------------+---------------
200
0.00000 | 0.00000 | 6378168.00000
203
SELECT cube_ll_coord(ll_to_earth(90,180),1)::numeric(20,5),
204
cube_ll_coord(ll_to_earth(90,180),2)::numeric(20,5),
205
cube_ll_coord(ll_to_earth(90,180),3)::numeric(20,5);
206
cube_ll_coord | cube_ll_coord | cube_ll_coord
207
---------------+---------------+---------------
208
0.00000 | 0.00000 | 6378168.00000
211
SELECT cube_ll_coord(ll_to_earth(-90,0),1)::numeric(20,5),
212
cube_ll_coord(ll_to_earth(-90,0),2)::numeric(20,5),
213
cube_ll_coord(ll_to_earth(-90,0),3)::numeric(20,5);
214
cube_ll_coord | cube_ll_coord | cube_ll_coord
215
---------------+---------------+----------------
216
0.00000 | 0.00000 | -6378168.00000
219
SELECT cube_ll_coord(ll_to_earth(-90,180),1)::numeric(20,5),
220
cube_ll_coord(ll_to_earth(-90,180),2)::numeric(20,5),
221
cube_ll_coord(ll_to_earth(-90,180),3)::numeric(20,5);
222
cube_ll_coord | cube_ll_coord | cube_ll_coord
223
---------------+---------------+----------------
224
0.00000 | 0.00000 | -6378168.00000
228
-- Test getting the latitude of a location.
230
SELECT latitude(ll_to_earth(0,0))::numeric(20,10);
236
SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
242
SELECT latitude(ll_to_earth(90,0))::numeric(20,10);
248
SELECT latitude(ll_to_earth(-45,0))::numeric(20,10);
254
SELECT latitude(ll_to_earth(-90,0))::numeric(20,10);
260
SELECT latitude(ll_to_earth(0,90))::numeric(20,10);
266
SELECT latitude(ll_to_earth(45,90))::numeric(20,10);
272
SELECT latitude(ll_to_earth(90,90))::numeric(20,10);
278
SELECT latitude(ll_to_earth(-45,90))::numeric(20,10);
284
SELECT latitude(ll_to_earth(-90,90))::numeric(20,10);
290
SELECT latitude(ll_to_earth(0,180))::numeric(20,10);
296
SELECT latitude(ll_to_earth(45,180))::numeric(20,10);
302
SELECT latitude(ll_to_earth(90,180))::numeric(20,10);
308
SELECT latitude(ll_to_earth(-45,180))::numeric(20,10);
314
SELECT latitude(ll_to_earth(-90,180))::numeric(20,10);
320
SELECT latitude(ll_to_earth(0,-90))::numeric(20,10);
326
SELECT latitude(ll_to_earth(45,-90))::numeric(20,10);
332
SELECT latitude(ll_to_earth(90,-90))::numeric(20,10);
338
SELECT latitude(ll_to_earth(-45,-90))::numeric(20,10);
344
SELECT latitude(ll_to_earth(-90,-90))::numeric(20,10);
351
-- Test getting the longitude of a location.
353
SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
359
SELECT longitude(ll_to_earth(45,0))::numeric(20,10);
365
SELECT longitude(ll_to_earth(90,0))::numeric(20,10);
371
SELECT longitude(ll_to_earth(-45,0))::numeric(20,10);
377
SELECT longitude(ll_to_earth(-90,0))::numeric(20,10);
383
SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
389
SELECT longitude(ll_to_earth(45,90))::numeric(20,10);
395
SELECT longitude(ll_to_earth(90,90))::numeric(20,10);
401
SELECT longitude(ll_to_earth(-45,90))::numeric(20,10);
407
SELECT longitude(ll_to_earth(-90,90))::numeric(20,10);
413
SELECT longitude(ll_to_earth(0,180))::numeric(20,10);
419
SELECT longitude(ll_to_earth(45,180))::numeric(20,10);
425
SELECT longitude(ll_to_earth(90,180))::numeric(20,10);
431
SELECT longitude(ll_to_earth(-45,180))::numeric(20,10);
437
SELECT longitude(ll_to_earth(-90,180))::numeric(20,10);
443
SELECT longitude(ll_to_earth(0,-90))::numeric(20,10);
449
SELECT longitude(ll_to_earth(45,-90))::numeric(20,10);
455
SELECT longitude(ll_to_earth(90,-90))::numeric(20,10);
461
SELECT longitude(ll_to_earth(-45,-90))::numeric(20,10);
467
SELECT longitude(ll_to_earth(-90,-90))::numeric(20,10);
474
-- For the distance tests the following is some real life data.
476
-- Chicago has a latitude of 41.8 and a longitude of 87.6.
477
-- Albuquerque has a latitude of 35.1 and a longitude of 106.7.
478
-- (Note that latitude and longitude are specified differently
479
-- in the cube based functions than for the point based functions.)
482
-- Test getting the distance between two points using earth_distance.
484
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
490
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
496
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(90,0))::numeric(20,5);
502
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,90))::numeric(20,5);
508
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))::numeric(20,5);
514
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(1,0))::numeric(20,5);
520
SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(30,1))::numeric(20,5);
526
SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(31,0))::numeric(20,5);
532
SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(60,1))::numeric(20,5);
538
SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(61,0))::numeric(20,5);
544
SELECT earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))::numeric(20,5);
550
SELECT (earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))*
551
100./2.54/12./5280.)::numeric(20,5);
558
-- Test getting the distance between two points using geo_distance.
560
SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5);
566
SELECT geo_distance('(0,0)'::point,'(180,0)'::point)::numeric(20,5);
572
SELECT geo_distance('(0,0)'::point,'(0,90)'::point)::numeric(20,5);
578
SELECT geo_distance('(0,0)'::point,'(90,0)'::point)::numeric(20,5);
584
SELECT geo_distance('(0,0)'::point,'(1,0)'::point)::numeric(20,5);
590
SELECT geo_distance('(0,0)'::point,'(0,1)'::point)::numeric(20,5);
596
SELECT geo_distance('(0,30)'::point,'(1,30)'::point)::numeric(20,5);
602
SELECT geo_distance('(0,30)'::point,'(0,31)'::point)::numeric(20,5);
608
SELECT geo_distance('(0,60)'::point,'(1,60)'::point)::numeric(20,5);
614
SELECT geo_distance('(0,60)'::point,'(0,61)'::point)::numeric(20,5);
620
SELECT geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)::numeric(20,5);
626
SELECT (geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
633
-- Test getting the distance between two points using the <@> operator.
635
SELECT ('(0,0)'::point <@> '(0,0)'::point)::numeric(20,5);
641
SELECT ('(0,0)'::point <@> '(180,0)'::point)::numeric(20,5);
647
SELECT ('(0,0)'::point <@> '(0,90)'::point)::numeric(20,5);
653
SELECT ('(0,0)'::point <@> '(90,0)'::point)::numeric(20,5);
659
SELECT ('(0,0)'::point <@> '(1,0)'::point)::numeric(20,5);
665
SELECT ('(0,0)'::point <@> '(0,1)'::point)::numeric(20,5);
671
SELECT ('(0,30)'::point <@> '(1,30)'::point)::numeric(20,5);
677
SELECT ('(0,30)'::point <@> '(0,31)'::point)::numeric(20,5);
683
SELECT ('(0,60)'::point <@> '(1,60)'::point)::numeric(20,5);
689
SELECT ('(0,60)'::point <@> '(0,61)'::point)::numeric(20,5);
695
SELECT ('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)::numeric(20,5);
701
SELECT (('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
708
-- Test getting a bounding box around points.
710
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
711
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
712
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
713
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
714
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
715
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
716
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
717
---------------+---------------+---------------+---------------+---------------+---------------
718
6266169.43896 | -111998.56104 | -111998.56104 | 6490166.56104 | 111998.56104 | 111998.56104
721
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
722
cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
723
cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5),
724
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
725
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
726
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5);
727
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
728
----------------+-----------------+-----------------+----------------+----------------+----------------
729
-6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
732
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
733
cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
734
cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5),
735
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
736
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
737
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5);
738
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
739
----------------+-----------------+-----------------+----------------+----------------+----------------
740
-6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
744
-- Test for points that should be in bounding boxes.
746
SELECT earth_box(ll_to_earth(0,0),
747
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001) @
754
SELECT earth_box(ll_to_earth(0,0),
755
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001) @
762
SELECT earth_box(ll_to_earth(0,0),
763
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*1.00001) @
770
SELECT earth_box(ll_to_earth(0,0),
771
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*1.00001) @
772
ll_to_earth(0,0.001);
778
SELECT earth_box(ll_to_earth(0,0),
779
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*1.00001) @
780
ll_to_earth(0,0.0001);
786
SELECT earth_box(ll_to_earth(0,0),
787
earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*1.00001) @
788
ll_to_earth(0.0001,0.0001);
794
SELECT earth_box(ll_to_earth(45,45),
795
earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*1.00001) @
796
ll_to_earth(45.0001,45.0001);
802
SELECT earth_box(ll_to_earth(90,180),
803
earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*1.00001) @
804
ll_to_earth(90.0001,180.0001);
811
-- Test for points that shouldn't be in bounding boxes. Note that we need
812
-- to make points way outside, since some points close may be in the box
813
-- but further away than the distance we are testing.
815
SELECT earth_box(ll_to_earth(0,0),
816
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @
823
SELECT earth_box(ll_to_earth(0,0),
824
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @
831
SELECT earth_box(ll_to_earth(0,0),
832
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*.57735) @
839
SELECT earth_box(ll_to_earth(0,0),
840
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*.57735) @
841
ll_to_earth(0,0.001);
847
SELECT earth_box(ll_to_earth(0,0),
848
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*.57735) @
849
ll_to_earth(0,0.0001);
855
SELECT earth_box(ll_to_earth(0,0),
856
earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*.57735) @
857
ll_to_earth(0.0001,0.0001);
863
SELECT earth_box(ll_to_earth(45,45),
864
earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*.57735) @
865
ll_to_earth(45.0001,45.0001);
871
SELECT earth_box(ll_to_earth(90,180),
872
earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*.57735) @
873
ll_to_earth(90.0001,180.0001);
880
-- Test the recommended constraints.
882
SELECT is_point(ll_to_earth(0,0));
883
ERROR: function is_point(earth) does not exist
884
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.
885
SELECT cube_dim(ll_to_earth(0,0)) <= 3;
891
SELECT abs(cube_distance(ll_to_earth(0,0), '(0)'::cube) / earth() - 1) <
898
SELECT is_point(ll_to_earth(30,60));
899
ERROR: function is_point(earth) does not exist
900
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.
901
SELECT cube_dim(ll_to_earth(30,60)) <= 3;
907
SELECT abs(cube_distance(ll_to_earth(30,60), '(0)'::cube) / earth() - 1) <
914
SELECT is_point(ll_to_earth(60,90));
915
ERROR: function is_point(earth) does not exist
916
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.
917
SELECT cube_dim(ll_to_earth(60,90)) <= 3;
923
SELECT abs(cube_distance(ll_to_earth(60,90), '(0)'::cube) / earth() - 1) <
930
SELECT is_point(ll_to_earth(-30,-90));
931
ERROR: function is_point(earth) does not exist
932
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.
933
SELECT cube_dim(ll_to_earth(-30,-90)) <= 3;
939
SELECT abs(cube_distance(ll_to_earth(-30,-90), '(0)'::cube) / earth() - 1) <