How to load data from shp file saved into MS SQL Server 2008 database

Topics: Data Access, General Topics, SharpMap Project, SharpMap v2.0
May 10, 2011 at 9:01 PM

I have managed to successfully save the shp file contents into a MS SQL Server 2008 database; however, I have been unable to sucessfully load the map from the database.

I have seen the Data.Providers.SQLServer008 in a few posts, but that isn't included in any of the namespaces that I have found thus far.  The OleDbPoint might possibly work; however, I don't have only an x and a y value to plot, as the information was stored with a min and max x and y value, and I get some weird error with setting the zoom when trying to load the map.  Also, the OleDbPoint requires 4 parameters, which doesn't seem like it will work in this situation.

Can anyone point me in the right direction?

Coordinator
May 10, 2011 at 9:48 PM

Hello nlraley,

if you're using SharpMap v2 you need to reference

SharpMap.Data.Providers.MsSqlServer2008

If you are using SharpMap v0.9 you don't need to reference anything, you can simply use

SharpMap.Data.Providers.SqlServer2008

If you do not find the provider, your code/component is probably very old.

Hth FObermaier

May 10, 2011 at 9:59 PM

Thanks, I actually cleared out my references and now it is visible there.  However I still fail to load the shape file.

 

Here is what I have:

private void button1_Click(object sender, EventArgs e)
{
     string ConnStr = "Server=" + tbServer.Text +
	//";Port=" + tbPort.Text +
	";User Id=" + tbUsername.Text +
	";Password=" + tbPassword.Text +
	";Database=" + tbDatabase.Text + ";";
     shpDS = new SharpMap.Data.Providers.SqlServer2008(ConnStr,"cen_lines","WKB_Geometry","oid");
     //    (ConnStr, "cen_lines", "oid");
     //shp = new SharpMap.Data.Providers.ShapeFile(tbShapefile.Text, chbSaveIndex.Checked);
     CreateMapDS();
     pictureBox1.Cursor = Cursors.Cross;
}

And my CreateMapDS is as follows:
private void CreateMapDS()
{
   //Initialize the map
   map = new SharpMap.Map(pictureBox1.Size);
   //Setup a shapefile vectorlayer with some default styles
   SharpMap.Layers.VectorLayer layer = new SharpMap.Layers.VectorLayer("preview layer");
   layer.DataSource = shpDS;
   layer.Style.Line = new Pen(Color.Blue);
   layer.Style.Outline = new Pen(Color.Black);
   layer.Style.EnableOutline = true;
   layer.Style.Fill = Brushes.Green;
   layer.Style.Symbol = new Bitmap(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("Shape2Pgsql.PointSymbol.bmp"));
   map.Layers.Add(layer);
   map.ZoomToExtents();
}

I get an error saying cannot call methods on image. Any ideas what's wrong?
 

May 10, 2011 at 10:15 PM
Edited May 10, 2011 at 10:17 PM

If I remove the map.ZoomToExtents call I get ambiguity errors, changing my provider line to the following:

shpDS = new SharpMap.Data.Providers.SqlServer2008(ConnStr, "dbo.cen_lines", "dbo.WKB_Geometry", "dbo.oid");

I get a:
System.Data.SqlClient.SqlException was unhandled
  Message=Cannot find either column "dbo" or the user-defined function or aggregate "dbo.WKB_Geometry.STIntersects", or the name is ambiguous.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  class="16"
  LineNumber=1
  Number=4121
  Procedure=""
  Server=192.168.0.115\SQLEXPRESS
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader()
       at SharpMap.Data.Providers.SqlServer2008.GetGeometriesInView(BoundingBox bbox) in D:\Development\Codeplex\sharpmap_svn0\SharpMap\Data\Providers\SqlServer2008.cs:line 203
       at SharpMap.Layers.VectorLayer.Render(Graphics g, Map map) in D:\Development\Codeplex\sharpmap_svn0\SharpMap\Layers\VectorLayer.cs:line 282
       at SharpMap.Map.RenderMap(Graphics g) in D:\Development\Codeplex\sharpmap_svn0\SharpMap\Map\Map.cs:line 207
       at SharpMap.Map.GetMap() in D:\Development\Codeplex\sharpmap_svn0\SharpMap\Map\Map.cs:line 162
       at Shape2Pgsql.Form1.CreateMapDS() in C:\Users\nlraley\Desktop\ShapeToPG\Shape2Pgsql\Form1.cs:line 187
       at Shape2Pgsql.Form1.button1_Click(Object sender, EventArgs e) in C:\Users\nlraley\Desktop\ShapeToPG\Shape2Pgsql\Form1.cs:line 417
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Shape2Pgsql.Program.Main() in C:\Users\nlraley\Desktop\ShapeToPG\Shape2Pgsql\Program.cs:line 17
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

