Skip to content

Spatialite and Java

Jump to bottom Edit New page
Andrea Antonello edited this page Dec 7, 2015 · 8 revisions

Get the libraries

To work with spatialite in java you will need to do a couple of steps before coding away.

  1. Download the native libraries. You will find everything necessary on the main homepage of the spatialite libraries.

    For linux you will have to compile the library yourself, if they are not available in the repositories of your distribution. But that is a quite simple task on linux, so get you source code tarball and compile away!

    In the case of windows things are way more tricky when it comes to compile with all the necessary support. Thanks to the father of Spatialite, the binaries are available for download here.

  2. Put the native libraries in the library path of your operating system.

    On my linux system I compiled the libraries in the most standard way and simply added the resulting paths to the library and execution paths:

    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib/
export PATH=$PATH:/usr/local/bin/

    On windows I had some problems since there is no such thing as an LD_LIBRARY_PATH. The path in which the libraries are in need to be added directly to the PATH variable. Since in my windows installation I had a pile of other spatial applications and libraries (for example Osgeo4W), basically I experienced continuos crashes of even the simplest java programs I was testing. Finally I realized that putting the libraries path as the first entry of the PATH variable solved the problems that seemed to be due to some conflicts.

  3. Get the java jdbc libraries.

    After trying different options I decided that the Xerial drivers are the best way to go. Their biggest problem is that the error messages they produce 85% of the times are not directly bound to the problem. So one needs to use a lot of imagination to really find issues.

    If you are working with maven, this is the simplest way to get the drivers into your project. Add this to you pom.xml:

    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.8.10.1</version>
</dependency>

Now you are ready to start to develop!!

Popup your favourite IDE and reference the libraries in it.

Code Snippets

The code snippets below will make use of a couple of simple helper classes:

public class SpatialiteGeometryColumns {
    // COLUMN NAMES
    public static final String TABLENAME = "geometry_columns";
    public static final String F_TABLE_NAME = "f_table_name";
    public static final String F_GEOMETRY_COLUMN = "f_geometry_column";
    public static final String GEOMETRY_TYPE = "geometry_type";
    public static final String COORD_DIMENSION = "coord_dimension";
    public static final String SRID = "srid";
    public static final String SPATIAL_INDEX_ENABLED = "spatial_index_enabled";

    // VARIABLES
    public String f_table_name;
    public String f_geometry_column;
    public int geometry_type;
    public int coord_dimension;
    public int srid;
    public int spatial_index_enabled;
}

It is also assumed that you will make use of the Java Topology Suite (JTS) library to handle geometries. If you are not, then most probably you should.

Load the Xerial jdbc driver

Before starting to query the database, the right jdbc drivers need to be loaded.

This is done by including in the first part of your class a method that loads org.sqlite.JDBC:

