Adding SpatiaLite to Visual Studio project (already has SQLite)

[Danny B.]

Greetings, very new to SpatiaLite here.

I am working on a large Visual Studio C++ project that already supports SQLite, and I am trying to add SpatiaLite capabilities to it. 

After reading the documentation and trying different approaches I am still not sure how to approach this.

The main C++ project loads a separate SQLite Visual Studio project in order to use database functions.

In this SQLite Visual Studio project, we have SQLite headers, DLLs, and LIBs. We use the linker to access them from our main C++ project.

1. Do you think it is viable to try to add the SpatiaLite extension to the SQLite Visual Studio project?

I am not sure we are approaching it correctly. When trying to simply add header files + spatialite.c, and using OMIT_ICONV, OMIT_GEOS, OMIT_PROJ, OMIT_FREEXL in the preprocessor for building, it has many linker problems.

For example:  

1>spatialite.obj : error LNK2019: unresolved external symbol _gaiaDoubleQuotedSql referenced in function _checkSpatialMetaData_ex

What are the minimal dependencies needed, are we missing files?

2. Is there a purpose of building SpatiaLite in MinGW if our main C++ project is built and run from Visual Studio?

I've tried to build SpatiaLite with MinGW 64 bit instructions, but failed. 

On libjpeg step, ran into this error when trying to do the line: cmake -G "MSYS Makefiles" -DCMAKE_INSTALL_PREFIX=/mingw64/local ..

-- The ASM_NASM compiler identification is unknown

-- Didn't find assembler

CMake Error at simd/CMakeLists.txt:41 (enable_language):

  No CMAKE_ASM_NASM_COMPILER could be found.

  Tell CMake where to find the compiler by setting either the environment

  variable "ASM_NASM" or the CMake cache entry CMAKE_ASM_NASM_COMPILER to the

  full path to the compiler, or to the compiler name if it is in the PATH.

---

I tried to skip down to libproj and libgeos, to just build spatialite, and libgeos on "make" step had failures.

error lines:

libtool: compile:  g++ -DHAVE_CONFIG_H -I. -I../../../include -I../../../include                                                                                                                                         /geos -I../../../include -DGEOS_INLINE -pedantic -Wall -ansi -Wno-long-long -ffl                                                                                                                                         oat-store -03 -MT IndexedPointInAreaLocator.lo -MD -MP -MF .deps/IndexedPointInA                                                                                                                                         reaLocator.Tpo -c IndexedPointInAreaLocator.cpp

g++.exe: error: unrecognized command line option '-03'

Thanks for any help.

[a.fu...@lqt.it]

Hi Danny,

too many different questions for a single post.
I'll answer separately, starting with the easier ones about MinGW.

> On libjpeg step, ran into this error when trying to do the line:
> cmake
> -G "MSYS Makefiles" -DCMAKE_INSTALL_PREFIX=/mingw64/local ..
>
> -- The ASM_NASM compiler identification is unknown
> -- Didn't find assembler
> CMake Error at simd/CMakeLists.txt:41 (enable_language):
> No CMAKE_ASM_NASM_COMPILER could be found.
>

libjpeg-turbo is surprisingly fast because it widely relies on
SIMD op-codes. but generating SIMD instructions strictly require
using routines written in Assembler and not in C.
NASM simply is the "Netwide Assembler", an open source asm
specifically intended for supporting x86 and amd64 code.

the above error message simply warns you that you've not yet
installed NASM; you just have to run "pacman -S NASM"

> reaLocator.Tpo -c IndexedPointInAreaLocator.cpp
> g++.exe: error: unrecognized command line option '-03'
>

sounds very improbable: -O3 simply is the standard gcc/g++ setting
for enabling the best speed optimization and is never expected to
cause a failure. the real error cause is probably another.

bye Sandro

[a.fu...@lqt.it]

On Mon, 13 Aug 2018 10:37:11 -0700 (PDT), Danny B. wrote:
> I am working on a large Visual Studio C++ project that already
> supports SQLite, and I am trying to add SpatiaLite capabilities to
> it.
> After reading the documentation and trying different approaches I am
> still not sure how to approach this.
>

me too ... I personally decided many years ago to definitely abandon
Visual Studio C++ and its mind boggling Project Files.
Rediscovering once again the elegant simplicity and robust
effectiveness
of the good old Unix Makefiles was both a mental liberation and a
physical
pleasure.

