Draw SQL Server points on map without shapefile

Mar 23, 2011 at 9:55 PM

I want to connect to a SQL Server to retrieve spatial points and render them on  a map.  I used to do this using MapWinGIS using a DrawingLayer.  Is there something similar in DotSpatial.  I don't really want or need to generate a shapefile to show my points.

Also, will there be support for SQL Server Geography data type?

Thanks

Scott

Editor
Mar 24, 2011 at 8:31 AM

You might find this interesting.

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

It is not directly for SqlServer2008, but with a little work you should get that done.

Hth FObermaier

May 2, 2011 at 3:01 AM

Scott

I am doing this by retreiving a sqlgeography STAsText() string via a standard 'SELECT <geography> FROM ...' and then going through the laborious process of parsing the string to extract the coordinates into a DotSpatial Coordinate array from which I make a DotSpatial Polygon that is then added to a FeatureSet.  It's very clunky but it works without having to make/save a shapefile. There must be a more efficient way to do this. It is (just) acceptably responsive for a single retrieved record from SQL Server but not for multiple records.

      FeatureSet fs1 = new FeatureSet(FeatureType.Polygon);  //temp feature set
      //make dotspatial polygon object from sqlgeography STAsText() string and STNumPoints() vertices
      Polygon outlinePoly = PolygonObject(polystring,vertices );  
      fs1.AddFeature(outlinePoly);                             //add the polygon to the feature set
      MapPolygonLayer polyLayer = (MapPolygonLayer)theMapControl.Layers.Add(fs1);  //add the featureSet as map layer

David

Jun 6, 2012 at 6:14 PM
Edited Jun 6, 2012 at 6:14 PM

