Read Map data from SQL Server 2012

Topics: SharpMap v0.9 / v1.x
Feb 20, 2014 at 10:33 PM
Hello everyone, I am trying to retrieve data from my database and set the datasource of my map but for some reason it doesn't show up. Here is my code:
 Private Function InitializeMap(ByVal outputsize As System.Drawing.Size) As SharpMap.Map
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString
        Dim map As SharpMap.Map = New SharpMap.Map(outputsize)
        Dim layCountries As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("states")

        Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID", SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry, True, 4326)
      
        layCountries.DataSource = d
       
        layCountries.Style.Fill = New SolidBrush(Color.Green)
        layCountries.Style.Outline = System.Drawing.Pens.Red
        layCountries.Style.EnableOutline = True
        map.Layers.Add(layCountries)
        Return map
    End Function
Any suggestion on how I can get this to work? When I do a selection in SQL Server Management Studio, it's showing the data just fine. I checked the datatype of of [geom] column is sys.geometry. Thanks.
Coordinator
Feb 21, 2014 at 8:03 AM
Add
map.ZoomToExtents()
before returning it.
Feb 24, 2014 at 8:33 PM
Thank you FObermaier for the reply. I added the line but it still doesn't work for me. Here is my whole code:
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Set up the map
        myMap = InitializeMap(New System.Drawing.Size(CInt(imgMap.Width.Value), CInt(imgMap.Height.Value)))
        If Page.IsPostBack Then
            'Page is post back. Restore center and zoom-values from viewstate
            myMap.Center = DirectCast(ViewState("mapCenter"), GeoAPI.Geometries.Coordinate)
            myMap.Zoom = CDbl(ViewState("mapZoom"))
        Else
            'This is the initial view of the map. Zoom to the extents of the map:
            myMap.ZoomToExtents()
            'Save the current mapcenter and zoom in the viewstate
            ViewState.Add("mapCenter", myMap.Center)
            ViewState.Add("mapZoom", myMap.Zoom)
            'Create the map
            CreateMap()
        End If
    End Sub
Here is how I initialize the map:
Private Function InitializeMap(ByVal outputsize As System.Drawing.Size) As SharpMap.Map
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString
        Dim map As SharpMap.Map = New SharpMap.Map(outputsize)
        Dim layCountries As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("states")

        Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID", SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry, True, 4326)
      
        layCountries.DataSource = d
     
        layCountries.Style.Fill = New SolidBrush(Color.Green)
        layCountries.Style.Outline = System.Drawing.Pens.Red
        layCountries.Style.EnableOutline = True
        map.Layers.Add(layCountries)
        map.ZoomToExtents()
        Return map
    End Function
Any suggestion please? Thanks a lot
Feb 24, 2014 at 8:51 PM
Just for more explanation, if I change my datasource from sqlserver to shape file, I can see the map is drawn, so I assume something is not right with my sqlserver datasource. Here is my code when shape file is used:
Private Function InitializeMap(ByVal outputsize As System.Drawing.Size) As SharpMap.Map
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString
        Dim map As SharpMap.Map = New SharpMap.Map(outputsize)
        Dim layCountries As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("state")

        layCountries.DataSource = New SharpMap.Data.Providers.ShapeFile(Server.MapPath("~\App_data\states_ugl.shp"), True)
        layCountries.Style.Fill = New SolidBrush(Color.Green)
        layCountries.Style.Outline = System.Drawing.Pens.Red
        layCountries.Style.EnableOutline = True
        map.Layers.Add(layCountries)
        map.ZoomToExtents()
        Return map
    End Function
Again thanks.
Coordinator
Feb 25, 2014 at 6:44 AM
And you are sure you have SqlServerGeometry and not SqlServerGeography stored in your database?
Feb 25, 2014 at 9:18 AM
Hello FObermaier, I triple checked the database and it has gemoetry datatype. So I assumed it is geometry. I used shape2sql to import the data from a shapefile to the database and I did not change anything. Please check the link for the screenshot of my database definition.

http://screencast.com/t/ilXQ4shn

Thank you very much for your help.
Coordinator
Feb 25, 2014 at 10:06 AM
Other possible issues:
  • Does your table reside in a special schema (other than dbo?)
  • Do d.GetFeatureCount() or d.GetExtents() return reasonable values?
  • Does d.GetFeatureById(<somevalidid>) return a featuredatarow?
  • You maybe need to change the datatype of your "ID" column to uint.
  • Did you try the provider in SharpMap.Data.Providers.SqlServerSpatialObjects?
Feb 26, 2014 at 11:37 AM
FObermaier, thank you for your reply again. I checked the database, here are what have found
  1. Yes the table is in dbo schema
  2. How do I know if d.GetFeatureCount() or d.GetExtents() return reasonable values? When I include that in my code, it threw error "Operation is not valid due to the current state of the object"
  3. d.GetFeatureById is not available. Only d.GetFeature is.
  4. There is no datatype uint. I am using MS SQL Server 2012
  5. I implemented this in my code above
Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID", SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry, True, 4326)
But as you can see, it doesn't show the map.

Again thanks.
Feb 26, 2014 at 11:38 AM
One more thing, where could I find the documentation explaining what's each method or function does. For example, d.GetFeatureCount(), what does it do and so on. Is there any explanation somewhere I can read.

Thanks for your patient.
Coordinator
Feb 26, 2014 at 8:27 PM
d.GetFeatureCount() should return the number of rows in your "polyline" table.
https://sharpmap.codeplex.com/SourceControl/latest#Branches/1.1/SharpMap/Data/Providers/SqlServer2008.cs
Look for public override int GetFeatureCount()

If that fails, I suppose there is sth wrong with your connection.
Feb 27, 2014 at 11:53 AM
Thank you FObermaier. After so much time this is the code that makes it work. Hope it will save someone time. This is the code that is working:
 Private Function InitializeMap(ByVal outputsize As System.Drawing.Size) As SharpMap.Map
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString
        Dim map As SharpMap.Map = New SharpMap.Map(outputsize)
        Dim layCountries As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("ID")

        Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "geom")
        d.GeometryColumn = "geom"
        d.Open()
        d.GetFeatureCount()
        d.ValidateGeometries = True


        layCountries.DataSource = d
        layCountries.Style.Fill = New SolidBrush(Color.Black)
        layCountries.Style.Outline = System.Drawing.Pens.Red
        layCountries.Style.EnableOutline = True
        map.Layers.Add(layCountries)
        map.ZoomToExtents()
        Return map
    End Function
Thank you again, FObermaier.
Marked as answer by asplearning on 2/27/2014 at 3:54 AM
Feb 27, 2014 at 12:05 PM
FObermaier, I can open another thread but just a quick check. I would like to be able to put legend on the my map and be able to click to select. Any link you could share so I can start at the right place?

Thank you