Create CNN-style Map in Reporting Services

  • jfogel

    SSCarpal Tunnel

    Points: 4135

    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: 4135

    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

    "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: 4135

    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: 4135

    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

    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: 4135

    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

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

  • jfogel

    SSCarpal Tunnel

    Points: 4135

    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: 2827

    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

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

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