OpenJUMP with SpatialLite
Introduction
There are two plugins that allow to use SpatialLite with OpenJUMP:
- the JUMP DB Query Plugin
- a separate plugin available here
Using SpatialLite with DB Query Plugin
this has been written by Jukka:
Installing and using JUMP Database Query Plugin with Spatialite databases
Tested with:
- Windows XP
- Java 1.6.0_ 06
- OpenJUMP 1.3.1
- DB Query Plugin 0.7.0 (19th January, 2010)
- SQLite JDBC driver downloaded from http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.6.20.1/sqlite-jdbc-3.6.20.1.jar
Getting the plugin and SQLite driver
Go to http://sourceforge.net/projects/jumpdbqplugin/files/ and download the plugin.
The readme file "README_jumpdbquery.txt" included in the downloaded zip file gives advice for downloading SQLite JDBC driver from http://www.zentus.com/sqlitejdbc/
I did my first test with the pure Java driver downloaded from zentus and plugin worked with it. However, next day I could not access the zentus web site any more. Fortunately I managed to find another SQLite JDBC driver from www.xerial.org and that worked with DB Query plugin as well. The one I used is this:
Installation
Installation is done simply by unzipping the contents of jumpdbplugin.zip and SQLite JDBC driver into bin\ext folder of OpenJUMP.
Configuring DB Query Plugin for Spatialite databases
DB Query plugin is configured by editing file "dbquery.properties" that is located at bin\ext folder. With Spatialite there is not much to configure, but there is a typo in the default properties file that comes with plugin version 0.7.0 and it needs to be corrected by hand. Note, that this problem has been corrected with the DB Query Plugin Version 0.7.1.
There reads originally
#"spatialite1" database jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.postgres.JumpSpatialiteDbQuery jump.dbquery.driver.spatialite1=org.sqlite.JDBC jump.dbquery.jdbcurl.spatialite1=jdbc:sqlite:/path_to_db_file.db jump.dbquery.username.spatialite1= #jump.dbquery.password.spatialite1=
The classpath is wrong and is must be corrected like this:
jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialite
Now the installation should be ready. This can be verified partly by starting OpenJUMP and checking if there new selection "Database Query" in the Tools menu list.
Creating a Spatialite database for testing
An easy way to create a Spatialite database for testing is to import some shapefiles with Spatialite-GUI utility. It can be downloaded from http://www.gaia-gis.it/spatialite/binaries.html Running Spatialite-GUI is simple, it is only one executable file. The rest of the process is described below with a bunch of screen captures. Shapefiles in the example come from http://download.geofabrik.de/osm/europe/germany/ I used zip file "berlin.shp.zip" but that is not important, any shapefiles will do.
Spatialite-GUI started.
Dialogue for creating a new Spatialite database.
Select file path and feed in the name of the new database.
"Load Shapefile" button is for loading shapefiles.
Select the shapefile.
Feed in the table name to be used in the database, projection of the data and character encoding (with Geofabrik shapefile the correct setting is probably UTF-8 but there were some import problems when using if).
Message after successful import.
View after importing a bunch of shapefiles. Layer "roads" widened to show the fields in the table. "Geometry" holds the geometries. Attribute fields are "osm_id", "name", "ref", "type", "oneway" and "maxspeed".
Using DB Query Plugin with Spatialite database
Everything is now ready for the real test. DB Query Plugin is installed and there is a test database named db_query_test.sqlite located in the folder D:\TEMP\db_tutorial\
Entering the query. Windows accepts both forward and back slashes in the database path. Remember to insert some SELECT into query, otherwise you will not get anything back.
The result of the query.
A bit more complex database query.
The result of the query above.
It is a real database with more than one layer.
Troubleshooting
Some typical errors are error when writing the location of the Spatialite database or in the SQL queries. DB Query Plugin gives often rather understandable results.
There are many ways to store spatial data in SQLite databases. DB Query Plugin understands many alternative methods but there may be problems in interoperability. In such case try to open the database with other tools and possibly write is again by extracting layers first into shapefiles and importing them back to a new database. Spatialite-GUI and DB Query Plugin seem to play together.
Hint: Limiting the size of the query result
Spatialite databases can be big. They work fine at least up till many gigabytes. Selecting the whole table with
SELECT * from TABLE
may give more data than OpenJUMP can handle in the memory. Use LIMIT for limiting the number of rows returned by the query. For example, to select at maximum 1000 rows use
SELECT * from TABLE LIMIT 10000