SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Peichung Shih / Create CNN-style Map in Reporting Services / Latest Posts

RE: Create CNN-style Map in Reporting Services

Paul Brewer — Sat, 02 Jun 2012 12:35:17 GMT

I'm try to do something similiar and going through this article, line by line, has given me a lot of ideas. It's excellent and helps a lot. Thanks

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 13:01:04 GMT

Ah! Now I see it. I didn't catch that when importing the shape file. I left geom as the default.

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Fri, 01 Jun 2012 12:56:47 GMT

See screenshot in step 2, lower right panel, I named Geometry name as BoundaryGeom.

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 12:49:47 GMT

I do not have those two. I see the add column statement for BoundaryGeog but nothing that either renames, drops and adds, etc BoundaryGeoM

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Fri, 01 Jun 2012 12:43:00 GMT

Please refer to my post. You should have two fields added to tl_2008_us_state: BoundaryGeom (GEOMETRY) and BoundaryGeog (GEOGRAPHY).

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 12:42:09 GMT

Per your explanation of column names changing the statement as seen below resolves the issue.UPDATE tl_2008_us_stateSET BoundaryGeog = GEOGRAPHY::STGeomFromWKB([b]Geom[/b].MakeValid().STAsBinary(), 4326)WHERE NAME NOT IN('Massachusetts', 'New Mexico', 'Wyoming', 'Montana', 'Texas', 'North Carolina', 'Arizona', 'Tennessee', 'Utah')

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 12:34:29 GMT

I changed BoundaryGeom to BoundaryGeog as seen below and now it throws the following:UPDATE tl_2008_us_stateSET BoundaryGeog = GEOGRAPHY::STGeomFromWKB(BoundaryGeog.MakeValid().STAsBinary(), 4326)WHERE NAME NOT IN('Massachusetts', 'New Mexico', 'Wyoming', 'Montana', 'Texas', 'North Carolina', 'Arizona', 'Tennessee', 'Utah')Msg 6506, Level 16, State 10, Line 2Could not find method 'MakeValid' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Fri, 01 Jun 2012 12:31:55 GMT

"geom" is the name you created during the import. I named it BoundaryGeom. BoundaryGeog is another field I added to the tl_2008_us_state table. geom is a Geometry field while BoundaryGeog is a geography field.

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 12:27:11 GMT

I noticed while looking at the table structure for tl_2008_us_state that there is a column called geom and one called boundaryGeog. Just mentioning this because it is odd those two names are a cross between what is causing the current situation.

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 12:22:58 GMT

I didn't actually. Not message, etc at all. A search of sys.objects in every database returns no results as well. On with the search..

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Fri, 01 Jun 2012 10:57:37 GMT

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.

RE: Create CNN-style Map in Reporting Services

jfogel — Fri, 01 Jun 2012 10:41:29 GMT

Step 3 seems to have issuesUPDATE tl_2008_us_stateSET 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 1Cannot find either column "BoundaryGeom" or the user-defined function or aggregate "BoundaryGeom.MakeValid", or the name is ambiguous.

RE: Create CNN-style Map in Reporting Services

suslikovich — Fri, 06 May 2011 14:49:01 GMT

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.

RE: Create CNN-style Map in Reporting Services

suslikovich — Fri, 06 May 2011 13:46:18 GMT

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 12Type AffineTransform is not a defined system type.What is wrong with my system? As I mentioned above. All other steps worked without any issues.Thanks,Stan

RE: Create CNN-style Map in Reporting Services

Mahlatse Sebona-383199 — Tue, 08 Mar 2011 14:03:35 GMT

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.:-DMaps can be downloaded ifrom most GIS web sites, regardless of you country of origin (not limited to US only).

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Mon, 07 Mar 2011 09:28:15 GMT

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,Peichung[quote][b]Kevin Rathgeber (3/7/2011)[/b][hr]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?[/quote]

RE: Create CNN-style Map in Reporting Services

Peichung Shih — Mon, 07 Mar 2011 09:09:45 GMT

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 [url=http://nationalatlas.gov/atlasftp.html?openChapters=chpbound#chpbound][u]National Atlas[/u][/url] and edit out the shorelines yourself. I highly recommend a free tool called [url=http://www.forestpal.com/fgis.html][u]fGIS[/u][/url] to do the editing. I created a short video (also attached) to show how the editing can be easily done in fGIS. Thanks,Peichung[quote][b]Mateo1041 (3/7/2011)[/b][hr]Thanks.Is it just me or is Michigan wrong, though?[/quote]

RE: Create CNN-style Map in Reporting Services

rmontgom — Mon, 07 Mar 2011 08:29:02 GMT

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

RE: Create CNN-style Map in Reporting Services

Kevin Rathgeber — Mon, 07 Mar 2011 08:16:45 GMT

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?

RE: Create CNN-style Map in Reporting Services

Mateo1041 — Mon, 07 Mar 2011 07:38:08 GMT

Thanks.Is it just me or is Michigan wrong, though?

RE: Create CNN-style Map in Reporting Services

lbarrueto — Mon, 07 Mar 2011 05:38:03 GMT

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

RE: Create CNN-style Map in Reporting Services

Nakul Vachhrajani — Mon, 07 Mar 2011 05:10:55 GMT

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!

RE: Create CNN-style Map in Reporting Services

steveb. — Mon, 07 Mar 2011 03:28:34 GMT

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

RE: Create CNN-style Map in Reporting Services

Samuel Vella — Mon, 07 Mar 2011 01:02:01 GMT

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

Create CNN-style Map in Reporting Services

Peichung Shih — Sun, 06 Mar 2011 23:27:45 GMT

Comments posted to this topic are about the item [B]Create CNN-style Map in Reporting Services[/B]