[Geoserver-users] New SpatiaLite JDBC fork

GeoServer GeoServer User
1

Hi Benjamin,

Neither way feels especially good for me but one or another should be selected if the aim is to develop Spatialite driver into a maintained extension. I understood that the statically linked standalone driver is painless for the end users, but how many different driver versions should be built and maintained for supporting Mac/Windows/and a few most common Linux brands , both 32 and 64 bit? Is it one for each combination?

You wrote that you will not try to build for anything else than OS X and I know that none of the core Geoserver developers work on Windows. On the other hand, building Spatialite with GEOS for Windows is somehow tricky if I have understood right what I have read from the Spatialite-users forum. Perhaps for this reason the Spatialite product builds ready-made mod_spatilite binaries for Windows which are downloadable with all the dependencies (http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/mod_spatialite-4.2.1-rc0-win-x86.7z or http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/mod_spatialite-4.2.1-rc0-win-amd64.7z) Using these binaries (and updating mod_spatialite to later versions when they are released) is quite easy on Windows despite some dll-hell troubles which may arise. With some help from an OpenJUMP developer I have managed to update an old OpenJUMP plugin to use Spatialite 4.2.1-RC0 instead of Spatialite 2.x and one other user has managed to install it from http://latuviitta.org/downloads/OJ_SPLite_32-bit_lib_ext.zip and http://latuviitta.org/downloads/OJ_SPLite_64-bit_lib_ext.zip so I know that an out-of-the-box installation with mod_spatialite it is doable on Windows. However, mod_spatialite is not available for other platforms as downloadable binaries which leads to all the pain you have suffered.

In conclusion, without being a programmer I have a feeling that maintaining Spatialite driver for all platforms would need some involvement and I guess that Geoserver developers will not take that workload without paying customers. Therefore I doubt there will be an official Spatialite driver with good support in near future. Personally I hope that somebody at least continues your work and builds your Spatialite driver fork for 64 bit Windows because Spatialite is the DB I use most nowadays.

What do you think about GeoPackage and libgpkg https://bitbucket.org/luciad/libgpkg/src?

-Jukka Rahkonen-

···

Lähettäjä: Stadin, Benjamin [mailto:Benjamin.Stadin@anonymised.com]
Lähetetty: 29. lokakuuta 2014 1:54
Vastaanottaja: GeoServer Mailing List List
Aihe: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Jukka,

Yes, I’ve considered this (I also came across this site). In general, the SQLite extension loading mechanism works very well. I’ve implemented some SQLite extensions before.

The driver could be changed easily to use mod_spatialite instead of the static library. But I dislike one thing about mod_spatialite - the same reasons for which xerial decided to embed SQLite into the driver (authors of the SQLite JDBC library): Dynamic linkage and manual configurations are very error prone to the user with a JDBC driver.

For an example, It took me some time to get all libraries right on my system. Some libs were absent or had a wrong version, or need to be recompiled some config option in a latest version changed and. It get’s worse when a user has to figure what’s wrong when the JDBC module just doesn’t work – it’s hard to debug even for developers.

So in my opinion a JDBC driver must embed as much as possible and preferably work out of the box. I otherwise agree with the SpatiaLite developers when no Java / JNI is involved.

  • Ben

Von: “Rahkonen Jukka (Tike)” <jukka.rahkonen@anonymised.com>
Datum: Dienstag, 28. Oktober 2014 23:30
An: Benjamin Stadin <benjamin.stadin@anonymised.com>, GeoServer Mailing List List <Geoserver-users@anonymised.comourceforge.net>
Betreff: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Benjamin,

Have you considered to make SpatiaLite driver so that SpatiaLite would be loaded as a dynamic extension https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite? At least update to next SpatiaLite versions should be easier then because only the native “mod_spatialite” component needs to be updated. Or is there something in the Spatialite driver that would not work if Spatialite were loaded as an extension?

-Jukka Rahkonen-

Stadin, Benjamin wrote:

Hi,

The new SpatiaLite 4.2.1 driver is now working with Geoserver 2.6 on OS X. Please help to get this ported to other platforms as well. I’ve copy pasted an update to the previous spatialite documentation. It shouldn’t be too hard to get it working on Windows as well, since the new Spatialite is based on autoconfig and there are not too many dependencies to resolve.

