What’s the Distance Between Two Zip Codes? Quick Tip

Vertica has a built-in function called DISTANCEV which returns the distance (in kilometers) between two points using the Vincenty formula. Because the Vincenty formula includes the parameters of the WGS-84 ellipsoid model, you need not specify a radius of curvature. You specify the latitude and longitude of both the starting point and the ending point.

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!