> The main C++ project loads a separate SQLite Visual Studio project in
> order to use database functions.
> In this SQLite Visual Studio project, we have SQLite headers, DLLs,
> and LIBs. We use the linker to access them from our main C++ project.
>
> 1. Do you think it is viable to try to add the SpatiaLite extension
> to
> the SQLite Visual Studio project?
>

certainly yes. I honestly have no idea about how to materially
implement this in the messy Project files own syntax, but it should
be surely possible.
attempting to further extend the SQLite's own Project isn't probably
a good idea, but the approach seems to be reasonable.
You basically have just to define a new Project with all headers, LIBS
and DLLs; it shouldn't be too much difficult.

> I am not sure we are approaching it correctly. When trying to simply
> add header files + spatialite.c
>

this approach is surely wrong: you don't have to add single sources
to your Project as e.g. spatialite.c; you must start by the assumption
that libspatialite (and all others dependent libraties) are just
LIBRARIES
compiled outside your Visual Studio Project.
your project simply needs to known where are the relevant header files,
and where are the DLLs and LIBs to be linked, it doesn't require to
known about each single source.

> using OMIT_ICONV, OMIT_GEOS, OMIT_PROJ, OMIT_FREEXL in the
> preprocessor
> for building
>

yet another bad idea.
- by omitting ICONV you'll lost the opportunity to convert from
a character set to another (and remember, SpatiaLite strictly
depends on UTF-8, that is not the native encoding supported on
Windows).
- by omitting GEOS you'll negate any serious spatial processing
capability (e.g. computing the intersection of two polygons,
checking if a linestring intersects another and so on).
- by omitting PROJ you'll lost the capability to transform coordinates
between a reference system and another.
- omitting FREEXL could have less devastating effects, but is pointless
because FREEXL is a very simple library requiring just a minimum
effort
for being supported.

a so badly butchered spatialite will be practically useless for any
serious scope.

> it has many linker problems.
> For example:
> 1>spatialite.obj : error LNK2019: unresolved external symbol
> _gaiaDoubleQuotedSql referenced in function _checkSpatialMetaData_ex
>
> What are the minimal dependencies needed, are we missing files?
>

yes, you are missing lots of files.
libspatialite is structured on more than 150 C sources; you've
just included in your project spatialite.c, but 99% of the
required link symbols are defined somewhere else.

this is the real reason why attempting to include in your Project
single source files is not a good idea.
it's by far simpler compiling all the libraries on their own, and then
including in your Project just the header files, DLLs and LIBs.

> 2. Is there a purpose of building SpatiaLite in MinGW if our main C++
> project is built and run from Visual Studio?
>

yes.
building a library using MinGW (and its wonderful support for Linux
makefiles, ./configure scripts and alike) is simple and
straightforward,
whilst attempting to build the same library on Visual Studio Projects
surely is a naughty nightmare, and could be impossible on few cases.

you can eventually take in serious consideration a possible
alternative:

https://trac.osgeo.org/osgeo4w/

this is a huge distribution covering many open source libraries
compiled by and for MSVC (geos, proj, iconv and spatialite itself).
basing your project on pre-built resources downloaded from
OSGeo4W will presumably save you a lot of time and fatigue.

bye Sandro

[br...@frogmouth.net]

I think you’ve got the architecture wrong. You should think of spatialite as a plugin to sqlite.

Starting with compiling in parts of spatialite is the wrong approach.

 

Instead, use a built version of spatialite, and make sure your sqlite can load the module. If you don’t know what I mean, start with using sqlite command line and a spatialite tutorial. Then realise that your big C++ project is just a replacement for the command line part.

 

The only hard part is making sure that the dependencies (geos, proj, etc) are in the library load path. How you do that is up to you.

 

Brad

 

[a.fu...@lqt.it]

yes, I fully agree with Brad; loading spatialite as a dynamic
extension module surely is the simplest and easiest solution.

this is the standard mechanism usually adopted by all developers
writing their applications in Java, Python, C#, PHP and many
other languages, and it will obviously perfectly work also
for C++ apps.

what you really need to do is adding just two lines in your
own C++ code immediately after establishing a new connection
to some SQLite database, something like this:

--------------------------------------------------------------
#include <sqlite3.h>

const char *path:
sqlite3 *handle;
int ret;

// establishing a new connection
ret = sqlite3_open_v2(path, &handle, SQLITE_OPEN_READWRITE, NULL);
if (ret)
{
// unexpected error
}

// dynamically loading the spatialite extension module
ret = sqlite3_exec(handle,
"SELECT load_extension('mod_spatialite')", NULL, 0, NULL);
if (ret != SQLITE_OK)
{
// unexpected error
}