I hope the driver will work stand-alone, it’s linked statically agains all libraries. But I did not yet test for hidden linkages.

OS X install instructions:

The driver currently contains only the Mac-64 build.

Regards

Ben

PS: The updated documentation (rough draft, done a bit in a hurry):

Spatialite4 Output Format Documentation

As prerequisite, we first need to build the appropriate libraries

For Windows 64-bits we need:

  • Just download the lastest version.

  • Execute the installer.

  • In repository catalogues, check “Download lastest repository”

  • In select components select all options exept “MinGW Developer Toolkit”

And now we let’s start to build libraries:

#ref = http://www.gaia-gis.it/spatialite-2.4.0/mingw_how_to.html (Steps 1, 6, 8 and 9)

  • First of all, we need to execute MSYS.bat, located at /path/to/mingw/msys/1.0/msys.bat.

  • Building PROJ.4 :

  • Building GEOS :
  • including LIBICONV :
  • just copy the ziped files (Binaries and Developer) into /path/to/minwg/msys/1.0/local

Now we’re ready to actually build SpatiaLite4 JDBC:

  • Install and configure (Check if the directory is in the PATH environment variable) Maven2 (or highter). #url = http://maven.apache.org/

  • Install and configure (Check if the directory is in the PATH environment variable) Mercurial (hg). #url = http://mercurial.selenic.com

  • Wither git clone spatialite4-jdbc, or simply download the repository (“Download ZIP”) from Github (spatialite4 branch):

  • Go inside the root of spatialite4-jdbc and run:
  • make
  • When finished, go inside “target” directory and search for “spatialite4-jdbc-($version)-SNAPSHOT.jar” (This one is the JDBC for our SO).

The makefile builds and configures both SpatiaLite and SQLite, and runs a test after successful build. If all tests pass, everything should be fine and you can copy the .jar file to it’s destined location.

2

On Wed, Oct 29, 2014 at 8:52 AM, Rahkonen Jukka (Tike) <
jukka.rahkonen@anonymised.com> wrote:

You wrote that you will not try to build for anything else than OS X and
I know that none of the core Geoserver developers work on Windows.

None of the ones active in their spare time did... until last Sunday.
I've installed Win 7 back on my notebook, which is rather unsettling, but
at the same time allows me to help slow/stop the bit rot on Windows.
That said, I won't be able to help with this spatialite upgrade, first I
have to finish the CSS rewrite in Java, which has been sucking
a good number of my weekends since end of June.

Let's hope the spatialite module maintainer (Justin) has time to follow up
on this one.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

3

Hi Benjamin,

Neither way feels especially good for me but one or another should be selected if the aim is to develop Spatialite driver into a maintained extension. I understood that the statically linked standalone driver is painless for the end users, but how many different driver versions should be built and maintained for supporting Mac/Windows/and a few most common Linux brands , both 32 and 64 bit? Is it one for each combination?

You wrote that you will not try to build for anything else than OS X and I know that none of the core Geoserver developers work on Windows. On the other hand, building Spatialite with GEOS for Windows is somehow tricky if I have understood right what I have read from the Spatialite-users forum. Perhaps for this reason the Spatialite product builds ready-made mod_spatilite binaries for Windows which are downloadable with all the dependencies (http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/mod_spatialite-4.2.1-rc0-win-x86.7z or http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/mod_spatialite-4.2.1-rc0-win-amd64.7z) Using these binaries (and updating mod_spatialite to later versions when they are released) is quite easy on Windows despite some dll-hell troubles which may arise. With some help from an OpenJUMP developer I have managed to update an old OpenJUMP plugin to use Spatialite 4.2.1-RC0 instead of Spatialite 2.x and one other user has managed to install it from http://latuviitta.org/downloads/OJ_SPLite_32-bit_lib_ext.zip and http://latuviitta.org/downloads/OJ_SPLite_64-bit_lib_ext.zip so I know that an out-of-the-box installation with mod_spatialite it is doable on Windows. However, mod_spatialite is not available for other platforms as downloadable binaries which leads to all the pain you have suffered.

In conclusion, without being a programmer I have a feeling that maintaining Spatialite driver for all platforms would need some involvement and I guess that Geoserver developers will not take that workload without paying customers. Therefore I doubt there will be an official Spatialite driver with good support in near future. Personally I hope that somebody at least continues your work and builds your Spatialite driver fork for 64 bit Windows because Spatialite is the DB I use most nowadays.

