Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Lat/Long --> County (FIPS) mapping?

Highlighted
Alteryx Partner

I've looked through some examples of creating centroids to lookup, but can't get all of the way there.

 

I want to add a FIPS code to the lat/long for the restaurants at these restaurants: http://www.fastfoodmaps.com/fastfoodmaps_locations_2007.csv.zip

 

This is eventually going into tableau but I want to link these restaurants with the rest of my data set which is using FIPS codes. 

 

Thanks so much, really appreciate any help!

Highlighted
Alteryx Certified Partner

Hi @cbozarth, assuming you have access to the add-on Core Data bundle package, you could use the US ZIP9 Coder Tool in Alteryx to fetch FIPS code. Alternatively, you could make an API call to a 3rd party URL by passing the relevant lat/longs.

 

In the attached example I manually construct the URL for every combination of lat/longs in your dataset and make an API call to get back data in JSON format, which is parsed downstream to get FIPS code. The data has other attributes that come back as part of the API call, so maybe there is more available for you to leverage than what you are looking for.

https://geo.fcc.gov/api/census/area?lat=25.8092&lon=-80.24&format=json

 

AbhilashR_0-1589947594525.png

I hope this helps and please let us know if this addresses your ask.

Highlighted
Alteryx Certified Partner

To link restaurant locations to a FIPS code, you need a FIPS code/county boundary.

I've taken your data, created a point for each location using the latitude and longitude with the Create Points tool but once you're there, you need some way to link it to a county (FIPS code).  You can download the free census data to get the county boundaries (and FIPS codes) and then use the Allocate input (to bring in the county boundaries) and then use the spatial match tool to identify which county each restaurant falls in and then append the FIPS code from it.

Here is my workflow and the output file in an Alteryx export package.  You need to install the free census data for it to work.  The free census data can be found where you download the Alteryx software in the Data Packages area.

 

Highlighted
Alteryx Certified Partner

Hi @cbozarth, as long as you have access to additional data package add-on's you can use Alteryx's US ZIP9 Coder tool to get FIPS. Alternatively you can make an API call to a 3rd party service by passing your lat/longs.

 

In the attached solution, I artificially create URL's using your lat/longs and pass it through the Download tool, which makes the API calls for us and fetches data back in JSON format. I use a JSON Parse tool and get the data we need downstream.

 

https://geo.fcc.gov/api/census/area?lat=25.8092&lon=-80.24&format=json

Note: the API call has information more than just FIPS, so give it a glance. It might come handy as you attempt to visualize this data in Tableau.

AbhilashR_0-1589948437777.png

 

I hope this helps and please let us know if this addresses your ask.

 

P.S.: my previous post to this thread magically vanished, so pardon me if you see my response twice.

Highlighted
Alteryx Partner

I ended up using the URL method and it worked great! 

 

If I were to have used the ZIP9 method, do I need to have an interim conversion from Lat/Long to ZIP9? 

Highlighted
Alteryx Certified Partner

I am inclined to say yes in your case.

Labels