spatialite-ubuntu.md

Java and Spatialite on Debian/Ubuntu

All instructions/links/version are valid as of Oct 25, 2014

Here is how I got up-and running with the Xerial JDBC driver and libspatialite on a Ubuntu 12 x86_64 box.

Getting the Native Libraries� (sqlite3/libspatialite)

Install libspatialite dependencies:

As root

1. apt-get install linux-headers-$(uname -r) build-essential
2. apt-get install openjdk-7-jdk
3. apt-get install curl wget libreadline-dev zlib1g-dev libxml2-dev
4. apt-get install libexpat1 libexpat1-dev

Compiling sqlite3

1. wget http://www.sqlite.org/2014/sqlite-autoconf-3080600.tar.gz
2. tar xzvf sqlite-autoconf-3080600.tar.gz
3. cd sqlite-autoconf-3080600; ./configure --prefix=/opt
4. make install

Compiling proj

1. wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
2. tar xvf proj-4.8.0.tar.gz
3. cd proj-4.8.0; ./configure --prefix=/opt
4. make install

Compiling GEOS, libspatialite dependency

wget http://download.osgeo.org/geos/geos-3.4.2.tar.bz2

For make check to pass in libspatialite, we need to be mindful of how geos is compiled

tar xvf geos-3.4.2.tar.bz2 
cd geos-3.4.2
export "CXXFLAGS=-DHAVE_STD_ISNAN=1 -DHAVE_LONG_LONG_INT_64=1 -DGEOS_ENABLE_INLINE=ON -DGEOS_ENABLE_TESTS=ON"
LD_LIBRARY_PATH=/opt/lib ./configure --prefix=/opt; make install
make check install

Compiling the latest version of libspatialite

see https://gist.github.com/happysundar/8741337 for building from the latest sources from version control. We will be building from (pre)-release archive.

Getting the source

wget http://www.gaia-gis.it/gaia-sins/libspatialite-sources/libspatialite-4.2.1-rc0.tar.gz

Compiling and installing libspatialite from source

Once we have the source, compiling and installing it is as simple as doing:

1. ldconfig # update library caches
2. tar xvf libspatialite-4.2.1-rc0.tar.gz; cd libspatialite-4.2.1-rc0
3. LD_LIBRARY_PATH=/opt/lib CFLAGS="-L/opt/lib -I/opt/include" ./configure --prefix=/opt --enable-freexl=no --enable-geocallbacks
4. make check install

Expected output:

============================================================================
Testsuite summary for libspatialite 4.2.1-rc0
============================================================================
# TOTAL: 81
# PASS:  81
# SKIP:  0
# XFAIL: 0
# FAIL:  0
# XPASS: 0
# ERROR: 0
============================================================================

Confirm spatialite loads

spat@ubuntu:~/libspatialite-4.2.1-rc0$ /opt/bin/sqlite3 
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select load_extension("/opt/lib/mod_spatialite");

sqlite> select sqlite_version(), spatialite_version();
3.8.6|4.2.1-rc0

The Java side of the story:

1. Install Java (JRE and JDK) : yes | sudo yum install java-1.7.0-openjdk java-1.7.0-openjdk-devel

2. In the <repositories> section of your pom.xml, add the following “snapshotâ€� repository:

    <repository>
        <id>oss-sonatype</id>
        <name>oss-sonatype</name>
        <url>https://oss.sonatype.org/content/repositories/snapshots/</url>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
   

3. In the <dependencies> section of your pom.xml,

    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.8.0-20130827.035027-1</version>
    </dependency>
   

4. Following the sample code given here, you can now connect to the spatialite database :

    import org.sqlite.SQLiteConfig;
    import java.io.IOException;
    import java.sql.*;
   
    public class App
    {
    	public static void main (String[] args) throws IOException, SQLException, ClassNotFoundException
    	{
    		// load the sqlite-JDBC driver using the current class loader
           	Class.forName( "org.sqlite.JDBC" );
    	    // enabling dynamic extension loading
    		// absolutely required by SpatiaLite
        	SQLiteConfig config = new SQLiteConfig();
        	config.enableLoadExtension( true );
    		config.setReadOnly( true );
            // create a database connection
    	    try (Connection conn = DriverManager.getConnection( "jdbc:sqlite:/melodis/geodata/us_geolocation.db", 	config.toProperties() );)
        	{
    	    	try (  Statement statement = conn.createStatement() )
        		{
            		statement.setQueryTimeout( 30 ); // set timeout to 30 sec.
                    // loading SpatiaLite
    	            statement.execute( "SELECT load_extension('/usr/local/lib/mod_spatialite')" );
    		        String sql = "SELECT sqlite_version(), spatialite_version()";
                	ResultSet rs = statement.executeQuery( sql );
                	while (rs.next())
    	            {
        	            // read the result set
    	    	        String msg = "SQLite version: ";
        	    	    msg += rs.getString( 1 );
            	    	System.out.println( msg );
               			msg = "SpatiaLite version: ";
                		msg += rs.getString( 2 );
                		System.out.println( msg );
            		}
        		}
    		}
    	}
    }