Using SpatiaLite from VB.NET on x64 Windows7

As SpatiaLite is basically an extension to the SQLite, so first get the driver for SQLite and then the SpatiaLite extension.

System.Data.SQLite driver

Download System.Data.SQLite driver from system.data.sqlite.org. Get the libraries for your version of .NET Framework and Windows version: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Create a new .Net project, C# or VB.NET.

Add System.Data.SQLite.dll as a reference. It will not work without SQLite.Interop.dll; add it to the project but DO NOT add it as reference.

In Project properties->Build set the Platform target to the appropriate version of the System.Data.SQLite.dll binary you have downloaded (x86 or x64).

SpatiaLite extension

Then download SpatiaLite extension. Pay attention to get mod_spatialite, not libspatialite. Only the former is intended to be used as a loadable extension. At the time of writing, the latest stable mod_spatialite for 64-bit windows is http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/mod_spatialite-4.3.0a-win-amd64.7z

Add all binary files from mod_spatialite to the project, in properties for each one set Copy to output directory to Copy always.

In code, SpatiaLite is loaded using the method LoadExtension(), passing the extension name and entrypoint function name as arguments:

Dim cn As System.Data.SQLite.SQLiteConnection
cn = New SQLiteConnection("Data Source=test.db; Pooling=true; FailIfMissing=false")
cn.EnableExtensions(True)
cn.LoadExtension("mod_spatialite", "sqlite3_modspatialite_init")

Theoretically, it was possible to load SQLite extensions using SQL syntax, like this:

Dim cmd As SQLiteCommand = cn.CreateCommand()
cmd.CommandText = "SELECT load_extension('mod_spatialite', 'sqlite3_modspatialite_init')"
cmd.ExecuteNonQuery()

It is not true anymore for the most recent versions, and attempts to load SpatiaLite using SQL will fail with ‘not authorized’ exception, no matter what you do.

More detailed information can be found here: https://groups.google.com/forum/#!topic/spatialite-users/u2QZpQL_6ek

When SpatiaLite extension is loaded, one can test it and create a spatially enabled SQLite database:

Dim cmd As SQLiteCommand = cn.CreateCommand()
cmd.CommandText = "SELECT InitSpatialMetaData(1)"
cmd.ExecuteNonQuery()

Notice the parameter for the InitSpatialMetaData() function. ‘1’ means to execute everything in one transaction. Without it, spatial metadata initialization takes 1-2 minutes, with it – one-two seconds.

Basically, that’s it.

Sample project

A sample project for Visual Studion 2015 and x64 Windows can be viewed and downloaded from git repository:

https://github.com/pmartinsons/SpatiaLiteTest

It contains all binaries, so you can use it as a qick start for a new project.

Leave a Reply

Your email address will not be published. Required fields are marked *