How to change the coordinate transformation function in Oracle? Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?How can I see the coordinate transformation parameters in QGIS?finding the correct transformation (from EPSG:31300 to EPSG:4326) or how to store two srid in one tablePostgis | Problems Reprojecting a Point from UTM ED50 to GEOGRAPHIC ETRS89How can I carry out a reliable coordinate transformation for point data?How to replicate ArcGIS coordinate transformation with ogr2ogr?How to improve performance of Oracle Spatial function sdo_aggr_mbropenlayers 3 (or oracle) convert 2d to 3dPostgis error “unknown spatial reference system” on ST_GeomFromGMLProj.4/GDAL/QGIS Transformation between CRSs that are defined the sameProjNet. Coordinate reference system transformation deviation
Does the Mueller report show a conspiracy between Russia and the Trump Campaign?
Getting out of while loop on console
What does this say in Elvish?
What does Turing mean by this statement?
Why do early math courses focus on the cross sections of a cone and not on other 3D objects?
Why datecode is SO IMPORTANT to chip manufacturers?
Is openssl rand command cryptographically secure?
Sally's older brother
What does it mean that physics no longer uses mechanical models to describe phenomena?
Trying to understand entropy as a novice in thermodynamics
AppleTVs create a chatty alternate WiFi network
Can you force honesty by using the Speak with Dead and Zone of Truth spells together?
What are the main differences between Stargate SG-1 cuts?
The Nth Gryphon Number
Did Mueller's report provide an evidentiary basis for the claim of Russian govt election interference via social media?
retrieve food groups from food item list
What adaptations would allow standard fantasy dwarves to survive in the desert?
Asymptotics question
Why not send Voyager 3 and 4 following up the paths taken by Voyager 1 and 2 to re-transmit signals of later as they fly away from Earth?
Moving a wrapfig vertically to encroach partially on a subsection title
How would a mousetrap for use in space work?
What would you call this weird metallic apparatus that allows you to lift people?
Are the endpoints of the domain of a function counted as critical points?
Test print coming out spongy
How to change the coordinate transformation function in Oracle?
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?How can I see the coordinate transformation parameters in QGIS?finding the correct transformation (from EPSG:31300 to EPSG:4326) or how to store two srid in one tablePostgis | Problems Reprojecting a Point from UTM ED50 to GEOGRAPHIC ETRS89How can I carry out a reliable coordinate transformation for point data?How to replicate ArcGIS coordinate transformation with ogr2ogr?How to improve performance of Oracle Spatial function sdo_aggr_mbropenlayers 3 (or oracle) convert 2d to 3dPostgis error “unknown spatial reference system” on ST_GeomFromGMLProj.4/GDAL/QGIS Transformation between CRSs that are defined the sameProjNet. Coordinate reference system transformation deviation
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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
coordinate-system oracle-spatial reprojection-mathematics
add a comment |
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
coordinate-system oracle-spatial reprojection-mathematics
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
1
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29
add a comment |
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
coordinate-system oracle-spatial reprojection-mathematics
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
coordinate-system oracle-spatial reprojection-mathematics
coordinate-system oracle-spatial reprojection-mathematics
edited Aug 30 '18 at 17:09
Carlos MSF
asked Aug 10 '18 at 12:45
Carlos MSFCarlos MSF
427215
427215
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
1
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29
add a comment |
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
1
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
1
1
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29
add a comment |
2 Answers
2
active
oldest
votes
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
add a comment |
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?!
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f292414%2fhow-to-change-the-coordinate-transformation-function-in-oracle%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
add a comment |
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
add a comment |
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
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
answered Aug 10 '18 at 16:01
Andreas W. BartelsAndreas W. Bartels
43829
43829
add a comment |
add a comment |
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?!
add a comment |
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?!
add a comment |
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?!
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?!
edited Aug 31 '18 at 14:21
answered Aug 30 '18 at 18:48
Carlos MSFCarlos MSF
427215
427215
add a comment |
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f292414%2fhow-to-change-the-coordinate-transformation-function-in-oracle%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I do not think Oracle Spatial, or any spatial-capable RDBMS as a matter of fact, supports grid-based (datum) transformation as mandated by the DGT. You can easily, quickly, and painlessly do it via ogr2ogr though.
– Ralph Tee
Sep 11 '18 at 3:42
1
@RalphTee: I have successfully implemented NTv2 transformation in PostgreSQL + PostGIS and Oracle Spatial, according to their own documentation, should do it too. I just wanted it to work on the database so I could invoke that transformation in a MATERIALIZED VIEW creation code
– Carlos MSF
Sep 11 '18 at 14:11
Great! =) Time to update my knowledge. Thank you for updating.
– Ralph Tee
Sep 11 '18 at 15:29