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;








3















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










share|improve this question
























  • 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

















3















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










share|improve this question
























  • 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













3












3








3


1






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










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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

















  • 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










2 Answers
2






active

oldest

votes


















0














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






share|improve this answer






























    0














    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?!






    share|improve this answer

























      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
      );



      );













      draft saved

      draft discarded


















      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









      0














      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






      share|improve this answer



























        0














        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






        share|improve this answer

























          0












          0








          0







          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






          share|improve this answer













          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Aug 10 '18 at 16:01









          Andreas W. BartelsAndreas W. Bartels

          43829




          43829























              0














              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?!






              share|improve this answer





























                0














                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?!






                share|improve this answer



























                  0












                  0








                  0







                  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?!






                  share|improve this answer















                  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?!







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 31 '18 at 14:21

























                  answered Aug 30 '18 at 18:48









                  Carlos MSFCarlos MSF

                  427215




                  427215



























                      draft saved

                      draft discarded
















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      រឿង រ៉ូមេអូ និង ហ្ស៊ុយលីយេ សង្ខេបរឿង តួអង្គ បញ្ជីណែនាំ

                      Crop image to path created in TikZ? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Crop an inserted image?TikZ pictures does not appear in posterImage behind and beyond crop marks?Tikz picture as large as possible on A4 PageTransparency vs image compression dilemmaHow to crop background from image automatically?Image does not cropTikzexternal capturing crop marks when externalizing pgfplots?How to include image path that contains a dollar signCrop image with left size given

                      QGIS export composer to PDF scale the map [closed] 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?Print Composer QGIS 2.6, how to export image?QGIS 2.8.1 print composer won't export all OpenCycleMap base layer tilesSave Print/Map QGIS composer view as PNG/PDF using Python (without changing anything in visible layout)?Export QGIS Print Composer PDF with searchable text labelsQGIS Print Composer does not change from landscape to portrait orientation?How can I avoid map size and scale changes in print composer?Fuzzy PDF export in QGIS running on macSierra OSExport the legend into its 100% size using Print ComposerScale-dependent rendering in QGIS PDF output