OpenJUMP with SpatialLite

Introduction

There are two plugins that allow to use SpatiaLite with OpenJUMP:

• the JUMP DB Query Plugin
• a separate Spatialite Reader Plugin available in OJ plugins files section.

Note: Does not work properly with OpenJUMP 1.4. Use version 1.3 instead.

Using SpatiaLite 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:

http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.6.20.1/sqlite-jdbc-3.6.20.1.jar

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 it).

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. Maximum speed of 0 km/h may feel odd. We will return to this later.

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 error messages.

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 1000

Using SpatiaLite with Spatialite Reader Plugin

Installing and using JUMP Spatialite Reader Plugin with Spatialite databases

Tested with:

• Windows XP
• Java 1.6.0_ 06
• OpenJUMP 1.3.1
• Spatialite Reader Plugin version 1.1

Getting the plugin and SQLite driver

Go to https://sourceforge.net/projects/jump-pilot/files/OpenJUMP_plugins/Database%20Plugins/SpatialLite_reader_plugin/ and download the plugin.

The readme.txt file is listing two URLs for downloading Spatialite precompiled libraries and SQLite3 JDBC driver. However, the zip file contained all the drivers needed for Windows. The plugin is using platform dependent components and for Linux and OS X users the plugin won't work as it is.

Installation

Windows users can just unzip the spatialliteplugin1.1.zip into bin\ext folder of OpenJUMP and the plugin is ready for use. After successful installation there is a new menu item: Layer - Import Spatialite Layer.

Using Spatialite Reader Plugin

The next examples are using the same Spatialite database as the previous tests with DB Query Plugin. The database is just renamed to "spatialitereader_test.sqlite"

Start via menu with Select Layer - Import Spatialite Layer

Press the only active button "+" and search the Spatialite database file.

The whole database structure comes visible.

By double clicking the layer title "roads" the SELECT clause is created automatically. You can also write is directly to the text box. The second button "SQL >" makes the query and lists results in the form without transferring them into OpenJUMP map yet.

After pressing the third button the query results, including geometries, are sent to OpenJUMP.

With the Spatialite reader plugin you can reach all the Spatialite functions through a menu. Menu items have tooltip help entries and double click tranfers a prototype text into query window. This query will return the road geometries in WGS84 UTM zone 33 projection (EPSG:32633).

Berlin roads reprojected from the native EPSG:4326 into EPSG:32633 by Spatialite. Note: This is possible only if the "spatial_ref_sys" table contains definitions for both the input and output projections. All the databases created with Spatialite-GUI tool have definitions for 3519 different projections.

This is not a Spatialite tutorial, but these two screenshots should give some idea about what kind of operations can be done inside Spatialite and visualised with OpenJUMP

Updating attribute values with Spatialite Reader Plugin

It is not possible to update Spatialite features which are read into OpenJUMP. However, the SQL window of the plugin connects directly to SpatiaLite database and thus everything that can be done with Spatialite SQL is doable through the plugin as well. For example, updating the "roads" layer by changing all maxpeed=0 values into null (empty) values goes easily. Error message "No ResultSet was produced" indicates only that nothing was selected from the database. A new query SELECT * FROM roads will show that the values were really updated.