March 8, 2013 at 9:53 pm
I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way to get City>County>State rollup data somewhere or get shapefiles for Counties in the US? I'm working on a database for someone that would be infinitely more useful if I could show like a heat map for sales by county across the country... there's a really cool map here[/url] that shows the county lines in it... and that's the part I want - the Counties. The granularity of the data I have is not too good, so county-level or so is about right.
Is there an easy way to create a map like that? (like a color-coded map from election night, but a county-by-county instead of state-by-state?) If so, how? And where would I get the shapefiles for the counties?
Thanks!
Pieter
March 18, 2013 at 9:16 pm
FWIW, Yuri's code, ProcessAddress() that used Yahoo's geocoding services used to return the County, but that appears to not work anymore.
If I can't get that to work, is there a table somewhere of ZipCode-->City--->County available somewhere?
March 20, 2013 at 9:09 am
Using SSRS 2008 I was able to build a county "heat map" of sales for one of our suppliers by following the instructions on https://www.simple-talk.com/sql/reporting-services/report-building-3.0-adding-maps-to-your-reports/ In the map layers property box you can choose "States by County" for the United States, after that it is setting up your analytical dataset query elements to correspond to the fields on the map.
March 20, 2013 at 9:57 pm
I had seen that article before, I'm now I'm walking through it. I noticed that the locations were already geocoded, so that's how the solution works. I did find a pretty interesting demonstration on YouTube about doing Geocoding in SSIS, but haven't implemented it yet. Took me a while to walk through it, but pretty good tutorial. (Just make sure you don't skip a step!) Super cool, because I'm trying to convince someone that Access is just not the tool for doing exactly what he does in the tutorial - mapping sales data. (Never mind the fact that Access has nothing like SSIS to do data imports and transformations).
Thanks for making me look at the tutorial more closely!
Now to figure out the Geocoding stuff a little better. Found an example on YouTube, and a website that will do small datasets, but the company I'm working with has several thousand addresses to geocode. (I used to use the YAddress geocoder, but that apparently no longer works... It had a ProcessAddress TVF that did the geocoding and returned the County each site was in.)
Pieter
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply