Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Defining custom SRID transformation

679565Jan 15 2009 — edited Feb 9 2010
We have some data in Gauss Kruger zone 5 system, based on a Bessel 1841 geodetic system.

Since my required conversion parameters are somewhat different from Oracle ones (transformation parameters for Bessel-WGS conversion and scale factor for projected system), I had to define my own coordinate systems. But I can't seem to be able to get the transformation between them to work. I first tried to get the Bessel -WGS transformation working and these are my steps:

--Define a Bessel 1841 geodetic coordinate reference system
insert into sdo_coord_ref_sys
(srid
,coord_ref_sys_name
,coord_ref_sys_kind
,coord_sys_id
,datum_id
,geog_crs_datum_id
,source_geog_srid
,projection_conv_id
,cmpd_horiz_srid
,cmpd_vert_srid
,information_source
,data_source
,is_legacy
,legacy_code
,legacy_wktext
,is_valid
,supports_sdo_geometry
)
values (1000003006
, 'Bessel 1841 based'
, 'GEOGRAPHIC2D'
, 6405
, 6004
, 6004
, null
, null
, null
, null
, 'EPSG'
, 'EPSG'
, 'FALSE'
, null
, null
, 'TRUE'
, 'TRUE')

--define a transofrmation operation
insert into sdo_coord_ops
(coord_op_id
,coord_op_name
,coord_op_type
,source_srid
,target_srid
,coord_tfm_version
,coord_op_variant
,coord_op_method_id
,uom_id_source_offsets
,uom_id_target_offsets
,information_source
,data_source
,show_operation
,is_legacy
,legacy_code
,reverse_op
,is_implemented_forward
,is_implemented_reverse
)
values (1000000003
, 'Bessel 1841 to WGS 84 (2d)'
, 'CONVERSION'
, 1000003006
, 4326
, null
, 1
, 9606
, null
, null
, null
, 'EPSG'
, 1
, 'FALSE'
, null
, 1
, 1
, 1)

--define the parameters for the 7-parameter transformation
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8605, -514, NULL,
NULL, NULL, 9001);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8606, -155.49, NULL,
NULL, NULL, 9001);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8607, -507.05, NULL,
NULL, NULL, 9001);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8608, 5.613852, NULL,
NULL, NULL, 9109);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8609, 3.613852, NULL,
NULL, NULL, 9109);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8610, -11.466815, NULL,
NULL, NULL, 9109);
Insert into SDO_COORD_OP_PARAM_VALS
(COORD_OP_ID, COORD_OP_METHOD_ID, PARAMETER_ID, PARAMETER_VALUE, PARAM_VALUE_FILE_REF,
PARAM_VALUE_FILE, PARAM_VALUE_XML, UOM_ID)
Values
(1000000003, 9606, 8611, -2.091013, NULL,
NULL, NULL, 9202);

And finally I try to do a conversion:
select mdsys.sdo_cs.transform(mdsys.sdo_geometry(2001, 1000003006, null, sdo_elem_info_array(1, 1, 1), sdo_ordinate_array(45.24, 15.13)), 4326) WGS
from dual;

and I get:

(2001; 4326; ; (1; 1; 1; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ); (45.24; 15.1303020016987; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ))

Infact the result is the same wether I define the coord_op or not.

What am I missing?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 8 2010
Added on Jan 15 2009
13 comments
3,785 views