A friend recently asked me about the cool looking bivariate maps produced with ArcOnline, lamenting that the capability seemed lacking in ArcGIS. Well, it turns out that ESRI has a .dll you can use, and there is a good article here. So, if you want to create these great looking maps in ArcGIS, it shouldn’t be a problem. The website will allow you to download the .dll, and you can also watch the video on how to use it. Well worth the time.
So of course that got me thinking: could we make the same map with spatial SQL. Well, sure, and it is super easy. If you want to get spun up on what these 9 color bivariate choropleth maps are, and the theory behind it, have a look at this great site. Josh does a great job explaining how this works, but it is a little cumbersome if you want to pump out map after map. But, with a very little bit of SQL, you can easily pull it off.
Let’s start with our data: I have a Postgres table of United States County boundaries, with the attributes percentobese and percentdiabetes, along with a FIPS code and a geometry column.
To prepare the data for the map, we simply issue this SQL query:
ntile(3) over (order by percentobese) || '.' ||
ntile(3) over (order by percentdiabetes) AS bimode
yes, that’s it. Really.
I’m not kidding. We’re done, folks. Go home, nothing left to see.
Well, if you insist on reading, I’ll tell you what the SQL does, and how to actually visualize the data.Continue reading