7

Closed

DotSpatial.Data.Database (interim) solution

description

Hello Folks,

I'm aware that Ted is working on some super duper IQueryable database connection
for DotSpatial. For those who do not like to wait, here is a simple, read-only
(that can change) implementation.

It is split up in the following projects
  • DotSpatial.Data.Database
    Core interfaces and abstract and/or base classes
  • DotSpatial.Data.Database.PostGis
    PostgreSql/PostGis vector provider
  • DotSpatial.Data.Database.SpatiaLite2
    SpatiaLite vector provider, x86 native binaries of rc3 and a small sample
    SQLite/Spatialite db file.
  • ExternalReferences
    Npgsql, Mono.Security, SQLite.Net
Other spatial database stores could be done in a similar way.

I think setup is straighforward, extract the zipfile to your root DotSpatial
folder, add projects to your solution file, add references to your DotSpatial
app.

To load the layers of the sample file you need to call:
var slvp = new DotSpatial.Data.Database.SpatiaLiteVectorProvider();
map.Layers.Add(slvp.Open("DataSource=<PathToYourFile>;", "countries",
"PK_UID", "geom");
map.Layers.Add(slvp.Open("DataSource=<PathToYourFile>;", "rivers", "PK_UID",
"geom");
map.Layers.Add(slvp.Open("DataSource=<PathToYourFile>;", "cities", "PK_UID",
"geom");


As always, comments are welcome, I'm aware that some of the properties must not
be exposed, but it is late...

One questions at last, where to put stuff like this or the
DotSpatial.Controls.BrutileLayer?

Enjoy,
FObermaier

file attachments

Closed Today at 9:03 AM by jany_
This issue list is no longer active. This issue has been copied to our issue list on github (https://github.com/DotSpatial/DotSpatial/issues)

Please check there to find out whether this issue was fixed.

comments

FObermaier wrote Jan 12, 2011 at 11:20 PM

I forgot to mention, that you will have to modify app.config file. The startup tag needs the attribute useLegacyV2RuntimeActivationPolicy set to true. Mine looks like this:
<?xml version="1.0"?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
</configuration>

Shade1974 wrote Jan 12, 2011 at 11:34 PM

Thanks for the post Felix. For the record, Ted was enjoying a nice vacation in Sunny Florida, so basically no work was being done by me. This weekend I have an all important task of setting up my newly purchased 3D HD TV, having the guy install DirectTV, and other stuff. With a new distraction like that around, I think I will have to take some time to enjoy my new toy. The weekend after that may see me resume some regularly scheduled Ted style work =).

Ted

FObermaier wrote Feb 7, 2011 at 8:13 AM

Is this something I should persue any further?

jirikadlec2 wrote Feb 7, 2011 at 6:04 PM

I really like this feature, thanks for sharing. We're using SQLite in our DotSpatial-based application (HydroDesktop). The SpatiaLite provider allows us to store the geospatial data (points, lines, polygons) in the same database as the time series data.

tmatrai wrote Mar 25, 2011 at 1:52 AM

Hi FObermaier!

Thank you for your comment and this feature.

I installed and tried it following your instructions: I implemented a new tool in DemoMap application:
var slvp = new DotSpatial.Data.Database.SpatiaLiteVectorProvider();
map1.Layers.Add(slvp.Open(@"Data Source=E:\Install\SQLite\SpatialDatabases\nvcgeocode.sqlite;", "megyehatar_region", "PK_UID", "Geometry"));

It throrws an exception (it seems it could not connect to the spatial database and connection is null):
System.NullReferenceException was unhandled
Message=Object reference not set to an instance of an object.
Source=DotSpatial.Data.Database
StackTrace:
   at DotSpatial.Data.Database.DbVectorProvider`1.GetOtherColumns(T connection) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database\DbVectorProvider`1.cs:line 153
   at DotSpatial.Data.Database.DbVectorProvider`1.Open(String connectionString, String tablespace, String schema, String table, String fidColumn, String geometryColumn) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database\DbVectorProvider`1.cs:line 72
   at DotSpatial.Data.Database.SpatiaLiteVectorProvider.Open(String connectionString, String table, String fidColumn, String geometryColumn) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database.SpatiaLite2\SpatiaLiteVectorProvider.cs:line 46
   at DemoMap.MainForm.toolStripButton5_Click(Object sender, EventArgs e) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Examples\DemoMap\MainForm.cs:line 380
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.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 DemoMap.Program.Main() in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Examples\DemoMap\Program.cs:line 34
   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:

Note, the connection path to spatialite db is correct and I can make spatial queries using libspatialite-2.dll from .Net projects on it without any problem. DotsSpatial source code I downloaded and use is a104e1bb6db6.

BR:
Tamas

tmatrai wrote Mar 25, 2011 at 2:03 AM

I made a step forward, I could fix the bug above by defining connection without .sqlite file extension. Another exception comes:

System.Data.SQLite.SQLiteException was unhandled
Message=SQLite error
no such table: geometry_columns
Source=System.Data.SQLite
ErrorCode=-2147467259
StackTrace:
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteScalar()
   at DotSpatial.Data.Database.SpatiaLiteVectorProvider.GetFeatureType(String connectionString, String tablespace, String schema, String table, String geometryColumn, String[] initialCommands) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database.SpatiaLite2\SpatiaLiteVectorProvider.cs:line 159
   at DotSpatial.Data.Database.DbVectorProvider`1.Open(String connectionString, String tablespace, String schema, String table, String fidColumn, String geometryColumn) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database\DbVectorProvider`1.cs:line 65
   at DotSpatial.Data.Database.SpatiaLiteVectorProvider.Open(String connectionString, String table, String fidColumn, String geometryColumn) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Data.Database.SpatiaLite2\SpatiaLiteVectorProvider.cs:line 46
   at DemoMap.MainForm.toolStripButton5_Click(Object sender, EventArgs e) in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Examples\DemoMap\MainForm.cs:line 380
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.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 DemoMap.Program.Main() in C:\Projektek\DotSpatial_a104e1bb6db6\DotSpatial.Examples\DemoMap\Program.cs:line 34
   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:

I checked, I have geometry_columns table in the database with the columns/data your query looks for.

tmatrai wrote Mar 25, 2011 at 2:27 AM

...the situation is the same with your sample database

FObermaier wrote Mar 25, 2011 at 10:23 AM

My guess would be, that DbVectorProvider<TConnection>.OpenConnection() fails.
That is probably due to the fact that PerformInitialCommands() because "SELECT load_extentsion('libspatialite-2.dll');" cannot be executed.
Please make sure that libspatialite-2.dll and its dependancies are in the same folder as your application or accessible via environments path variable.

FObermaier wrote Mar 25, 2011 at 10:24 AM

and readd that .sqlite extension. otherwise sqlite will create a file with the path you specified.

tmatrai wrote Mar 25, 2011 at 11:17 AM

Thanks, finally I made it work. ;)

As I see the code, after connection, you load all features into the memory, so all in all it is a ram-based vectory data/layer solution. is it right? Isn't it possilbe to load features inside the visible extent dynamically? E.g. based on spatial index?

Thanks very much again!

FObermaier wrote Mar 25, 2011 at 11:33 AM

As I understand the current DotSpatial data model it is - unlike e.g. http://sharpmap.codeplex.com - an all in ram approach. If I'm wrong, I'd like some hint on where i'm going wrong.

tmatrai wrote Mar 25, 2011 at 11:44 AM

I dod not say it is wrong, I am not an expert of dotspatial it was just a question to make it clear, that it works in the same way as other vector providers. All in all it is a really sad news for me, I have to work with large datasets (gigabytes of maps), so it won't be the right solution for me. :(

I also tested sharpmap's spatialite provider, that is extremely slow, even if it loads data dynamically. I don't know any other opensource spatialite providers that could be used for map rendering in .net environment.

sommerforst wrote Apr 1, 2011 at 10:33 AM

Hi FObermaier,

thank you very much for the PostGIS and SpatiaLite Providers! I encountered a problem while loading a feature with the PostGIS provider.
I get an exception, when the feature is added to the featureset with the following message:
"DBNull not allowed for [PK_COLUMN]"

Your Code in DbVectorProvider^1.cs:
    public IDataSet Open(string connectionString, string tablespace, string schema, string table, string fidColumn, string geometryColumn)
    {
     [...]
                     // Add columns to the feature sets datatable
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                DataColumn dc = dt.Columns[i];
                DataColumn newDataColumn = featureSet.DataTable.Columns.Add(dc.ColumnName, dc.DataType);
                newDataColumn.AllowDBNull = dc.AllowDBNull;   //<---- Can be changed to true as a workaorund
                [...]

            [...]
            if (dr.HasRows)
            {
                WKBReader wkbReader = new WKBReader();
                dt = featureSet.DataTable;
                //int fid = 0;
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        IGeometry geom = wkbReader.Read((byte[]) dr[0]);

                        IFeature f = featureSet.AddFeature(geom);   //<-------- Exception is thrown
When I set AllowDBNull to true everything works fine, but I cannot explain this behaviour.

Regards,
Johannes

worlanyo wrote Mar 2, 2016 at 7:51 PM

Hello I am trying to use the DotSpatial.Data.Database but I keep getting the error

Error 2 The type or namespace name 'WKBReader' could not be found (are you missing a using directive or an assembly reference?) C:\Users\OWNER\Documents\DotSpatial.Data.Database\DotSpatial.Data.Database\DbVectorProvider`1.cs 112 47 DotSpatial.Data.Database

I will be grateful if someone can point me to how I can resolve this error.


Kind Regard
Worlanyo

mogikanin wrote Oct 31, 2016 at 1:27 PM

Felix, would you like to provide your solution as pull request at github repo? Probably it make sense to convert into plugin.

Thanks.