Re: Accuracy with computing UTM from decimal degrees

Hi Bill,

It may have something to do with the contents of your SDO_CS_SRS table
(which the view CS_CRS is based on).
When I run your query

SELECT srid
        INTO v_srid
        FROM MDSYS.cs_srs
       WHERE cs_name LIKE v_datum || '%UTM zone ' || v_zone || v_dir;

I get ORA-01422: exact fetch returns more than requested number of rows

e.g.

select cs_name,srid from mdsys.sdo_cs_srs where cs_name like 'NAD 27%17N%';

CS_NAME                                    SRID
------------------------------ ----------------
NAD 27 / UTM zone 17N                   2032614
NAD 27 / UTM zone 17N                   2032617

This is on a 10.2.0.3 database, on a 9.2 database I had lying around CS_SRS
was a table but my version doesn't have any rows of the format  'NAD
27%17N%' - you didn't mention what version you are on.

Interestingly enough the first entry looks as though the cs_name is wrong
and should be for zone 14N. Using the second entry in your code I get the
following co-ordinates

Northing: 479917
Easting: 2831426

which is different to both yours and ARC (although the Northing is the
same)

This probably hasn't helped too much but I think your code is fine and it
is probably the metadata that needs looking at - possibly a job for an SR
I'm afraid.

Cheers,

Ian



|---------+----------------------------->
|         |           wbfergus@xxxxxxxxx|
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           28/08/2008 16:01  |
|         |           Please respond to |
|         |           wbfergus          |
|         |                             |
|---------+----------------------------->
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       oracle-l@xxxxxxxxxxxxx                                      
                                 |
  |       cc:                                                                   
                                 |
  |       Subject:  Accuracy with computing UTM from decimal degrees            
                                 |
  
>--------------------------------------------------------------------------------------------------------------|




Okay, let me preface this with I am not a GIS person. Also, I was
trying to get this posted on the Oracle forums, but the site is still
experiencing hiccups from their software upgrade last weekend, so
sorry that this isn't a real DBA-type question/problem.

I have a table for geo coordinates (which are all point locations),
which holds the DD, DMS, and UTM values for each point. If a user
updates one, the other two get automatically calculated.

My users don't seem to have any issues with any DD - DMS comversions,
but have noticed some discrepency with the dd -> UTM conversions. So,
here is what I am doing for the conversions and the basic table
layout.

Fields (pertinent to this):
lat_dec      number   (7,5)   -- decimal latitude
lon_dec     number   (8,5)   -- decimal longitude
utm_z       varchar2 (3)       -- UTM zone with 'N' or 'S' identifier
utm_n       number (10)      -- UTN northing
utm_e       number (10)      -- UTM easting
datum       varcahr2 (80)     -- datum name (from the mdsys.cs_srs view)

For the datum, I actually only have 8 entries in a smaller lookup
table for what is valid on my system, which also has the SRID.

To calculate the UTM from the DD, my function is:
   PROCEDURE utm_from_dd(
      datum_in     IN       VARCHAR2,
      lat_dec_in   IN       NUMBER,
      lon_dec_in   IN       NUMBER,
      utm_z_out    IN OUT   VARCHAR2,
      utm_n_out    IN OUT   NUMBER,
      utm_e_out    IN OUT   NUMBER)
   IS
/*
first, determine which UTM zone
second, find SRID to use from datum_in in mdsys.cs_srs and whether lat
is plus or minus
third, compute UTM
*/
      v_zone       PLS_INTEGER  := NULL;
      v_dir        VARCHAR2(1)  := NULL;
      v_datum      VARCHAR2(80) := NULL;
      v_srid       PLS_INTEGER  := NULL;
      v_geometry   SDO_GEOMETRY := NULL;
   BEGIN
      IF lat_dec_in < 0
      THEN
         v_dir := 'S';
      ELSE
         v_dir := 'N';
      END IF;

      v_zone := TRUNC((lon_dec_in + 186) / 6, 0);
      v_datum :=
         CASE datum_in
            WHEN 'NAD27'
               THEN 'NAD 27'
            WHEN 'WGS84'
               THEN 'WGS 84'
            WHEN 'ED50'
               THEN 'ED50'
            WHEN 'ETRS89'
               THEN 'ETRS89'
            WHEN 'NAD83'
               THEN 'NAD83 '
            WHEN 'SAD69'
               THEN 'SAD69'
            WHEN 'WGS72'
               THEN 'WGS 72'
            ELSE datum_in
         END;

      SELECT srid
        INTO v_srid
        FROM MDSYS.cs_srs
       WHERE cs_name LIKE v_datum || '%UTM zone ' || v_zone || v_dir;

      utm_z_out := v_zone || v_dir;
      v_geometry :=
         MDSYS.sdo_cs.transform
                      (MDSYS.SDO_GEOMETRY(2001,
                                          8307,
                                          MDSYS.sdo_point_type(lon_dec_in,
                                                               lat_dec_in,
                                                               NULL),
                                          NULL,
                                          NULL),
                       v_srid);

      SELECT TRUNC(t.x, 0), TRUNC(t.y, 0)
        INTO utm_e_out, utm_n_out
        FROM DUAL, TABLE(MDSYS.sdo_util.getvertices(v_geometry)) t;
   END utm_from_dd;

Using 25.6 as lat_dec and -81.2 for lon_dec and NAD27 for the datum,
one of the users found the following:


               my code                  ARC
Northing   2831201              2831242
Easting     479897                479917

This site (
http://www.rcn.montana.edu/resources/tools/coordinates.aspx?nav=11,
from Montana State University) also returns the same values as ARC
does.

So, my question is, am I doing something incorrectly, or is there
something different in the way Oracle converts UTM's?

Thanks,


--
-- Bill Ferguson
--
http://www.freelists.org/webpage/oracle-l



This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.


For the latest data on the economy and society consult National Statistics at 
http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for 
compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not 
necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure 
Intranet virus scanning service supplied by Cable&Wireless in partnership with 
MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this 
email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or 
recorded for legal purposes.

Other related posts: