I created a custom TopoJSON map of 2014 US CBSAs as defined by the U.S. Census Bureau for anyone to reuse. Simply copy the URL below to the ‘TopoJSON URL’ field in the editor window as shown in the screenshot below. Make sure you have selected ‘Static Map Regions’ in the visualization pane and not the default map icon.
The map should render in your browser as long as the field in your query contains CBSA or MSA (Metropolitan Statistical Area) codes. This TopoJSON file excludes Hawaii and Alaska.
If you need a custom map that includes HI, AK and US territories, you can use other custom TopoJSON files I created in a public Gist on Github here. You will have to copy the raw Gist URL to rawgit in order to host the TopoJSON file. Then simply copy the production URL into the ‘TopoJSON URL’ field in Looker. Refer to this discourse for a more detailed description of how to host a TopoJSON file on rawgit.
Thanks for sharing this
@aching! I was curious what timeframe you used to create the CBSA regions? I’m wanting to use a CBSA --> Zip mapping table from https://www.huduser.gov/portal/datasets/usps_crosswalk.html but they have new mappings for each quarter. Can you let me know when you pulled the 2014 US CBSAs?
Hope this helps. Makes me think I should create a public repo with the raw data to make the transformation more transparent as I’m sure I’ll have to periodically update the files.
I just called the Census department and they’re going to get back to me on the “as-of date” of the shapefile you used. I was surprised they picked up! I’ll let you know what they say.
@law did you ever figure out what the as-of-date of the shapefile was? trying to map the zip codes to CBSA myself 🙂
also, one hack I thought was neat is that you can set the visualization to display the name of the CBSA instead of just the number:
Whoa blast from the past! Sorry, I don’t think I ever did figure out the as-of-date…
I originally posted about this custom Topo JSON for CBSA geographic boundaries. When I created the public gist, I grabbed the 2014 shape files, which are located here. Since then, the Census Bureau has added shape files for 2015 and 2016. Hope this helps.
thanks Anne and Lawrence!
I fixed the issue, but it’s a bit complicated: the HUD provides Zip -> CBSA mappings every quarter like Lawrence posted above, so I tried unsuccessfully to match the right snapshot to the CBSAs that are in Anne’s shapefiles (and the new ones from the Census Bureau). The reason I wasn’t successful is that the HUD seemingly uses a different set of CBSAs than the Census Bureau does, so even prominent CBSAs (like the one LA is in) have different CBSA IDs.
What I was able to do was get a Zip -> CBSA mapping here: https://www2.census.gov/geo/docs/maps-data/data/rel/zcta_cbsa_rel_10.txt . This is from 2010, so some of the CBSAs are outdated, but there was much more overlap than with the HUD mapping file. I did also go in and update many of the CBSAs using this data: http://mcdc.missouri.edu/data/georef/Tools/cbsa_changes.lst.
Hope this helps the next brave soul who decides to go down the path of mapping Zip codes and CBSA!