static {
    try {
        Class.forName("org.sqlite.JDBC");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

Open an existing database

// enabling dynamic extension loading
// absolutely required by SpatiaLite
SQLiteConfig config = new SQLiteConfig();
config.enableLoadExtension(true);
// create a database connection
conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath, config.toProperties());
try (Statement stmt = conn.createStatement()) {
    // set timeout to 30 sec.
    stmt.setQueryTimeout(30);
    // load SpatiaLite
    stmt.execute("SELECT load_extension('mod_spatialite')");
}

Create a new database

To create a new database, you first need to run the same code you use to open an existing database.

This will create the sqlite database used as basis for the spatial db. The you need to initialize the spatial metadata like this:

String options = "";

conn.setAutoCommit(false);
String sql = "SELECT InitSpatialMetadata(" + options + ")";
try (Statement stmt = conn.createStatement()) {
    stmt.execute(sql);
}
conn.setAutoCommit(true);

I am leaving the options string on purpose, since there are a couple of interesting possibilities here, have a look at the functions table.

Create a new spatial table

To create a new spatial table, first you need to create a normal sqlite table, omitting the spatial column.

After that, you need to add the geometry column and most probably you also want to create a spatial index. The following method here gives you a possible impllementation:

/**
 * Adds a geometry column to a table.
 *
 * @param tableName the table name.
 * @param geomColName the geometry column name.
 * @param geomType the geometry type (ex. LINESTRING);
 * @param epsg the optional epsg code (default is 4326);
 * @throws SQLException
 */
public void addGeometryXYColumnAndIndex( String tableName, String geomColName, String geomType, String epsg )
        throws SQLException {
    String epsgStr = "4326";
    if (epsg != null) {
        epsgStr = epsg;
    }
    String geomTypeStr = "LINESTRING";
    if (geomType != null) {
        geomTypeStr = geomType;
    }

    if (geomColName == null) {
        geomColName = defaultGeomFieldName;
    }

    try (Statement stmt = conn.createStatement()) {
        String sql = "SELECT AddGeometryColumn('" + tableName + "','" + geomColName + "', " + epsgStr + ", '" + geomTypeStr
                + "', 'XY')";
        stmt.execute(sql);

        sql = "SELECT CreateSpatialIndex('" + tableName + "', '" + geomColName + "');";
        stmt.execute(sql);
    }
}

Get information about geometry column

The table geometry_columns contains a load of information about the geometry column name, the srid, the availability of the spatial index. It might be useful to collect these data before working with inserts and updates. One possible way is the following:

/**
 * Get the geometry column definition for a given table.
 *
 * @param tableName the table to check.
 * @return the {@link SpatialiteGeometryColumns column info}.
 * @throws Exception
 */
public SpatialiteGeometryColumns getGeometryColumnsForTable( String tableName ) throws SQLException {
    String sql = "select " + SpatialiteGeometryColumns.F_TABLE_NAME + ", " //
            + SpatialiteGeometryColumns.F_GEOMETRY_COLUMN + ", " //
            + SpatialiteGeometryColumns.GEOMETRY_TYPE + "," //
            + SpatialiteGeometryColumns.COORD_DIMENSION + ", " //
            + SpatialiteGeometryColumns.SRID + ", " //
            + SpatialiteGeometryColumns.SPATIAL_INDEX_ENABLED + " from " //
            + SpatialiteGeometryColumns.TABLENAME + " where " + SpatialiteGeometryColumns.F_TABLE_NAME + "='" + tableName
            + "'";
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            SpatialiteGeometryColumns gc = new SpatialiteGeometryColumns();
            gc.f_table_name = rs.getString(1);
            gc.f_geometry_column = rs.getString(2);
            gc.geometry_type = rs.getInt(3);
            gc.coord_dimension = rs.getInt(4);
            gc.srid = rs.getInt(5);
            gc.spatial_index_enabled = rs.getInt(6);
            return gc;
        }
        return null;
    }
}

Insert a geometry

The following method describes a possible way to insert a geometry into a table. In this case the choice is to use the well known text representation of the geometry, properly converted by the sql function GeomFromText. It is surely more compact to use the well known binary version (WKB), if your system supports it. Check out the functions page for the appropriate conversion functions.

/**
 * Insert a geometry into a table.
 *
 * @param tableName the table to use.
 * @param geometry the geometry to insert.
 * @param epsg the optional epsg.
 * @throws SQLException
 */
public void insertGeometry( String tableName, Geometry geometry, String epsg ) throws SQLException {
    String epsgStr = "4326";
    if (epsg == null) {
        epsgStr = epsg;
    }

    SpatialiteGeometryColumns gc = getGeometryColumnsForTable(tableName);
    String sql = "INSERT INTO " + tableName + " (" + gc.f_geometry_column + ") VALUES (GeomFromText(?, " + epsgStr + "))";
    try (PreparedStatement pStmt = conn.prepareStatement(sql)) {
        pStmt.setString(1, geometry.toText());
        pStmt.executeUpdate();
    }
}

How to properly query spatially considering the spatial index

A simple spatial query will not consider the spatial index of spatialite properly. This usually leads to extreme performance loss and many emails of people complaining in the spatialite mailinglist.

The following method should represent the quickest possible way to query the spatial index:

/**
 * Get the where cause of a Spatialindex based BBOX query.
 *
 * @param tableName the name of the table.
 * @param x1 west bound.
 * @param y1 south bound.
 * @param x2 east bound.
 * @param y2 north bound.
 * @return the sql piece.
 * @throws SQLException
 */
public String getSpatialindexBBoxWherePiece( String tableName, double x1, double y1, double x2, double y2 )
        throws SQLException {
    String rowid = tableName + ".ROWID";
    SpatialiteGeometryColumns gCol = getGeometryColumnsForTable(tableName);
    String sql = "ST_Intersects(" + gCol.f_geometry_column + ", BuildMbr(" + x1 + ", " + y1 + ", " + x2 + ", " + y2
            + ")) = 1 AND " + rowid + " IN ( SELECT ROWID FROM SpatialIndex WHERE "//
            + "f_table_name = '" + tableName + "' AND " //
            + "search_frame = BuildMbr(" + x1 + ", " + y1 + ", " + x2 + ", " + y2 + "))";
    return sql;
}

