MySQL存储过程实现点与点之间距离的计算

素颜马尾好姑娘i 2022-10-25 11:20 185阅读 0赞

**一、需求概述**

1.  通过各个地级市中心点经纬度，计算出全国各地级市之间的距离。
2.  最后需整理出来的表字段需要提供以下内容，包括：  
    （1）省份编码、城市编码、城市名称、经度、纬度、距离等四种类型字段。

**二、实现步骤**

1.  实现手段  
    使用mysql存储过程来完成本次的需求内容。
2.  距离计算方法  
    使用mysql自带的计算地理距离的函数：ST\_Distance(g1, g2 \[, unit\])以及ROUND() 函数(把数值字段舍入为指定的小数位数)

SQL ROUND() 语法
    SELECT ROUND(column_name,decimals) FROM table_name;

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>column_name</td> 
   <td>必需。要舍入的字段。</td> 
  </tr> 
  <tr> 
   <td>decimals</td> 
   <td>必需。规定要返回的小数位数。</td> 
  </tr> 
 </tbody> 
</table>

1.  存储过程需要用到的模块，如下所示：

（1）使用 DECLARE 关键字来定义变量，其基本语法如下：

DECLARE var_name[,...] type [DEFAULT value]
    ·DECLARE 关键字是用来声明变量的；
    ·var_name 参数是变量的名称，这里可以同时定义多个变量；
    ·type 参数用来指定变量的类型；
    ·DEFAULT value 子句将变量默认值设置为 value，没有使用 DEFAULT 子句时，默认值为 NULL。

（2）使用SELECT …INTO语句对变量进行赋值，该语句在数据库中进行查询，并将得到的结果赋值给变量，其基本语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr 
    ·col_name：要从数据库中查询的列字段名；
    ·var_name：变量名，列字段名按照在列清单和变量清单中的位置对应，将查询得到的值赋给对应位置的变量；
    ·table_expr：SELECT语句中的其余部分，包括可选的FROM子句和WHERE子句。
    ·需要注意的是，在使用SELECT …INTO语句时，变量名不能和数据表中的字段名不能相同，否则会出错。范例语句：

（3） IF语句允许您根据表达式的某个条件或值结果来执行一组SQL语句，具体语句如下所示：

###1、单纯的if判断
    IF expression THEN 
       statements;
    END IF;
    ###2、if/else判断
    IF expression THEN
       statements;
    ELSE
       else-statements;
    END IF;
    ###3、if/elseif/else判断
    IF expression THEN
       statements;
    ELSEIF elseif-expression THEN
       elseif-statements;
    ...
    ELSE
       else-statements;
    END IF;

（4）INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

INSERT INTO table2
    (column_name(s))
    SELECT column_name(s)
    FROM table1;

（5）MySQL存储过程的语句中有三个标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。几个循环语句的格式如下：

WHILE……DO……END WHILE
    REPEAT……UNTIL END REPEAT
    LOOP……END LOOP
    GOTO（不建议使用）

第一种循环的小案例：

mysql> create procedure pro10()
        -> begin
        -> declare i int;
        -> set i=0;
        -> while i<5 do
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        -> end while;
        -> end;//

第二种循环的小案例：

mysql> create procedure pro11()
        -> begin
        -> declare i int default 0;
        -> repeat
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     until i>=5
        -> end repeat;
        -> end;//

第三种循环的小案例：

mysql> create procedure pro12()
        -> begin
        -> declare i int default 0;
        -> loop_label: loop
        ->     insert into t1(filed) values(i);
        ->     set i=i+1;
        ->     if i>=5 then
        ->         leave loop_label;
        ->     end if;
        -> end loop;
        -> end;//

**三、实现思想**  
（1）统计出来地级市表的所有数据量

SELECT 
     count(*) as idx 
    FROM GR_AREA_CITY INTO v_idx;

（2）然后根据对象id = 数据量，来查询相应的数据

-------------------------
     SET v_idx = v_idx - 1;  
     ------------------------
     SELECT 
       s.prov_id,
       s.city_id,
       s.name,
       s.lon,
       s.lat
      FROM GR_AREA_CITY s
      WHERE s.object_id = v_idx
      INTO v_prov_id,v_city_id,v_name,v_lon,v_lat;

（3）根据查出来的数据，插入到新的表中

INSERT INTO GR_AREA_DIST(prov_id_1,city_id_1,name1,lon1,lat1,prov_id_2,city_id_2,name2,lon2,lat2,distance) 
    SELECT
        s1.prov_id as prov_id_1,
        s1.city_id as city_id_1,
        s1.name as name1,
        s1.lon as lon1,
        s1.lat as lat1,
        v_prov_id as prov_id_2, 
        v_city_id as city_id_2,  
        v_name as name2,
        v_lon as lon2,
        v_lat as lat2,
        round((st_distance(point(s1.lon, s1.lat), point(v_lon, v_lat)) / 0.0111)) AS distance
    FROM GR_AREA_CITY s1;

**四、实现好的存储过程**

CREATE DEFINER=`root`@`%` PROCEDURE `P_calc_dist_1`()
    BEGIN
     DECLARE
            v_prov_id VARCHAR(10);
     DECLARE
            v_city_id VARCHAR(10);
       DECLARE
            v_name VARCHAR(255);
          DECLARE
            v_lon VARCHAR(40);
          DECLARE
            v_lat VARCHAR(40);
          DECLARE v_idx INT;
         SELECT 
         count(*) as idx 
         FROM GR_AREA_CITY INTO v_idx;
       SELECT v_idx;
    loop_label: loop
     IF (v_idx > 0) THEN
       SELECT 
       s.prov_id,
      s.city_id,
      s.name,
        s.lon,
         s.lat
         FROM GR_AREA_CITY s
       WHERE s.object_id = v_idx
         INTO v_prov_id,v_city_id,v_name,v_lon,
       v_lat;
      #SELECT v_lon,v_lat;
     INSERT INTO GR_AREA_DIST(prov_id_1,city_id_1,name1,lon1,lat1,prov_id_2,city_id_2,name2,lon2,lat2,distance) 
           SELECT
        s1.prov_id as prov_id_1,
        s1.city_id as city_id_1,
            s1.name as name1,
                   s1.lon as lon1,
                   s1.lat as lat1,
        v_prov_id as prov_id_2, 
          v_city_id as city_id_2,  
            v_name as name2,
                   v_lon as lon2,
                   v_lat as lat2,
                   round((st_distance(point(s1.lon, s1.lat), point(v_lon, v_lat)) / 0.0111)) AS distance
            FROM GR_AREA_CITY s1;
     ELSE
         SELECT '数据库表已没有数据供计算';
         leave loop_label;
     END IF;
     SET v_idx = v_idx - 1;    
    end loop;
    END

**五、整理好的内容展示**  
![在这里插入图片描述][20210131145207439.png]

[20210131145207439.png]: /images/20221024/cc01c670f0074e6ba63844af1907ba00.png