The DISTANCE and DISTANCEV Functions

[JimKnicely]

Vertica includes an interesting function named DISTANCE. It returns the distance (in kilometers) between two points. To use it simply pass in the latitude and longitude of both the starting point and the ending point.

For example, using these two very well-known exotic locations…

• #1: Innovations HQ
  30 Isabella St Pittsburgh PA 15212
  Latitude and Longitude: (40.447914, -80.003696)

• #2: Vertica HQ
  150 Cambridgepark Dr Cambridge MA 02140
  Latitude and Longitude: (42.394914, -71.146388)

…we can find out how far apart (in a straight line) they are via the DISTANCE function in Vertica like this:

Code: Select all

dbadmin=> SELECT distance(40.447914, -80.003696, 42.394914, -71.146388) distance_to_Vertica_HQ;
distance_to_Vertica_HQ
------------------------
       769.188185254648
(1 row)

If we want to be more accurate, the DISTANCEV function can be used. It returns the distance (in kilometers) between two points using the Vincenty formula (which we all learned in grade school).

Code: Select all

dbadmin=> SELECT distancev(40.447914, -80.003696, 42.394914, -71.146388) distance_to_Vertica_HQ_v;
distance_to_Vertica_HQ_v
--------------------------
         770.949054725888
(1 row)

To verify that Vertica calculated the distance correctly, I compared the results from the web site: http://www.gpsvisualizer.com/calculators:

• 

Have fun!