Get the geometries contained in a bounding box

To query the list of geometries contained in an JTS envelope, making use of the above described method, use this:

/**
 * Get the geometries of a table inside a given envelope.
 *
 * @param tableName the table name.
 * @param envelope the envelope to check.
 * @return The list of geometries intersecting the envelope.
 * @throws SQLException
 * @throws ParseException
 */
public List<Geometry> getGeometriesIn( String tableName, Envelope envelope ) throws SQLException, ParseException {
    List<Geometry> geoms = new ArrayList<Geometry>();

    SpatialiteGeometryColumns gCol = getGeometryColumnsForTable(tableName);
    String sql = "SELECT ST_AsBinary(" + gCol.f_geometry_column + ") FROM " + tableName;

    if (envelope != null) {
        double x1 = envelope.getMinX();
        double y1 = envelope.getMinY();
        double x2 = envelope.getMaxX();
        double y2 = envelope.getMaxY();
        sql += " WHERE " + getSpatialindexBBoxWherePiece(tableName, null, x1, y1, x2, y2);
    }
    WKBReader wkbReader = new WKBReader();
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery(sql);
        while( rs.next() ) {
            byte[] geomBytes = rs.getBytes(1);
            Geometry geometry = wkbReader.read(geomBytes);
            geoms.add(geometry);
        }
        return geoms;
    }
}

Get the bounds of a table

To get the bounds of a table, there are two possible ways. If the vector_layers_statistics are available, then those can be queried quickly, if not, then the whole table needs to be scanned:

/**
 * Get the bounds of a table.
 *
 * @param tableName the table to query.
 * @return the {@link Envelope} of the table.
 * @throws SQLException
 */
public Envelope getTableBounds( String tableName ) throws SQLException {
    SpatialiteGeometryColumns gCol = getGeometryColumnsForTable(tableName);
    String geomFieldName = gCol.f_geometry_column;

    String trySql = "SELECT extent_min_x, extent_min_y, extent_max_x, extent_max_y FROM vector_layers_statistics WHERE table_name='"
            + tableName + "' AND geometry_column='" + geomFieldName + "'";
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery(trySql);
        if (rs.next()) {
            double minX = rs.getDouble(1);
            double minY = rs.getDouble(2);
            double maxX = rs.getDouble(3);
            double maxY = rs.getDouble(4);

            Envelope env = new Envelope(minX, maxX, minY, maxY);
            if (env.getWidth() != 0.0 && env.getHeight() != 0.0) {
                return env;
            }
        }
    }

    // OR DO FULL GEOMETRIES SCAN

    String sql = "SELECT Min(MbrMinX(" + geomFieldName + ")) AS min_x, Min(MbrMinY(" + geomFieldName + ")) AS min_y,"
            + "Max(MbrMaxX(" + geomFieldName + ")) AS max_x, Max(MbrMaxY(" + geomFieldName + ")) AS max_y " + "FROM "
            + tableName;

    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery(sql);
        while( rs.next() ) {
            double minX = rs.getDouble(1);
            double minY = rs.getDouble(2);
            double maxX = rs.getDouble(3);
            double maxY = rs.getDouble(4);

            Envelope env = new Envelope(minX, maxX, minY, maxY);
            return env;
        }
        return null;
    }
}

Get general database infos

To gather information about the versions of sqlite and spatialite:

/**
 * Get database infos.
 *
 * @return the string array of [sqlite_version, spatialite_version, spatialite_target_cpu]
 * @throws SQLException
 */
public String[] getDbInfo() throws SQLException {
    // checking SQLite and SpatiaLite version + target CPU
    String sql = "SELECT sqlite_version(), spatialite_version(), spatialite_target_cpu()";
    try (Statement stmt = conn.createStatement()) {
        ResultSet rs = stmt.executeQuery(sql);
        String[] info = new String[3];
        while( rs.next() ) {
            // read the result set
            info[0] = rs.getString(1);
            info[1] = rs.getString(2);
            info[2] = rs.getString(3);
        }
        return info;
    }
}
Add a custom footer
Add a custom sidebar
Clone this wiki locally