I eventually did this

 ''' <summary>
    ''' Queries a Sql Server database using the supplied ConnectionString and maps the resulting points
    ''' </summary>
    ''' <param name="SqlServerConnectionString">See http://www.connectionstrings.com/sql-server-2005 for help on Sql Server ConnectionStrings</param>
    ''' <remarks></remarks>
    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

Feb 1, 2013 at 8:05 AM
Edited Feb 1, 2013 at 9:41 AM
Hi shadhow,

I am trying to create a polygon from sqlserver spatial data using WKT for web app i am using DOtspatialWeb sample code , could you please reply the sample about creating polygons from WKT where i had parsed the string i don't know where to pass the coordinates and convert to polygon.
Feb 3, 2013 at 9:49 PM
Edited Feb 3, 2013 at 9:54 PM
In the code below I have included two approaches. The first is one using WKT that I referred to in my March post. As I said it is very inefficient. The second is the approach I am currently using with Microsoft.SqlServer.Types.SqlGeography.
These work only for one part lines and polygons.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Drawing;

using DotSpatial.Controls;
using DotSpatial.Data;
using DotSpatial.Symbology;
using DotSpatial.Topology;

using Microsoft.SqlServer.Types;

namespace MAGIX202
{
    class Class1
    {
        SqlGeography theGeography = new SqlGeography();
        Map myMap = new DotSpatial.Controls.Map();


        //First attempt ... using WKT
        public void DrawOutline(SurveyBlock theBlock, Color fillColor, Color outlineColour, double width)
        {
            string polyString = (string)theGeography.STAsText().ToSqlString();
            int numPoints = (int)theBlock.Outline.STNumPoints();

            // 'polystring' is WKT form that comes out of a SS2008 geography type with .STASText()
            //e.g. "POLYGON ((127.652000002563 -26.244400002062321, 127.652000002563 -26.194399997591972, ....., 127.652000002563 -26.244400002062321))";


            FeatureSet fs1 = new FeatureSet(FeatureType.Polygon);  

            Polygon outlinePoly = PolygonObject(polyString, numPoints);                  //make a dotspatial polygon object from the sqlgeography AsText() string

            fs1.AddFeature(outlinePoly);                                                 //add the polygon to the feature set

            MapPolygonLayer myLayer = (MapPolygonLayer)myMap.Layers.Add(fs1);           //add the featureSet as map layer
        }

        private Polygon PolygonObject(string polystring, int vertices)
        {
            //extract the vertices
            int startpoint = 10;
            //int fulllength= polystring.Length
            int extractlength = polystring.Length - 12;

            // this is now the list of coordinates as comma-delimited pairs
            polystring = polystring.Substring(startpoint, extractlength);

            Coordinate[] coords = new Coordinate[vertices];
            double xcoord = 0;
            double ycoord = 0;
            int xbreakpoint = 0;
            int ybreakpoint = 0;
            int xlength = 0;
            int ylength = 0;
            string xstring = "";
            string ystring = "";

            int blob = polystring.LastIndexOf(")");

            for (int i = 0; i < vertices - 1; i++)
            {
                //get first/next coord pair
                xbreakpoint = polystring.IndexOf(" ");  // space indicates end of x coord
                xstring = polystring.Substring(0, xbreakpoint);
                //textBox2.Text = xstring;
                xlength = xstring.Length;

                extractlength = polystring.Length - xlength - 1;  // current length of polystring
                polystring = polystring.Substring(xlength + 1, extractlength); // strip off x ccord string + space

                ybreakpoint = polystring.IndexOf(","); // comma indicates end of y coord
                ystring = polystring.Substring(0, ybreakpoint);
                if (ystring.LastIndexOf(")") == ystring.Length)
                    ystring = ystring.Substring(1, ystring.Length - 1);

                ylength = ystring.Length;

                extractlength = polystring.Length - ylength - 2;  // current length of polystring
                polystring = polystring.Substring(ylength + 2, extractlength); // strip off y ccord string + comma + space

                xcoord = Convert.ToDouble(xstring);
                ycoord = Convert.ToDouble(ystring);
                coords[i] = new Coordinate(xcoord, ycoord);
            }
            coords[vertices - 1] = new Coordinate(coords[0].X, coords[0].Y);

            //creates a new polygon from the coordinate array
            Polygon pg = new Polygon(coords);
            return pg;
        }




        //current approach ... using Microsoft.SqlServer.Types.SqlGeography

        private void DrawTheGeography(SqlGeography geog, string myLayerName, string myItemLegend, Color fillColour, Color outlineColour, double lineWidth, bool zoomTo)
        {
            if (geog == null)
                return;

            //draws geography as relevant feature type (point, line, polygon)
            FeatureType featureType = new FeatureType();

            int numPoints = (int)geog.STNumPoints();
            Coordinate[] coords = new Coordinate[numPoints];
            double xcoord = 0;
            double ycoord = 0;

            for (int i = 0; i < numPoints; i++)
            {
                xcoord = (double)geog.STPointN(i + 1).Long;
                ycoord = (double)geog.STPointN(i + 1).Lat;
                coords[i] = new Coordinate(xcoord, ycoord);
            }

            switch ((int)geog.STDimension())
            {
                case 2: featureType = FeatureType.Polygon; break;
                case 1: featureType = FeatureType.Line; break;
                case 0: featureType = FeatureType.Point; break;
            }

            FeatureSet featureSet = new FeatureSet(featureType);                                   //temp feature set
            featureSet.Projection = myMap.Projection;                                              //set projection

            switch ((int)geog.STDimension())
            {
                case 2:        //polygon
                    Polygon polyShape = new Polygon(coords);
                    featureSet.AddFeature(polyShape);                                               //add the polygon to the feature set
                    MapPolygonLayer polyLayer = (MapPolygonLayer)myMap.Layers.Add(featureSet);     //add the featureSet as map layer
                    polyLayer.DataSet.Name = myLayerName;
                    polyLayer.LegendText = myItemLegend;
                    polyLayer.Symbolizer = new PolygonSymbolizer(fillColour, outlineColour, lineWidth);        //set the colour scheme
                    if (zoomTo)                                                                     //zoom to shape
                        myMap.ViewExtents = polyLayer.Extent;

                    break;

                case 1:         //line
                    LineString lineShape = new LineString(coords);
                    featureSet.AddFeature(lineShape);                                       //add the line to the feature set
                    MapLineLayer lineLayer = (MapLineLayer)myMap.Layers.Add(featureSet);   //add the featureSet as map layer
                    lineLayer.DataSet.Name = myLayerName;
                    lineLayer.LegendText = myItemLegend;
                    lineLayer.Symbolizer = new LineSymbolizer(fillColour, lineWidth);               //set the colour scheme
                    if (zoomTo) // && theMapControl.ViewExtents != surveyLayer.Extent)      //zoom to shape
                        myMap.ViewExtents = lineLayer.Extent;

                    break;

                case 0:
                    DotSpatial.Topology.Point pointShape = new DotSpatial.Topology.Point(coords[0]);
                    featureSet.AddFeature(pointShape);                                       //add the pont to the feature set
                    PointLayer pointLayer = (PointLayer)myMap.Layers.Add(featureSet);       //add the featureSet as map layer
                    pointLayer.DataSet.Name = myLayerName;
                    pointLayer.LegendText = myItemLegend;
                    pointLayer.Symbolizer = new PointSymbolizer(fillColour, DotSpatial.Symbology.PointShape.Rectangle, lineWidth);        //set the colour scheme
                    if (zoomTo) // && theMapControl.ViewExtents != surveyLayer.Extent)      //zoom to shape
                        myMap.ViewExtents = pointLayer.Extent;

                    break;
            }

            if (zoomTo) // && theMapControl.ViewExtents != surveyLayer.Extent)      //zoom to shape
            {
                int zoomFactor = 3; // TODO: make zoom factor an input paramente
                for (int i = 0; i < zoomFactor; i++)
                {
                    myMap.ZoomOut();
                }
            }
        }

    }
}
Feb 5, 2013 at 9:43 AM
Edited Feb 5, 2013 at 9:50 AM
Hi,

Thanks for sending the code, as i am incorporating it into DotSpatialWeb as a web application i can't use MapPolygonLayer myLayer = (MapPolygonLayer)myMap.Layers.Add(fs1); instead of myMap i need to use webmap as i had created the featuredataset based on your sample i need to add the layer to webmap (DotSpatial.WebControls.WebMap;).

Do You have any idea how to overlay it on web.

Regards
Arun Mohan
Feb 7, 2013 at 2:01 AM
Sorry... I have not used ds.web at all
Feb 15, 2013 at 9:41 AM
Edited Feb 15, 2013 at 9:42 AM
Hi,

Thanks for your reply as your first approach is working well as you mentioned as inefficient method i tried the second approach which uses geography, when i debug the coded i am getting the following error.Please help me if you have any idea
___System.Reflection.TargetInvocationException was unhandled by user code
  Message=Exception has been thrown by the target of an invocation.
  Source=mscorlib
  StackTrace:
       at System.RuntimeMethodHandle._SerializationInvoke(IRuntimeMethodInfo method, Object target, SignatureStruct& declaringTypeSig, SerializationInfo info, StreamingContext context)
       at System.Runtime.Serialization.ObjectManager.CompleteISerializableObject(Object obj, SerializationInfo info, StreamingContext context)
       at System.Runtime.Serialization.ObjectManager.FixupSpecialObject(ObjectHolder holder)
       at System.Runtime.Serialization.ObjectManager.DoFixups()
       at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(HeaderHandler handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
       at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(Stream serializationStream, HeaderHandler handler, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
       at System.Resources.ResourceReader.DeserializeObject(Int32 typeIndex)
       at System.Resources.ResourceReader._LoadObjectV2(Int32 pos, ResourceTypeCode& typeCode)
       at System.Resources.ResourceReader.LoadObjectV2(Int32 pos, ResourceTypeCode& typeCode)
       at System.Resources.ResourceReader.LoadObject(Int32 pos, ResourceTypeCode& typeCode)
       at System.Resources.RuntimeResourceSet.GetObject(String key, Boolean ignoreCase, Boolean isString)
       at System.Resources.RuntimeResourceSet.GetObject(String key, Boolean ignoreCase)
       at System.Resources.ResourceManager.GetObject(String name, CultureInfo culture, Boolean wrapUnmanagedMemStream)
       at System.Resources.ResourceManager.GetObject(String name, CultureInfo culture)


       at DotSpatial.Symbology.SymbologyImages.get_select() in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Symbology\SymbologyImages.Designer.cs:line 121
       at DotSpatial.Symbology.FeatureLayer.Configure(IFeatureSet featureSet) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Symbology\FeatureLayer.cs:line 249
       at DotSpatial.Symbology.FeatureLayer..ctor(IFeatureSet featureSet) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Symbology\FeatureLayer.cs:line 148
       at DotSpatial.Symbology.PolygonLayer..ctor(IFeatureSet inFeatureSet) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Symbology\PolygonLayer.cs:line 44
       at DotSpatial.Controls.MapPolygonLayer..ctor(IFeatureSet inFeatureSet) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Controls\MapPolygonLayer.cs:line 84
       at DotSpatial.Controls.MapLayerCollection.Add(IFeatureSet featureSet) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.Controls\MapLayerCollection.cs:line 205
       at DotSpatial.WebControls.GDIMap.AddLayer1(FeatureSet sqlpoly) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.WebControls\GDIMap.cs:line 164
       at DotSpatial.WebControls.WebMap.AddLayer1(FeatureSet sqlpoly) in D:\Arun Working\Dot Spatial\dotspatial-66654\DotSpatial.WebControls\WebMap.cs:line 399
       at Element_DotSpatial.MapReport.DrawTheGeography(SqlGeography geog, String myLayerName, String myItemLegend, Color fillColour, Color outlineColour, Double lineWidth, Boolean zoomTo) in D:\Arun Working\Projects\Element_DotSpatial\Element_DotSpatial\MapReport.aspx.cs:line 216
       at Element_DotSpatial.MapReport.CreateMap() in D:\Arun Working\Projects\Element_DotSpatial\Element_DotSpatial\MapReport.aspx.cs:line 56
       at Element_DotSpatial.MapReport.Page_Load(Object sender, EventArgs e) in D:\Arun Working\Projects\Element_DotSpatial\Element_DotSpatial\MapReport.aspx.cs:line 29
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: System.ComponentModel.Win32Exception
       Message=The operation completed successfully
       Source=System.Drawing
       ErrorCode=-2147467259
       NativeErrorCode=0
       StackTrace:
            at System.Drawing.Icon.Initialize(Int32 width, Int32 height)
            at System.Drawing.Icon..ctor(SerializationInfo info, StreamingContext context)
       InnerException: 

___
Regards
Arun Miohan
Feb 18, 2013 at 3:38 AM
Sorry Arun. No idea. Does the polyshape generate OK before you try to plot it on the map?
{POLYGON ((121.6829999983310000 -29.2837999984622000, 121.69399999827100 ....
Feb 18, 2013 at 6:02 AM
Edited Feb 18, 2013 at 6:02 AM
Hi,

Yes,It generates the polyshape if i try to add a single polygon it adds to the map when i try to add all the polygons of US State it gives this error on FeatureLayer.cs at the line below

SymbologyMenuItem selection = new SymbologyMenuItem(Msg.FeatureLayer_Selection, SymbologyImages.select, null);

Regards
Arun Miohan
Feb 19, 2013 at 2:58 AM

I am using a data table to get multiple shapes from the database.
I am still testing but this seems to work for me (so far). I have replaced my original PolygonObject approach with

```
private Polygon NewPolygonObject(SqlGeography geog)
{
int numPoints = (int)geog.STNumPoints();
Coordinate[] coords = new Coordinate[numPoints];
double xcoord = 0;
double ycoord = 0;

for (int i = 0; i < numPoints; i++)
{
xcoord = (double)geog.STPointN(i + 1).Long;
ycoord = (double)geog.STPointN(i + 1).Lat;
coords[i] = new Coordinate(xcoord, ycoord);
}

//creates a new polygon from the coordinate array
Polygon pg = new Polygon(coords);
return pg;
}

```
Then I add the multiple shapes that I have stored in a DataTable from a 'select geography' call to the database.

```
private void NewMapSurveyResults(DataTable blocksOutlinesTable)
{
DataColumn RNumCol = new DataColumn("RNumber");
DataColumn blockNameCol = new DataColumn("BlockName");
DataColumn blockOutlineCol = new DataColumn("Block_Outline");

resultsFeatureSet = new FeatureSet(FeatureType.Polygon); //empty any existing data
resultsFeatureSet.Name = "Results (polygons)";
resultsFeatureSet.Projection = TheMap.Projection; //set projection
resultsFeatureSet.DataTable.Columns.Add(RNumCol);
resultsFeatureSet.DataTable.Columns.Add(blockNameCol);

MapPolygonLayer resultsLayer = new MapPolygonLayer();

if (blocksOutlinesTable.Rows.Count == 0) ///should already be tested in Control Form
return;

int i = 0;
foreach (DataRow r in blocksOutlinesTable.Rows)
{
int RNum = int.Parse(r["RNumber"].ToString());
string blockName = r["Block_Name"].ToString();
SqlGeography geog = ( SqlGeography)r["Block_Outline"];

try
{
Polygon poly = NewPolygonObject(geog); //make a dotspatial polygon object from the sqlgeography AsText() string
resultsFeatureSet.AddFeature(poly); //add the polygon to the feature set
resultsFeatureSet.DataTable.Rows[i]["RNumber"] = RNum;
resultsFeatureSet.DataTable.Rows[i]["BlockName"] = blockName;
}
catch
{
}
i++;
}

try
{
resultsLayer = (MapPolygonLayer)TheMap.Layers.Add(resultsFeatureSet); //add the featureSet as map layer
resultsLayer.Symbolizer = new PolygonSymbolizer(Color.Empty, Color.RoyalBlue); //set the colour scheme
}
catch
{
//TODO: this was put in to stop failure on yet undefined occasional error 21/12/12
}

string resultsShape = Globals.SystemSettings.DefaultShapefilePath + "Results (polygons).shp";
resultsFeatureSet.SaveAs(resultsShape, true);

if (blocksOutlinesTable.Rows.Count == 1)
{
TheMap.ViewExtents = resultsLayer.Extent;

int zoomFactor = 3; // TODO: make zoom factor an input paramente
for (int ii = 0; ii < zoomFactor; ii++)
{
TheMap.ZoomOut();
}
}
else
TheMap.ViewExtents = TheMap.Layers[1].Extent;
}

```
As I said, this seems to work for me in my Windows Forms app.


Editor
Feb 19, 2013 at 5:34 PM
I don't know if you are aware of NetTopologySuite.IO.SqlServer?
Together with NetTopologySuite.DotSpatial.Converter you should be able to pretty much handle any SqlServer Geometry of Geography within DotSpatial

Hth FObermaier
Feb 25, 2013 at 10:18 AM
Hi FObermaier,

After installing NetTopologySuite.IO.SqlServer and NetTopologySuite.DotSpatial.Converter i cant even see the shapes in my aspx page its giving then following error

System.MissingFieldException was unhandled by user code
Message=Field not found: 'DotSpatial.Projections.KnownCoordinateSystems.Geographic'.
Editor
Feb 25, 2013 at 5:47 PM
ArunMohan wrote:
System.MissingFieldException was unhandled by user code
Message=Field not found: 'DotSpatial.Projections.KnownCoordinateSystems.Geographic'.
This is no functionality of the libraries I mentioned
Feb 26, 2013 at 4:44 AM
Hi FObermaier,

Can you give me an example how to use NetTopologySuite.IO.SqlServer and NetTopologySuite.DotSpatial.Converter any sample or help to this

Regards
Arun Mohan
Editor
Feb 26, 2013 at 3:36 PM
Here you go, works for me
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DotSpatial.Data;
using DotSpatial.Topology;
using GeoAPI.Geometries;
using GeoAPI.IO;
using Microsoft.SqlServer.Types;
using NetTopologySuite.IO;

namespace NTSIOWithDS
{
    public class SqlServerToFeatureSet
    {
        static SqlServerToFeatureSet()
        {
            GeoAPI.GeometryServiceProvider.Instance = NetTopologySuite.NtsGeometryServices.Instance;
        }
        
        public static IFeatureSet LoadFeatureSet(string connectionString, string sql)
        {
            IFeatureSet res = null;
            using (var cn = new SqlConnection(connectionString))
            {
                cn.Open();
                var cmd = new SqlCommand(sql, cn);
                using (var r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        r.Read();
                        DataTable table;
                        Func<SqlDataReader, int, IBasicGeometry> gr;
                        int gIndex;
                        ColumMapper columMapper;

                        res = new FeatureSet(GetFeatureType(r, out table, out gr, out gIndex, out columMapper));
                        
                        var values = new object[r.FieldCount];
                        table.BeginLoadData();
                        do
                        {
                            var g = gr(r,gIndex);
                            var num = r.GetValues(values);
                            var f = res.AddFeature(g);
                            var dr = table.LoadDataRow(columMapper.Map(values), true);
                            f.DataRow = dr;

                        } while (r.Read());
                        
                        table.EndLoadData();
                    }
                }
                cmd.Dispose();
            }
            return res;
        }

        private class ColumMapper
        {
            private readonly Dictionary<int, int> _map = new Dictionary<int, int>();

            public object[] Map(object[] values)
            {
                var res = new object[_map.Count];
                foreach (var t in _map)
                    res[t.Value] = values[t.Key];

                return res;
            }

            public void AddMap(int o, int t)
            {
                _map.Add(o,t);
            }
            
        }

        private static class SqlServerReaderUtility
        {
            private static readonly MsSql2008GeometryReader GeometryReader = new MsSql2008GeometryReader();
            private static readonly MsSql2008GeographyReader GeographyReader = new MsSql2008GeographyReader();
            
            public static IBasicGeometry ReadGeometry(SqlDataReader reader, int index)
            {
                return GeometryReader.Read((SqlGeometry) reader.GetValue(index)).ToDotSpatial();
            }
            public static IBasicGeometry ReadGeography(SqlDataReader reader, int index)
            {
                return GeographyReader.Read((SqlGeography) reader.GetValue(index)).ToDotSpatial();
            }
        }

        private static FeatureType GetFeatureType(SqlDataReader r, out DataTable table, out Func<SqlDataReader, int, IBasicGeometry> gc, out int gIndex, out ColumMapper mapper)
        {
            table = new DataTable();
            mapper = new ColumMapper();
            gIndex = -1;
            gc = null;

            var res = FeatureType.Unspecified;

            for (var i = 0; i < r.FieldCount; i++)
            {
                var t = r.GetFieldType(i);
                if (t == null)
                    throw new InvalidOperationException("Could not get column type");
                
                if (t == typeof (SqlGeometry))
                {
                    gc = SqlServerReaderUtility.ReadGeometry;
                    gIndex = i;
                    res = gc(r, gIndex).FeatureType;
                }
                else if (t == typeof (SqlGeography))
                {
                    gc = SqlServerReaderUtility.ReadGeography;
                    gIndex = i;
                    res = gc(r, gIndex).FeatureType;
                }
                else
                {
                    table.Columns.Add(r.GetName(i), t);
                    mapper.AddMap(i, table.Columns.Count);
                }
            }

            if (gIndex == -1)
                throw new InvalidOperationException("No geometry column found");
            return res;
        }
    }
}
Feb 28, 2013 at 5:36 AM
Edited Feb 28, 2013 at 6:00 AM
Hi FObermaier,

Thanks a ton its working fine and now the map loads even faster than the previous method.

And a special thanks to shadhow for helping in this and i had so many other things to do in this app this will be a great start.

Regards
Arun Mohan
Mar 1, 2013 at 1:42 AM
FObermaier --- that's great! loads really fast. Thanks.
Editor
Mar 1, 2013 at 4:06 PM
Just noticed that the columnmapper ist off by one
Mar 11, 2013 at 4:57 AM
I noticed that. Have you got a fix for it? There's no problem if just the loction column is selected but it fails for more than one column.
Editor
Mar 11, 2013 at 8:55 AM
Edited Mar 11, 2013 at 8:56 AM
shadhow wrote:
I noticed that. Have you got a fix for it? There's no problem if just the loction column is selected but it fails for more than one column.
Either you call the mapper.Add(...) function prior to adding the column to the columns collection or you change
mapper.AddMap(i, table.Columns.Count);
to
mapper.AddMap(i, table.Columns.Count - 1);
Mar 11, 2013 at 11:52 PM
Many thanks!

As an aside (not sure if worth opening another thread) do you know of any library which will plot features regardless of whether they are ponts, lines or polygons (like the spatial tab in SQL Server) ? AS far as I can tell, DotSpatial keeps features separately.
Editor
Mar 12, 2013 at 8:40 AM
You can try SharpMap.
Mar 19, 2013 at 7:29 AM
Edited Mar 19, 2013 at 11:46 AM
Hi,

I tried to fetch a string column along with geography column from SQLServer and i need to label it, as i am getting only the last row of the datatable for eg: same country name for all polygons. How can i fetch all the rows so that i can label the polygons.

I am using MapLabelLayer
Apr 2, 2013 at 6:45 AM
Edited Apr 2, 2013 at 6:48 AM
Hi FObermaier ,

I had used your methodology to read the polygons its working well, as now i have to create labels and color the polygons based on the values of other string column in database where i had added a row to datatable and read the data
 public static FeatureSet LoadFeatureSet(string connectionString, string sql)
        {
            FeatureSet res = null;
            using (var cn = new SqlConnection(connectionString))
            {
                cn.Open();
                var cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                
                using (var r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        r.Read();

                        DataTable table;
                        Func<SqlDataReader, int, IBasicGeometry> gr;
                        int gIndex;
                        ColumMapper columMapper;
                        string CountyName;
                       
                        res = new FeatureSet(GetFeatureType(r, out table, out gr, out gIndex, out columMapper));
                        //table.Columns.Add("geo1");
                        //table.Columns.Add("attri1");
                       // res.DataTable.Columns.Add("geo");  
                        
                        var values = new object[r.FieldCount];
                        // var values = new object[0];
                        res.DataTable.Columns.Add("attri");
                        table.BeginLoadData();
                        int AttCounter = 0;
                       
                        do
                        {
                           
                            var g = gr(r, gIndex);
                            var num = r.GetValues(values);
                            var f = res.AddFeature(g);
                            CountyName = r["CountyName"].ToString();
                            var dr = table.LoadDataRow(columMapper.Map(values),true);
                            f.DataRow = dr;
                           // res.DataTable.Rows[0]["geo"] = columMapper.Map(values); 
                           

                            res.DataTable.Rows[AttCounter]["attri"] = CountyName;

                            AttCounter++;
                          

                        } while (r.Read());
                       
                        table.EndLoadData();
                       
                    }
                }
                cmd.Dispose();
            }
          
            return res;
           

        }
and reading the table rows in other method to create labels and color
string name = string.Empty;
            IMapFeatureLayer StateMaplayer = (IMapFeatureLayer)WebMap1.AddSQLLayer(test);
            foreach (DataRow dr in test.DataTable.Rows)
            {
                name = dr["attri"].ToString();
                
                
            }

            
            if (name == "1")
            {
                Color redColor = Color.FromArgb(255, 0, 0);
                Color outColor = Color.FromArgb(255, 200, 100);
                StateMaplayer.Symbolizer = new PolygonSymbolizer(redColor, outColor, 2);
            }
            else
            {
                Color redColor = Color.FromArgb(100, 200, 0);
                Color outColor = Color.FromArgb(255, 110, 100);
                StateMaplayer.Symbolizer = new PolygonSymbolizer(redColor, outColor, 2);

            }
            LabelSymbolizer stateLabelSymbolizer = new LabelSymbolizer()
            {
                FontFamily = "Tahoma",
                FontColor = Color.Black,
                FontSize = 8,
                BackColor = Color.White,
                BackColorEnabled = true,
                BackColorOpacity = 0.5f,
                Orientation = ContentAlignment.MiddleRight,
                PartsLabelingMethod = PartLabelingMethod.LabelAllParts,
                OffsetX = 5
            };
            ILabelCategory stateLabelCategory = new LabelCategory()
            {
                Expression = name,
                Symbolizer = stateLabelSymbolizer,
                //Name = "Vessels' MMSIs"
            };

            StateMaplayer.LabelLayer = new MapLabelLayer(test);
            StateMaplayer.ShowLabels = true;
            StateMaplayer.LabelLayer.Symbology.Categories.Clear();
            StateMaplayer.LabelLayer.Symbology.Categories.Add(stateLabelCategory);
            StateMaplayer.LabelLayer.CreateLabels();
where i used to get the values of the last row for example 273 label for all polygons and same color for all polygons.

Please help me in this.

Regards
Arun Mohan
Editor
Apr 2, 2013 at 9:03 AM
Edited Apr 2, 2013 at 9:13 AM
I have no idea how to solve your problem. All I can say is that on my sample dataset every feature has its own DataRow with its own values. Maybe there is something wrong with you SQL statement.

I have no idea how to setup a label layer with DotSpatial. But I wonder why you iterate over all rows in the data table, get some value and don't do anything with it. Maybe you need to close the curly bracket at a later point?

Hth FObermaier
Aug 28, 2014 at 6:28 AM
I have noticed that in above code Featureset doesn't have any attrebute table and we must set

res.datatable=table;

before return res;

Any other idea to solve this problem?
Sep 10, 2014 at 12:37 AM
Edited Sep 10, 2014 at 12:40 AM
I know SQL Server OGC geometry and geography. I don't really know dotSpatial. A quick look at the dotSpatial docs shows, e.g. in the case of a polygon, you can likely write an extension that reads the OGC from SQL server and then populates a dotSpatial polygon class instance. Any dotSpatial experts out there who can refine this, please do. I looked at the source code for this discussion provided by other users, and just glancing I don't see any mention of ring orientation. Does dotSpatial have ring orientation requirements?