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 nonzero 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';
>...
>
>
>
