Initializing Map with spatial data from MSSQL 2008

Topics: Data Access, General Topics, SharpMap Project, SharpMap v0.9 / v1.x, SharpMap v2.0
Apr 21, 2012 at 11:04 AM
Edited Apr 21, 2012 at 11:07 AM

I need a function that takes spatial data stored in mssql 2008 and be loaded as an image and wms layer

From the given example

it load a specific layer hard code into the function

i have made  a slight changes for it to accept an ID in reference to a reference field in database and load the layer/tables as a layer through a loop

however, it doesn't work the response was blank, if i would to replace it with a shapefile, it does show the exact response and image

i have loaded the countries.shp file to the database .

     access with http://localhost/wms.ashx?id=7&REQUEST=GetMap&Layers=countries&STYLES=&CRS=EPSG:4326&BBOX=-180,-90,180,90&WIDTH=600&HEIGHT=300&FORMAT=image/png&VERSION=1.3.0&TRANSPARENT=true
            List<Color> colorsLine = new List<Color>();


            List<Brush> colorsFill = new List<Brush>();


SharpMap.Map map = new SharpMap.Map(size);
GITMapperEntities mapperDB = new GITMapperEntities();

var layerlist = mapperDB.sample_attachment.Where(x=>x.FT_key ==id).Select(x=> x.filename).ToList();

            IList<string> layers = new List<string>();

            foreach (string layername in layerlist)
                layers.Add(layername.Substring(layername.IndexOf('-') + 1, layername.IndexOf('.') - (layername.IndexOf('-') + 1)));

            foreach (string layername in layers)
                VectorLayer layer = new VectorLayer(layername);


string connstr = ConfigurationManager.ConnectionStrings["GeoDatabase"].ConnectionString;

//Set the datasource to a shapefile in the App_data folder

SharpMap.Data.Providers.SqlServer2008 data = new SharpMap.Data.Providers.SqlServer2008(connstr, layername, "WKB_Geometry", "oid");

int featureCount = data.GetFeatureCount(); //REturns 147

layer.DataSource = data;
                //layer.DataSource = new ShapeFile(HttpContext.Current.Server.MapPath(@"~\Uploads\201204190807-countries.shp"), true);
                layer.Style.Line = new Pen(colorsLine[counter]);
                layer.Style.EnableOutline = true;
                layer.Style.Fill = colorsFill[counter];
                layer.SRID = 4326;
            map.MaximumZoom = 360;
            map.BackColor = Color.LightBlue;

            map.Zoom = 360;
            map.Center = new Point(0, 0);
            HttpContext.Current.Trace.Write("Map initialized");
            return map;

can this be done? i am getting returns 147rows from the featuresCount,

I have verified there is geometry table, because i am able to generate an image
with this function
   foreach(string layer in layerList){

                SharpMap.Data.Providers.SqlServer2008 shpDS = new SharpMap.Data.Providers.SqlServer2008(connstr, layer, "WKB_Geometry", "oid");
                shpDS.ValidateGeometries = true;
                //Initialize the map
                //Add the layer

                //Setup a shapefile vectorlayer with some default styles
                SharpMap.Layers.VectorLayer vlayer = new SharpMap.Layers.VectorLayer("preview layer");
                vlayer.DataSource = shpDS;
                vlayer.Style.Line = new Pen(colorsLine[counter]); 
                vlayer.Style.EnableOutline = true;
                vlayer.Style.Fill = colorsFill[counter];




            Image imgMap = map.GetMap();

How do i debug this further.

Apr 21, 2012 at 5:50 PM

are you using ValidateGeometries = true everywhere?

anyway, are you sure that you have no exceptions at all? 

using sharpmap, you can debug the wmshandler from the entry point to the details, and see the query that is made to the db (you can use the sql proviler for the same purposes) .

be sure that the query made is correct.

Apr 22, 2012 at 4:11 AM
Edited Apr 22, 2012 at 6:33 AM

Hi D_Guidi,

I have added ValidateGeometries to any call after data is retrieved.

no exceptions at all.

i found that my GetGeometriesInView is returning me nothing

Query Execute is : SELECT g.WKB_Geometry.STAsBinary()  FROM countries g WHERE WKB_Geometry.MakeValid().STIntersects(geometry::STGeomFromText('POLYGON ((-180 -90, 180 -90, 180 90, -180 90, -180 -90))', 4326)) = 1


in my Database i have 147 records

with Colums of

oid (PK), WKB_Geometry (geometry): Name (varchar), POPDENS (bigint)

sample data

oid  :1     

WKB_Geometry    :0x00000000010444010000020000E09D1D61400300004036A4424....

NAME    :Japan   


what did i do wrong?


Apr 22, 2012 at 6:35 AM

Query:  SELECT WKB_Geometry.STSrid from countries
Fixed with ->  UPDATE countries SET WKB_Geometry.STSrid = 4326

  Fixed it. now wms REQUEST=GetMap is returning image



Now why didn't the SRID being inserted automatically?


Apr 22, 2012 at 8:55 AM

>Now why didn't the SRID being inserted automatically?

If you're using SqlSpatial Tool, there is an option to set the correct SRID (that isn't read from shapefile, if available). 

Default SRID in SqlServer is '0' (null).

Apr 23, 2012 at 12:45 AM

Notice that,

I am dealing shapefile from a browse side. SQL spatial is good example but doesn't work in my case.

Looks like i just have to run an update after rows of data have been inserted to the database.



Apr 23, 2012 at 6:27 AM
vcheahhs wrote:

Notice that,

I am dealing shapefile from a browse side. SQL spatial is good example but doesn't work in my case.

Looks like i just have to run an update after rows of data have been inserted to the database.



I'm talking about Sql Spatial Tools:

A simple yet useful tool to load data inside sql server.

Anyway, problem solved, right? :)