Unable to use MsSqlServer2008Provider in SharpMap v2.0

Topics: Data Access, SharpMap v2.0
Feb 20, 2010 at 6:49 PM

Just started playing around with SharpMap v2.0 and managed to compile the sources from the latest trunk.

I am trying to create a new layer using MsSqlServer2008Provider as the datasource using the following code:

GeometryServices svc = new GeometryServices();
MsSqlServer2008Provider<int> sqlProvider = new MsSqlServer2008Provider<int>(svc.DefaultGeometryFactory,
                "Server=*;Database=*;User ID=*;Password=*;", 
                "dbo", "Member_landholding", "Land_ID", "Polygon");
GeometryLayer lyr = new GeometryLayer(sqlProvider);


map = new Map(svc.DefaultGeometryFactory, svc.CoordinateTransformationFactory);

if (map.Layers.Count == 0 && lyr.SpatialReference != null)
    map.SpatialReference = lyr.SpatialReference;

map.Layers.Insert(0, lyr);

lyr.Style = RandomStyle.RandomGeometryStyle();

if (map.Layers.Count == 1) { mapViewControl.Map = map; mapViewControl.ZoomToExtents(); }
But I keep getting an SqlException from MsSqlServer2008Provider.cs in the GetExtents method. Below is the exception details.

Message: Cannot find either column "Geom" or the user-defined function or aggregate "Geom.STEnvelope", or the name is ambiguous.
Stack Trace: at System.Data.SqlClient.SqlConnection.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.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at SharpMap.Data.Providers.MsSqlServer2008Provider`1.GetExtents()
   at SharpMap.Data.Providers.AsyncProviderAdapter.GetExtents()
   at SharpMap.Layers.Layer.get_Extents()
   at SharpMap.Map.handleLayersChanged(Object sender, ListChangedEventArgs args)
   at System.ComponentModel.BindingList`1.OnListChanged(ListChangedEventArgs e)
   at SharpMap.Layers.LayerCollection.OnListChanged(ListChangedEventArgs e)
   at System.ComponentModel.BindingList`1.FireListChanged(ListChangedType type, Int32 index)
   at System.ComponentModel.BindingList`1.InsertItem(Int32 index, T item)
   at SharpMap.Layers.LayerCollection.InsertItem(Int32 index, ILayer item)
   at System.Collections.ObjectModel.Collection`1.Insert(Int32 index, T item)

Should'nt the provider build queries using the Geometry column name I have provided in the constructor as opposed to a hardcoded one?

Feb 20, 2010 at 9:39 PM
Edited Feb 20, 2010 at 9:46 PM

Good catch, i'll sort it out ASAP. Just to explain there are 3 modes by which extents can be calculated. One way requires SqlSpatialTools to be installed in the db. Another involves extra envelope columns on the table (fastest) and the default is basically a scan through all geometries and can be very slow - this is the path with the current hard coding issue. BTW there are some static methods on the provider to aid in creating envelope columns and geometry columns table (improves srid lookup performance) etc Cheers jd

Feb 21, 2010 at 5:53 AM

I have modified the switch case in question to use GeometryColumn property instead of the hardcoded one. Now I seem to be stuck in another area.

In the MapPresenter2D.cs, the RenderFeatureLayer seems to be choking out. It goes into the switch case for RenderPhase.Normal.

The FeatureQueryExpression is set to Extensts.Intersects(ThisExpression). After that FeatureLayer.Select method returns null. It should have returned more than 13K rows!

I am not very good at Expression Trees. Had a hard time grasping it while learning Linq as well. But could you please help me sort this out?


Feb 21, 2010 at 6:27 AM


Found a very strange behaviour while debugging through the code to find the root cause of the problem.

The source of the exception is from line 806 in SpatialDbProviderBase.cs. If I simply step over (F10) that line of code while debugging,
I get a NullReferenceException. If I step into (F11) the code then the query seems to be building fine!

Seriously, I checked it thrice before coming back and posting here to make sure I am able to reproduce this behaviour correctly.

One interesting to note however was, after the map rendered for the first time, when I resized the window, the breakpoint on this line was hit again.
This time however, no matter what I did, I kept getting NullReferenceException.


Feb 21, 2010 at 6:32 AM
Edited Feb 21, 2010 at 7:22 AM

I think I have nailed down the problem to line 687 in ExpressionTreeToSqlCompilerBase.cs. The Provider.SpatialReference property is null!

But one interesting observation is that after executing line no 804 in SpatialDbProviderBase.cs, if I wait for sometime in debugger
before stepping to next line everything seems to work fine. Is something happening asynchronously?

Please help me fix this. I am unable to move forward at all.


Feb 21, 2010 at 10:11 AM

Hi Raghu, data access from the control is async see

at SharpMap.Data.Providers.MsSqlServer2008Provider`1.GetExtents()   
at SharpMap.Data.Providers.AsyncProviderAdapter.GetExtents()   
at SharpMap.Layers.Layer.get_Extents()

In your stack above.  If your spatial reference is null it may mean that the SridMap is not configured properly, or that the db is unable to determine the srid of the data.

As I briefly mentioned in a different thread it is advisable to have a geometry columns table with the srid for the table/view set.

You can use the static methods on the MsSql2008Provider to aid you with this.

There is also the FormatConverter demo project which can import shapefiles into a db for you and provides options for spatially indexing the table, adding envelope columns and configuring the geometry_columns table.
Ensure that the build output from all the sibling projects has been copied to the bin directory of the exe. This should be done automagically but usually takes two builds.

If you profile the database you can see the queries being made in an attempt to configure the provider.


hth jd