I think I am close but missing something here.

Coordinator
May 10, 2011 at 10:44 PM

If you want to work with geometries on SqlServer2008, you need to specify those columns as either Geometry or Geography.

From your first post, it seems to me, that you have some BLOB field containing the WKB representation of the geometry?

Hth FObermaier

May 10, 2011 at 10:51 PM
Ah, the WKB_Geometry field was set as an image.

I used the source code in the examples to do this, the example created the table via:

string sql = "CREATE TABLE " + tablename + " (oid INTEGER IDENTITY PRIMARY KEY, WKB_Geometry Image, " +
                             "Envelope_MinX real, Envelope_MinY real, Envelope_MaxX real, Envelope_MaxY real";
                foreach (DataColumn col in columns)
                    if (col.DataType != typeof(String))
                        sql += ", " + col.ColumnName + " " + Type2SqlType(col.DataType).ToString();
                    else
                        sql += ", " + col.ColumnName + " VARCHAR(256)";
                command.CommandText = sql + ");";
                command.ExecuteNonQuery();

Which declared it as type Image.  Is this wrong?  If so what should I do to correct this and if not how would I load the data from the SQL table?

Coordinator
May 11, 2011 at 10:12 AM

That source code is for MsSqlServerSpatial, a spatial extension for Microsoft SQL Server 2005.

For creating a spatial table in SqlServer 2008, you can omit the envelope columns and declare the geometry column as geometry or geography.

CREATE TABLE myTable (oid INTEGER PRIMARY KEY, geom geometry);

For inserting data your command would look like this:I

INSERT INTO myTable VALUES(1, geometry::STGeomFromWKB(@P1));
Hth FObermaier

May 11, 2011 at 2:48 PM
Thanks for the help, that pointed me in the right direction and allowed me to work on it some more along the route you mentioned.

However, I did run into a few issues that maybe you can shed some light on.

First of all, here is my new code for creating the table and inserting the data:

//Create new table for storing the datasource
string sql = "CREATE TABLE " + tablename + " (oid INTEGER IDENTITY PRIMARY KEY, WKB_Geometry geometry";
foreach (DataColumn col in columns)
if (col.DataType != typeof(String))
   sql += ", " + col.ColumnName + " " + Type2SqlType(col.DataType).ToString();
else
   sql += ", " + col.ColumnName + " VARCHAR(256)";
command.CommandText = sql + ");";
command.ExecuteNonQuery();
counter++;

