This page last changed on Jul 31, 2006 by cholmes.

Spatial DB in Box


I've been thinking about a generic method for adding spatial capabilities to non-spatial databases. "Spatial DB in Box" is the result of this research.

The basic idea is two fold:
1. use JTS (the Java Topology Suite) to carry out ALL the spatial processing
2. use autogenerated code to bind JTS to the Database

Main advanages

1. Any improvements to JTS algorithms are instantly realized in all Databases
2. Any improvements that are made in any DB should be realized in all DBs
3. Very little code to maintain for each DB (just the DB specific autocode generator)
4. Applications that work on one DB should work the same on all
5. Common community for discussion

The biggest advantage is that there is only one code base that all the databases derive from. When JTS maintainer Martin Davis adds or improves functionality, these changes automatically get wrapped into all the databases. If someone adds a "complex" spatial algorithm to one of the databases, that will also be available to all the DBs.

Martin has added significant functionality to JTS for versions 1.6 (like optimized rectangle queries, buffer algorithm improvements, and topology preserving simplification) and 1.7 (snap rounding - making the overlay operations mostly robust). Other projects (like geotools and the Java Conflation Suite (JCS)) have added other JTS-based functionality.

In fact, this method is generic enough that you can add non-spatial Java smarts to a database - like David Zwiers' fancy Geotools GML parser.

Non-Java Databases

Since JTS is a Java application, you might think that it would not be accessible from non-java databases. I've solved this problem with GJC (the java plugin to the GCC compiler) - the results are very good. For more information see the Compiling JTS page and the Postgresql Bindings page.

Basic Architecture

Architecture (Java DB)
                            JAVA Spatial Algorthims (JTS + others)
                                      StaticGeometry (java)
                   Auto-Generated DB Bindings and SQL CREATE FUNCTION Bindings
                                            JAVA DB
Architecture (Non-Java DB)
                            JAVA Spatial Algorthims (JTS + others, compiled)
                                 StaticGeometry (compiled java)
                                  Static Geometry Wrapper (C++)
                   Auto-Generated DB Bindings and SQL CREATE FUNCTION Bindings
                                            JAVA DB

The architecture is similiar for both cases - at the top is the actual smarts: JTS and any other java libraries. Only the StaticGeometry class interacts with this level.

StaticGeometry is a very simple class that has a bunch of static methods that call JTS. The original StaticGeometry class was auto-generated by walking around the JTS com.vividsolutions.jts.geom.* class hierarchy. To add more functionality to the databases, simply provide a function here. See Add A Function
for an example.

For a non-Java database, an autogenerated C++ wrapper class is formed that will handle the interaction with the compiled Java code. This is formed by looking at what's inside the StaticGeometry class.

Finally, two more files are automatically created that are specialized for each supported database. The first one is code (java or otherwise) that the database will actually call to execute a function - it's simple code that passes the evalutation off to the StaticGeometry class. The second file is a set of SQL CREATE FUNCTION statements that tell the DB what functions it needs to call. These are formed by looking at what's inside the StaticGeometry class.

If you look at the Derby/Cloudscape implementation, you'll see:
1. a JTS jar (the spatial smarts)
2. StaticGeometry - a static wrapper class that calls JTS
3. DerbyCodeGenerator - a java class that reads in StaticGeometry and produces:
a) — the database calls functions in there
b) DerbyJTSWrapper.sql — SQL create function statements that call functions in the DerbyJTSWrapper

If you look at the Postgresql bindings you'll see:
1. a JTS jar (the spatial smarts - exactly the same as above)
2. StaticGeometry - a static wrapper class that calls JTS (exactly the same as above)
3. – reads in StaticGeometry, produces a C++ wrapper for it
4. – reads in StaticGeometry, produces postgresql specific glue for calling the C++ wrapper
5. – reads in StaticGeometry, produces SQL CREATE FUNCTION commands that call the "C" postgresql specific glue code

More information

Compiling JTS
Derby (Cloudscape) bindings
Postgresql Bindings
Hsql Bindings
Add A Function

JTS Home Page


The hsqldb project is expecting to implement GiST indexing soon (which means that a spatial index will be available for hsqldb databases).


I cannot find the download for all the classes found on this article. Can you provide me a working link ?

Have you resolved all your questions with Apache Derby? Does it actually work? If it does initially, I can test it.



Posted by alexandros efentakis at Oct 09, 2006 03:28

Where can i find the files which are mentioned in this article, especially the hsql bindings?

Posted by starkilla at May 09, 2007 20:08

They are attachments to the pages, but unfortunately somewhat obscured right now, you have to hit 'page operations' on the left, and then 'attachments'. The direct link to the hsql ones is:

Posted by cholmes at May 09, 2007 22:16
Document generated by Confluence on May 14, 2014 23:00