Database Software

Okay, let me again preface this with I am not a GIS person.

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 (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