db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Blasby <dbla...@gmail.com>
Subject Spatial Datablade for Derby (inital version)
Date Fri, 04 Feb 2005 05:58:52 GMT
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).

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

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:


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

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 a bout 50 spatial functions defined.

(actually, the mailing list does not allow .jar/.zip attachments, but
you can find it attached to this wiki page (at the bottom):



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))');
POLYGON ((7 10, 10 10, 10 7, 7 7, 7 10))

1 row selected


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

          return _this.intersection(arg1);

  static public String intersection(String geo0,String geo1)
       Geometry arg0 = deserialize(geo0);
       Geometry arg1 = deserialize(geo1);
        return serialize(StaticGeometry.intersection(arg0,arg1));
CREATE FUNCTION intersection(arg0 varchar(10000),arg1 varchar(10000))
RETURNS varchar(10000)
EXTERNAL NAME 'DerbyJTSWrapper.intersection';

View raw message