This discussion is archived
13 Replies Latest reply: Jan 30, 2013 10:32 PM by 983826 RSS

SDO_CS.TRANSFORM gives unexpected results

983826 Newbie
Currently Being Moderated
Gurus,
would anyone help me to discover how the coordinate system operations work in Oracle 11g?

I am trying to transfrom from local Czech coordinate system (SRID 2065) to ETRS-89 (SRID 4258):

-- TEST sdo_cs.transform FORWARD
select sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type 14,50,null),null,null),2065)) as wkt_geom from dual;

In Ora 11g I get:
POINT (774135.822823993 1048589.68103062)
However, correct result should be
POINT (774041.368 1048448.779)
Source: Transformation service of Geoportal of Czech national mapping agency http://geoportal.cuzk.cz/Default.aspx?head_tab=sekce-01-gp&mode=TextMeta&text=wcts&menu=19

Changing the parameters for datum transformation (mdsys.sdo_datums) does not seem to influence the results. Hence I am not sure if this is the correct place to change the x-y-z shifts/rotations and scale_adjust.

-- TEST sdo_cs.transform REVERSE
select sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,2065,sdo_point_type(774135.822823993,1048589.68103062,null),null,null),4258)) as wkt_geom from dual;

In Ora 11g I get:
POINT (14.0 50.0000000647905)
According my experience the coordinate conversion is normally done in two steps (reverse case):
1. projected coordinates in SRID 2065 are converted (by reverse Krovak projection algorithm, coord_op_id=9819) to geographic coordinates in S-JTSK/GEOGRAPHIC2D (SRID 4156), datum "Jednotne Trigonometricke Site Katastralni" (EPSG ID 6156) based on Bessel Ellipsoid (EPSG ID 7004)
2. then the geographic coordinates should be transformed using Position Vector 7-param. transformation (coord_op_id=9606) to ETRS-89 (SRID 4258)
Assuming that above two steps are true in Oracle, and the projection algorithm (coord_op_id=9819) is correctly implemented (I wonder if it is, btw is it hardcoded?), then we should get the expected results.

I have checked that the Oracle defaults for the 7-param.transformation are very close to what we are using in Intergraph Geomedia, which give us acceptable results, which are again very close to the results given by Transformation service of Geoportal of Czech National Mapping Agency.

I would appreciate any comments...

