Create CNN-style Map in Reporting Services

  • Comments posted to this topic are about the item Create CNN-style Map in Reporting Services

  • Not had a chance to go through it properly but at first skim read that looks like a massively comprehensive article!

    Thanks! :w00t:

  • Really good article, thanks for putting this together...

  • I have not worked on maps, but this article will get me started!

    I have just gone through it once, and it is indeed quite thorough - must have taken a lot of time and effort to put this together. Thank-you for the research!

    Thanks & Regards,
    Nakul Vachhrajani.

    Follow me on
    Twitter: @sqltwins

  • I am not sure, but I supose these map tools are available only for USA.

    could be great if it is available for Peru for example...

    However this post is a great for me. congratulations

  • Thanks.

    Is it just me or is Michigan wrong, though?

  • Question:

    Does the database and SSRS have to be at R2 level or can the data be in SQL Server 2008 and then say a seperate instance of 2008 R2 running SSRS?

  • Very complete article, but there seems to be a lot of re-inventing the wheel here. We already have GIS (Geographic Information System) software that does automatically (in the background) much of what had to be coded. The article also doesn't mention using GIS data already stored in SQL Server, i.e. in an SDE (Spatial Database Engine) geodatabase. While this is specific to ESRI, a very large chunk of GIS data in many organizations is stored that way.

    Another concern is turning database people untrained in cartographic principles loose on mapmaking. I have seen extremely misleading maps made by very well-intentioned folks. A good book about this issue is "How to Lie with Maps" by Matk Monmonier. It is very easy for those untrained in spatial thinking to apply the wrong processes to solve a problem, resulting in totally wrong conclusions.

    Ray Montgomery

  • Hi,

    Thanks for your comment. The Great Lakes shorelines were not carved out in the 2008 census shapefile. In our production environment, we use ESRI state shapefile. With the same parameters discussed in the article, the map looks much better (see attached screenshot). Alternatively, you can use county shapefile (countyp020.tar.gz) from National Atlas and edit out the shorelines yourself. I highly recommend a free tool called fGIS to do the editing. I created a short video (also attached) to show how the editing can be easily done in fGIS. Thanks,


    Mateo1041 (3/7/2011)


    Is it just me or is Michigan wrong, though?

  • Hi Kevin,

    Thanks for your comment. Unfortunately, I don't have SQL Server 2008 in our environment to test this. However, I'd think it will work as long as your SSRS runs on R2. Please let us know if you find out otherwise. Thanks,


    Kevin Rathgeber (3/7/2011)


    Does the database and SSRS have to be at R2 level or can the data be in SQL Server 2008 and then say a seperate instance of 2008 R2 running SSRS?

  • Very informative and easy to follow quick kick start article on using SQL Server with Maps (storing the data inside db engine) and capitalising on SSRS R2 map capabilities. I like.:-D

    Maps can be downloaded ifrom most GIS web sites, regardless of you country of origin (not limited to US only).

  • Someone, please help me. I successfully registered the assembly SQLSpatialTools but when I run these statements to use the AffineTransform function I get this error:

    Msg 243, Level 16, State 4, Line 12

    Type AffineTransform is not a defined system type.

    What is wrong with my system? As I mentioned above. All other steps worked without any issues.



  • Never mind my previous post. I ran the Register.sql on a wrong db. I was able to run all the sql statements after I ran Register.sql.

  • Step 3 seems to have issues

    UPDATE tl_2008_us_state

    SET BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeom.MakeValid().STAsBinary(), 4326)

    WHERE NAME NOT IN('Massachusetts', 'New Mexico', 'Wyoming', 'Montana', 'Texas', 'North Carolina', 'Arizona', 'Tennessee', 'Utah')

    Msg 4121, Level 16, State 1, Line 1

    Cannot find either column "BoundaryGeom" or the user-defined function or aggregate "BoundaryGeom.MakeValid", or the name is ambiguous.


  • Did you get any errors while importing the shapefile: tl_2008_us_state.shp ? From the error message, it looks like BoundaryGeom wasn't there in the table. Please double check the Geometry coumn's name.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply