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.
8
SET client_min_messages = warning;
10
RESET client_min_messages;
12
-- The radius of the Earth we are using.
14
SELECT earth()::numeric(20,5);
21
-- Convert straight line distances to great circle distances.
23
SELECT (pi()*earth())::numeric(20,5);
29
SELECT sec_to_gc(0)::numeric(20,5);
35
SELECT sec_to_gc(2*earth())::numeric(20,5);
41
SELECT sec_to_gc(10*earth())::numeric(20,5);
47
SELECT sec_to_gc(-earth())::numeric(20,5);
53
SELECT sec_to_gc(1000)::numeric(20,5);
59
SELECT sec_to_gc(10000)::numeric(20,5);
65
SELECT sec_to_gc(100000)::numeric(20,5);
71
SELECT sec_to_gc(1000000)::numeric(20,5);
78
-- Convert great circle distances to straight line distances.
80
SELECT gc_to_sec(0)::numeric(20,5);
86
SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
92
SELECT gc_to_sec(10*earth())::numeric(20,5);
98
SELECT gc_to_sec(pi()*earth())::numeric(20,5);
104
SELECT gc_to_sec(-1000)::numeric(20,5);
110
SELECT gc_to_sec(1000)::numeric(20,5);
116
SELECT gc_to_sec(10000)::numeric(20,5);
122
SELECT gc_to_sec(100000)::numeric(20,5);
128
SELECT gc_to_sec(1000000)::numeric(20,5);
135
-- Set coordinates using latitude and longitude.
136
-- Extract each coordinate separately so we can round them.
138
SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
139
cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
140
cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
141
cube_ll_coord | cube_ll_coord | cube_ll_coord
142
---------------+---------------+---------------
143
6378168.00000 | 0.00000 | 0.00000
146
SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
147
cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
148
cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
149
cube_ll_coord | cube_ll_coord | cube_ll_coord
150
---------------+---------------+---------------
151
6378168.00000 | 0.00000 | 0.00000
154
SELECT cube_ll_coord(ll_to_earth(180,180),1)::numeric(20,5),
155
cube_ll_coord(ll_to_earth(180,180),2)::numeric(20,5),
156
cube_ll_coord(ll_to_earth(180,180),3)::numeric(20,5);
157
cube_ll_coord | cube_ll_coord | cube_ll_coord
158
---------------+---------------+---------------
159
6378168.00000 | 0.00000 | 0.00000
162
SELECT cube_ll_coord(ll_to_earth(180,360),1)::numeric(20,5),
163
cube_ll_coord(ll_to_earth(180,360),2)::numeric(20,5),
164
cube_ll_coord(ll_to_earth(180,360),3)::numeric(20,5);
165
cube_ll_coord | cube_ll_coord | cube_ll_coord
166
----------------+---------------+---------------
167
-6378168.00000 | 0.00000 | 0.00000
170
SELECT cube_ll_coord(ll_to_earth(-180,-360),1)::numeric(20,5),
171
cube_ll_coord(ll_to_earth(-180,-360),2)::numeric(20,5),
172
cube_ll_coord(ll_to_earth(-180,-360),3)::numeric(20,5);
173
cube_ll_coord | cube_ll_coord | cube_ll_coord
174
----------------+---------------+---------------
175
-6378168.00000 | 0.00000 | 0.00000
178
SELECT cube_ll_coord(ll_to_earth(0,180),1)::numeric(20,5),
179
cube_ll_coord(ll_to_earth(0,180),2)::numeric(20,5),
180
cube_ll_coord(ll_to_earth(0,180),3)::numeric(20,5);
181
cube_ll_coord | cube_ll_coord | cube_ll_coord
182
----------------+---------------+---------------
183
-6378168.00000 | 0.00000 | 0.00000
186
SELECT cube_ll_coord(ll_to_earth(0,-180),1)::numeric(20,5),
187
cube_ll_coord(ll_to_earth(0,-180),2)::numeric(20,5),
188
cube_ll_coord(ll_to_earth(0,-180),3)::numeric(20,5);
189
cube_ll_coord | cube_ll_coord | cube_ll_coord
190
----------------+---------------+---------------
191
-6378168.00000 | 0.00000 | 0.00000
194
SELECT cube_ll_coord(ll_to_earth(90,0),1)::numeric(20,5),
195
cube_ll_coord(ll_to_earth(90,0),2)::numeric(20,5),
196
cube_ll_coord(ll_to_earth(90,0),3)::numeric(20,5);
197
cube_ll_coord | cube_ll_coord | cube_ll_coord
198
---------------+---------------+---------------
199
0.00000 | 0.00000 | 6378168.00000
202
SELECT cube_ll_coord(ll_to_earth(90,180),1)::numeric(20,5),
203
cube_ll_coord(ll_to_earth(90,180),2)::numeric(20,5),
204
cube_ll_coord(ll_to_earth(90,180),3)::numeric(20,5);
205
cube_ll_coord | cube_ll_coord | cube_ll_coord
206
---------------+---------------+---------------
207
0.00000 | 0.00000 | 6378168.00000
210
SELECT cube_ll_coord(ll_to_earth(-90,0),1)::numeric(20,5),
211
cube_ll_coord(ll_to_earth(-90,0),2)::numeric(20,5),
212
cube_ll_coord(ll_to_earth(-90,0),3)::numeric(20,5);
213
cube_ll_coord | cube_ll_coord | cube_ll_coord
214
---------------+---------------+----------------
215
0.00000 | 0.00000 | -6378168.00000
218
SELECT cube_ll_coord(ll_to_earth(-90,180),1)::numeric(20,5),
219
cube_ll_coord(ll_to_earth(-90,180),2)::numeric(20,5),
220
cube_ll_coord(ll_to_earth(-90,180),3)::numeric(20,5);
221
cube_ll_coord | cube_ll_coord | cube_ll_coord
222
---------------+---------------+----------------
223
0.00000 | 0.00000 | -6378168.00000
227
-- Test getting the latitude of a location.
229
SELECT latitude(ll_to_earth(0,0))::numeric(20,10);
235
SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
241
SELECT latitude(ll_to_earth(90,0))::numeric(20,10);
247
SELECT latitude(ll_to_earth(-45,0))::numeric(20,10);
253
SELECT latitude(ll_to_earth(-90,0))::numeric(20,10);
259
SELECT latitude(ll_to_earth(0,90))::numeric(20,10);
265
SELECT latitude(ll_to_earth(45,90))::numeric(20,10);
271
SELECT latitude(ll_to_earth(90,90))::numeric(20,10);
277
SELECT latitude(ll_to_earth(-45,90))::numeric(20,10);
283
SELECT latitude(ll_to_earth(-90,90))::numeric(20,10);
289
SELECT latitude(ll_to_earth(0,180))::numeric(20,10);
295
SELECT latitude(ll_to_earth(45,180))::numeric(20,10);
301
SELECT latitude(ll_to_earth(90,180))::numeric(20,10);
307
SELECT latitude(ll_to_earth(-45,180))::numeric(20,10);
313
SELECT latitude(ll_to_earth(-90,180))::numeric(20,10);
319
SELECT latitude(ll_to_earth(0,-90))::numeric(20,10);
325
SELECT latitude(ll_to_earth(45,-90))::numeric(20,10);
331
SELECT latitude(ll_to_earth(90,-90))::numeric(20,10);
337
SELECT latitude(ll_to_earth(-45,-90))::numeric(20,10);
343
SELECT latitude(ll_to_earth(-90,-90))::numeric(20,10);
350
-- Test getting the longitude of a location.
352
SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
358
SELECT longitude(ll_to_earth(45,0))::numeric(20,10);
364
SELECT longitude(ll_to_earth(90,0))::numeric(20,10);
370
SELECT longitude(ll_to_earth(-45,0))::numeric(20,10);
376
SELECT longitude(ll_to_earth(-90,0))::numeric(20,10);
382
SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
388
SELECT longitude(ll_to_earth(45,90))::numeric(20,10);
394
SELECT longitude(ll_to_earth(90,90))::numeric(20,10);
400
SELECT longitude(ll_to_earth(-45,90))::numeric(20,10);
406
SELECT longitude(ll_to_earth(-90,90))::numeric(20,10);
412
SELECT longitude(ll_to_earth(0,180))::numeric(20,10);
418
SELECT longitude(ll_to_earth(45,180))::numeric(20,10);
424
SELECT longitude(ll_to_earth(90,180))::numeric(20,10);
430
SELECT longitude(ll_to_earth(-45,180))::numeric(20,10);
436
SELECT longitude(ll_to_earth(-90,180))::numeric(20,10);
442
SELECT longitude(ll_to_earth(0,-90))::numeric(20,10);
448
SELECT longitude(ll_to_earth(45,-90))::numeric(20,10);
454
SELECT longitude(ll_to_earth(90,-90))::numeric(20,10);
460
SELECT longitude(ll_to_earth(-45,-90))::numeric(20,10);
466
SELECT longitude(ll_to_earth(-90,-90))::numeric(20,10);
473
-- For the distance tests the following is some real life data.
475
-- Chicago has a latitude of 41.8 and a longitude of 87.6.
476
-- Albuquerque has a latitude of 35.1 and a longitude of 106.7.
477
-- (Note that latitude and longitude are specified differently
478
-- in the cube based functions than for the point based functions.)
481
-- Test getting the distance between two points using earth_distance.
483
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
489
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
495
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(90,0))::numeric(20,5);
501
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,90))::numeric(20,5);
507
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))::numeric(20,5);
513
SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(1,0))::numeric(20,5);
519
SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(30,1))::numeric(20,5);
525
SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(31,0))::numeric(20,5);
531
SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(60,1))::numeric(20,5);
537
SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(61,0))::numeric(20,5);
543
SELECT earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))::numeric(20,5);
549
SELECT (earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))*
550
100./2.54/12./5280.)::numeric(20,5);
557
-- Test getting the distance between two points using geo_distance.
559
SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5);
565
SELECT geo_distance('(0,0)'::point,'(180,0)'::point)::numeric(20,5);
571
SELECT geo_distance('(0,0)'::point,'(0,90)'::point)::numeric(20,5);
577
SELECT geo_distance('(0,0)'::point,'(90,0)'::point)::numeric(20,5);
583
SELECT geo_distance('(0,0)'::point,'(1,0)'::point)::numeric(20,5);
589
SELECT geo_distance('(0,0)'::point,'(0,1)'::point)::numeric(20,5);
595
SELECT geo_distance('(0,30)'::point,'(1,30)'::point)::numeric(20,5);
601
SELECT geo_distance('(0,30)'::point,'(0,31)'::point)::numeric(20,5);
607
SELECT geo_distance('(0,60)'::point,'(1,60)'::point)::numeric(20,5);
613
SELECT geo_distance('(0,60)'::point,'(0,61)'::point)::numeric(20,5);
619
SELECT geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)::numeric(20,5);
625
SELECT (geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
632
-- Test getting the distance between two points using the <@> operator.
634
SELECT ('(0,0)'::point <@> '(0,0)'::point)::numeric(20,5);
640
SELECT ('(0,0)'::point <@> '(180,0)'::point)::numeric(20,5);
646
SELECT ('(0,0)'::point <@> '(0,90)'::point)::numeric(20,5);
652
SELECT ('(0,0)'::point <@> '(90,0)'::point)::numeric(20,5);
658
SELECT ('(0,0)'::point <@> '(1,0)'::point)::numeric(20,5);
664
SELECT ('(0,0)'::point <@> '(0,1)'::point)::numeric(20,5);
670
SELECT ('(0,30)'::point <@> '(1,30)'::point)::numeric(20,5);
676
SELECT ('(0,30)'::point <@> '(0,31)'::point)::numeric(20,5);
682
SELECT ('(0,60)'::point <@> '(1,60)'::point)::numeric(20,5);
688
SELECT ('(0,60)'::point <@> '(0,61)'::point)::numeric(20,5);
694
SELECT ('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)::numeric(20,5);
700
SELECT (('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
707
-- Test getting a bounding box around points.
709
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
710
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
711
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
712
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
713
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
714
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
715
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
716
---------------+---------------+---------------+---------------+---------------+---------------
717
6266169.43896 | -111998.56104 | -111998.56104 | 6490166.56104 | 111998.56104 | 111998.56104
720
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
721
cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
722
cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5),
723
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
724
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
725
cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5);
726
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
727
----------------+-----------------+-----------------+----------------+----------------+----------------
728
-6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
731
SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
732
cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
733
cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5),
734
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
735
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
736
cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5);
737
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
738
----------------+-----------------+-----------------+----------------+----------------+----------------
739
-6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
743
-- Test for points that should be in bounding boxes.
745
SELECT earth_box(ll_to_earth(0,0),
746
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001) @>
753
SELECT earth_box(ll_to_earth(0,0),
754
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001) @>
761
SELECT earth_box(ll_to_earth(0,0),
762
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*1.00001) @>
769
SELECT earth_box(ll_to_earth(0,0),
770
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*1.00001) @>
771
ll_to_earth(0,0.001);
777
SELECT earth_box(ll_to_earth(0,0),
778
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*1.00001) @>
779
ll_to_earth(0,0.0001);
785
SELECT earth_box(ll_to_earth(0,0),
786
earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*1.00001) @>
787
ll_to_earth(0.0001,0.0001);
793
SELECT earth_box(ll_to_earth(45,45),
794
earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*1.00001) @>
795
ll_to_earth(45.0001,45.0001);
801
SELECT earth_box(ll_to_earth(90,180),
802
earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*1.00001) @>
803
ll_to_earth(90.0001,180.0001);
810
-- Test for points that shouldn't be in bounding boxes. Note that we need
811
-- to make points way outside, since some points close may be in the box
812
-- but further away than the distance we are testing.
814
SELECT earth_box(ll_to_earth(0,0),
815
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @>
822
SELECT earth_box(ll_to_earth(0,0),
823
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @>
830
SELECT earth_box(ll_to_earth(0,0),
831
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*.57735) @>
838
SELECT earth_box(ll_to_earth(0,0),
839
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*.57735) @>
840
ll_to_earth(0,0.001);
846
SELECT earth_box(ll_to_earth(0,0),
847
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*.57735) @>
848
ll_to_earth(0,0.0001);
854
SELECT earth_box(ll_to_earth(0,0),
855
earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*.57735) @>
856
ll_to_earth(0.0001,0.0001);
862
SELECT earth_box(ll_to_earth(45,45),
863
earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*.57735) @>
864
ll_to_earth(45.0001,45.0001);
870
SELECT earth_box(ll_to_earth(90,180),
871
earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*.57735) @>
872
ll_to_earth(90.0001,180.0001);
879
-- Test the recommended constraints.
881
SELECT is_point(ll_to_earth(0,0));
882
ERROR: function is_point(earth) does not exist
883
LINE 1: SELECT is_point(ll_to_earth(0,0));
885
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
886
SELECT cube_dim(ll_to_earth(0,0)) <= 3;
892
SELECT abs(cube_distance(ll_to_earth(0,0), '(0)'::cube) / earth() - 1) <
899
SELECT is_point(ll_to_earth(30,60));
900
ERROR: function is_point(earth) does not exist
901
LINE 1: SELECT is_point(ll_to_earth(30,60));
903
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
904
SELECT cube_dim(ll_to_earth(30,60)) <= 3;
910
SELECT abs(cube_distance(ll_to_earth(30,60), '(0)'::cube) / earth() - 1) <
917
SELECT is_point(ll_to_earth(60,90));
918
ERROR: function is_point(earth) does not exist
919
LINE 1: SELECT is_point(ll_to_earth(60,90));
921
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
922
SELECT cube_dim(ll_to_earth(60,90)) <= 3;
928
SELECT abs(cube_distance(ll_to_earth(60,90), '(0)'::cube) / earth() - 1) <
935
SELECT is_point(ll_to_earth(-30,-90));
936
ERROR: function is_point(earth) does not exist
937
LINE 1: SELECT is_point(ll_to_earth(-30,-90));
939
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
940
SELECT cube_dim(ll_to_earth(-30,-90)) <= 3;
946
SELECT abs(cube_distance(ll_to_earth(-30,-90), '(0)'::cube) / earth() - 1) <