Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Custom TopoJSON for 2014 Core Based Statistical Areas (CBSA)

  • 20 January 2016
  • 9 replies
  • 662 views
aching
Userlevel 1

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.


https://cdn.rawgit.com/anneching/bb4982bbc63966e63bdd/raw/87b1986b1b9e0a9547fbb60b4c609932f6ddc148/cbsa_us_2014_ex_hi_ak.json


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.

9 replies

law
Userlevel 2

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?


Lawrence

aching
Userlevel 1

@law I believe the timeframe was 2Q 2015, but I’m not completely certain. Here’s the link where I grabbed the boundary shapefiles: https://www.census.gov/geo/maps-data/data/cbf/cbf_msa.html

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.


Anne

law
Userlevel 2

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
Userlevel 2

@aching I did have one more question. In using your shapefile, do you know why when hovering over different regions, it sometimes shows the CBSA code (5 digits) and others it seems like it is showing a CSA code (3 digits).

aching
Userlevel 1

@law I’m actually not sure why the CSA code appears since the data in my example only contain CBSA codes. Somehow both CSA and CBSA codes must be embedded in the topojson file. I noticed that if you single click on the areas that display CSAs, the corresponding CBSA will appear in a pop-up window as shown in the screenshot below.


A

hey @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:

law
Userlevel 2

Whoa blast from the past! Sorry, I don’t think I ever did figure out the as-of-date…

aching
Userlevel 1

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.

A

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!

Reply


Terms of Service