Spatialite

Oct 31, 2011 at 8:08 AM

Hi

This project appears very useful. I am using Spatialite and would like to incorporate it into Excel. I wonder if it is possible to load the spatialitelib extension at the initialisation stage (perhaps) and use the functions from it.

I have successfully used the spatialite dll in place of the sqlite version, but spatial queries using a spatial database and working query yields no results in the spreadsheet.

http://www.gaia-gis.it/spatialite/

Oct 31, 2011 at 9:17 AM

Hi,

I don't know much about Spatialite and what might be the problem to get it working. If the C API needs no extensions, I think it should work as is. Do you perhaps need a load_extensions(...) call?

Otherwise I suggest you look into the .NET route, with Excel-DNA (http://exceldna.codeplex.com) and something like SharpMap (which apparently also has a Spatialite extension (http://sharpmap.codeplex.com/wikipage?title=SpatiaLite). I am happy to support the Excel-DNA a bit better.

-Govert

Nov 1, 2011 at 7:30 AM
Edited Nov 1, 2011 at 9:52 AM

Hi Govert

I'm not really sure if the extension needs to be loaded manually or not, but I suspect that might be the case. It can be done with a SELECT load_extension statement, but I have no experience in this type of database. I would like to open a connection to the database and leave it open with the application, as I would like to pass a gps location to the query for the spatial database for each updated location.

I have managed to return a result from loading the extension and I am looking further into your code and the SQLite documentation and it is becoming clearer. It is an interesting step from the OLEDB providers and ADO I am used to. I'll let you know how I go. My system environment is very tightly locked down so the use of .NET is out of the question, and I have found Excel a great way to produce applications circumventing the need to install applications or register DLLs and the like.

 

Duncan

 

Nov 1, 2011 at 12:07 PM

Hi Duncan,

Please write back as you find out more about using Spatialite.

I really don't know enough about SQLite or the extensions to be of much help. But I think it helps that the SQLite for Excel bits expose the SQLite calls as directly as possible, so it not really a layer on top.

[As long as .NET is installed on the machine (which is true for Windows XP SP2+ and the default Windows Vista & Windows 7 installs), Excel-DNA needs no further admin rights, registration or installation. In that way, it's much nicer than the other ways of getting .NET to work with Excel when it comes to such locked down environments.]

-Govert

Dec 15, 2011 at 1:45 AM

Hi Govert

It's been a while, but I have had a deeper look into the inner workings of SQLite and Spatialite as well as SQLite for Excel. I am a bit out of my depth with calling dll functions and C, but having had a look at your code I can see that it is not that difficult. What I can't understand is why we can't call functions from SQLite directly, and what your wrapper does to enable it.

As I see it now, I could load the spatialite as an extension if your wrapper exposed a couple more SQLite functions, or I would need to write a similar wrapper for spatialite. Spatialite does contain a full copy of SQLite, but as I see it the exported functions are different (probably wrapped by spatialite) so it would be preferable to do that to minimise dependencies. I must have been mistaken when I thought I had received results from replacing SQLite with spatialite as I can't repeat it. I wonder if you could explain a bit about the intermediate process of translating these dlls for use in VB.

I have had a look at Excel-DNA and it looks quite interesting, but I just haven't got around to getting familiar with .Net.

I had also planned to compile this library for Windows Mobile as the idea is for a GPS application on desktop and mobile, but my unfamiliarity with C is holding me back so far.

Duncan

Dec 15, 2011 at 6:47 AM
Edited Dec 15, 2011 at 6:48 AM

Hi Duncan,

There are different calling conventions for calling library functions, depending on whether parameters are passed on the stack or in processor registers etc. VB6 and VBA only support calling libraries with the StdCall calling convention. The pre-complied SQLite library is compiled with the C calling convention. So to use SQLite from VBA we need to either recompile the SQLite library from source, with the different calling convention, or we need a wrapper library that will expose the function in the StdCall calling convention, and will load and bind the original SQLite library, passing the calls through.

So with the wrapper I have chosen the latter route, so that the pre-compiled SQLite.dll can be used as-is, via the very simple SQLite_StdCall .dll which VBA is able to call. For most function it would not be hard to extend the SQLite_StdCall library to call additional functions, and then add corresponding declarations on the VBA side.

I'm not sure what your plan for Windows Mobile is, but there is a .NET runtime, and with the Xamarin products there is good .NET support on iOS and Android too)

Govert

 

Dec 16, 2011 at 3:49 AM

Hi Govert

Your excellent explanation makes it all very clear. I have had a look through the source code and I am getting a feel for the way you have approached this. It looks like I may be able to use it as a template to do the same for Spatialite, given that many of the functions are SQLite functions.

The simpler route would be (at least for me if my reading of the documentation is correct) is if your library could be expanded to include sqlite3_enable_load_extension, sqlite3_load_extension and possibly even sqlite3_exec it should load the Spatialite library as an extension, and in theory my spatial queries should be resolved on demand. So at this stage I might try that first.

The mobile thing is a plan to do the same application for a handheld as it works from a GPS to provide information about the current location, but I'll get it working on the desktop first. I'll have to do a lot more reading about C program structures before I can get to compile the project for CE

Duncan

 

Dec 21, 2011 at 4:17 PM

Hi Duncan,

My understanding is that you can use the load_extension function (http://www.sqlite.org/lang_corefunc.html#load_extension) as a command instead of calling the C API function sqlite3_load_extension.

So you'd execute something like 

    select load_extension('libspatialite-4.dll'); 

If so, that might mean you need not extend the C API coverage to load the SpatiaLite extension. But this would require the sqlite3.dll you use to have the extension loading option enabled, and I don't know if the pre-compiled binary on the SQLite site has this flag on or not.

-Govert

Dec 23, 2011 at 5:43 AM

Hi Govert

Yes you are right about that. Unfortunately according to the docs it looks like the enable_load_extension function is off by default.

Enable Or Disable Extension Loading

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

So as not to open security holes in older applications that are unprepared to deal with extension loading, and as a means of disabling extension loading while evaluating user-entered SQL, the following API is provided to turn the sqlite3_load_extension() mechanism on and off.

Extension loading is off by default. See ticket #1863. Call the sqlite3_enable_load_extension() routine with onoff==1 to turn extension loading on and call it with onoff==0 to turn it back off again.

Duncan