You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by David Blasby <db...@gmail.com> on 2005/02/04 06:58:52 UTC
Spatial Datablade for Derby (inital version)
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 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):
http://docs.codehaus.org/display/GEOS/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';
...
Re: Spatial Datablade for Derby (inital version)
Posted by David Blasby <db...@gmail.com>.
Okay - I'll change my implementation to return smallint instead of booleans.
> VARCHAR FOR BIT DATA would be better than VARCHAR as it is not text based.
Unfortunately, I dont think you can send non-fixed-length objects as
arguments to functions.
dave
Re: Spatial Datablade for Derby (inital version)
Posted by Daniel John Debrunner <dj...@debrunners.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Kathey Marsden wrote:
> In ij you just need to turn autocommit off (once) and then the
> transaction will begin automatically.
>
> ij> autocommit off;
> ij> <statement>;
> ij><statement>;
> ij> COMMIT;
> ij> <statement>;
> ij> COMMIT;
Just to note that the COMMIT is an ij command and not a SQL statement in
Derby. Derby only supports commit through JDBC auto commit and
Connection.commit().
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFCA4VSIv0S4qsbfuQRAoQEAJ0V5j91kMiQzhvobZKRAuVUvN8jEgCbB1Rt
prLXJCs7E6agmzeZVEU7YS0=
=5sq2
-----END PGP SIGNATURE-----
Re: Spatial Datablade for Derby (inital version)
Posted by Kathey Marsden <km...@sbcglobal.net>.
David Blasby wrote:
>I'm just about finished writing a Spatial Datablade for Derby. Its
>based on JTS (Java Topology Suite -
>
>
How Exciting ! Below are a couple of the easy answers. I'll leave the
hard ones to Mike and others.
[snip]
>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."
>
>
>
In ij you just need to turn autocommit off (once) and then the
transaction will begin automatically.
ij> autocommit off;
ij> <statement>;
ij><statement>;
ij> COMMIT;
ij> <statement>;
ij> COMMIT;
>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?
>
>
>
You should use SMALLINT. 0 for false, 1 (or really any non-zero value)
for true.
The ResultSet.getBoolean function will return false or true accordingly.
>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[].
>
>
>
VARCHAR FOR BIT DATA would be better than VARCHAR as it is not text based.
>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):
>
>http://docs.codehaus.org/display/GEOS/SpatialDerby
>
>)
>
>
>
I think Mike Matrigali is looking at this, but don't know the status.
>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';
>...
>
>
>