Re: Accuracy with computing UTM from decimal degrees
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. †Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^Received on Fri Aug 29 2008 - 06:07:14 CDT