// enforcing Foreign Key constraints
ret = sqlite3_exec(handle,
"PRAGMA foreign_keys = 1", NULL, 0, NULL);
if (ret != SQLITE_OK)
{
// unexpected error
}
--------------------------------------------------------------

after this your application will be ready to execute any
possible Spatial SQL query based on SQLite+SpatiaLite.
Your own Visual Studio C++ Project files will not be minimally
affected because just the basic SQLite support will be
required.
in this scenario loading SpatiaLite will simply become a pure
SQL run time problem, and will no longer interest in any way
how your application is compiled and linked.

bye Sandro

[mj10777]

You should also add a check that the loading succeeded

- spatialite may not be installed on some systems

SELECT spatialite_version();

The result of this command must never be empty

- if 'Error: no such function:' is returned then 'mod_spatialite' failed

Mark

bye Sandro

[Danny B.]

Thanks for the help everyone. I will start with the dynamic approach today.

[Danny B.]

Getting tripped up on the basics. I'm using the tutorial here on loading spatialite in sqlite3.exe https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html#t2.1

I installed OSGeo4W64 and located dlls and libs of the mentioned dependencies of spatialite from http://www.gaia-gis.it/gaia-sins/mingw64_how_to.html#libspatialite 

so libiconv, libproj, libgeos, FreeXL dlls and libs and headers, plus mod_spatialite.dll , spatialite.dll, sqlite3.dll, and libs and headers. Not sure what to do with them yet.

Back to the tutorial. I have init_spatialite-2.2.sql as my database from a different spatialite tutorial. So in cmd I go to a folder with sqlite3.exe, sqlite3.dll, and init_spatialite-2.2.sql and run 

sqlite3 init_spatialite-2.2.sql

I noticed the SQLite version from OSGeo4W64 is 3.17.0. (not sure if this is recent enough, hopefully it is)

At this point the tutorial says to run output formatting which works fine, but then when I try to load spatialite

sqlite> .load 'libspatialite-2.dll'

I know I don't have this file, I have the OSGeo4W64 dll's. So I tried with 'mod_spatialite.dll' and 'spatialite.dll' and put those files in the same folder but response is

Error: The specified module could not be found.

What should I do from here?

I also tried with the sqlite3.exe that our project is using but it does not have 'load' as a possible command. I'm guessing it doesn't have load available and that will have to change in order to use spatialite.

[a.fu...@lqt.it]

On Wed, 15 Aug 2018 09:05:21 -0700 (PDT), Danny B. wrote:
> Getting tripped up on the basics. I'm using the tutorial here on
> loading spatialite in
>
> sqlite3.exe https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html#t2.1
>

OMG !!!

Danny,

please, immediately throw in the trash bin that obsolete documentation;
it was written about ten years ago and nowadays is completely
misleading.
(as the BIG YELLOW BANNER at the very top of the page warns).

NOTE: current version is 4.3.0, and the release cycle of version 5.0.0
is already started. Never ever use nothing released before v.4.0.0 !!!

what you really need to read is this Wiki page:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

bye Sandro

[mj10777]

On Wednesday, 15 August 2018 18:05:22 UTC+2, Danny B. wrote:

Getting tripped up on the basics. I'm using the tutorial here on loading spatialite in sqlite3.exe https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html#t2.1

I installed OSGeo4W64 and located dlls and libs of the mentioned dependencies of spatialite from http://www.gaia-gis.it/gaia-sins/mingw64_how_to.html#libspatialite 

so libiconv, libproj, libgeos, FreeXL dlls and libs and headers, plus mod_spatialite.dll , spatialite.dll, sqlite3.dll, and libs and headers. Not sure what to do with them yet.

The starting point for learning about Spatialite should always be:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

here a list of valid sql-commands are shown.

At the top of that page you will see what the latest, released, version is.

At present this is: 'SpatiaLite 4.3.0'

Any page starting with:

Warning

This document is obsolete and contains outdated information. It's still available only to preserve full historical record.
The updated documentation supporting recent versions of SpatiaLite is now published at the following URLs:

Tutorials: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=misc-docs
Cookbook: http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html

should therefore be avoided, since it is based on version 2.3.

The shown links should be used and not anything pertaining to a version less that 4.0.

 

Back to the tutorial. I have init_spatialite-2.2.sql as my database from a different spatialite tutorial. So in cmd I go to a folder with sqlite3.exe, sqlite3.dll, and init_spatialite-2.2.sql and run 

sqlite3 init_spatialite-2.2.sql

This is even worse: for Version 2.2. 

