Distance between two zip codes

Hi Community,

Was looking through and trying to find a solution with no luck. I'm trying to find the distance between two US Postal Codes, is there any tool I can use in AX to create a third column called "distance" that calculates the distance between the two zips? Trying to find driving distance in miles between the two points or I'm fine with distance in miles "as the crow flies" as well.

Cheers,

david

zipcode.sample.yxmd

sample zip codes.csv

Location Optimizer

Accepted answers

wwatson

then all you need to do is use the Distance tool to measure the distance between your points.

The formula tool also has a spacial distance function. This would be available in the multi-row formula tool as well.

echuong1

Thanks for uploading your input file!

You will need to first create spatial points from your zip codes. This can be done using the Street Geocoder tool, but this requires a spatial license to run. The tool can find the corresponding spatial points for each zip code, and then the Distance tool can calculate the overhead distance between the two.

Without the spatial license, you'd need to find the latitude and longitude of each zip code. From there, you'd use the create points tool to create the geospatial points and then again use the distance tool. I've dynamically created the latitude and longitude using the Street Geocoder tools to example the rest of the process.

See attached for an example.

New Workflow2.yxzp

CharlieS

Hi @dlopez

What we need to calculate distance is a spatial object for each ZIP code. There are a few ways we can do this, either use a geocoder to assign a spatial object to each, or use a list of spatial objects and match them by ZIP value. The Census Bureau published a set of publicly available spatial objects called the TIGER/line files. One of the categories in this publication is the ZIP Code Tabulation Areas (ZCTAs). They can be downloaded here (along with lots of other spatial objects):

https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-line-file.html

ftp://ftp2.census.gov/geo/tiger/TIGER2019/ZCTA5/

Why use ZCTAs? ZIPs are a grouping of mail delivery points and assigning spatial objects can get a little messy. We can talk more about ZIPs in geospatial analysis later if you'd like, for now here's a link to some ZCTA documentation.

if we download the file from the Census Bureau, unzip then, and bring those into Alteryx, we can join the file to the two fields so now we have a spatial object for all the matched records. In the Attached workflow, I Transpose the data first so there's only one field to Join before using a Cross Tab to revert this afterwards. A RecordID tool is helpful to keep everything organized during these transformations.

Now that we've assigned a spatial objects to the {Home ZIP] and [Work ZIP] values, a Distance tool can calculate the straight line distance value between then. FYI the Formula tool has spatial functions and could be used instead of the Distance tool if you'd like.

Check out the attached example and let me know if you have any questions.

20200724-ZIPDistance.yxmd

All comments

echuong1

Are you able to upload your csv file? The workflow you uploaded doesn't contain any data.

You'd need to find the geospatial point of each zip code first. If you have latitude and longitude, you can use the Create Points tool to do this. From there, you can use the Distance tool to calculate the distance between the two.

If you have access to the Spatial data pack, you can use the geocoder tools to create the geospatial points from the zip codes directly.

Hope this helps!

wwatson

You would need to convert the ZIP codes into points first. There is a tool for it but you may need additional license to access the data it uses.

dlopez

Hi @wwatson

I've got that tool, and added it to the attachment. I've also cleaned up the zip codes to where all of them are five digit codes, there were some nine digit codes in there.

dlopez

Hi @echuong1

Thanks for your feedback on this, just uploaded the data.

Cheers,

david

echuong1

Thanks for uploading your input file!

See attached for an example.

New Workflow2.yxzp

CharlieS

Hi @dlopez

https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-line-file.html

ftp://ftp2.census.gov/geo/tiger/TIGER2019/ZCTA5/

Check out the attached example and let me know if you have any questions.

20200724-ZIPDistance.yxmd

dlopez

Hi Community!

All your solutions worked, they were all very similar and very helpful. I'll try to summarize what I needed to do and how I did it based off everyone's feedback.

What I needed to do:

1. turn my zip codes into lat & long points (did this by joining my data with another data table that had lat/long associated with zip code)

2. turn my lat & long points into spatial points (create point tool twice, the tool only takes lat & long once, so I needed two)

2. calculate the distance between the two spatial points (feed this tool the two points to measure between)

Thank you guys very much, learned something new today and learned how to do it a few ways.

Cheers,

david

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703