What do you think about GeoPackage and libgpkg https://bitbucket.org/luciad/libgpkg/src?

-Jukka Rahkonen-

···

Lähettäjä: Stadin, Benjamin [mailto:Benjamin.Stadin@anonymised.com…6763…]
Lähetetty: 29. lokakuuta 2014 1:54
Vastaanottaja: GeoServer Mailing List List
Aihe: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Jukka,

Yes, I’ve considered this (I also came across this site). In general, the SQLite extension loading mechanism works very well. I’ve implemented some SQLite extensions before.

The driver could be changed easily to use mod_spatialite instead of the static library. But I dislike one thing about mod_spatialite - the same reasons for which xerial decided to embed SQLite into the driver (authors of the SQLite JDBC library): Dynamic linkage and manual configurations are very error prone to the user with a JDBC driver.

For an example, It took me some time to get all libraries right on my system. Some libs were absent or had a wrong version, or need to be recompiled some config option in a latest version changed and. It get’s worse when a user has to figure what’s wrong when the JDBC module just doesn’t work – it’s hard to debug even for developers.

So in my opinion a JDBC driver must embed as much as possible and preferably work out of the box. I otherwise agree with the SpatiaLite developers when no Java / JNI is involved.

  • Ben

Von: “Rahkonen Jukka (Tike)” <jukka.rahkonen@anonymised.com>
Datum: Dienstag, 28. Oktober 2014 23:30
An: Benjamin Stadin <benjamin.stadin@anonymised.com>, GeoServer Mailing List List <Geoserver-users@lists.sourceforge.net>
Betreff: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Benjamin,

Have you considered to make SpatiaLite driver so that SpatiaLite would be loaded as a dynamic extension https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite? At least update to next SpatiaLite versions should be easier then because only the native “mod_spatialite” component needs to be updated. Or is there something in the Spatialite driver that would not work if Spatialite were loaded as an extension?

-Jukka Rahkonen-

Stadin, Benjamin wrote:

Hi,

The new SpatiaLite 4.2.1 driver is now working with Geoserver 2.6 on OS X. Please help to get this ported to other platforms as well. I’ve copy pasted an update to the previous spatialite documentation. It shouldn’t be too hard to get it working on Windows as well, since the new Spatialite is based on autoconfig and there are not too many dependencies to resolve.

I hope the driver will work stand-alone, it’s linked statically agains all libraries. But I did not yet test for hidden linkages.

OS X install instructions:

The driver currently contains only the Mac-64 build.

Regards

Ben

PS: The updated documentation (rough draft, done a bit in a hurry):

Spatialite4 Output Format Documentation

As prerequisite, we first need to build the appropriate libraries

For Windows 64-bits we need:

  • Just download the lastest version.

  • Execute the installer.

  • In repository catalogues, check “Download lastest repository”

  • In select components select all options exept “MinGW Developer Toolkit”

And now we let’s start to build libraries:

#ref = http://www.gaia-gis.it/spatialite-2.4.0/mingw_how_to.html (Steps 1, 6, 8 and 9)

  • First of all, we need to execute MSYS.bat, located at /path/to/mingw/msys/1.0/msys.bat.

  • Building PROJ.4 :

  • Building GEOS :
  • including LIBICONV :
  • just copy the ziped files (Binaries and Developer) into /path/to/minwg/msys/1.0/local

Now we’re ready to actually build SpatiaLite4 JDBC:

  • Install and configure (Check if the directory is in the PATH environment variable) Maven2 (or highter). #url = http://maven.apache.org/

  • Install and configure (Check if the directory is in the PATH environment variable) Mercurial (hg). #url = http://mercurial.selenic.com

  • Wither git clone spatialite4-jdbc, or simply download the repository (“Download ZIP”) from Github (spatialite4 branch):

  • Go inside the root of spatialite4-jdbc and run:
  • make
  • When finished, go inside “target” directory and search for “spatialite4-jdbc-($version)-SNAPSHOT.jar” (This one is the JDBC for our SO).

The makefile builds and configures both SpatiaLite and SQLite, and runs a test after successful build. If all tests pass, everything should be fine and you can copy the .jar file to it’s destined location.

4

