Re: Accuracy with computing UTM from decimal degrees

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Fri, 29 Aug 2008 12:07:14 +0100
Message-ID: <OFC27E7AE3.48216FAB-ON802574B4.003A8E65-802574B4.003D16AF@ons.gsi.gov.uk>


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_at_gmail.com|
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 28/08/2008 16:01 |
| | Please respond to |
| | wbfergus |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | 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.
i0zX+n{+i^
Received on Fri Aug 29 2008 - 06:07:14 CDT

Original text of this message