OpenJUMP with SpatialLite

From OpenJUMP Wiki
Jump to navigation Jump to search


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

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:

Getting the plugin and SQLite driver

Go to and download the plugin.

The readme file "README_jumpdbquery.txt" included in the downloaded zip file gives advice for downloading SQLite JDBC driver from

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 and that worked with DB Query plugin as well. The one I used is this:


Installation is done simply by unzipping the contents of 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 "" 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

The classpath is wrong and is must be corrected like this:


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 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 I used zip file "" but that is not important, any shapefiles will do.

Dbqyery image001.png

Spatialite-GUI started.

Dbqyery image002.png

Dialogue for creating a new Spatialite database.

Dbqyery image003.png

Select file path and feed in the name of the new database.

Dbqyery image004.png

"Load Shapefile" button is for loading shapefiles.

Dbqyery image005.png

Select the shapefile.

Dbqyery image006.png

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

Dbqyery image007.png

Message after successful import.

Dbqyery image008.png

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\

Dbqyery image009.png

Dbqyery image010.png

Dbqyery image011.png

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.

Dbqyery image012.png

The result of the query.

Dbqyery image013.png

A bit more complex database query.

Dbqyery image014.png

The result of the query above. Maximum speed of 0 km/h may feel odd. We will return to this later.

Dbqyery image015.png

It is a real database with more than one layer.

Dbqyery image016.png


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.

Dbqyery image017.png

Dbqyery image018.png

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


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


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


Windows users can just unzip the 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"

Spatialitereader 01.png

Start via menu with Select Layer - Import Spatialite Layer

Spatialitereader 02.png

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

Spatialitereader 03.png

The whole database structure comes visible.

Spatialitereader 04.png

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.

Spatialitereader 05.png

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

Spatialitereader 06.png

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

Spatialitereader 07.png

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.

Spatialitereader 08.png

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

Spatialitereader 09.png

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.

Spatialitereader 10.png