Accuracy with computing UTM from decimal degrees
Date: Thu, 28 Aug 2008 09:01:18 -0600
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:
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)
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?
- Bill Ferguson -- http://www.freelists.org/webpage/oracle-l