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