
Example:
dbadmin=> SELECT DISTANCE(40.4406, -79.9959, 28.3852, -81.5639) Disney_is_this_far_from_home;
Disney_is_this_far_from_home
------------------------------
1348.12778743491
(1 row)
But what if I want to measure the distance between two zip codes? No problem! But first we’ll need a table that stores the longitude and latitude by zip code.Longitude and Latitude by zip code data is readily available online. For this example, I grabbed the needed data from here:
https://gist.githubusercontent.com
I put the data from the above link into a file called /home/dbadmin/zips.txt and loaded it into a Vertica table: dbadmin=> CREATE TABLE zipcode_geography (zipcode VARCHAR(5), latitude NUMERIC(25,6), longitude NUMERIC(25,6)); CREATE TABLE
dbadmin=> COPY zipcode_geography FROM '/home/dbadmin/zips.txt' DIRECT;
Rows Loaded
-------------
33144
(1 row)
I can find the longitude and latitudes for two zips code like this:
dbadmin=> SELECT z1.zipcode, z1.latitude, z1.longitude, z2.zipcode, z2.latitude, z2.longitude
dbadmin-> FROM zipcode_geography z1
dbadmin-> CROSS JOIN zipcode_geography z2
dbadmin-> WHERE z1.zipcode = '15090' -- Wexford, PA
dbadmin-> AND z2.zipcode = '43210'; -- OSU (Columbus, OH)
zipcode | latitude | longitude | zipcode | latitude | longitude
---------+-----------+------------+---------+-----------+------------
15090 | 40.625015 | -80.067058 | 43210 | 40.005435 | -83.023227
(1 row)
Now I can use the DISTANCEV function to get the distance between the two zip codes in kilometers and miles!
dbadmin=> SELECT distancev(z1.latitude, z1.longitude, z2.latitude, z2.longitude) kms,
dbadmin-> distancev(z1.latitude, z1.longitude, z2.latitude, z2.longitude) / 1.609 miles
dbadmin-> FROM zipcode_geography z1
dbadmin-> CROSS JOIN zipcode_geography z2
dbadmin-> WHERE z1.zipcode = '15090' -- Wexford, PA
dbadmin-> AND z2.zipcode = '43210'; -- OSU (Columbus, OH)
kms | miles
------------------+-----------------
260.507361625431 | 161.90637764166
(1 row)
Helpful Link:https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/DISTANCEV.htm
Have fun!