Hello Community,
for the needs of one of my customers, I have to migrate Data from one Coordinate System to another one.
The Source Coordinate System is "Tahiti 52 / UTM zone 6S" (SRID = 2976). The Target Coordinate System is "RGPF / UTM zone 6S" (SRID = 3297).
Informations on Source Coordinate System :
http://georepository.com/crs_2976/Tahiti-52-UTM-zone-6S.html
http://epsg.io/2976
Informations on Target Coordinate System :
http://georepository.com/crs_3297/RGPF-UTM-zone-6S.html
http://epsg.io/3297
Customer Users use Autodesk AutoCAD Map to manage their data.
As far as I know, none of these two coordinates systems are natively present in Oracle Spatial. If I'm right, some years ago, I did put into Oracle Spatial the definition of "Tahiti 52 / UTM zone 6S" (I don't remember). Now, to achieve my goal, I have two points to manage :
1- declare the new Coordinate System,
2- declare a way of transformation or projection from Source to Target Coordinate System.
Is it possible to do this? Transforming data from one Coordinate System to another one ? Shall I have to use an intermediate Coordinate System (WGS 84, or another one) ? Both coordinate systems share the same Projection UTM Zone 6S (Code 16106).
1- First Step : declaring the new Coordinate System.
====================================================
A/ "RGPF / UTM zone 6S" is based on "RGPF" Base Geographic CRS. This CRS does not exists. I have to declare it. So what I did is : Insert into SDO_COORD_REF_SYSTEM values for "RGPF" CRS :
Insert into SDO_COORD_REF_SYSTEM (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,LEGACY_CS_BOUNDS,IS_VALID,SUPPORTS_SDO_GEOMETRY)
values (4687,'RGPF','PROJECTED',4400,999999,999999,null,null,null,null,'Oracle','EPSG','TRUE',null,'
GEOGCS["RGPF",DATUM["Reseau_Geodesique_de_la_Polynesie_Francaise",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0.072,-0.507,-0.245,-0.0183,0.0003,-0.007,-0.0093],AUTHORITY["EPSG","6687"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4687"]]',
null,'TRUE','TRUE');
B/ Then for this CRS, I did also add : Insert into SDO_ELLIPSOIDS and Insert into SDO_DATUMS values for "RGPF" CRS :
Insert into SDO_ELLIPSOIDS (ELLIPSOID_ID,ELLIPSOID_NAME,SEMI_MAJOR_AXIS,UOM_ID,INV_FLATTENING,SEMI_MINOR_AXIS,INFORMATION_SOURCE,DATA_SOURCE,IS_LEGACY,LEGACY_CODE)
values (999999,'RGPF',6378137,9001,298.257222101,6356752.31414035584785210686152953307862,'Reseau_Geodesique_de_la_Polynesie_Francaise','EPSG','FALSE',NULL);
Insert into SDO_DATUMS (DATUM_ID,DATUM_NAME,DATUM_TYPE,ELLIPSOID_ID,PRIME_MERIDIAN_ID,INFORMATION_SOURCE,DATA_SOURCE,SHIFT_X,SHIFT_Y,SHIFT_Z,ROTATE_X,ROTATE_Y,ROTATE_Z,SCALE_ADJUST,IS_LEGACY,LEGACY_CODE)
values (999999,'Reseau_Geodesique_de_la_Polynesie_Francaise','GEODETIC',999999,8901,'Reseau_Geodesique_de_la_Polynesie_Francaise','EPSG',0.072,-0.507,-0.245,-0.0183,0.0003,-0.007,-0.0093,'TRUE',null);
C/ Once the Base Geographic CRS is created, I have to declare "RGPF / UTM zone 6S" Coordinate System. I did the following : Insert into SDO_COORD_REF_SYSTEM.
Insert into SDO_COORD_REF_SYSTEM (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,LEGACY_CS_BOUNDS,IS_VALID,SUPPORTS_SDO_GEOMETRY)
values (3297,'RGPF / UTM zone 6S','PROJECTED',4400,999999,
999999,null,
null,null,null,'Oracle','EPSG','TRUE',null,'PROJCS["RGPF / UTM zone 6S",GEOGCS["RGPF",DATUM["Reseau_Geodesique_de_la_Polynesie_Francaise",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0.072,-0.507,-0.245,-0.0183,0.0003,-0.007,-0.0093],AUTHORITY["EPSG","6687"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4687"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-147],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","3297"]]',
null,'TRUE','TRUE');
2- Second Step : declaring Projection
=====================================
I did declare the relevant Projection 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 (999999999,'RGPF / UTM zone 6S','CONVERSION',2976,3297,NULL,NULL,9807,null,null,NULL,'EPSG',1,'FALSE',null,1,1,1);
Insert into SDO_COORD_OP_PARAM_VALS (COORD_OP_ID,COORD_OP_METHOD_ID,PARAMETER_ID,PARAMETER_VALUE,PARAM_VALUE_FILE_REF,PARAM_VALUE_XML,UOM_ID)
values (999999999,9807,8801,0,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_XML,UOM_ID)
values (999999999,9807,8802,-147,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_XML,UOM_ID)
values (999999999,9807,8805,0.9996,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_XML,UOM_ID)
values (999999999,9807,8806,500000,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_XML,UOM_ID)
values (999999999,9807,8807,10000000,null,null,9001);
3- And now ? How to migrate data ?
==================================
In the Customer Schema, I did declare the use of both Coordinate Systems in FDO Tables (F_SPATIALCONTEXTxxxxxxx).
And finally, to migrate data of one layer to a new one, I used the following statement :
CALL MDSYS.SDO_CS.TRANSFORM_LAYER('MANHOLE','GEOMETRY','MANHOLE4',3297);
But I get as return value the following error :
Error report -
SQL Error: ORA-13282: failure on initialization of coordinate transformation
ORA-06512: at "MDSYS.SDO_CS", line 5524
ORA-06512: at "MDSYS.SDO_CS", line 271
13282. 00000 - "failure on initialization of coordinate transformation"
So now, I'm totally lost. I thought that I was in the good way. But this goes over my knowledge. Does anyone could help me please ? Have I missed something ? Where did I get wrong ? Thanks a lot by advance for any help.
Best regards,
Thomas.