Create FeatureSet directly from database

Nov 20, 2010 at 3:41 AM

Hello:

I cannot find any sample or document on how to create a featureset directly from a database since my data stored in database instead of .shp file,

so I have to create a shp file first from datatable using shapelib, then add it to mapcontrol.

Does anybody can advise me a better way?

Developer
Nov 20, 2010 at 3:49 AM

What format is your database?

I just posted an OGR Dataprovider.  If the format is supported by the ogr project then you may be able to read it with this extensoin

Nov 20, 2010 at 1:37 PM

thanks keenedge, I am using Postgresql.

 

Developer
Nov 20, 2010 at 3:15 PM

This new extension is very experimental and I don't have experience with postgis.  If you need help getting it running private message me and we'll work through it. 

May 13, 2011 at 7:06 PM

hello, I'm new at this, I need to put the geographic information of my country, which is in a postgresql table in the control map of dotspatial, thank a specific example, if you can to VB. NET OC #, THANKS

Editor
May 13, 2011 at 8:35 PM

this might be of interest

http://dotspatial.codeplex.com/workitem/206

hth FObermaier

Jun 22, 2011 at 6:58 AM
keenedge wrote:

What format is your database?

I just posted an OGR Dataprovider.  If the format is supported by the ogr project then you may be able to read it with this extensoin

I want to store data into ms sqlserver database, and then retrieve them.

codes are shown below:

1              string strConn = "MSSQL:server=LOCALHOST;database=mssqlspatial;trusted_connection=yes;tables=boundary";

2              OgrVectorProvider ogrProvider = new OgrVectorProvider();

3              FeatureSet fs = ogrProvider.Open(strConn) as FeatureSet;

4              map1.Layers.Add(fs);

But exception is raised at line #2. Could you tell me why? Thanks.

Developer
Jun 23, 2011 at 10:35 PM

Can someone who has gotten this working post some installation tips?

It looks like we need Microsoft.SqlServer.Types.dll but maybe we can get away without that if we are not using SqlServer?

It looks like it needs references to ogr_csharp.dll and osr_csharp.dll from FWTools.

Would it work to just download a couple of OGR DLLs and put it in the same folder with DotSpatial.Data.OgrExtension.dll?

Do we need to run an installer for FWTools?

Jun 6, 2012 at 6:12 PM

I'm doing it like this but it doesn't use the Sql Server geography data type

    Private Sub MapDatabasePoints(ByVal SqlServerConnectionString As String)
        'create a point featureset to add the database points to
        Dim MyPointFeatureSet As New FeatureSet(FeatureType.Point)

        'the points coming from the database will be WGS84 geographic coordinates
        'so make that explicit with the featureset
        MyPointFeatureSet.Projection = KnownCoordinateSystems.Geographic.World.WGS1984

        'open a connection to the database using the supplied connectionstring.  .
        Dim MySqlConnection As New SqlConnection(SqlServerConnectionString)
        MySqlConnection.Open()

        'query the database and get the results into a DataReader
        Dim MySqlCommand As New SqlCommand("SELECT CaribouID,Lat,Lon FROM CaribouLocations WHERE CaribouID='0969';", MySqlConnection)
        Dim MySqlDataReader As SqlDataReader = MySqlCommand.ExecuteReader()

        'loop through the DataReader
        Do While MySqlDataReader.Read()
            'get the relevant data from the current reader row
            Dim CaribouID As String = MySqlDataReader.GetString(0)
            Dim Lat As Decimal = MySqlDataReader.GetDecimal(1)
            Dim Lon As Decimal = MySqlDataReader.GetDecimal(2)

            'create a coordinate
            Dim MyCoordinate As New Coordinate
            MyCoordinate.X = Lon
            MyCoordinate.Y = Lat

            'create a point and add the feature to it
            Dim MyPoint As New DotSpatial.Topology.Point(MyCoordinate)

            'create a feature and add the point with the coordinates to it.  Man this is complicated.
            Dim MyFeature As IFeature = MyPointFeatureSet.AddFeature(MyPoint)

            'add the feature to the featureset
            MyPointFeatureSet.AddFeature(MyFeature)
        Loop

        'add the featureset to the map
        Me.Map.Layers.Add(MyPointFeatureSet)

        'close the database connection
        MySqlConnection.Close()
    End Sub

Jul 1, 2012 at 8:23 PM

