How to get Java and Spatialite working on a base Centos 6 box

java_and_spatialite.md

Up and Running with Java and libspatialite : a from-the-scratch how-to

All instructions/links/version are valid as of Jan 29, 2014

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

Getting the “Native Libraries” (sqlite3/libspatialite)

Installing required YUM Repositories

1. Add the EPEL repo:

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

2. Add the ELGIS repo:

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

Install libspatialite dependencies:

1. yes | sudo yum install -y geos geos-devel
2. yes | sudo yum install -y proj proj-devel
3. yes | sudo yum install -y expat expat-devel

Compiling sqlite3

1. cd /tmp; wget http://www.sqlite.org/2013/sqlite-autoconf-3080200.tar.gz

2. tar xzvf sqlite-autoconf-3080200.tar.gz

3. cd sqlite-autoconf-3080200; ./configure --enable-dynamic-extensions=yes

4. sudo make install

Compiling the latest version of libspatialite

The developer of libspatialite uses a source control system called fossil, so we need to install the client for this source control system, before we can fetch the source.

Installing fossil

Because the default binaries of fossil don’t come with SSL compiled in, we have to compile this from source:

1. cd /tmp; wget http://www.fossil-scm.org/download/fossil-src-20140127173344.tar.gz
2. tar xzvf fossil-src-20140127173344.tar.gz
3. cd fossil-src-20140127173344
4. yes | sudo yum install openssl-devel
5. ./configure; make; sudo make install

Fetching the latest libspatialite sources

1. cd /tmp; fossil clone https://www.gaia-gis.it/fossil/libspatialite libspatialite.fossil
2. mkdir libspatialite; cd libspatialite; fossil open ../libspatialite.fossil

Compiling and installing libspatialite from source

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

1. cd libspatialite; ./configure --enable-freexl=no --enable-iconv=no
2. sudo make install

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