SourceForge

Download

News

Quick Start

Sample code

UI

Derby Quick Start

Hatbox has two distinct parts: the core library and a Geotools DataStore. The core library can be used entirely independently of the Geotools DataStore. This quick start will use only the core library to get data into your new Hatbox spatialized table and to issue spatial queries on it. It will focus on using Hatbox with JavaDB (Sun's packaging of Apache Derby) simply because it comes as part of a Sun JDK 6 install.

Installation

In a Windows JDK 6 installation the default location of JavaDB is Program Files\Sun\JavaDB, and you will need to add the bin directory to your path. This quick start will run a Derby network server so that you can use your favourite SQL client (eg SQuirreL) to browse the database objects we create.

Perhaps the most important installation issue to deal with is placing the hatbox.jar on the classpath for your derby server. For the purposes of this quick start simply set CLASSPATH to hatbox.jar and jts.jar eg
CLASSPATH=C:\hatbox\lib\hatbox.jar;C:\hatbox\lib\jts-1.9.jar
Run startNetworkServer from within a suitable data directory and your derby network server is now able to access the hatbox classes it needs. Create a connection to the server with your favourite SQL client using a url such as jdbc:derby://localhost/test;create=true and your test database is ready to get started.

Spatialize Database

The first thing to do is 'spatialize' the database. This amounts to creating a set of common procedures and functions that table triggers will later use. These objects will be created in the HATBOX schema. The hatbox.jar has a command line tool set as its default class. Run
java -jar hatbox.jar help
to list the command help topics. To run any other command you will also need the jdbc driver and JTS jar on your classpath. Spatialize the database using
java -jar hatbox.jar spatializedb -url jdbc:derby://localhost/test
You should now be able to see 5 functions and 7 procedures in the HATBOX schema.

Create a table

Now create a spatial table in your test database. Because Hatbox is a user-space tool, the table you create is a perfectly ordinary Derby/JavaDB table. There are a couple of requirements of it:

• It must have a primary key
• The primary key must be SMALLINT, INTEGER or BIGINT
• It must have a column to store the geometry that is CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA or BLOB

create table T1 (
ID integer not null generated always as identity,
NAME varchar (20),
GEOM varchar (200) for bit data,
primary key (ID))

java -jar hatbox.jar list -url jdbc:derby://localhost/test should generate the following listing:

Listing tables for schema: APP
T1 : Candidate

Spatialize the table

You should now be able to see two tables in the APP schema: T1 and T1_HATBOX. The T1_HATBOX table contains one row. This row is the index meta-data. You should never change data in the _HATBOX tables directly

java -jar hatbox.jar list -url jdbc:derby://localhost/test should now generate the following listing:

Listing tables for schema: APP
T1 : Spatialized NO_INDEX

java -jar hatbox.jar meta -url jdbc:derby://localhost/test -t T1 produces a more detailed display of the index meta data:

Meta Data for table: APP.T1
Index Status     : NO_INDEX
PK Column        : ID
Geometry Column  : GEOM
Geometry Type    : GEOMETRY
SRID             : 4326
Expose PK        : false
Entries per node : 98

Insert Data

The following program inserts a set of point features in the new table.

package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.impl.CoordinateArraySequenceFactory;
import com.vividsolutions.jts.io.WKBWriter;

public class TestInsert {
    public static void main(String[] args) throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver"); 
        Connection conn = DriverManager.getConnection("jdbc:derby://localhost/test");
        GeometryFactory geomFactory = new GeometryFactory();
        CoordinateArraySequenceFactory factory = CoordinateArraySequenceFactory.instance();
        Coordinate[] coord = new Coordinate[1];
        WKBWriter writer = new WKBWriter();
        PreparedStatement ps = conn.prepareStatement("insert into T1 (NAME, GEOM) values (?,?)");
        ps.setString(1, "Test Point");
        coord[0] = new Coordinate(145.0, -37.0);
        ps.setBytes(2, writer.write(new Point(factory.create(coord), geomFactory)));
        ps.execute();
        coord[0] = new Coordinate(145.1, -37.1);
        ps.setBytes(2, writer.write(new Point(factory.create(coord), geomFactory)));
        ps.execute();
        coord[0] = new Coordinate(145.2, -37.2);
        ps.setBytes(2, writer.write(new Point(factory.create(coord), geomFactory)));
        ps.execute();
        coord[0] = new Coordinate(145.3, -37.3);
        ps.setBytes(2, writer.write(new Point(factory.create(coord), geomFactory)));
        ps.execute();
        ps.close();
        conn.close();
    }
}

You should now be able to see 4 rows in table T1. There should be no change to the meta data row in T1_HATBOX.

Build Index

java -jar hatbox.jar list -url jdbc:derby://localhost/test should now generate the following listing:

Listing tables for schema: APP
T1 : Spatialized INDEXED

Table T1 is identified as an INDEXED spatialized table. You should now be able to see 5 triggers on T1 that keep the index in sync with the data table. Also the index root node (table T1_HATBOX, ID = 2) should have been inserted and show ENTRIES_COUNT of 4. When more than 98 entries are inserted the root node will split and a new node will become the root of a tree of nodes.

Query

The following program selects point features that fall within a query envelope.

package test;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import com.vividsolutions.jts.io.WKBReader;

public class TestQuery {
    public static void main(String[] args) throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver"); 
        Connection conn = DriverManager.getConnection("jdbc:derby://localhost/test");
        Statement stmt = conn.createStatement();
        WKBReader reader = new WKBReader();
        ResultSet rs = stmt.executeQuery("select ID, GEOM from T1 t inner join " +
                "table(HATBOX.MBR_INTERSECTS_ENV('APP','T1',145.05,145.25,-37.25,-37.05)) i " +
                "on t.ID = i.HATBOX_JOIN_ID");
        while (rs.next()) {
            System.out.println(rs.getInt(1) + ":" + reader.read(rs.getBytes(2)));
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

The output should display two of the four points that fall within the query envelope. This is the simplest type of query possible. Hatbox also supports queries with secondary filtering on all the common predicates (intersects, within etc). Refer to the reference document for details.