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

NOTE: The derby Spatial DB in a Box is now a supported datastore in the Geotools project.

Derby is a new Apache project contributed by IBM, open sourcing their cloudscape database. We think it would be great if someone would implement a Derby Spatial, basically add native spatial data types to the database. No, we don't know of anyone doing it, or even interested past our desire to see it done. And maybe this is just a crazy idea not worth pursuing. But it would certainly be nice to have in GeoServer. The database could be embedded, so that users would not have to worry about setting up a spatial database. They could just interact directly through GeoServer. This also leads to interesting caching potential, as existing databases could be 'spatialized' by loading them in a middle layer of Derby Spatial/GeoServer. The spatial operations would then be performed much faster. It could also form the basis for different views and joins of other backend data.

As for implementation, the first place to start would be the OGC's Simple Features for SQL specification. This is how MySQL and PostGIS have implemented. The obvious next step is to use JTS , as it does all the simple feature operations in Java. It's memory and speed would likely have to be worked on, but supposedly that's the mission for their next release. Since the hard work is already done you would probably just have to figure out how to get everything in line with the spec. Figure out the Derby internals, how to add new data types. If anyone starts work on this, or thinks it's just a stupid idea, please add a comment.

-------------------------------------------------------------
Initial version (post to Derby development email list) by David Blasby

See Also SpatialDBBox

I'm just about finished writing a Spatial Datablade for Derby. Its based on JTS (Java Topology Suite - http://jump-project.org/project.php?PID=JTS&SID=OVER ) which is a very
sophisticated (and robust) Java geomatics engine.

Once I solve the problems below, Derby should be about 95% of the way to being an Open GIS Consortium Simple Features for SQL (OGC SF SQL) compliant database. The SQL/MM spatial extension is basically exactly the same as the OGC SF SQL specification. There's more about the OGC spec here: http://www.opengeospatial.org/docs/99-049.pdf .

I'd also like to add Derby Spatial as a datasource for
Geotool/Geoserver - an OGC WFS-T (Web Feature Server - Transactional).
http://geoserver.sourceforge.net/html/index.php
http://www.geotools.org/

I was the original architect of PostGIS (Spatial Objects for PostgreSQL - http://postgis.org), and I'm experimenting with a better way to build a spatial database. The new method is based on auto-generation of most of the code - basically handing off ALL the operations to JTS instead of trying to write code specific to a database. Since its based on autogeneration of code and is applicable to a large number of different databases, I call it "Spatial DB in a Box". I will probably support several databases, but my first bindings are for Derby.

I hope to re-build the PostgreSQL (writen in "C") PostGIS based on a compiled version of JTS (using either GCJ or a commercial java compiler).

My very initial version has four components:

1. "StaticGeometry.java" - I autogenerate this by walking around the geometry classes inside JTS. It basically converts the Object Oriented nature of JTS into a very simple class with a bunch of simple static methods.

2. "DerbyJTSWrapper.java" - Autogenerated from StaticGeometry. Its the database specific class for wrapping StaticGeometry

3. "DerbySQL.sql" – SQL 'CREATE FUNCTION' statements for DerbyJTSWrapper

4. Code generators for #1, #2, #3 (in java, based on reflection)

I've come across a set of Derby-specific issues I'm hoping to get resolved. Most of them are fighting with the custom-type/custom-function support.

1. begin/commit

I'm running ij, and I'd expect to be able to do this:

BEGIN;
<statement>;
<statement>;
COMMIT;

But it responds with "ERROR 42X01: Syntax error: Encountered "BEGIN"
at line 2, column 1."

2. return a string from a function

From a custom function, how do I return a java.lang.String?
Currently, I do something like this:

CREATE FUNCTION ... RETURNS varchar(10000) ...

but I dont want to have to limit my return string length.

3. boolean type

I couldnt find a boolean type. I have a set of functions that return true/false results. Whats the name of the Derby type I should be using?

4. custom types/"long" datatype arguments for functions

In the current implementation, I use a "VARCHAR(10000)" as my geometry type (with a Well Known Test - WKT - version of the geometry). This is an extremely poor representation - (a) its fixed length and (b) based on text!

I'd really like to have a Derby type called "Geometry" that was just a byte[] that I can throw a WKB - Well Known Binary - version of the Geometry into. Or at least the Java serialized form. The create function command doesnt allow this type of thing to happen.

I'd like to see my CREATE FUNCTION commands look like:

CREATE FUNCTION intersection(arg0 Geometry,arg1 Geometry)
RETURNS Geometry ...;

Then have my actual java function get passed something like a byte[].

5. Indexing

I noticed that there was some discussion about GiST indexes in derby a while ago - has there been any movement on this? It would be really good to get an RTree index!!

I've attached the java files in a .zip - and the JTS jar file. Just stick them in your class path then execute the SQL script using ij. You'll get errors for the boolean functions (see #3, above) - just ignore them for now. There's about 50 spatial functions defined.

(actually, the mailing list does not allow .jar/.zip attachments, but you can find them here:
SpatialDerby
)

Here's an example for intersection(<geometry>,<geometry>):

ij> values intersection('POLYGON((0 0,0 10,10 10,10 0,0 0))',
                       'POLYGON((7 7,7 20,20 20,20 7,7 7))');
1
------------------------------------------------
POLYGON ((7 10, 10 10, 10 7, 7 7, 7 10))

1 row selected

StaticGeometry.java:

...

    static public Geometry intersection(Geometry arg0,Geometry arg1)
    {
          Geometry _this = arg0;

          return _this.intersection(arg1);
    }

...

DerbyJTSWrapper.java:
...

  static public String intersection(String geo0,String geo1)
  {
       Geometry arg0 = deserialize(geo0);
       Geometry arg1 = deserialize(geo1);
        return serialize(StaticGeometry.intersection(arg0,arg1));
  }

...
DerbySQL.sql:
....


CREATE FUNCTION intersection(arg0 varchar(10000),arg1 varchar(10000))
RETURNS varchar(10000)
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'DerbyJTSWrapper.intersection';


...


darbyspatial.zip (application/zip)
Document generated by Confluence on May 14, 2014 23:00