地图上根据圆心经纬度、半径创建多边形存储(JS/MYSQL实现)

原创已于 2022-11-11 15:12:34 修改 · 766 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#javascript #mysql #angular.js

于 2022-11-11 08:53:16 首次发布

gis 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了如何使用JavaScript和MySQL根据圆心经纬度和半径创建多边形，以便在MYSQL空间表中存储。通过循环计算每个角度的经纬度，最后将这些点连接成多边形。提供了JS代码示例和MYSQL存储过程的实现。

前提：

开发中遇到一个问题。前端创建一个圆。要把经纬度保存在MYSQL空间表中。但是空间数据库只支持点线面。自己上网搜了下。可以根据-180到180度循环。每几个度数，有一个经纬度，创建一个多边形。实现代码JS和MYSQL如下

<!--

* @Description:

* @Version: 1.0

* @Autor: zhouwei

* @Date: 2022-11-10 15:11:33

* @LastEditors: zhouwei

* @LastEditTime: 2022-11-10 16:11:14

-->

<!DOCTYPE html>

<html>

<head>

<title>圆转多边形</title>

</head>

<body>

var ss = [];

function createCoord(coord, bearing, distance)

{

/** http://www.movable-type.co.uk/scripts/latlong.html

φ is latitude, λ is longitude,

θ is the bearing (clockwise from north),

δ is the angular distance d/R;

d being the distance travelled, R the earth’s radius*

**/

var

radius = 6371e3, //meters

δ = Number(distance) / radius, // angular distance in radians

θ = Number(bearing).toRad();

φ1 = coord[1].toRad(),

λ1 = coord[0].toRad();

var φ2 = Math.asin(Math.sin(φ1)*Math.cos(δ) + Math.cos(φ1)*Math.sin(δ)*Math.cos(θ));

var λ2 = λ1 + Math.atan2(Math.sin(θ)*Math.sin(δ)*Math.cos(φ1), Math.cos(δ)-Math.sin(φ1)*Math.sin(φ2));

λ2 = (λ2+3*Math.PI) % (2*Math.PI) - Math.PI; // normalise to -180..+180°

console.log(λ2.toDeg(), φ2.toDeg());

ss.push([λ2.toDeg(), φ2.toDeg()]);

return [λ2.toDeg(), φ2.toDeg()]; //[lon, lat]

}

Number.prototype.toDeg = function() { return this * 180 / Math.PI; }

Number.prototype.toRad = function() { return this * Math.PI / 180;}