Sorry for the late reply on this guys. Good stuff Ben. Upgrading the version of spatialite we use for the driver has been on my list of things to do, but haven’t got around to it. However, I was actually thinking of taking a different approach since as you may have seen the way the sqlite/spatialite library is kind of brittle.

The idea was inspired by a recent post from Jukka who stated that with newer versions of spatialite it is possible to load it as a module into an existing sqlite build as opposed to having to compile sqlite directly into it. I haven’t tried this out myself yet but if it does work the idea would be that the spatialite module wouldn’t need a custom sqlite driver anymore and would just load the spatialite extension dynamically at runtime.

To make this a bit nicer to use out of the box i was thinking we could follow suite after the xerial sqlite jdbc driver and bundle spatialite libraries for each platform directly into the module so that the module could be completely self contained and not have to worry about library path issues.

···

On Wed, Oct 29, 2014 at 2:05 AM, Stadin, Benjamin <Benjamin.Stadin@anonymised.com> wrote:

Hi Jukka,

In conclusion, without being a programmer I have a feeling that maintaining Spatialite driver for all platforms would need some involvement and I guess that Geoserver developers will not take that workload without paying customers.

You are right, maintenance is an issue. But I had also improved maintainability in mind when I rebased on Xerial’s current SQLite JDBC and the new autoconfig build of SpatiaLite. It is now again possible to easily merge any changes from Xerials repository into the new spatialite repo.

So the maintenance for different OS for the SQLite part depends on them. The maintenance for different OS / configurations of the SpatiaLite part now depends on Spatialite itself, because they use a new autoconfig build system and removed they old amalgamation sources which left the developer to find proper configuration.

From maintenance perspective it doesn’t matter whether mod_spatialite or a statical lib is used at the moment – if required we can switch at any time transparently. Both mod_spatialite and the dynamic and static libs are the result of the same spatialite build step (./configure with some params, then make).

So in conclusion, it should be possible to maintain this library. And it doesn’t matter whether mod_spatialite or a static lib is used, we can switch to either with ease. But of course it needs some volunteers.

  • Ben

Von: “Rahkonen Jukka (Tike)” <jukka.rahkonen@anonymised.com>
Datum: Mittwoch, 29. Oktober 2014 08:52

An: Benjamin Stadin <benjamin.stadin@anonymised.com>, ‘GeoServer Mailing List List’ <Geoserver-users@anonymised.comurceforge.net>
Betreff: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Benjamin,

Neither way feels especially good for me but one or another should be selected if the aim is to develop Spatialite driver into a maintained extension. I understood that the statically linked standalone driver is painless for the end users, but how many different driver versions should be built and maintained for supporting Mac/Windows/and a few most common Linux brands , both 32 and 64 bit? Is it one for each combination?

You wrote that you will not try to build for anything else than OS X and I know that none of the core Geoserver developers work on Windows. On the other hand, building Spatialite with GEOS for Windows is somehow tricky if I have understood right what I have read from the Spatialite-users forum. Perhaps for this reason the Spatialite product builds ready-made mod_spatilite binaries for Windows which are downloadable with all the dependencies (http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/mod_spatialite-4.2.1-rc0-win-x86.7z or http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/mod_spatialite-4.2.1-rc0-win-amd64.7z) Using these binaries (and updating mod_spatialite to later versions when they are released) is quite easy on Windows despite some dll-hell troubles which may arise. With some help from an OpenJUMP developer I have managed to update an old OpenJUMP plugin to use Spatialite 4.2.1-RC0 instead of Spatialite 2.x and one other user has managed to install it from http://latuviitta.org/downloads/OJ_SPLite_32-bit_lib_ext.zip and http://latuviitta.org/downloads/OJ_SPLite_64-bit_lib_ext.zip so I know that an out-of-the-box installation with mod_spatialite it is doable on Windows. However, mod_spatialite is not available for other platforms as downloadable binaries which leads to all the pain you have suffered.

In conclusion, without being a programmer I have a feeling that maintaining Spatialite driver for all platforms would need some involvement and I guess that Geoserver developers will not take that workload without paying customers. Therefore I doubt there will be an official Spatialite driver with good support in near future. Personally I hope that somebody at least continues your work and builds your Spatialite driver fork for 64 bit Windows because Spatialite is the DB I use most nowadays.

