db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmarsdende...@sbcglobal.net>
Subject Re: Spatial Datablade for Derby (inital version)
Date Fri, 04 Feb 2005 12:41:01 GMT
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';
>...
>
>  
>


Mime
View raw message