-- Created by:  James Robinson
-- Date:  11-13-03
-- update_decimal_coord.sql
-- Calculate and set the decimal version of the GPS coordinate columns in the STORET TSMALP table.
  
update tsmalp
set lat_dec_deg_msr=round(to_number(lat_degree_msr + (lat_minute_msr / 60) + (lat_second_msr / (60 * 60))), 7)
where lat_dec_deg_msr is null
AND substr(d_userid_code,1,3) = 'SIM'
/

update tsmalp
set long_dec_deg_msr=-1*round(to_number(long_degree_msr + (long_minute_msr / 60) + (long_second_msr / (60 * 60))), 7)
where long_dec_deg_msr is null
AND substr(d_userid_code,1,3) = 'SIM'
/

-- SLP 11/13/03 - Added additional update statement for missing GPS_LAT_DEGREE_MSR.
-- This value is always equal to the LAT_DEGREE_MSR value.

UPDATE tsmalp
SET gps_lat_degree_msr = lat_degree_msr
WHERE gps_lat_degree_msr IS NULL
AND substr(d_userid_code,1,3) = 'SIM'
/

-- DTW 11/26/03 - Add scripts to make sure lat / long signs are correct.
-- Only lat_dec_deg_measure and long_dec_deg_measure are signed values
UPDATE tsmalp
SET long_dec_deg_msr = Decode(long_direction,'W','-','+')||abs(long_dec_deg_msr),
    lat_dec_deg_msr = Decode(lat_direction,'S','-','+')||abs(lat_dec_deg_msr)
WHERE substr(d_userid_code,1,3) = 'SIM'  
/

COMMIT
/