What do you think about GeoPackage and libgpkg https://bitbucket.org/luciad/libgpkg/src?

-Jukka Rahkonen-

Lähettäjä: Stadin, Benjamin [mailto:Benjamin.Stadin@anonymised.com]
Lähetetty: 29. lokakuuta 2014 1:54
Vastaanottaja: GeoServer Mailing List List
Aihe: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Jukka,

Yes, I’ve considered this (I also came across this site). In general, the SQLite extension loading mechanism works very well. I’ve implemented some SQLite extensions before.

The driver could be changed easily to use mod_spatialite instead of the static library. But I dislike one thing about mod_spatialite - the same reasons for which xerial decided to embed SQLite into the driver (authors of the SQLite JDBC library): Dynamic linkage and manual configurations are very error prone to the user with a JDBC driver.

For an example, It took me some time to get all libraries right on my system. Some libs were absent or had a wrong version, or need to be recompiled some config option in a latest version changed and. It get’s worse when a user has to figure what’s wrong when the JDBC module just doesn’t work – it’s hard to debug even for developers.

So in my opinion a JDBC driver must embed as much as possible and preferably work out of the box. I otherwise agree with the SpatiaLite developers when no Java / JNI is involved.

  • Ben

Von: “Rahkonen Jukka (Tike)” <jukka.rahkonen@anonymised.com>
Datum: Dienstag, 28. Oktober 2014 23:30
An: Benjamin Stadin <benjamin.stadin@anonymised.com>, GeoServer Mailing List List <Geoserver-users@lists.sourceforge.net>
Betreff: Re: [Geoserver-users] New SpatiaLite JDBC fork

Hi Benjamin,

Have you considered to make SpatiaLite driver so that SpatiaLite would be loaded as a dynamic extension https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite? At least update to next SpatiaLite versions should be easier then because only the native “mod_spatialite” component needs to be updated. Or is there something in the Spatialite driver that would not work if Spatialite were loaded as an extension?

-Jukka Rahkonen-

Stadin, Benjamin wrote:

Hi,

The new SpatiaLite 4.2.1 driver is now working with Geoserver 2.6 on OS X. Please help to get this ported to other platforms as well. I’ve copy pasted an update to the previous spatialite documentation. It shouldn’t be too hard to get it working on Windows as well, since the new Spatialite is based on autoconfig and there are not too many dependencies to resolve.

I hope the driver will work stand-alone, it’s linked statically agains all libraries. But I did not yet test for hidden linkages.

OS X install instructions:

The driver currently contains only the Mac-64 build.

Regards

Ben

PS: The updated documentation (rough draft, done a bit in a hurry):

Spatialite4 Output Format Documentation

As prerequisite, we first need to build the appropriate libraries

For Windows 64-bits we need:

  • Just download the lastest version.

  • Execute the installer.

  • In repository catalogues, check “Download lastest repository”

  • In select components select all options exept “MinGW Developer Toolkit”

And now we let’s start to build libraries:

#ref = http://www.gaia-gis.it/spatialite-2.4.0/mingw_how_to.html (Steps 1, 6, 8 and 9)

  • First of all, we need to execute MSYS.bat, located at /path/to/mingw/msys/1.0/msys.bat.

  • Building PROJ.4 :

  • Building GEOS :
  • including LIBICONV :
  • just copy the ziped files (Binaries and Developer) into /path/to/minwg/msys/1.0/local

Now we’re ready to actually build SpatiaLite4 JDBC:

  • Install and configure (Check if the directory is in the PATH environment variable) Maven2 (or highter). #url = http://maven.apache.org/

  • Install and configure (Check if the directory is in the PATH environment variable) Mercurial (hg). #url = http://mercurial.selenic.com

  • Wither git clone spatialite4-jdbc, or simply download the repository (“Download ZIP”) from Github (spatialite4 branch):

  • Go inside the root of spatialite4-jdbc and run:
  • make
  • When finished, go inside “target” directory and search for “spatialite4-jdbc-($version)-SNAPSHOT.jar” (This one is the JDBC for our SO).

The makefile builds and configures both SpatiaLite and SQLite, and runs a test after successful build. If all tests pass, everything should be fine and you can copy the .jar file to it’s destined location.

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

Justin Deoliveira
VP Engineering | Boundless
jdeolive@anonymised.com
@boundlessgeo