3 Replies Latest reply on Mar 16, 2016 8:11 AM by Thomas Exter

    Utilize CBSA/MSA Geographic Role without actually having the values

    Kevin Colwell

      Hello forum. I have been tasked with displaying the year over year growth for real estate sales prices by CBSA/MSA. My database contains the following fields: Street, City, State, Zip code, Latitude, and Longitude. Is there anyway that I can utilize any of these fields to get a filled map to populate by CBSA/MSA? I have tried to connect to the Firebird database to create a query that I could join my results to but I have been unsuccessful in creating the proper joins. Any assistance would be greatly appreciated.

        • 1. Re: Utilize CBSA/MSA Geographic Role without actually having the values
          Shawn Wallwork

          Your best bet is to use a db that has both ZIPs and CBSAs in it. I attached a 9.2 workbook that uses an old (2014) Zip code db that includes both. Here's what yo can do with it:

           

           

          --Shawn

          • 2. Re: Utilize CBSA/MSA Geographic Role without actually having the values
            Kevin Colwell

            Thank you for your reply Shawn. With no other replies it appears that there is not much I can do.

            • 3. Re: Utilize CBSA/MSA Geographic Role without actually having the values
              Thomas Exter

              Hi Kevin,

               

              If you want/need to wrangle with this a bit more, you can take Shaun's idea of zips within CBSA's a couple more steps. By the way, I'm not trying to take you down a rabbit hole but your issue is tricky. You might want to do/think about the following:

              1. Instead of "filled maps" for CBSA's think about bubbles (mark maps) tied to each metro. I like these because the size of the bubble can be the number of home sales in the metro and the color coding can be the mean price of home sold in a given time period.

              2. Aggregate your point sales data by zip then by metro using either the average or median function. (You might want to weight the average by the number of sales per zip.) Create other calculated fields as needed like absolute change in average price or percent change. Any one or two of these values can be used in your mark map.

              3. I see that Shaun's data is pretty comprehensive; not sure of the source or permissions. If you can't use that to create a Zip Code to CBSA correspondence you can still test this idea with a ZCTA to CBSA correspondence found here: 2010 ZIP Code Tabulation Area (ZCTA) Relationship File Layouts and Contents - Geography - U.S. Census Bureau (Of course these 2010 ZCTA's don't account for new or dropped zips since 2010 but you should be able to match a good percentage plus you have your Lat Longs to fall back on.)

              4. A final point on metros: CBSA's of course can be huge, so Divisions and stand-alone MSA's might be preferred. All depends on how you organization has been reports these stats in the past. Boundary files can be presented via Mapbox but that's another story.

               

              Again, hope I'm not confusing the issue, it's just that your question poses several of these types of challenges.

               

              Best regards,

              Tom