[Oracle]计算地表两点之间的距离

--创建存储过程point_diatance

create or replace procedure point_distance  Authid Current_User is

 

      --定义变量

      net1        float;

      net2        float;

      net3        int;

      net4        int;

      net5        int;

      lata        float;

      lona        float;

      latb        float;

      lonb        float;

      net6        float;

      net7        float;

      net8        float;

      net9        float;

      net10       float;

      net11       float;

      net12       float;

      net13       float;

 

 begin

 for net3 in 1..49

   loop --开始循环

     for net4 in 1..49

       loop

 

         --从aa_point表格中读取经纬度

         select latitude into lata from  AA_POINT where point_id = net3;

         select longtitude into lona from  AA_POINT where point_id = net3;

         select latitude into latb from  AA_POINT where point_id = net4;

         select longtitude into lonb from  AA_POINT where point_id = net4;      

 

         --开始计算过程--核心公式

         net6 := sin(lata);

         net7 := sin(latb);

         net8 := cos(lona-lonb);

         net9 := cos(lata);

         net10 := cos(latb);

         net11:= net6 * net7 * net8 + net9 * net10;

         if net11 >= 1 then net12 := acos(0.999);

         else if  net11 <= -1 then net12 := acos(-0.999);

         else  net12 := acos(net11);

         end if;

         end if;

         net2 := 6371.004 * net12 * 3.1415926/180;

         --net1 := sin(lata)*sin(latb)*cos(lona-lonb)+cos(lata)*cos(latb);

         --net2 := 6371.004*acos(net1)*3.1415926/180;

 

        --输出计算结果到aa_distance表格

        update aa_distance set point_1=net3,point_2=net4,distance=net2

                  where id_number = net5;

       end loop;

   end loop;--结束循环

 commit;--提交任务

 end  point_distance;