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