 |
Forums |
Admin  Start New Thread
By: David Adler
 RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-15 14:57
|
If your code worked before on an earlier DB2 version, the problem could be due to changes in DB2 V9.7 for the default casting support which was changed to improve SQL compatibility with other DBMS.
I was able to get this to work by creating a new transform group specifically for ST_Polygon with the following DDL:
DROP TRANSFORM wkb_poly_trans FOR db2gse.ST_Polygon;
-- *-----------------------------------------------------------------------
-- * transform group for well-known binary for polygons
-- *-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test.PolyAsWKB ( geometry db2gse.ST_Polygon )
RETURNS BLOB(2147483647)
SPECIFIC test.PolyAsWKB
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN geometry..ST_AsBinary()
;
-- Specify SRID to be used in ST_Polygon constructor
CREATE OR REPLACE FUNCTION test.PolyFromWKB ( wkb BLOB(2147483647) )
RETURNS db2gse.st_polygon
SPECIFIC test.PolyFromWKB
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURN db2gse.ST_Polygon(wkb, 1003)
;
CREATE TRANSFORM FOR db2gse.ST_Polygon wkb_poly_trans
( FROM SQL WITH FUNCTION test.PolyAsWKB(db2gse.ST_Polygon),
TO SQL WITH FUNCTION test.PolyFromWKB(BLOB(2147483647)) )
;
|
By: Brandon Medenwald
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-14 14:07
|
Ok, maybe you're on to something here. Maybe I added another transform group that was applied to ST_Polygon and, after an upgrade, it's no longer available?
If I wanted to add a transform group for ST_Polygon that would behave like the one for ST_Geometry, how would I go about that?
|
By: David Adler
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-13 15:49
|
I'm a little surprised that this ever worked for INSERT statements because the transform function uses the ST_Geometry constructor and would have trouble inserting an ST_Geometry into an ST_Polygon column. Can you try this with a column of type ST_Geometry?
Can you tell me who the actual customer is? I own the DB2 Spatial Extender and it is helpful to know who is using our technology as there is no direct way to get this information.
For reasons unknown, Ruby is crashing on my system when the server is started so I can't test this right now.
|
By: Brandon Medenwald
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-13 15:16
|
For background, we're using Rails with GeoRuby and Spatial Adapter to try to handle our polygons in nice Ruby objects. GeoRuby is responsible for the nice objects and generating WKB and Spatial Adapter is responsible for the DB2 SQL. Spatial Adapter doesn't support DB2, so I've hacked the support in myself. In order to make this work, I had to force DB2 to deal with me in WKB by default, seeing as that's what I have. This had been working, but something has changed and now I can't get the database to keep that declaration.
I'm using 9.5, fixpack 7.
We're using SRID 4326, not the 0 base type to my knowledge.
|
By: David Adler
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-13 14:29
|
Can you give me some background on what you are trying to do? How are you generating the WKB for the INSERT?
What version and fixpack of DB2 are you using?
When you use the default transforms, the default spatial reference system ID is 0 which often isn't useful. Have you redefined srid 0?
Sorry I missed the original post.
|
By: Brandon Medenwald
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-08-13 13:58
|
The CLI trace provided no real information on this, so that was no help. Any other ideas?
Is it possible to establish another transform group similar to the ST_WellKnownBinary to rule out issues with the default transform group somehow?
|
By: Brandon Medenwald
RE: DB2 GeoSpatial Transform Group [ reply ]
2012-07-30 19:51
|
We'll work to get some more trace information.
This did work once upon a time. I know we never changed major versions of Rails (we were in the 3.0.x when this began to fail and it still fails in 3.1). We've been on the same version of the ibm_db gem as well until recently.
|
By: Brandon Medenwald
DB2 GeoSpatial Transform Group [ reply ]
2012-07-30 16:08
|
I'm using DB2 GepSpatial Extender, Rails and the Spatial Adapter gem. In order to get this gem working, I need to tell DB2 to use the default transform group ST_WELLKNOWNBINARY, which allows the database to speak the right language.
I've had this working in the past, but now this doesn't seem to work no matter how hard I try. I've boiled it down to these steps within a transaction:
1. Set the default transform group
2. Check the value to make sure it happened
3. Try to run my SQL
However, the SQL I try to run always errors out by saying: a transform group "DB2_PROGRAM" is not defined. This is the behavior that should happen if a default wasn't specified, but since I've specified and checked it worked right above this, I don't understand how it isn't being adhered to. The ST_WELLKNOWNBINARY transform group is a DB2 default, so it should just work.
Here's some logging to illustrate my steps:
120730 10:56:05 [DEBUG]:: (1.3ms) SET CURRENT DEFAULT TRANSFORM GROUP = ST_WellKnownBinary
120730 10:56:05 [DEBUG]:: (1.3ms) values (current default transform group)
120730 10:56:05 [DEBUG]:: Values: ST_WELLKNOWNBINARY
120730 10:56:06 [DEBUG]:: SQL (8.9ms) INSERT INTO saved_poly (coords, link_id, ma_tech_id, mod_timestamp, polygon, table_name, tech_id) VALUES (?, ?, ?, ?, ?, ?, ?)
120730 10:56:06 [DEBUG]:: RuntimeError: Failed to prepare sql INSERT INTO saved_poly (coords, link_id, ma_tech_id, mod_timestamp, polygon, table_name, tech_id) VALUES (?, ?, ?, ?, ?, ?, ?) due to: [IBM][CLI Driver][DB2/LINUXX8664] SQL20015N A transform group "DB2_PROGRAM" is not defined for data type "DB2GSE.ST_POLYGON". SQLSTATE=42741 SQLCODE=-20015: INSERT INTO saved_poly (coords, link_id, ma_tech_id, mod_timestamp, polygon, table_name, tech_id) VALUES (?, ?, ?, ?, ?, ?, ?)
120730 10:56:06 [FATAL]:: ActiveRecord::StatementInvalid: while copying search with message RuntimeError: Failed to prepare sql INSERT INTO saved_poly (coords, link_id, ma_tech_id, mod_timestamp, polygon, table_name, tech_id) VALUES (?, ?, ?, ?, ?, ?, ?) due to: [IBM][CLI Driver][DB2/LINUXX8664] SQL20015N A transform group "DB2_PROGRAM" is not defined for data type "DB2GSE.ST_POLYGON". SQLSTATE=42741 SQLCODE=-20015: INSERT INTO saved_poly (coords, link_id, ma_tech_id, mod_timestamp, polygon, table_name, tech_id) VALUES (?, ?, ?, ?, ?, ?, ?)
I'm using ibm_db 2.5.10 and Rails 3.1.
|
|
|
