Hfrhyu

I'm trying to reproject some data from one CRS to another (SRID:27493 to SRID:3763) using the SDO_CS.TRANSFORM function.

The function works and returns no errors, but the coordinate transformation function that Oracle is using must be inadequate, because the results are inaccurate.

So, I would like to alter or define a new coordinate transformation function that suits my data.

I've realized that in the SDO_COORD_REF_SYS table there is an PROJECTION_CONV_ID attribute that should lead somewhere, but the transformation function and its parameters seem to be dispersed through several tables.

Anyone has experience in this and / or can point some manuals / tutorials?

Problem 1.

One of the problems I had with the coordinate transformation between data in 'Datum 73 / Modified Portuguese Grid' (SRID:27493) and ETRS89/PT-TM06 (SRID:3763), was that the results I was getting were different of the ones I would get through PostgreSQL/PostGIS, which uses Proj4 libraries and gives identical results than the default coordinate transformation assume by EPSG.

I realized in time that Oracle was using deprecated parameters for the Tranverse Mercator projection from Datum73 - SRID:4274 (the geodetic coordinate system in which SRID:27493 is based) and WGS84 - SRID:4326, and that was influencing the projections of any coordinate system based on Datum73.

Problem 2.

The coordinate transformation method recomended by Direcção-Geral do Território (DGT) between SRID:27493 and SRID:3763, therefore the most accurate, is based on NTv2 transformation and the Grid Shift Matrices provided by DGT.

So I would like to had this new transformation method and its parameters to Oracle Spatial.

NOTE: the Oracle documentation mentioned in comments was no definite help on this

Search for Spatial Developer Guide Chapter 6.9.6 Creating a Transformation Operation

Chapter 6.9.7 contains an example how to change the trans formation based of helmert parameter to ntv2

this is the current link for Release 18

Answer for Problem 1.

I found out that the easier and most effective way to change the transformation parameters used by the Tranverse Mercator projection between Datum73 and WGS84 was to change the WKT defintion for the affected coordinate systems.

The WKT definition is set in the MDSYS.SDO_CS_SRS table (therefore you will need SYS privileges to update it). The SQL code to update the table in the required rows is as follows (it will correct both SRID 4274 and 27493 simultaneously):

UPDATE MDSYS.SDO_CS_SRS
SET
 wktext = REPLACE(wktext, '-231.0, 102.6, 29.8, 0.615, -0.198, 0.881, 1.79', '-223.237, 110.193, 36.649, 0, 0, 0, 0'),
 wktext3d = REPLACE(wktext3d, '-231.0, 102.6, 29.8, 0.615, -0.198, 0.881, 1.79', '-223.237, 110.193, 36.649, 0, 0, 0, 0')
WHERE srid IN (4274, 27493);

NOTE: The correct parameters were taken from the updated WKT definition for SRID:27493. By the way, Oracle was using the deprecated parameters of the transformation with code 1945 (EPSG).

NOTE 2: I decided to not use the SDO_CS.ADD_PREFERENCE_FOR_OP procedure to set the desired parameters (they were already defined in the SDO_COORD_OPS table under the COORD_OP_ID = 1983), because I would have to set preferences for all possible transformations from and to SRID 4274 or 27493. Updating the WKT definition prevented all that and influences all the coordinate transformations from and to SRID 4274 and 27493.

As for Problem 2, I'm still unable to successfully define and use the NTv2 transformation function, so I'll write here were I've got so far:

From Oracle documentation on Creating a Transformation Operation you get an example on how to configure a transformation based on the NTv2 method and how to load the grid shift matrices (usually provided as a binary .gsb file) to the database, but I tried the procedure and had no success.

As stated in the documentation, Oracle will not be able to directly read the binary .gsb that I got from DGT, so I had to convert it into a text file in .gsa format. For this I used ntv2-file-routines to convert from .gsb to .gsa. The convertion wasn't perfect and I had to tweak the .gsa file afterwards, but the resulting file was valid and producing good results, as I tried it with the ntv2_cvt.exe tool.

After using the PL/SQL procedure to load the .gsa file and using the SDO_CS.ADD_PREFERENCE_FOR_OP procedure to set the new transformation method as the default method for transformation between SRID 27493 and 3763, it presented an error when using SDO_CS.TRANSFORM.

Checking the MDSYS.SDO_COORD_OP_PARAM_VALS table, where the file was loaded, I realized that the file was not correctly recognized, showing a lot of erroneous characters in the field value. I then used the DBMS_LOB.LOADCLOBFROMFILE procedure, instead of the DBMS_LOB.LOADFROMFILE proposed, and the content of the file was correctly loaded to the table.

The SDO_CS.TRANSFORM function now presents no errors, but delivers the same result as if the new transformation method was not defined!

Checking the MDSYS.SDO_COORD_OP_PARAM_VALS table for others records using NTv2 grid matrices I see that the PARAM_VALUE_FILE field, where the documentation says to load the .gsa file, as NULL values for all of them!!! You can check this through the next query:

SELECT
 ops.coord_op_name,
 ops.coord_op_type,
 ops.source_srid,
 ops.target_srid,
 param.*
FROM MDSYS.SDO_COORD_OP_PARAM_VALS param
 JOIN MDSYS.sdo_coord_ops ops
 ON ops.coord_op_id = param.coord_op_id
WHERE ops.coord_op_method_id = 9615;

So where are the grid shift matrices for the other transformation functions already set by Oracle?!