Thank you.. Vlad
  • 1. Re: SDO_CS.TRANSFORM gives incorrect results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vlad,

    Have you checked the parameters for both SRID's, and compared those to the values used in GM's CSF files? Are there significant differences?
    There should be, because the difference in the resulting coords is quite large.

    Also: Are you sure that those SRID's are the correct ones to use? I know for example that there are several SRID's which could be used for the netherlands, but not all of them are correct. Maybe something like that is happening here.

    And lastly: could 2D/3D be an issue? There can be differences between 2D and 3D transformations (sometimes quite large), and as far as I know ETRS89 is a 3D CS (don't have access to Oracle right now, should check tonight).

    As I said, at the moment I'm at a workplace where I don't have (direct) access to Oracle, but I'll have a closer look at home tonight.

    Regards,
    Stefan
  • 2. Re: SDO_CS.TRANSFORM gives incorrect results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi again Vlad,

    if I check
    select * from 
      MDSYS.SDO_COORD_OPS
    where 
      coord_op_id=9819; 
    or
    select * from 
      MDSYS.SDO_COORD_OPS
    where 
      SOURCE_SRID = 2065 AND
      TARGET_SRID = 4258;
    I get zero results (I'm running Oracle Database 11g Enterprise Edition Release 11.2.0.1.0). When I run your sample transformations I'm getting exactly the same results, so the default Spatial Transformation is most likely not the one that the Czech national mapping agency is using.

    Can you ask them for a description of their transformation? It is quite possible to create your own preferred transformation, so that way you may be able to duplicate theirs. You could even ask them how they do it in Oracle, if memory serves me right (I visited the Czech national mapping agency some 4, maybe 5 years ago) they use Oracle as a database as well, so they may have the correct transformations already set up.

    This is the only thing I can find that may solve this difference, even though there are several S-JTSK and ETRS89 coordnate systems defined in Oracle (maybe that is the problem: are you sure that 4258 is the right ETRS89?).

    HTH,
    Stefan
  • 3. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Hello Stefan,

    thank you for your response, you are correct, I get the same results.
    I am afraid something is wrong in my workflow and I guess I am on the track...

    I suspect, that the problem is, that SRID 2065 refers to a projected coordinate sytem, while SRID 4258 is geographic.
    Logically, this would mean to include two steps to make the coordinate conversion work properly. First step would be to convert the data from SRID 2065 Krovak projected to S-JTSK geographic (hope that SRID 4156 is the correct one), and then, as a second step to convert resulting geographic coordinates (geographic coordinates referring to SRID 4156) to ETRS-89 using coord_op_id 1622 (S-JTSK to ETRS89 (1) (EPSG OP 1622))...

    What I am not sure is the fact, that no sdo_coord_op seems to exist between SRID 2065 and 4156:
    select * from MDSYS.SDO_COORD_OPS where source_srid in (2065,4156) and target_srid in (2065,4156) ;
    select * from sdo_coord_ops where coord_op_name like '%JTSK%' or coord_op_name like '%Krovak%' ;
    However, I believe following two operations are those I would expect to be involved in the conversion chain (two steps mentioned above):
    select * from sdo_coord_op_methods where coord_op_method_id in (9606,9819);
    9606     Position Vector 7-param. transformation          1     EPSG guidance note #7.
    9819     Krovak Oblique Conic Conformal          1     Research Institute for Geodesy Topography and Cartography (VUGTK); Prague.
    Now, when I try what I have indicated earlier:
    select sdo_util.to_wktgeometry(sdo_cs.transform(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type(14,50,null),null,null),4156),2065)) as wkt_geom from dual;
    I get:
    POINT (774135.825117089 1048589.67606019)
    ..which is again, very close to my initial unexpected result.

    So, at this point I am a bit confused...

    What do you think?

    Thank you.
    Best regards, Vlad

    Edited by: Vlad Pek on 28.1.2013 1:26

    Edited by: Vlad Pek on 28.1.2013 1:42
  • 4. Re: SDO_CS.TRANSFORM gives unexpected results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vlad,

    You could give it a try, doing the dual-transformation as you describe and see what results that gives you. To be honest I am not sure what could be so different that you get obviously different results. In my mind your best option is to ask the Czech Cadastre. They should be able to give you the correct parameters and algorithms for your transformation, which you could then compare to Oracle's.

    That process should give you the answer and the correct transformations to use.

    It's been too long since I've done anything with this much detail about coordinate transformations :-)

    HTH,
    Stefan
  • 5. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Stefan,
    undestand, I have contacted VUGTK Prague last Friday, no response yet. Also please note my earlier message (edited).
    Thank you, Vladimir
  • 6. Re: SDO_CS.TRANSFORM gives unexpected results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vlad,
    Vlad Pek wrote:
    What I am not sure is the fact, that no sdo_coord_op seems to exist between SRID 2065 and 4156:
    Maybe try SDO_CS.DETERMINE_DEFAULT_CHAIN(2056, 4156); and see what that gives. Maybe that gives us a clue as to where this is going wrong? Since the values are 94 and 141 meter apart, I am thinking this has something to do with the parameters for the datum, but I can't find any differences. But we are not entirely sure which parameters VUGTK Prague is using, are we?
    Vlad Pek wrote:
    So, at this point I am a bit confused...
    Me too :-)
    I'm not sure what can be done at this point, except wait for an answer from VUGTK Prague. The answer should gives us the transformation you are trying to reproduce, and whit that knowledge we should be able to duplicate it in Oracle.

    Regards,
    Stefan
  • 7. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Stefan,

    I recall my previous assumption regarding the CS operations chain.
    select * from table(SDO_CS.DETERMINE_DEFAULT_CHAIN( 2065,4258));

    I get following, which is correct:
    2065
    4818
    4258

    similar, for 102067
    select * from table(SDO_CS.DETERMINE_DEFAULT_CHAIN(102067,4258));

    returns following, also correct:
    102067
    4156
    4258

    As 102067 is my user-defined CS, you would get nothing.

    Btw, how wold you explain, that changing parameters from null to something for datum_id = 6818 under sdo_datums does not influence the result of the transformation?

    In my test I have tried to copy the 7 values (shift_x, y, z, rotate_x, y, z, scale_adjust) from datum_id 6156 to datum_id 6818

    compare:
    select * from sdo_datums where datum_id in(6818,6156);

    Thank you, Vladimir
  • 8. Re: SDO_CS.TRANSFORM gives unexpected results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vladimir,

    It's interesting that (in my database at least) datum 6818 contains NULL for all values in SDO_DATUMS. That would explain why nothing happens. The question then becomes: was the update you tried succesfull? Because then I think there should have been some effect ...
    We'd need to know which algorithms Oracle uses when transforming (the algorithms).

    Unless VUGTK gives us more information and we're missing something.

    Stefan
  • 9. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Stefan,

    exactly. In my case also I had 7 null values. I have updated them using the values from 6156 datum (which I think does not make sense, because 6156 should refer to Greenwich as prime meridian, while 6818 refers to Ferro).

    However, as you say, I would expect some change in transformation result after update.
    And yes, the update was successfull, I've checked by select * from mdsys.sdo_datums where datum_id = 6818;

    Could you please try what you get:

    select '4156:' as txt, sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type(14,50,null),null,null),4156)) as wkt_geom from dual
    union all
    select '4818:', sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type(14,50,null),null,null),4818)) from dual;

    Thanks, Vlad
  • 10. Re: SDO_CS.TRANSFORM gives unexpected results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vlad,
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 29 20:34:08 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select '4156:' as txt, sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type(14,50,null),null,null),4156)) as wkt_geom from dual
      2  union all
      3  select '4818:', sdo_util.to_wktgeometry(sdo_cs.transform(sdo_geometry(2001,4258,sdo_point_type(14,50,null),null,null),4818)) from dual;
    
    TXT    WKT_GEOM
    -------------------------------------------------------------------------------------
    4156:  POINT (14.0010258818362 50.0007861342037)
    4818:  POINT (31.666667 49.9994109369388)
    
    SQL>
    Now this becomes very interesting: 31.666 - 14.001 = 17.665. That is a bit too close to the Prime Meridian 8909 to be coincidence. It's almost as if Oracle is not even looking at the datums in it's calculations....

    Depending on if you get an answer from VUGTK, this actually may need to be logged with oracle.
    Stefan
  • 11. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Hello Stefan,

    yes, exactly, that is my suspicion (Oracle ignoring datum for this case), that would probably explain, why changing 6818 datum's "TO_WGS" parameters would not make any difference.

    Also, I have the answer available from VUGTK's expert, who is involved in coordinate transfomations, and who was my professor during my university studies. He said that they do not use Oracle for transfoming data, because they use own precise algorithms. For the purpose of the precise geodetic works, they need to reduce the local deformations introduced historically into the cadastral mapping data (long story), by implementing a special correction grid over the area of the Czech republic.

    But, if we omit these deformations, we can still use Oracle based transformation, assuming that Krovak's Projection algortihm would be implemented correctly in Oracle for our purpose. Here at CUZK (aka COSMC), as main Czech mapping authority, we are interested to use it, since we need to implement the transformation for the purpose of publishing 1:10k data according to Inspire directive. For this specific purpose, the data transfomed with internal precision of up to 20-30 cm would be still almost perfect result.

    The only thing we need to do, would be to ask the almighty Oracle to implement the Krovak projection algorithm properly, i.e. the conversion from JTSK/Krovak projected coordinates, to SRID:4156 (which is the intermediate step in the default chain) or to SRID:4258.

    (I have tested, that the transformation between SRID:4156 and SRID:4258 provides acceptable results in Oracle, i.e. there is a difference at 8th decimal place in geographic decimal coordinates.)

    Since Krovak projection is specific and quite complex, it in fact involves four steps, I wonder whether it is possible to realize it simply by inserting appropriate parameters to Oracle system tables. So, in case it is not, we can provide a complete algorithm of conversion from JTSK projected coordinates to SRID:4156.

    Anyway, I'm raising my hand and would be happy to help in case Oracle is willing to file a CR (what is a standard way to file a CR?, do we need to go through our local support here in the Czech republic?).

    Best regards, Vlad
  • 12. Re: SDO_CS.TRANSFORM gives unexpected results
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Vlad,
    Vlad Pek wrote:
    Also, I have the answer available from VUGTK's expert, who is involved in coordinate transfomations, and who was my professor during my university studies. He said that they do not use Oracle for transfoming data, because they use own precise algorithms. For the purpose of the precise geodetic works, they need to reduce the local deformations introduced historically into the cadastral mapping data (long story), by implementing a special correction grid over the area of the Czech republic.
    Aha! That explains a lot. I'm not sure that those types of corrections could be implemented in Oracle.
    Vlad Pek wrote:
    But, if we omit these deformations, we can still use Oracle based transformation, assuming that Krovak's Projection algortihm would be implemented correctly in Oracle for our purpose
    Hmmm. To be honest, I'm not sure how to implement a projection algorithm. It's pretty well described how to define your own CS, but the projection algorithm ... I think I would consider developing my own transformation procedure. It depends a bit on how often you have to transform your geometries, but seeing as it's for INSPIRE I don't think transformations need to happen realtime, right? So that would enable you to do the transformation yourself, using the proper algorithm. Don't know how good your PL/SQL knowledge is, but I would consider it. especially since
    Krovak projection is specific and quite complex, it in fact involves four steps, I wonder whether it is possible to realize it simply by inserting appropriate parameters to Oracle system tables. So, in case it is not, we can provide a complete algorithm of conversion from JTSK projected coordinates to SRID:4156.
    It may not be possible at all, allthough the Dutch projection is in there and that is a three-step projection. But I'm not familiar enough with Oracle's way of doing things to be able to tell if the Krovak Projection is possible in oracle.
    Anyway, I'm raising my hand and would be happy to help in case Oracle is willing to file a CR (what is a standard way to file a CR?, do we need to go through our local support here in the Czech republic?).
    I'm not sure. I know that Oracle employees visit these forums, and sometimes react and file problem reports based on discussions here. But formally I would think you would have to go through your local Czech Oracle office.

    Regards,
    Stefan
  • 13. Re: SDO_CS.TRANSFORM gives unexpected results
    983826 Newbie
    Currently Being Moderated
    Ok Stefan,
    thank you for your effort, I think your answers helped me to clarify certain things. And yes, I think I should be able to rewrite the projection algortihm from my old Mathcad worksheets to Oracle user function. The advantage is, that for Inspire purpose we need just one direction ;-)
    So, let's leave this discussion for a while...
    Thank you, Vladimir

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points