4 Replies Latest reply: Jul 30, 2012 4:09 PM by 948335 RSS

    Converting from UTM meter to latitude,longitude in oracle

    user12134253_xavierite
      I am trying to write a procedure which takes in utm data and converts to latitude,longitude
      mostly on the net i am getting some formulas for conversion with no proper explanation.
      Can any of you suggest any previously written oracle procedure or other help.
      UTM data is available in a column, i want to convert the easting,northing data into lat,long and take the lat
      from easting in deg,minustes,seconds and likewise longitude from northing.
      there are some methods like using excel or in java, but i want to do it in Oracle.
      Please help as i am looking for suggestions and help.
        • 1. Re: Converting from UTM meter to latitude,longitude in oracle
          Jack Wang
          Hi,
          You can use the following function to transform a UTM geometry to (longitude,latitude) representation

          SDO_CS.TRANSFORM(
          geom IN SDO_GEOMETRY,
          to_srid IN NUMBER
          ) RETURN SDO_GEOMETRY


          an example from UTM Zone 12 (Northern Hemsphere (WGS 84), SRID = 82223) to WGS 84 (SRID = 8307)
           select sdo_cs.transform(sdo_geometry(2001,82223,sdo_point_type(500000,5000000,null),null,null),8307) from dual;
          
          SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,82223,SDO_POINT_TYPE(500000,5000000,NULL),NUL
          --------------------------------------------------------------------------------
          SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-111, 45.1534772, NULL), NULL, NULL)
          You can find the UTM SRID information from the cs_srs table:
          select cs_name,srid from cs_srs where cs_name like 'UTM Zone%';
          Please note the results are defined as follows:

          The UTM point (easting,northing,null) and the WGS 84 (longitude,latitude,null) in the sdo_point_type field.

          hope it helps!

          jack
          • 2. Re: Converting from UTM meter to latitude,longitude in oracle
            user12134253_xavierite
            But that is converting the geometry,

            i have written this pl/sql procedure but it is giving accuracy error.
            i used converter with formulae from web link http://www.uwgb.edu/dutchs/usefuldata/utmformulas.htm

            the code for utm conversion i made from this formula is given below.

            error is very small but there, please help.When i check with converter xls i get small error but it is not clear how to solve it
            code runs, except for lat_sec, long_sec which gives 0.
            please help

            test values are

            E_UTM_TEMP N_UTM_TEMP ZONE_ LAT_DEG_TEMP LAT_MIN_TEMP LAT_SEC_TEMP LONG_DEG_TEMP LONG_MIN_TEMP LONG_
            ---------- ---------- ----- ------------ ------------ ------------ ------------- ------------- -----
            432610 2799504 40 25 11 0 56 16 0
            433216 2799064 40 25 11 0 56 16 0
            434318 2798726 40 25 11 0 56 17 0
            433506 2798509 40 25 11 0 56 16 0
            434619 2797494 40 25 10 0 56 17 0
            432629 2796550 40 25 10 0 56 16 0
            433994 2795498 40 25 9 0 56 17 0
            432620 2794674 40 25 9 0 56 16 0
            432001 2794000 40 25 8 0 56 15 0
            40
            430381 2797501 40 25 10 0 56 14 0
            431083 2794989 40 25 9 0 56 15 0
            431699 2795202 25 9 0
            428842 2794324 40 25 9 0 56 13 0
            430663 2794869 40 25 9 0 56 14 0
            431282 2795604 40 25 9 0 56 15 0
            432092 2795477 40 25 9 0 56 15 0
            431983 2796031 40 25 10 0 56 15 0
            40
            434323 2799803 40 25 12 0 56 17 0











            ==================================
            declare

            cursor cur_latlong is
            select * from master_gpn_temp for update;

            a number;
            east_prime number;
            arc_length number;
            northing number;
            easting number;
            k0 number;
            e number;
            e1sq number;
            ei number;
            mu number;
            var1 number;
            var2 number;
            var3 number;
            phi number;
            phi1 number;
            phi2 number;
            phi3 number;
            phi4 number;
            -- C1( replace with cu1)
            cu1 number;
            T1 number;
            N1 number;
            --R1( replace with row1)
            row1 number;
            D number;
            Fact1 number;
            Fact2 number;
            Fact3 number;
            Fact4 number;
            LofFact1 number;
            LofFact2 number;
            LofFact3 number;
            Delta_long number;
            zonecm number;
            raw_latitude number;
            raw_longitude number;
            lat_deg number;
            lat_min number;
            lat_sec number;
            long_deg number;
            long_min number;
            long_sec number;
            temp1 number;
            temp2 number;
            temp3 number;
            temp4 number;
            temp5 number;
            temp6 number;
            temp7 number;
            temp8 number;
            temp9 number;
            temp10 number;
            temp11 number;
            temp12 number;
            temp13 number;
            temp14 number;
            temp15 number;
            temp16 number;
            temp17 number;
            temp18 number;
            temp19 number;
            temp20 number;
            temp21 number;
            temp22 number;
            temp23 number;
            temp24 number;
            temp25 number;
            temp26 number;
            temp27 number;
            zone1 number;
            begin

            for r1 in cur_latlong
            loop
            e:=0.0818191909289069;
            easting:=r1.e_utm_temp;
            northing:=r1.n_utm_temp;
            zone1:=r1.zone_temp;
            --dbms_output.put_line('value of easting is'||easting);
            --dbms_output.put_line('value of northing is'||northing);
            --dbms_output.put_line('value of zone1 is'||zone1);
            east_prime := 500000-easting;
            k0:=0.9996;
            e1sq:=0.006739496756587;
            arc_length:=northing/k0;
            a:=6378137;
            --dbms_output.put_line('value of east_prime is'||east_prime);
            --dbms_output.put_line('value of arclength is'||arc_length);

            -- calculating mu,var1,var2,var3

            var1:= power(e,2);
            var2:= power(var1,2);
            var3:= power(var2,2);
            -- dbms_output.put_line('value of var1 is'||var1);
            -- dbms_output.put_line('value of var2 is'||var2);
            --dbms_output.put_line('value of var3 is'||var3);

            mu:=arc_length/(a*(1-(var1/4)-3*(var2/64)-5*(var3/256)));

            dbms_output.put_line('value of mu is'||mu);
            -- calculating phi(footprint),phi1,phi2,phi3,phi4,temp1,temp2,temp3,temp4,temp5,temp6,temp7
            temp1:= 1-var1;
            temp2:= power(temp1,0.5);
            ei:= (1-temp2)/(1+temp2);
            temp3:= 27*ei;
            temp4:= power(temp3,3);
            phi1:= (3*ei)/2-(temp4/32);
            temp5:= power(ei,2);
            temp6:= power(temp5,2);
            phi2 := (21*temp5)/16 - (55*temp6)/32;
            temp7:= power(ei,3);
            phi3:=(151*temp7)/96;
            phi4:=(1097*temp6)/512;

            phi:= mu + phi1*sin(2*mu)+phi2*sin(4*mu)+phi3*sin(6*mu)+phi4*sin(8*mu);


            -- calculate cu1 in place of c1,temp8,temp9, temp10, temp11,T1

            temp8:=cos(phi);
            temp9:=power(temp8,2);

            cu1:= temp5*(temp9);

            temp10:= tan(phi);
            temp11:= power(temp10,2);
            T1:= temp11;

            -- calculate N1, temp12,temp13,temp14,temp15

            temp12:=e*sin(phi);
            temp13:= power(temp12,2);
            temp14:= 1-temp13;
            temp15:= power(temp14,0.5);
            N1:= a/temp15;

            -- calculate row1 in place of R1, temp16,temp17, temp18,temp19

            temp16:= e* sin (phi);
            temp17:= power(temp16,2);
            temp18:= 1- temp17;
            temp19:= power(temp18,1.5);
            row1:= a*(1-var1)/temp19;

            -- calculate D, Fact1,Fact2
            D:= east_prime/(N1*k0);
            Fact1:= (N1* tan(phi))/row1;


            -- calculate Fact3,Fact4,temp20,temp21, cu1 in place of c1

            temp20:=(5+3*T1+10*cu1-4*cu1*cu1-9*temp5)*D;

            temp21:= power(temp20,4);

            Fact3:= (temp21)/24;

            temp22:= (61+(90*T1)+(298*cu1+T1*T1)-(252*temp5)-(3*cu1*cu1))*D;

            temp23:= power(temp22,6);

            Fact4:= temp23/720;

            -- calculate LofFact1,LofFact2,temp24,temp25, cu1 in place of c1

            LofFact1:=D;

            temp24:= (1+2*T1+cu1)*D;

            temp25:= power(temp24,3);

                 LofFact2:= temp25/6;

            -- calculate LofFact3,temp26,temp27, cu1 in place of c1

            temp26:= (5-2*cu1+28*T1-3*cu1*cu1+8*e1sq*e1sq +24*T1*T1)*D;
            temp27:= power(temp26,5);
            LofFact3:= temp26/120;



            -- calculate remaining Delta_long, zonecm, raw_latitude raw_latitude,raw_longitude,lat_deg,lat_min,,lat_sec,long_deg,long_min,long_sec

            Delta_long:= (LofFact1-LofFact2+LofFact3)/cos(phi);


            zonecm:= (6*zone1)-183;


            raw_latitude:=(180*mu)/(22/7);
            --dbms_output.put_line('value of latitude is'||raw_latitude);

            raw_longitude:=zonecm-(delta_long*180)/(22/7);
            --dbms_output.put_line('value of longitude is'||raw_longitude);
                      lat_deg:=round(raw_latitude);

            lat_min:=abs((60*(raw_latitude-lat_deg)));
                 
            lat_sec:=3600*(abs(raw_latitude)-abs(lat_deg)-lat_min/60);

            long_deg:=round(raw_longitude);

            long_min:=abs((60*(raw_longitude-long_deg)));

            long_sec:=3600*(abs(raw_longitude)-abs(long_deg)-long_min/60);

                 update master_gpn_temp set lat_deg_temp=lat_deg where current of cur_latlong;
                 update master_gpn_temp set lat_min_temp=lat_min where current of cur_latlong;
                 update master_gpn_temp set lat_sec_temp=lat_sec where current of cur_latlong;
                 update master_gpn_temp set long_deg_temp=long_deg where current of cur_latlong;
                 update master_gpn_temp set long_min_temp=long_min where current of cur_latlong;
                 update master_gpn_temp set long_sec_temp=long_sec where current of cur_latlong;
            end loop;
            end;
            /
            • 3. Re: Converting from UTM meter to latitude,longitude in oracle
              Jack Wang
              You can get the longitude and latitude from the geometry(sdo_point_type field). The result is in decimal degree. You can then convert the decimal degree into degree/minute/second representation. Oracle spatial does most of the work for you and you don't have to maintain the transformation code.

              jack
              • 4. Re: Converting from UTM meter to latitude,longitude in oracle
                948335
                This function work for me:
                -----
                -- UTM2LAT // CONVERT AUTOCAD CORD TO LAT LON
                CREATE OR REPLACE FUNCTION UTM2LAT (CORDX NUMBER, CORDY NUMBER, LATZONE VARCHAR2, LONZONE NUMBER, )
                    RETURN VARCHAR2
                 IS 
                 
                    UTM_F0 NUMBER;
                    ESQUARED NUMBER;
                    EPRIMESQUARED NUMBER;
                    CX NUMBER;
                    CY NUMBER;
                    PHI1RAD NUMBER;
                    A1 NUMBER; E1 NUMBER; M1 NUMBER; MU NUMBER; N1 NUMBER; T1 NUMBER; C1 NUMBER; R1 NUMBER; D1 NUMBER; PI NUMBER;
                    
                    LATITUDE NUMBER;
                    LONGITUDE NUMBER;
                    
                 BEGIN   
                    
                    UTM_F0 := 0.9996;
                    ESQUARED := 0.0066943800667647;
                    EPRIMESQUARED := 0.0067394968199361;
                    A1 := 6378137;
                    E1 := 0.0016792204056686;
                    PI := 3.1415926535898;
                    CX := CORDX - 500000.0;
                    
                    
                    -- CORRECT CY FOR SOUTHERN HEMISPHERE
                    IF ( ASCII(LATZONE) - ASCII('N') ) < 0 THEN    
                        CY := CORDY - 10000000.0;
                    ELSE
                        CY :=  CORDY;   
                    END IF;
                        
                    M1 := CY / UTM_F0;
                    MU := M1 / ( A1 * ( 1.0 - ESQUARED / 4.0 - 3.0 * ESQUARED * ESQUARED / 64.0 - 5.0 * POWER( ESQUARED, 3.0 ) / 256.0 ) ) ;    
                    
                    PHI1RAD := MU + ( 3.0 * E1 / 2.0 - 27.0 * POWER( E1, 3.0 ) / 32.0 ) * SIN( 2.0 * MU ) + ( 21.0 * E1 * E1 / 16.0 - 55.0 * POWER( E1, 4.0 ) / 32.0 ) * SIN( 4.0 * MU ) + ( 151.0 * POWER( E1, 3.0 ) / 96.0 ) * SIN( 6.0 * MU ) ;
                
                    N1 := A1 / SQRT( 1.0 - ESQUARED * SIN( PHI1RAD ) * SIN( PHI1RAD ) ) ;
                    T1 := TAN( PHI1RAD ) * TAN( PHI1RAD ) ;
                    C1 := EPRIMESQUARED * COS( PHI1RAD ) * COS( PHI1RAD ) ;
                    R1 := A1 * ( 1.0 - ESQUARED ) / POWER( 1.0 - ESQUARED * SIN( PHI1RAD ) * SIN( PHI1RAD ), 1.5 ) ;
                    D1 := CX / ( N1 * UTM_F0 ) ;
                    
                    LATITUDE := ( PHI1RAD - ( N1 * TAN( PHI1RAD ) / R1 ) * ( D1 * D1 / 2.0 - ( 5.0 + ( 3.0 * T1 ) + ( 10.0 * C1 ) - ( 4.0 * C1 * C1 ) - ( 9.0 * EPRIMESQUARED ) ) * POWER( D1, 4.0 ) / 24.0 + ( 61.0 + ( 90.0 * T1 ) + ( 298.0 * C1 ) + ( 45.0 * T1 * T1 ) - ( 252.0 * EPRIMESQUARED ) - ( 3.0 * C1 * C1 ) ) * POWER( D1, 6.0 ) / 720.0 ) ) * ( 180.0 / PI ) ;
                    LONGITUDE := ( ( LONZONE - 1.0 ) * 6.0 - 180.0 + 3.0 ) + ( ( D1 - ( 1.0 + 2.0 * T1 + C1 ) * POWER( D1, 3.0 ) / 6.0 + ( 5.0 - ( 2.0 * C1 ) + ( 28.0 * T1 ) - ( 3.0 * C1 * C1 ) + ( 8.0 * EPRIMESQUARED ) + ( 24.0 * T1 * T1 ) ) * POWER( D1, 5.0 ) / 120.0 ) / COS( PHI1RAD ) ) * ( 180.0 / PI ) ;
                      
                    RETURN REPLACE(SUBSTR(LATITUDE,1,16),',','.') || ',' || REPLACE(SUBSTR(LONGITUDE,1,16),',','.');
                   
                 END;
                SELECT UTM2LAT (347852.7978, 6290665.6966, 'H', 19) FROM DUAL
                -----

                Edited by: user13081849 on 30-jul-2012 14:08