Viewing Census Data on Bing Maps
Census data can be extremely useful in business. Being able to visualize demographic data on a map is something that many companies do within their business intelligence tools. This gives them insight into areas such as income, population, race, even the types of industry people work in. This kind of information can prove vital when trying to research new sales regions or putting together targeted marketing campaigns. Another interesting use for this data is to compare the census data to the election polls.
As I mentioned before the Census Bureau makes this data available to the public. One of the most commonly used data is the boundaries information. This information traditionally has been made available in Tiger format but in recent years has been made available in more common formats such as ESRI Shapefile and KML format. In addition to the boundary data there is tons of demographic data that is related to each boundary. Needless to say there is a massive amount of data available from the Census Bureau; there is several gigabytes worth of data in boundary information alone.
With the elections around the corner one common site used by those who follow the elections is Ballotpedia. This site has made use of CensusViewer to provide voter data for the Congressional districts of New York. These publicly accessible maps show registered voter counts inside of Bing Maps. Visitors can zoom in on any area of the state and compare old and new districts projected on the map. Check it out for yourself here; NY US Congress: Census 2001, Census 2011; NY State Senate: Census 2001, Census 2011; NY State House: Census 2001, Census 2011. Here is a screen shot of the NY State Senate data from 2001.
What is CensusViewer?
CensusViewer is an online application built by Moonshadow Mobile. This application gives you access to the 2010 and 2000 Census “Summary File 1” data from the U.S. Census Bureau as well as the selected American Community Survey (ACS) data and extensive data including registered voters and frequently updated commercial data sources. It is an excellent online tool for demographic analysis of the U.S. population. With CensusViewer you can navigate the census data from within the familiar Bing Maps interface coupled with Moonshadow’s cutting-edge database technology, to provide an intuitive platform for accessing and analyzing the data. Now, there are a couple different versions which give you access to different levels of data. If you want to try it out yourself, you’re in luck because there’s a free version. Here is a heat map created using this tool of the US population that is at or below the poverty level.
Integration with Bing Maps
Integrating census data with Bing Maps is something that I come across on a fairly regular basis. One common issue many developers come across is that the boundary data and the demographic data are two separate data sets. It’s up to the developer to connect the two. This isn’t overly difficult as there are census track ID’s that tie everything together, but the real issue comes with figuring out how to link the data programmatically.
So how do we do it? Using the data in KML format may be tempting as it is in an easy human readable format. However, a single KML file that has the boundaries for a single county in the US is roughly 3MB in size. If you try loading in the entire boundary dataset as KML format it won’t take long before you run into performance issues. In addition to this you would have to write a lot of code to link the demographic data to the boundary information. All of this can be done but there is a much better way.
I personally prefer working with the ESRI Shapefiles over KML. One big benefit is that it’s rather easy to upload an ESRI Shapefile into SQL Server as I described in a recent blog post. Once this data is in SQL Server linking it up with the demographic data is rather simple due to the relational nature of SQL. Once you have the data in SQL Server you are half way to visualizing it on a map. In fact if you want to quickly view this data you can simply do queries inside of SQL Management studio to see what it looks like without a map. You can take it a step further and install SQL Report Builder which can easily overlay your spatial data on top of a static Bing Maps image. Note that SQL Server 2008, 2012 and Azure all support spatial data.
Being able to see the data in SQL Reports is a good start but let’s be honest, we want view it using an interactive map. There is a couple of ways to go about this. One method is to create a web service for exposing your data to your map. This method works well with small data sets but not so much for larger data sets. Ideally you don’t want your users having to download all the census data to their maps. This would not make for a very good user experience and would likely be too much data for the browser. One option is to make use of the spatial tools in SQL Server and reduce the resolution of the data based on zoom level. This method can significantly reduce the size of the data while keeping the data interactive.
Another approach is to convert your spatial data into an image and overlay that on the map. Using this approach the user only has to download a small image which will make for fast loading on the map. This drastically improves performance. Now you might be thinking “how do you interact with the data if it’s an image?” Well you can add a click event to the map and when the user clicks the map, send the coordinates through a web service to your database and query any data that intersects with your coordinates. This is a rather simple query with the spatial tools in SQL Server. You can then return the data and represent to the user. I have had great success using this approach in a number of projects.
So how do you turn your data into an image? Well I actually wrote a blog posts on this topic in the past. Since then one of our Bing Maps partners, OnTerra, has create an open source project called AJAX Map DataConnector. This tool uses the MVC framework to expose spatial data in SQL Server to the Bing Maps AJAX control. In addition to this it also has several common spatial queries built into it and the ability to render the data on the map as either interactive data or as an image. This tool is a great place to start if you want to develop something yourself. OnTerra also has a commercially supported version of this tool called GeoSavvy.
Now so far all of these solutions require you to manage the data and do some development. What if you just want to visualize the data on Bing Maps without all the hassle of developing an application? Well CensusViewer is the solution for you!