How do I use SpatiaLite?

Topics: Data Access, General Topics, SharpMap Project, WinForms Controls
Apr 27, 2009 at 5:00 PM
I can't find any code/examples of how to connect to and display SpatiaLite data.

Can anyone point me in the right direction or post an example?
Coordinator
Apr 28, 2009 at 9:48 PM
Hi jwolter,

general information on SpatiaLite can be found here:
Download:http://www.gaia-gis.it/spatialite/
Documentation: http://www.gaia-gis.it/spatialite/docs.html

If you are looking for information on how to use SpatiaLite with SharpMap you may
- take samples for e.g. postgis provider of SharpMap V0.9 since both provider
  implement IProvider interface.
- look at the various demo projects or SpatiaLite Tests for SharpMap V2

Hth
FObermaier
May 4, 2009 at 5:16 PM
FObermaier,

I can't even open the DLL - File not found.  I have it in the Debug folder, the bin folder, the lib folder, the database folder, ..., but no matter where I put it it can't be found.

Is there something I'm missing?



        private void LoadSpatialLite()
        {
            SQLiteConnection cnn = new SQLiteConnection(getConnectionString());
            cnn.Open();

            //Load the Spatialite DLL
            DbCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "SELECT load_extension('libspatialite-2.dll')";
            try
            {
                cmd.ExecuteNonQuery(); // Run queries here
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failure to execute Query:\n\n'" +
                    cmd.CommandText + "'\n\n" + ex.Message);
            }
        }
Coordinator
May 4, 2009 at 8:09 PM
Edited May 4, 2009 at 8:27 PM
Hi JWolter,

first you need to download all the spatialite files:
- libspatialite-win-x86-2.3.0
- proj-win-x86-4.6.1
- geos-win-x86-3.1.0
- libiconv-win-x86-1.9.2

Extract all dll-files in those zips ( usually in the bin folder ) to the debug/release folder.
If you copy them elsewhere you need to add that folder to the environment path variable.

Then you should be all set.

Hth
FObermaier


May 4, 2009 at 10:10 PM
Hi FObermaier,

All of those files reside in bin\Release and bin\Debug and I still get the error.

The executable path is bin\Debug

Joe

Coordinator
May 4, 2009 at 11:43 PM
Joe,

have you run depends.exe on libspatialite-2.dll. Are all dependencies fullfilled?
For me it works fine having all six libspatialite files in the bin\debug or bin\release
folders. If i rename one of them, i get that file not found exception, too.

Hth
FObermaier
May 5, 2009 at 1:54 PM
FObermaier,

depends.exe listed three missing DLLs
   LIBGEOS_C-1.DLL
   LIBCONV2.DLL
   LIBPROJ-0.DLL     (?? This was libproj.dll so I renamed it to libproj-0.dll ??)

I found these DLLs in a PostGres folders and copied them the my Debug and Results folder.  The presence of those DLLs made no difference.

Did I install something wrong?  Did I miss installing something?  Why were these DLLs missing?

Joe
Coordinator
May 5, 2009 at 8:16 PM
Hi Joe,

if you download the zip-files listed in a previous post, extract the contained dll-files (there are 6 altogether, all in a bin folder inside the zips) to the bin\Debug or bin\Release files you should be all set. The files you found probably belong to a postgis installation on your machine, and don't necessarily match those needed by spatialite, since release 2.3 is very fresh.

Hth
FObermaier
May 5, 2009 at 8:48 PM
Hi FObermaier,

The last post helped a lot - Thanks. I missed the reference to the last three zip files in the earlier post. After loading all of the DLLs, the 'libspatialite-2.dll' loads properly.

Now I get the following error when I try to run.

Error: Could not open database file.
Inconvertable type mismatch between SourceColumn 'Geometry' of Byte[] and the DataColumn 'Geometry' of Object.


I loaded the shapefiles using the spatialite-gui.exe, why the mismatch?

Joe
Coordinator
May 5, 2009 at 9:50 PM
Hi Joe,

what version are you working with, sharpmap v0.9 or v2.0?

If you are working with v2.0 your problems could arise from the new version of SpatiaLite for which I didn't yet find the time to test with. The provider for v2.0 has been developed for SpatiaLite 2.2. The needed binaries (DLLs) should be in the SQLite folder of the SpatiaLitePrivider project.

Could you submit a stack trace?

FObermaier
May 11, 2009 at 11:16 PM

hi all

I am currently using sharpmap v0.9 to display layers and i am planning on exporting some of the results i produce using NTS operations and
NTS geometries into a shapefile using the spatiaLite provider for sharpmap v0.9

I posted a nice little demo using sharpmap and spatialite that someone (a newbie) could find usefull. It's writen in vb.net

I experience some problems though and i posted to the spatialite group :

http://groups.google.com/group/spatialite-users/browse_thread/thread/20177b99908b5ffd/a513f6737578405b?hl=en#a513f6737578405b

I hope that this is not considered a cross post! Any help is appreciated.

It would be nice to see a little demo of saving shapefiles using sharpmap v2 at some time :)

Thanks a lot and keep up the good work.