Create CNN-style Map in Reporting Services

Question

Create CNN-style Map in Reporting Services

Viewing 10 posts - 16 through 24 (of 24 total)

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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 1

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..

Cheers

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 2

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.

Cheers

Peichung Shih Old Hand Points: 391 More actions · Answer 3

"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.

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 4

I changed BoundaryGeom to BoundaryGeog as seen below and now it throws the following:

UPDATE tl_2008_us_state

SET 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 2

Could not find method 'MakeValid' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'

Cheers

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 5

Per your explanation of column names changing the statement as seen below resolves the issue.

UPDATE tl_2008_us_state

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

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

Cheers

Peichung Shih Old Hand Points: 391 More actions · Answer 6

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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 7

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

Cheers

Peichung Shih Old Hand Points: 391 More actions · Answer 8

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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 9

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

Cheers

Paul Brewer SSCrazy Points: 2955 More actions · Answer 10

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

Create CNN-style Map in Reporting Services

Cookies on SQLServerCentral