一、需求概述
- 通过各个地级市中心点经纬度,计算出全国各地级市之间的距离。
- 最后需整理出来的表字段需要提供以下内容,包括:
(1)省份编码、城市编码、城市名称、经度、纬度、距离等四种类型字段。
二、实现步骤
- 实现手段
使用mysql存储过程来完成本次的需求内容。 - 距离计算方法
使用mysql自带的计算地理距离的函数:ST_Distance(g1, g2 [, unit])以及ROUND() 函数(把数值字段舍入为指定的小数位数)
SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name;
参数 | 描述 |
---|---|
column_name | 必需。要舍入的字段。 |
decimals | 必需。规定要返回的小数位数。 |
- 存储过程需要用到的模块,如下所示:
(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
五、整理好的内容展示