Posted on

This is the first part of a two part series about how to set up leaflet maps to show zipcode areas with some extra data coming along for the ride allowing us to color the zip code areas based on demographics or any other reporting data. I did this using an ASP.NET MVC site and Web API with a Sql Server database. A database is required for this approach but there's nothing stopping you from reimplementing this in your backend architecture of choice.

Here's what it looks like sans the external controls on the page to change reporting parameters and the coloring function (more on that later).



This part will focus on getting the zipcode data into your db and your backend set up to serve it out in JSON, ready for leaflet maps to receive it.

1. Get the census shape data

First up is getting the basic boundary data for all the zip codes in the US. Luckily our handy census bureau is kind enough to provide this. Head on over to data.gov and get the line shapefile. Inside that zip file should be a ~816mb .shp file.

2. Shrink it up with QGIS

The level of detail is really way more than we need for leaflet, so to spare everyone slow loading times we're going to simplify some of the geometry in the shape file. QGIS is a free tool that can open up the .shp file and simplify it. To do that, add the layer by going to the menu Vector > Geometry > Simplify Geometry. I used the simplification of 0.0003 to get the file down from the ~816m down to ~96mb. Have the shrinker save it to a new file for the next step. If you've never seen the whole US zip code map before it's quite the sight!.

3. Convert the shape into GeoJSON

Leaflet needs all this shape data in a standard JSON format so to get it from .shp to GeoJSON we need FWTools. Thanks to the GIS Collective for their tutorial on this step. Once you have the FWTools installed go to their bin directory and run the ogr2ogr command to convert the file:

C:\Program Files (x86)\FWTools2.4.7\bin>ogr2ogr.exe -f "geoJSON" "C:

ew-path\simp.js" "C:\old-path\simp3.shp"

With the JSON overhead the file is now sitting at around 175mb. If you're feeling like a mean web developer you could make your clients download that file for their map now ;) However, I was feeling generous so let's get the data into a DB so we only serve up what they really need to see.

4. Create your table

5. Get the data into your DB

Now we need to import our GeoJSON data into the database but also pick up where each zip code is at. If you crack open the .js file now you can see that each row after the first few lines has some meta data about what zip code it is and its latitude and longitude. Since this will be a one time deal I got hacky and wrote a simple LinqPad script to loop through the lines and put it in the DB. Here it is for convienience but it requires you to chop off everything but the contents of the features array inside the .js file.

Gotta love those hard coded string positions. If I wanted to waste any more brain cycles on this little bit I'd run it through a proper parser and then import but the quick and dirty way was faster here.

If you were paying close attention you would have noticed there's a point column in the table we created. This is a nifty feature of Sql Server that allows us to store a proper latitude longitude point in a single column which can be queried against in a much more elegant way as we'll see later. Pre storing this in a column instead of creating it in the query is muuuuch faster. Promise.

UPDATE zipcode_geojson SET point = geography::Point(latitude, longitude, 4326)

Optionally, you can now update the point column to be non-nullable if you want to crack your DBA whip.

7. Get some demographic data

This is also optional, but makes the results much more interesting. I opted to get a cheap zipcode demographic database from easydbs.com for $30. It comes with a decent amount of data about income/age/population which is just what I was looking for. Here's their schema for reference that I'm working off of.

8. Serve it up

This is the most complicated bit to get the JSON data out of the database, join it together with with the demographic data, and serve it back out as JSON. Quickly. To do this I've got some help from Dapper to get the data from Sql Server into objects, and C# Async to fetch any other reporting data at the same time as the zipcode data since that can also take a bit of time.

Let's start with the geoJSON model that queries the zipcode database we worked so hard to get.

I started with the circle approach of fetching zips based on the center point of the map and some radius based on zoom until I realized I could get the lat/long corners of the leaflet map. A future improvement would be to expand the query viewport slightly to capture the zips who's center is outside the boundaries but still has area inside the viewport.

To explain the model a bit, the zipCombined class is used for the query result we get out of sql server that has the demographic data joined in. The rest of the geoX classes are the format of GeoJSON strongly typed out (save for the geoGeometry coordinates which are complicated and don't need to be strongly typed for what we need to do).

Onto the controller:

The basic idea is to fetch the zip data and any other reporting data simultaneously, wait for both to finish, join up the results, and then pop it on down to the client. I also was originally saving the client from calculating the min and max revenue but ended up doing it on the client side anyways. You might also notice notice I'm using a closure with the httpContext in GetReportingDataAsync. This has to be passed as a parameter to an async function if you want to use it for caching.

This just about wraps it up for the backend part of getting some wonderful leaflet maps on your page with zip code data. One final thought about serving up this massive stack of JSON zip code data, make sure you have gzip enabled on your server. The responses for a decent size area were upwards of 5mb without compression on and less than 500kb once I got it set up.

Check out Leaflet Zip Code Map Part 2 for how to set up the front end.