-- 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';
/

-- set missing GPS_LAT_DEGREE_MSR 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';
/

-- 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;
/