window.onload = function(){

for(var i = -180; i<=180; i=i+10){

createCoord([120.794478,14.599962], i, 8193);

}

</script>

</body>

</html>

MYSQL代码

set @radius = 6371e3;
set @δ =8193/@radius;
set @θ = -180*PI() / 180;
set @φ1 = 14.599962*PI()/180;
set @λ1 = 120.794478*PI()/180;
           set @φ2 = asin(sin(@φ1)*cos(@δ) + cos(@φ1)*sin(@δ)*cos(@θ));
           set @λ2 = @λ1 + atan2(sin(@θ)*sin(@δ)*cos(@φ1), cos(@δ)-sin(@φ1)*sin(@φ2));
           set @λ2 = (@λ2+3*PI()) % (2*PI()) - PI();
           set @φ2 = @φ2* 180 / PI();
           set @λ2 = @λ2* 180 / PI();
           SELECT @φ2,@λ2;

根据经纬度。半径。点数画多边形存储过程

CREATE DEFINER=`admin01`@`%` PROCEDURE `create_polygonWtihCircle`(IN log FLOAT8,IN lat FLOAT8,IN distance FLOAT8,IN count INT)
BEGIN
DECLARE n FLOAT8 DEFAULT 0;
DECLARE i FLOAT8 DEFAULT 0;
SET i = -180.0;
set n = 180.0;
set @strText = 'POLYGON((';
WHILE i<=n DO
           set @radius = 6371e3;
set @δ =8193/@radius;
set @θ = i*PI() / 180;
set @φ1 = lat*PI()/180;
set @λ1 = log*PI()/180;
           set @φ2 = asin(sin(@φ1)*cos(@δ) + cos(@φ1)*sin(@δ)*cos(@θ));
           set @λ2 = @λ1 + atan2(sin(@θ)*sin(@δ)*cos(@φ1), cos(@δ)-sin(@φ1)*sin(@φ2));
           set @λ2 = (@λ2+3*PI())%(2*PI())-PI();
           set @φ2 = @φ2* 180 / PI();
           set @λ2 = @λ2* 180 / PI();
           /*SELECT @λ2,@φ2;*/
           set @strText = concat(@strText,@λ2,' ',@φ2,',');
           SET i = i + 360.0/count;
END WHILE;
set @strText = LEFT(@strText,char_length(@strText)-1);
set @strText = concat(@strText,'))');
SELECT @strText;
End

执行存储过程

call create_polygonWtihCircle(120.794478,14.599962,8193,36);

返回结果字符串

POLYGON((120.79447800000001 14.526280580827075,120.78126077542778 14.527399595755407,120.76844475757211 14.530722673219863,120.7564190097302 14.536148939340919,120.7455486717201 14.54351366716311,120.73616389616127 14.552593264128953,120.72854983355164 14.563112044104733,120.72293796842497 14.574750580784821,120.7194990697484 14.587155391435454,120.71833797163649 14.599949659371388,120.71949034658844 14.612744671537296,120.72292157424405 14.625151625113071,120.72852774572596 14.63679344494596,120.73613877880494 14.6473162523065,120.74552355435561 14.656400135127688,120.75639692188373 14.663768890338382,120.76842836336769 14.669198439617977,120.78125205225237 14.672523660012624,120.79447800000001 14.673643419172919,120.80770394774756 14.672523660012624,120.82052763663233 14.669198439617977,120.83255907811629 14.663768890338382,120.84343244564441 14.656400135127688,120.852817221195 14.6473162523065,120.86042825427397 14.63679344494596,120.86603442575587 14.625151625113071,120.86946565341158 14.612744671537296,120.87061802836344 14.599949659371388,120.86945693025153 14.587155391435454,120.86601803157495 14.574750580784821,120.86040616644829 14.563112044104733,120.85279210383875 14.552593264128953,120.84340732827982 14.54351366716311,120.83253699026984 14.536148939340919,120.82051124242781 14.530722673219863,120.80769522457213 14.527399595755407,120.79447800000001 14.526280580827075))

创建方法。前端船值过来。直接插入数据库

SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION polygonWithCircle
create FUNCTION polygonWithCircle(log FLOAT8,lat FLOAT8,distance FLOAT8,count INT) RETURNS VARCHAR(16383)
BEGIN
DECLARE n FLOAT8 DEFAULT 0;
DECLARE i FLOAT8 DEFAULT 0;
DECLARE strText VARCHAR(16383);
SET i = -180.0;
set n = 180.0;
set strText = 'POLYGON((';
WHILE i<=n DO
           set @radius = 6371e3;
set @δ =8193/@radius;
set @θ = i*PI() / 180;
set @φ1 = lat*PI()/180;
set @λ1 = log*PI()/180;
           set @φ2 = asin(sin(@φ1)*cos(@δ) + cos(@φ1)*sin(@δ)*cos(@θ));
           set @λ2 = @λ1 + atan2(sin(@θ)*sin(@δ)*cos(@φ1), cos(@δ)-sin(@φ1)*sin(@φ2));
           set @λ2 = (@λ2+3*PI())%(2*PI())-PI();
           set @φ2 = @φ2* 180 / PI();
           set @λ2 = @λ2* 180 / PI();
           /*SELECT @λ2,@φ2;*/
           set strText = concat(strText,@λ2,' ',@φ2,',');
           SET i = i + 360.0/count;
END WHILE;
set strText = LEFT(strText,char_length(strText)-1);
set strText = concat(strText,'))');
RETURN strText;
END;