Hi all,
I'd like discussing a couple of things with you after all the tests I've done during this first time past for finding out which was the most reasonable approach to get SQLite and SpatiaLite support into gvSIG.
Brief introduction:
I've tested all the approaches and resources we've discussed [1] and all those I could find asking around and scanning the web.
Results:
SQLITE SUPPORT
- The Xerial SQLite driver[2], derived from the Zentus'one [3], developed and mantained by Taro L. Saito of the University of Tokyo looks to be the most suitable for accomplishing our tasks.
The reasons are mainly:
- it's kept updated and recompiled with every new version of SQLite
- it has some improvements and some fixing
- you need to include in your project just one file (sqlite-jdbc.jar) in order to use it
- as far as it's a java jar it's multiplatform
SPATIALITE
- you can import the libspatialite.* file (according to your system) with Xerial sqlite-JDBC driver(see above)[2] and reading data from a SpatiaLite database
- I've found many bugs in doing Spatialite's operations (due probably to bugs in memory handling of SpatiaLite functions into java-driver): that means I can do any SELECT with SpatiaLite's functions (I've tested many of them) retrieving the data I want BUT with a silly workaround that avoids the crash of the JVM (usually in connection closing / statment reset)
- I can't write data at the moment (I've found no workarounds/fixing yet) because of other errors in memory handling.
I've got in touch with Taro L. Saito but I guess he has not found any fixing for that segmentation fault.
I will try to write him again hoping he has time to fix this problem.
CONCLUSIONS
I guess that thanks to SQLite-JDBC driver [2] including SQLite support into gvSIG should be quite quick (Cèsar has suggested me to see the libFMap_daldb for understanding how MySQL, etc are already supported in our application).
SpatiaLite support needs to be discussed a little bit because the reading it's quite straightforward (keeping in mind we can make it work with a workaround. That's not proper but as a temporary solution could be partially acceptable), including the proper library (according to OS and architecture) thanks to the SQLite-JDBC driver as well.
SOLUTION I PROPOSE ABOUT SPATIALITE
Well, I'd need hearing your ideas about this matter anyway and discuss together about which whould be the better way to get spatialite support.
However I've a proposal to get finally a much better work in my opinion: I've almost finished the code for decoding the SPATIALITE BLOB[5] (that's the particular structure that stores every Spatialite infos) and I guess the better thing would be implementing a Java version of SpatiaLite or at least of its most common functionalities.
For many of them we could have help from JTS[4] (as partially suggested by Juan Lucas [1]).
Firstly it would let us having just one file (a common java jar) for every OS and architecture, instead of having a lib file for each most common machine.
It would avoid the necessity of trying to wrap a C lib into a JAVA interface with all the problems we have already with the memory handling in importing libspatialite.* into the already fully working driver [2].
It would take some time BUT finally we will have a much cleaner and multiplatform solution.
Sorry for the maybe too long paper,
Cheers,
Luca
[1] http://osgeo-org.1803224.n2.nabble.com/New-Student-for-GVSIG-within-Google-Summer-Of-Code-2010-quick-introduction-td5014112.html#a5014112
[2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
[3] http://www.zentus.com/sqlitejdbc/
[4] http://www.vividsolutions.com/jts/jtshome.htm
[5] http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.1.html#t3.3
Hi all,
I'd like discussing a couple of things with you after all the tests I've done during this first time past for finding out which was the most reasonable approach to get SQLite and SpatiaLite support into gvSIG.
Brief introduction:
I've tested all the approaches and resources we've discussed [1] and all those I could find asking around and scanning the web.
Results:
SQLITE SUPPORT
- The Xerial SQLite driver[2], derived from the Zentus'one [3], developed and mantained by Taro L. Saito of the University of Tokyo looks to be the most suitable for accomplishing our tasks.
The reasons are mainly:
- it's kept updated and recompiled with every new version of SQLite
- it has some improvements and some fixing
- you need to include in your project just one file (sqlite-jdbc.jar) in order to use it
- as far as it's a java jar it's multiplatform
SPATIALITE
- you can import the libspatialite.* file (according to your system) with Xerial sqlite-JDBC driver(see above)[2] and reading data from a SpatiaLite database
- I've found many bugs in doing Spatialite's operations (due probably to bugs in memory handling of SpatiaLite functions into java-driver): that means I can do any SELECT with SpatiaLite's functions (I've tested many of them) retrieving the data I want BUT with a silly workaround that avoids the crash of the JVM (usually in connection closing / statment reset)
- I can't write data at the moment (I've found no workarounds/fixing yet) because of other errors in memory handling.
I've got in touch with Taro L. Saito but I guess he has not found any fixing for that segmentation fault.
I will try to write him again hoping he has time to fix this problem.
CONCLUSIONS
I guess that thanks to SQLite-JDBC driver [2] including SQLite support into gvSIG should be quite quick (Cèsar has suggested me to see the libFMap_daldb for understanding how MySQL, etc are already supported in our application).
SpatiaLite support needs to be discussed a little bit because the reading it's quite straightforward (keeping in mind we can make it work with a workaround. That's not proper but as a temporary solution could be partially acceptable), including the proper library (according to OS and architecture) thanks to the SQLite-JDBC driver as well.
SOLUTION I PROPOSE ABOUT SPATIALITE
Well, I'd need hearing your ideas about this matter anyway and discuss together about which whould be the better way to get spatialite support.
However I've a proposal to get finally a much better work in my opinion: I've almost finished the code for decoding the SPATIALITE BLOB[5] (that's the particular structure that stores every Spatialite infos) and I guess the better thing would be implementing a Java version of SpatiaLite or at least of its most common functionalities.
For many of them we could have help from JTS[4] (as partially suggested by Juan Lucas [1]).
Firstly it would let us having just one file (a common java jar) for every OS and architecture, instead of having a lib file for each most common machine.
It would avoid the necessity of trying to wrap a C lib into a JAVA interface with all the problems we have already with the memory handling in importing libspatialite.* into the already fully working driver [2].
It would take some time BUT finally we will have a much cleaner and multiplatform solution.
Sorry for the maybe too long paper,
Cheers,
Luca
[1] http://osgeo-org.1803224.n2.nabble.com/New-Student-for-GVSIG-within-Google-Summer-Of-Code-2010-quick-introduction-td5014112.html#a5014112
[2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
[3] http://www.zentus.com/sqlitejdbc/
[4] http://www.vividsolutions.com/jts/jtshome.htm
[5] http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.1.html#t3.3