java_and_spatialite.md

Java and Spatialite on Centos 6.5

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

Here is how I got up-and running with the Xerial JDBC driver and libspatialite on a Centos 6.5 x86_64 box. This was tested on a instance of the Hortonworks Sandbox

Getting the “Native Libraries” (sqlite3/libspatialite)

Installing required YUM Repositories

1. Add the EPEL repo:

   rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

2. Add the ELGIS repo:

   rpm -Uvh http://elgis.argeo.org/repos/6/elgis-release-6-6_0.noarch.rpm

Install libspatialite dependencies:

As root

1. echo "/usr/local/lib" >> /etc/ld.so.conf.d/locallib.conf
2. yum groupinstall "Development Tools"
3. yum install -y wget readline-devel zlib zlib-devel libxml2 libxml2-devel
4. yum install -y expat expat-devel

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. sudo make install

Compiling PROJ, libspatiliate dependency

wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
tar xvf proj-4.8.0.tar.gz 
cd proj-4.8.0/
LD_LIBRARY_PATH=/opt/lib ./configure --prefix=/opt
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"
./configure --prefix=/opt
make check
make 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. ./configure --enable-freexl=no --enable-geocallbacks
4. make check

Expected output:

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

Test the Install

$ /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 );
            		}
        		}
    		}
    	}
    }