I noticed the SQLite version from OSGeo4W64 is 3.17.0. (not sure if this is recent enough, hopefully it is)

Here you can see what the latest sqlite3 version is:

https://www.sqlite.org/download.html

Using something near the latest version should be used.

Mark

[Danny B.]

Thanks again, all. I saw the notice but I did get lost navigating all the documentation. I'll continue with the more recent ones.

[Danny B.]

I was able to successfully build with mingw64 and run SQLite on command line and use the SELECT load_extension('mod_spatialite');  command to load spatialite. It didn't complain and I ran some spatialite command successfully.

I'm not 100% certain I understand it yet though:

I opened command line in this location: mingw64/local/bin, where sqlite3.exe built from the instructions is. I also copied in my database file here. I had to also copy in mod_spatialite.dll, dll.a and .la files from mingw64/local/lib (not sure I needed all three). Without the mod_spatialite files in the bin folder it would say it can't find the specified module, which makes sense but I'm wondering: what is the general correct way to organize these files?

The next step would be to figure out if our sqlite in the Visual Studio project can support load_extension. I'm not sure it can. If not, we may have to revert to static linking approach. Thanks again everyone for the help, I am not good at this.

[br...@frogmouth.net]

In general, getting the paths right is usually the hardest part on using spatialite in larger projects. It depends on what you already have set up for library paths, and the deployment scenario.

 

I’m curious: if you can’t load the module, what do you think static linking is going to do?

 

Brad

 

[a.fu...@lqt.it]

On Wed, 15 Aug 2018 15:21:09 -0700 (PDT), Danny B. wrote:
> I had to also copy in mod_spatialite.dll, dll.a
> and .la files from mingw64/local/lib (not sure I needed all three).
>

Hi Danny,

only *.dll files are required; *.dll.a and *.la files are absolutely
useless. a quick explanation:
- *.dll is the real DLL (shared / dynamic library).
- *.dll.a is the corresponding stub for permanently linking the DLL
to some application (same of LIB/DEF files on MSVC++); but we are
now using deferred dynamic loading, so it's not required.
- *.la simply is a plain text file (you can read/edit it with any
text editor), and is intended for supplying to "libtool" (a linux
helper tool for compiling/linking) detailed informations about
each library, so it's completely useless at run time.

on Windows platforms (but not on Linux) there is little annoyance;
MinGW will directly install all "ordinary" *.DLL files on -/local/bin,
but "loadable modules" (such as mod_spatialite.dll) will be installed
instead on -/local/lib, so they require to be manually copied on
-/local/bin

> Without the mod_spatialite files in the bin folder it would say it
> can't find the specified module, which makes sense but I'm wondering:
> what is the general correct way to organize these files?
>

you must absolutely read this document about the folders where
Windows searches any required DLL file:

https://docs.microsoft.com/en-us/windows/desktop/dlls/dynamic-link-library-search-order#standard-search-order-for-desktop-applications

as you can notice, the first folder to be searched will always be the
same from where the main executable *.EXE has been loaded; so the
most obvious place where installing your DLLs is in the same folder
where you usually install your C++ executable file.

installing your own DLLs on the system folders isn't a good idea,
but modifying the PATH system variable could be eventually useful.
NOTE: installer programs usually make simple handling all these
details; check the appropriate documentation.

> The next step would be to figure out if our sqlite in the Visual
> Studio project can support load_extension. I'm not sure it can.
>

well, checking if can or cannot is basically simple.
and resolving any related issue is even simpler.

1. first of all check which specific version of libsqlite3 is
currently supported by your Visual Studio Project.
the current version is 3.24.0 (warmly recommended).
any version previous to 3.20.0 has to be considered as
noticeably outdated and should be quickly updated.
hint: SQLite adopts a very short development cycle, and
more or less every two months a new version is released;
using versions of the previous year (or even older) never
is a good idea.
you can read the full list of all SQLite's versions with
the corresponding release dates from here:
https://www.sqlite.org/changes.html

2. then you should test if "SELECT load_extension" do effectively
works. write a simple C++ source and test it. NOTE: you must call
sqlite3_enable_load_extension(handle, 1);
before attempting to load the dynamic extension, otherwise a
"not authorized" error will be reported.
HINT: always carefully check all the retcodes returned by SQLite.

3. some prebuilt versions of libsqlite3 do not support
"load_extension".
if this is your case, carefully check the Project File; it will
presumably define a SQLITE_OMIT_LOAD_EXTENSION directive.
remove it and rebuild once again.

bye Sandro

[Danny B.]

