This page last changed on May 12, 2007 by cholmes.

Hsql Binding for Spatial DB in a Box

This page describes the Spatial DB in a Box bindings for the Hsql database. Hsql is a popular java-based database (previously called Hypersonic SQL) often used as an imbedded database in Java applications.


a) The source code is attached to this page. (
b) Make sure the JTS.jar and StaticGeometry.class file are in the CLASSPATH for the server.
d) generate the sql file (
e) execute the sql file

See the hsql documentation for how to setup a server. The unix quickstart (in the hsql manaul) is a simple place to start.

Start your server with a classpath that includes JTS and StaticGeometry:

java -classpath hsqldb.jar;jts-1.5.jar;spatialdb.jar org.hsqldb.Server

Connect to it normally:

java -jar lib\hsqldb.jar localhost-sa

Add the spatial functions aliases by executing the generated .sql file

\i hgis.sql

Example queries:

sql> call totext(intersection(
         GeomFromWKT('LINESTRING(0.1 0.1, 1 1)'),
         GeomFromWKT('LINESTRING(0 0, 0.5 0.5)')));

LINESTRING (0.1 0.1, 0.5 0.5)

sql> call GeometryType(GeomFromWKT('POINT(0 0)'));


sql> call relate(GeomFromWKT('POINT(0 0)'),GeomFromWKT('LINESTRING(0 0, 10 10)'));


Remember to use the "GeomFromWKT()" and "ToText()" functions!

Notes/To Do

1. The serialized form of the Geometries is the normal JTS serialized form (which may not be efficient space-wise or parse-time-wise). We should change this so its WKB based.

2. Hsql developers are currently working on GiST indexes, which means an Rtree spatial index can be implemented. I do not know the time frame for this.

3. See the Hsql Home Page for more information on what the database supports and some of its design tradeoffs.

4. Hsql is very easy to bind to - you can just call the functions directly from the database w/o doing any work. The generated .sql file is just convenience functions so you don't have to type "org.openplans.spatialDBbox.StaticGeometry." all the time.

CREATE ALIAS interiorRingN FOR "org.openplans.spatialDBbox.StaticGeometry.getInteriorRingN";

5. use type "OBJECT" to store your geometries in a table. (application/x-zip-compressed)

Given the definition of your spatial data types, how will the GiST indexes developed by the HSQLDB project relate to these types?

Posted by at Sep 21, 2005 08:52
Document generated by Confluence on May 14, 2014 23:00