Using Skeeter Deeter's basic idea, I came up with the following. Since I have already imported the data from the database for other uses, I just got the data from the datatable I used there.

 Dim fname As New Font("Arial", 8)
    Dim fsize As Double = 8.0
    Dim fcolor As Color = Drawing.Color.Navy
 'create a point featureset to add the database points to
        Dim MySiteFeatureSet As New FeatureSet(FeatureType.Point)
 
        MySiteFeatureSet.Projection = KnownCoordinateSystems.Geographic.World.WGS1984
 
        MySiteFeatureSet.DataTable.Columns.Add("pk_sites").DataType = System.Type.GetType("System.Int32")
        MySiteFeatureSet.DataTable.Columns.Add("site_name").DataType = System.Type.GetType("System.String")
        MySiteFeatureSet.DataTable.Columns.Add("latitude").DataType = System.Type.GetType("System.Double")
        MySiteFeatureSet.DataTable.Columns.Add("longitude").DataType = System.Type.GetType("System.Double")
        MySiteFeatureSet.DataTable.Columns.Add("elevation").DataType = System.Type.GetType("System.Double")
        For i As Integer = 0 To projectsiteData.Rows.Count - 1
            MySiteFeatureSet.DataTable.Rows.Add()
            MySiteFeatureSet.DataTable.Rows(i).Item("pk_sites") = Convert.ToInt32(projectsiteData.Rows(i)("pk_sites"))
            MySiteFeatureSet.DataTable.Rows(i).Item("site_name") = projectsiteData.Rows(i)("site_name").ToString
            Dim latitude As Double = Convert.ToDouble(projectsiteData.Rows(i)("latitude"))
            MySiteFeatureSet.DataTable.Rows(i).Item("latitude") = latitude
            Dim longitude As Double = Convert.ToDouble(projectsiteData.Rows(i)("longitude"))
            MySiteFeatureSet.DataTable.Rows(i).Item("elevation") = Convert.ToDouble(projectsiteData.Rows(i)("elevation"))
            'create a coordinate
            Dim SiteLocation As New Coordinate
            SiteLocation.X = longitude
            SiteLocation.Y = latitude
            'create a point and add the feature to it
            Dim MyPoint As New DotSpatial.Topology.Point(SiteLocation)
            'create a feature and add the point with the coordinates to it.  Man this is complicated.
            Dim MyFeature As IFeature = MySiteFeatureSet.AddFeature(MyPoint)
            'add the feature to the featureset
            MySiteFeatureSet.AddFeature(MyFeature)
        Next
        uxMap.Layers.Add(MySiteFeatureSet)
 uxMap.AddLabels(uxMap.Layers(0), "[" & "site_name" & "]", fname, fcolor)

This works fine except for the labels, which just get the first point.

BY the way, I went to the University of Alaska so I understand your name: Skeeter Deeter
Jul 10, 2012 at 10:13 PM

I circled back to this a couple of days ago and saw your code.  Really good.  Drawing inspiration from your code I wrote a Module to create a DotSpatial point FeatureSet with attributes from a SQL Server query 'on-the-fly'.

Imports DotSpatial.Projections
Imports DotSpatial.Topology
Imports DotSpatial.Data
Imports System.Data.SqlClient

Module GetSqlServerFeatureSet
    ''' <summary>
    ''' Queries a SQL Server database using the supplied query string and returns a DatSpatial FeatureSet.
    ''' </summary>
    ''' <param name="SqlServerConnectionString">SQL Server connection string</param>
    ''' <param name="Query">A SELECT query minimally containing latitude and longitude columns.  Additional selected columns will be included with the returned FeatureSet's DataTable</param>
    ''' <param name="LatitudeColumnIndex">Integer.  The zero-based index of the query column containing the latitude data</param>
    ''' <param name="LongitudeColumnIndex">Integer.  The zero-based index of the query column containing the longitude data</param>
    ''' <param name="Projection">DotSpatial ProjectionInfo corresponding to the projection of the data</param>
    ''' <returns>DotSpatial.FeatureSet</returns>
    ''' <remarks></remarks>
    Public Function GetSqlServerPointFeatureSet(ByVal SqlServerConnectionString As String, ByVal Query As String, ByVal LatitudeColumnIndex As Integer, ByVal LongitudeColumnIndex As Integer, ByVal Projection As ProjectionInfo) As FeatureSet
        'declare some objects
        Dim MyFeatureSet As New FeatureSet()
        Dim MyDataTable As New DataTable

        Try
            'open a connection to the database using the supplied connectionstring.  .
            Dim MySqlConnection As New SqlConnection(SqlServerConnectionString)
            MySqlConnection.Open()

            'query the database and get the results into a DataReader
            Dim MySqlCommand As New SqlCommand(Query, MySqlConnection)
            Dim MySqlDataReader As SqlDataReader = MySqlCommand.ExecuteReader()

            'load the datareader into my temporary datatable
            MyDataTable.Load(MySqlDataReader)

            'close the connection
            MySqlConnection.Close()


            'now that I have the query results in a temporary datatable I can duplicate it in the featureset's datatable object. 
            'note: you might wonder why I need the temporary table: while it's possile to load the data reader directly into the 
            'featureset.datatable I would end up with a featureset with attributes but no features.  I needed to load the attributes
            'while loading the features simultaneously. this is most easily accomplished from a temporary datatable. 
            For Each Col As DataColumn In MyDataTable.Columns
                MyFeatureSet.DataTable.Columns.Add(New DataColumn(Col.ColumnName, Col.DataType))
            Next

            'loop through the temporary datatable and load in the features and attributes
            For RowIndex As Integer = 0 To MyDataTable.Rows.Count - 1
                'extract the data from the datatable
                Dim Lat As Double = MyDataTable.Rows(RowIndex).Item(LatitudeColumnIndex)
                Dim Lon As Double = MyDataTable.Rows(RowIndex).Item(LongitudeColumnIndex)

                'add the feature to the featureset
                MyFeatureSet.AddFeature(New Feature(New Coordinate(Lon, Lat)))

                'now update the feature's row with attributes
                For ColumnIndex As Integer = 0 To MyDataTable.Columns.Count - 1
                    MyFeatureSet.DataTable.Rows(RowIndex).Item(ColumnIndex) = MyDataTable.Rows(RowIndex).Item(ColumnIndex)
                Next
            Next

            'apply the projection
            MyFeatureSet.Projection = Projection
        Catch ex As Exception
            Throw New Exception("Error returning SQL Server FeatureSet: " & ex.Message)
        End Try

        'return the featureset
        Return MyFeatureSet
    End Function
End Module

Nov 28, 2012 at 3:46 AM

Links above are broken,

Have started new topic linking to this topic, you may be interested

http://dotspatial.codeplex.com/discussions/404817

 

Dec 6, 2012 at 2:39 PM

Where can I find the (interim) solution containing database classes (PostGIS etc..) ?

Feb 13, 2013 at 1:21 AM
Thx this helped me a lot......