re: Brad: I'm not sure. I thought that static linking was including the spatialite files (headers) which would allow the VS project to use spatial methods. But since it is a plugin to sqlite, maybe it doesn't work the same way.

re: Sandro. Thank you for listing out the basic steps. I believe our sqlite is older than 3.20 so I'll have to see what I can do about it, but it does seem doable.

[a.fu...@lqt.it]

On Thu, 16 Aug 2018 06:32:40 -0700 (PDT), Danny B. wrote:
> I thought that static linking was including
> the spatialite files (headers) which would allow the VS project to
> use
> spatial methods. But since it is a plugin to sqlite, maybe it doesn't
> work the same way.
>

Danny,

SpatiaLite is _TWO_ different things at the same time:

1. a C/C++ library supporting a set of Spatial API
2. an extension adding than 500 Spatial SQL functions
(based on the top of the above API) to the standard
core directly supported by SQLite itself.

by adopting the dynamic module approach (mod_spatialite)
you'll enable just the second method, not the first one.
your application will automatically acquire the capability
to process complex Spatial SQL queries, exactly as it
happens on Java, C#, Python and many other popular languages,
but it will not be able in any way to directly access the
underlaying C API. so including the spatialite's own header
files will be completely useless in this scenario.

by adopting a more conventional approach based on directly
linking the underlaying libraries (statically or dynamically,
it doesn't matters) you'll enable both the C API and the SQL
interface at the same time.
this surely is a more powerful and flexible configuration,
only available to C or C++ developers, but it's surely harder
to implement and will have a very deep impact on the overall
configuration of your project.

so the real question is:
how are you planning to employ SpatiaLite in your app ?
using the basic SQL interface alone could it be sufficient ?
or for any good reason you absolutely need to adopt a
"bare metal" approach by directly interacting with the C API ?

only you can give yourself the most appropriate answers.

what I can say you is simply this: using the SQL interface
is reasonably simple and straightforward, and reserves very
few nasty surprises.
using the C API is rather difficult, and you'll be prepared
to face many boring debugging sessions for carefully
caring many unexpected crashes or memory leaks (at least,
until you'll finally acquire a decent familiarity with the
C API, which usually takes several weeks of initial training).

when developing SpatialiteGUI (the Visual tool for SpatiaLite,
developed in wxWidgets C++) I personally use 90% of SQL approach
and just 10% of C API approach only to support the most irksome
technical tasks of administering and configuring DB objects.

bye Sandro

[Danny B.]

Yes, the sql dynamic module approach is vastly preferable, so I'm working towards that.

[Danny B.]

Hi, recently a teammate was capable of dynamically loading mod_spatialite into our project and running a spatial query (verified that distance() and PtDistWithin() were functional at least). Thanks everyone for the help.

As a followup question, does anyone have any experience with SpatialiteC++ library? https://dpmcmlxxvi.github.io/SpatiaLiteCpp/web/start.html 

Said teammate also was able to get this API to work in our project similarly, and it comes equipped with some data function that might make our lives easier. 

Also, does it seem like it would be easy to support if we wanted to upgrade Spatialite version in the future? (Haven't looked much into it yet, apologies if it's obvious)

[a.fu...@lqt.it]

On Wed, 22 Aug 2018 12:45:46 -0700 (PDT), Danny B. wrote:
> As a followup question, does anyone have any experience with
> SpatialiteC++ library? 
> https://dpmcmlxxvi.github.io/SpatiaLiteCpp/web/start.html
>

no, I personally ignored its very existence.

> Said teammate also was able to get this API to work in our project
> similarly, and it comes equipped with some data function that might
> make our lives easier.
>

I'm not a big fan of C++ libraries wrapping C libraries; it seems
to me that more often than not the final result is just introducing
some redundant extra complexity.

If I understand well, in this case the most obvious benefit added
by the C++ wrapper is in supporting "shared/smart pointers", that
will automatically handle any dynamic memory allocation/deallocation,
thus completely freeing the programmer from caring about malloc()
and free().

> Also, does it seem like it would be easy to support if we wanted to
> upgrade Spatialite version in the future? (Haven't looked much into
> it
> yet, apologies if it's obvious)
>

exactly this seems to be the most obvious design weakness; the
current implementation of SpatialiteC++ is based on SpatiaLite
4.3.0 (released on September 2015) and SQLite 3.17.0 (released
on February 2017).

updating to more recent versions (SpatiaLite 5.0.0 and SQLite
3.24.0) will presumably require a not-so-trivial rewriting
of the C++ wrapping code; and it will worsen for any further
future version being released.

bye Sandro