Difference between revisions of "OpenJUMP with SpatialLite"
(Created page with '== Introduction == There are two plugins that allow to use SpatialLite with OpenJUMP: * the [http://sourceforge.net/projects/jumpdbqplugin/files/ JUMP DB Query Plugin] * a separa…') |
|||
| Line 35: | Line 35: | ||
There reads originally | There reads originally | ||
| − | + | <pre> | |
#"spatialite1" database | #"spatialite1" database | ||
jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.postgres.JumpSpatialiteDbQuery | jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.postgres.JumpSpatialiteDbQuery | ||
| Line 42: | Line 42: | ||
jump.dbquery.username.spatialite1= | jump.dbquery.username.spatialite1= | ||
#jump.dbquery.password.spatialite1= | #jump.dbquery.password.spatialite1= | ||
| − | + | </pre> | |
The classpath is wrong and is must be corrected like this: | The classpath is wrong and is must be corrected like this: | ||
| + | <pre> | ||
jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialite | jump.dbquery.queryclass.spatialite1=org.freevoice.jumpdbqueryextension.spatialite.JumpSpatialite | ||
| + | </pre> | ||
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. | 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. | ||
| Line 54: | Line 56: | ||
I used zip file "berlin.shp.zip" but that is not important, any shapefiles will do. | I used zip file "berlin.shp.zip" but that is not important, any shapefiles will do. | ||
| − | + | [[File:Dbqyery_image001.png]] | |
Spatialite-GUI started. | Spatialite-GUI started. | ||
| − | + | [[File:Dbqyery_image002.png]] | |
| − | |||
Dialogue for creating a new Spatialite database. | Dialogue for creating a new Spatialite database. | ||
| − | + | [[File:Dbqyery_image003.png]] | |
| − | |||
Select file path and feed in the name of the new database. | Select file path and feed in the name of the new database. | ||
| − | + | [[File:Dbqyery_image004.png]] | |
| − | |||
| − | |||
"Load Shapefile" button is for loading shapefiles. | "Load Shapefile" button is for loading shapefiles. | ||
| − | + | [[File:Dbqyery_image005.png]] | |
Select the shapefile. | Select the shapefile. | ||
| − | + | [[File: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 if). | 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). | ||
| − | + | [[File:Dbqyery_image007.png]] | |
Message after successful import. | Message after successful import. | ||
| − | + | [[File: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". | 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 === | === Using DB Query Plugin with Spatialite database === | ||
| Line 99: | Line 92: | ||
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\ | 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\ | ||
| + | [[File:Dbqyery_image009.png]] | ||
| − | + | [[File:Dbqyery_image010.png]] | |
| − | + | [[File: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. | 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. | ||
| − | + | [[File:Dbqyery_image012.png]] | |
The result of the query. | The result of the query. | ||
| − | + | [[File:Dbqyery_image013.png]] | |
| − | |||
A bit more complex database query. | A bit more complex database query. | ||
| − | + | [[File:Dbqyery_image014.png]] | |
| − | |||
The result of the query above. | The result of the query above. | ||
| − | + | [[File:Dbqyery_image015.png]] | |
| − | |||
It is a real database with more than one layer. | It is a real database with more than one layer. | ||
| − | + | [[File:Dbqyery_image016.png]] | |
| − | |||
=== Troubleshooting === | === 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. | 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. | ||
| + | |||
| + | [[File:Dbqyery_image017.png]] | ||
| + | |||
| + | [[File: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. | 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 === | === Hint: Limiting the size of the query result === | ||
| Line 144: | Line 132: | ||
Spatialite databases can be big. They work fine at least up till many gigabytes. Selecting the whole table with | Spatialite databases can be big. They work fine at least up till many gigabytes. Selecting the whole table with | ||
| − | SELECT * from TABLE | + | 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 | 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 | + | SELECT * from TABLE LIMIT 10000 |
Revision as of 00:32, 8 February 2010
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.
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