//Select all indexes in shapefile, loop through each feature and insert them one-by-one
foreach (uint idx in indexes)
{
   //Get feature from shapefile
   FeatureDataRow feature = shp.GetFeature(idx);
   if (counter == 0)
   {
       //Create insert script
       string strSQL = " (";
       foreach (DataColumn col in feature.Table.Columns)
       strSQL += "@" + col.ColumnName + ",";

       strSQL += "@WKB_Geometry)";
       strSQL = "INSERT INTO " + tablename + strSQL.Replace("@", "") + " VALUES" + strSQL;

       command.CommandText = strSQL;
       command.Parameters.Clear();
       //Add datacolumn parameters
       foreach (DataColumn col in feature.Table.Columns)
       command.Parameters.Add("@" + col.ColumnName, Type2SqlType(col.DataType));

       //Add geometry parameters
       SqlParameter geometry = command.Parameters.Add("@WKB_Geometry", SqlDbType.NVarChar);
       //geometry.UdtTypeName = "geometry";
  }
  //Set values
  foreach (DataColumn col in feature.Table.Columns)
       command.Parameters["@" + col.ColumnName].Value = feature[col];
  if (feature.Geometry != null)
  {
       command.Parameters["@WKB_Geometry"].Value = feature.Geometry.AsText();
  }
  else
  {
      command.Parameters["@WKB_Geometry"].Value = DBNull.Value;
  }
  //Insert row
  command.ExecuteNonQuery();
  counter++;
Now as you might guess, there is no geometry data type for SqlDbType and therefore I had tried using a .Udt and then setting the type name to geometry.  However, I couldn't determine what to do with the feature.Geometry line in that case, as the binary failed here.
 
I was able to accomplish posting to the database if I set the SqlDbType to a NVarChar and saved the geometry value as feature.Geometry.AsText(); however, it saves fine but I am still unable to load from the database.
 
Here is what I am currently using:
string ConnStr = "Server=" + tbServer.Text + 
     ";User Id=" + tbUsername.Text +
     ";Password=" + tbPassword.Text +
     ";Database=" + tbDatabase.Text + ";";
shpDS = new SharpMap.Data.Providers.SqlServer2008(ConnStr, "cen_lines", "WKB_Geometry", "oid");
//Initialize the map
map = new SharpMap.Map(pictureBox1.Size);
//Setup a shapefile vectorlayer with some default styles
SharpMap.Layers.VectorLayer layer = new SharpMap.Layers.VectorLayer("preview layer");
layer.DataSource = shpDS;
layer.Style.Line = new Pen(Color.Blue);
layer.Style.Outline = new Pen(Color.Black);
layer.Style.EnableOutline = true;
layer.Style.Fill = Brushes.Green;
layer.Style.Symbol = new Bitmap(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("Shape2Pgsql.PointSymbol.bmp"));
map.Layers.Add(layer);
map.ZoomToExtents();
Am I off base here?  What am I doing wrong? 
 

May 11, 2011 at 3:27 PM

I'd also like to add that I had tried setting the Parameter for the geometry as Binary and using the feature.Geometry.AsBinary() for the value but I get the following exception:System.Data.SqlClient.SqlException was unhandled
  Message=A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: One of the identified items was in an invalid format.
System.FormatException:
   at Microsoft.SqlServer.Types.GeoData.Read(BinaryReader r)
   at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r)
   at SqlGeometry::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )
.
The statement has been terminated.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  class="16"
  LineNumber=1
  Number=6522
  Procedure=""
  Server=192.168.0.115\SQLEXPRESS
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Shape2Pgsql.Form1.btnUpload_Click(Object sender, EventArgs e) in C:\Users\nlraley\Desktop\ShapeToPG\Shape2Pgsql\Form1.cs:line 280
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Shape2Pgsql.Program.Main() in C:\Users\nlraley\Desktop\ShapeToPG\Shape2Pgsql\Program.cs:line 17
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

May 11, 2011 at 4:20 PM

Nevermind, the sample I had it as was acutally working.  I forgot to call the map.GetMap() call after loading the data source.

Thanks for all the help.

May 14, 2011 at 9:18 AM

Hello!

I'm using SharpMap v0.9 and SharpMap.Data.Providers.SqlServer2008.

While everything is fine starting from the shapefiles (countries and cities) that you provided in the demo,

I'm having problems with all the other shapefiles I imported in SqlServer 2008 using Shape2Sql.

There are 2 different kind of errors (i converted 6 shapefiles):

1) Impossible to find the STEnvelope method for the type Microsoft.SqlServer.Types.SqlGeography

    in the assembly Microsoft.SqlServer.Types.

2) .NET error during the execution of the aggregation or routine defined from "geometry" user.

    System.ArgumentException: 24144: instance not valid. Use MakeValid......

Have you some idea I can solve these problems? What is wrong? Is there any other way

to import shapefiles into SQL Server 2008?

Thanks for your help.

Salvatore

 

 

 

 

 

 

 

 

 

 

 

Developer
May 14, 2011 at 12:34 PM

Sql2008 provider contains a ValidatesGeometry (default is false) that can help you with MakeValid error. 

May 15, 2011 at 1:19 PM

Is this method available in SharpMap 0.9? I don't find it.

 

Salvatore 

Coordinator
May 16, 2011 at 11:24 AM

sorrentmutie, it is there: http://sharpmap.codeplex.com/SourceControl/changeset/view/88678#996329

If it is not in your code, please check out latest source.

Hth FObermaier

May 17, 2011 at 8:10 PM

You were right. Problem solved, thanks!

 

Salvatore