一、需求概述

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

二、实现步骤

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

参数

描述

column_name

必需。要舍入的字段。

decimals

必需。规定要返回的小数位数。

  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

五、整理好的内容展示

mysql 根据四个坐标获取范围内数据 mysql地理位置计算_mysql 根据四个坐标获取范围内数据