oracle操作之经纬度转换

来源：互联网发布：学java有前途吗编辑：程序博客网时间：2024/06/06 05:19

一、度分秒转点度

select t.lon,substr(t.lon,0,instr(t.lon,'°')-1) ,
substr(t.lon,instr(t.lon,'°')+1,instr(t.lon,'′')-instr(t.lon,'°')-1) ,
substr(t.lon,instr(t.lon,'′')+1,instr(t.lon,'″')-instr(t.lon,'′')-1) from mtr_wprd_station t;

select t.lat,substr(t.lat,0,instr(t.lat,'°')-1) ,
substr(t.lat,instr(t.lat,'°')+1,instr(t.lat,'′')-instr(t.lat,'°')-1) ,
substr(t.lat,instr(t.lat,'′')+1,instr(t.lat,'″')-instr(t.lat,'′')-1) from mtr_wprd_station t;

update mtr_wprd_station t set t.lon_decimal= substr(t.lon,0,instr(t.lon,'°')-1)
+substr(t.lon,instr(t.lon,'°')+1,instr(t.lon,'′')-instr(t.lon,'°')-1)/60
+substr(t.lon,instr(t.lon,'′')+1,instr(t.lon,'″')-instr(t.lon,'′')-1)/3600;

update mtr_wprd_station t set t.lat_decimal= substr(t.lat,0,instr(t.lat,'°')-1)
+substr(t.lat,instr(t.lat,'°')+1,instr(t.lat,'′')-instr(t.lat,'°')-1)/60
+substr(t.lat,instr(t.lat,'′')+1,instr(t.lat,'″')-instr(t.lat,'′')-1)/3600;

select t.lon_decimal from mtr_wprd_station t;
select t.lat_decimal from mtr_wprd_station t;

二、点度转度分秒

update STT_ASM_STATION_TMP_201503 t
set t.lon1 = trunc(t.lon),
t.lon2 = trunc((t.lon - trunc(t.lon)) * 60),
t.lon3 = trunc(((t.lon - trunc(t.lon)) * 60 - trunc((t.lon - trunc(t.lon)) * 60)) * 60);

update STT_ASM_STATION_TMP_201503 t
set t.lat1 = trunc(t.lat),
t.lat2 = trunc((t.lat - trunc(t.lat)) * 60),
t.lat3 = trunc(((t.lat - trunc(t.lat)) * 60 - trunc((t.lat - trunc(t.lat)) * 60)) * 60);