首页 > 数据库 >SQL Server根据地图坐标经纬度计算距离

SQL Server根据地图坐标经纬度计算距离

时间：2022-12-11 23:35:07浏览次数：72  
标签：REAL distance 经纬度 Server SQL lat myLocation lng dis


实战1-亲测,返回结果是米（m）

如果嫌麻烦直接跳转至：“参考方案一”

注意：该方法仅支持 SQL Server 2008 和该版本以上的数据库 

1、准备-工具

百度坐标拾取器：

​​http://api.map.baidu.com/lbsapi/getpoint/index.html​​

百度地图：​​https://map.baidu.com/​​

2、准备-SQL语句，新增一张表

USE [DBTase]
GO
/****** Object:  Table [dbo].[distance_lat_lng]    Script Date: 2021/11/4 16:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[distance_lat_lng](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nchar](50) NOT NULL,
  [lng] [real] NOT NULL,
  [lat] [real] NOT NULL,
 CONSTRAINT [PK_distance_lat_lng] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[distance_lat_lng] ON 

GO
INSERT [dbo].[distance_lat_lng] ([id], [Name], [lng], [lat]) VALUES (1, N'河南青少年中心', 113.694893, 34.78782)
GO
INSERT [dbo].[distance_lat_lng] ([id], [Name], [lng], [lat]) VALUES (2, N'财源大厦', 113.693436, 34.78596)
GO
INSERT [dbo].[distance_lat_lng] ([id], [Name], [lng], [lat]) VALUES (5, N'我的位置【中原油气】公司宿舍', 113.695381, 34.7867737)
GO
SET IDENTITY_INSERT [dbo].[distance_lat_lng] OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'经度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'distance_lat_lng', @level2type=N'COLUMN',@level2name=N'lng'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'纬度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'distance_lat_lng', @level2type=N'COLUMN',@level2name=N'lat'
GO

3、基础测试：

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(113.694893 34.78782)', 4326);
SET @h = geography::STGeomFromText('POINT(113.693436 34.78596)', 4326);
SELECT @g.STDistance(@h);

结果

245.681793435016

根据下面“图一”和“图二”的测距结果，和sql结果对比，得出的距离是米，距离范围相差不多

图一

SQL Server根据地图坐标经纬度计算距离_sqlserver

图二

4、创建一个“标量函数”

CREATE FUNCTION fun_Get_distance_lat_lng_1
(
 @destination_lng varchar(30),
 @destination_lat varchar(30),
 @myLocation_lng varchar(30),
 @myLocation_lat varchar(30)
)
RETURNS varchar(30) AS 

-- @destination_lng 目标经度
-- @destination_lat 目标纬度
-- @myLocation_lng  我的经度
-- @myLocation_lat  我的纬度

BEGIN
  declare @distance varchar(30);--返回值
  declare @destination geography,@myLocation geography;
  --@destination 目标位置
  --@myLocation 我的位置
  --@distance 距离
  SET @destination = geography::STGeomFromText('POINT(' +@destination_lng+ ' ' + @destination_lat +')', 4326);
  SET @myLocation = geography::STGeomFromText('POINT(' + @myLocation_lng+ ' ' + @myLocation_lat+')', 4326);
  SET @distance=cast(@destination.STDistance(@myLocation) as varchar(30));
 
  return @distance
END

不知道标量函数的可以看下面的文章 

​​sql server 中的表值

SQL Server根据地图坐标经纬度计算距离_数据库_02

 这个就是刚才新建的“标量函数”

5、调用“标量函数”

select dbo.fun_Get_distance_lat_lng_1('113.694893','34.78782','113.693436','34.78596')

 结果

245.682

 根据“图一”和“图二”的测距结果，和sql结果对比，得出的距离是米，距离范围相差不多

 6、根据刚才创建的表 批量算距离

select * from [dbo].[distance_lat_lng]
--根据刚才创建的表 批量算距离
declare @myLocation_lng varchar(30), @myLocation_lat varchar(30);
set @myLocation_lng='113.695381';
set @myLocation_lat='34.7867737';
-- @myLocation_lng  我的经度
-- @myLocation_lat  我的纬度

select
 dis.id,
 dis.lng,
 dis.lat,
 dis.Name,
 dbo.fun_Get_distance_lat_lng_1(cast(dis.lng as varchar(30)),cast(dis.lat as varchar(30)),@myLocation_lng,@myLocation_lat) as distance
 from [dbo].[distance_lat_lng] as dis

数据存储的数据

id

Name

lng

lat

1

河南青少年中心                                           

113.694893

34.78782

2

财源大厦                                              

113.693436

34.78596

5

我的位置【中原油气】公司宿舍                                    

113.695381

34.7867737

执行结果 

id

lng

lat

Name

distance（距离）

1

113.694893

34.78782

河南青少年中心                                           

124.37

2

113.693436

34.78596

财源大厦                                              

199.595

5

113.695381

34.7867737

我的位置【中原油气】公司宿舍                                    

0

 根据“图三”和“图四”的测距结果，和sql结果对比，得出的距离是米，距离范围相差不多

SQL Server根据地图坐标经纬度计算距离_sql_03

图三

SQL Server根据地图坐标经纬度计算距离_sqlserver_04

图四

7、跟坐标距离小于5公里的数据

select
 dis.id,
 dis.lng,
 dis.lat,
 dis.Name,
 dbo.fun_Get_distance_lat_lng_1(cast(dis.lng as varchar(30)),cast(dis.lat as varchar(30)),@myLocation_lng,@myLocation_lat) as distance
 from [dbo].[distance_lat_lng] as dis  WHERE dbo.fnGetDistance(cast(dis.lat as REAL),cast(dis.lng as REAL),@myLocation_lat,@myLocation_lng) < 5000

 结果

id

lng

lat

Name

distance

1

113.694893

34.78782

河南青少年中心                                           

0.124602102

2

113.693436

34.78596

财源大厦                                              

0.199557602

5

113.695381

34.7867737

我的位置【中原油气】公司宿舍                                    

0

实战2-亲测-返回结果是公里（km）


1、创建一个“标量函数”

CREATE FUNCTION [dbo].[fnGetDistance](@LatBegin REAL, @LngBegin REAL, @LatEnd REAL, @LngEnd REAL) RETURNS FLOAT
  AS
BEGIN
  --距离(千米)
  DECLARE @Distance REAL
  DECLARE @EARTH_RADIUS REAL
  SET @EARTH_RADIUS = 6378.137  
  DECLARE @RadLatBegin REAL,@RadLatEnd REAL,@RadLatDiff REAL,@RadLngDiff REAL
  SET @RadLatBegin = @LatBegin *PI()/180.0  
  SET @RadLatEnd = @LatEnd *PI()/180.0  
  SET @RadLatDiff = @RadLatBegin - @RadLatEnd  
  SET @RadLngDiff = @LngBegin *PI()/180.0 - @LngEnd *PI()/180.0   
  SET @Distance = 2 *ASIN(SQRT(POWER(SIN(@RadLatDiff/2), 2)+COS(@RadLatBegin)*COS(@RadLatEnd)*POWER(SIN(@RadLngDiff/2), 2)))
  SET @Distance = @Distance * @EARTH_RADIUS  
  --SET @Distance = Round(@Distance * 10000) / 10000  
  RETURN @Distance
END

不知道标量函数的可以看下面的文章 

​​sql server 中的表值函数

SQL Server根据地图坐标经纬度计算距离_数据库_05

 2、根据“实战1”创建的表 批量算距离 -返回结果是公里（km）

--根据刚才创建的表 批量算距离
declare @myLocation_lng REAL, @myLocation_lat REAL;
set @myLocation_lng=113.695381;
set @myLocation_lat=34.7867737;
-- @myLocation_lng  我的经度
-- @myLocation_lat  我的纬度

select
 dis.id,
 dis.lng,
 dis.lat,
 dis.Name,
 dbo.fnGetDistance(cast(dis.lat as REAL),cast(dis.lng as REAL),@myLocation_lat,@myLocation_lng) as distance
 from [dbo].[distance_lat_lng] as dis

结果 

id

lng

lat

Name

distance

1

113.694893

34.78782

河南青少年中心                                           

0.124602102

2

113.693436

34.78596

财源大厦                                              

0.199557602

5

113.695381

34.7867737

我的位置【中原油气】公司宿舍                                    

0

这个结果和“实战1”的结果一模一样，唯一不一样的返回是公里（km）

3、跟坐标距离小于5公里的数据  

--跟坐标距离小于5公里的数据
SELECT * FROM [distance_lat_lng] as dis WHERE dbo.fnGetDistance(cast(dis.lat as REAL),cast(dis.lng as REAL),@myLocation_lat,@myLocation_lng) < 5

结果 

id

Name

lng

lat

1

河南青少年中心                                           

113.694893

34.78782

2

财源大厦                                              

113.693436

34.78596

5

我的位置【中原油气】公司宿舍                                    

113.695381

34.7867737

计算两点距离，4326是空间引用标识符，4326代表GCS-WGS-1984坐标系，是系统默认的坐标系。

地理坐标系空间需要用geography ，平面坐标系空间用geometry，计算距离使用STDistance

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(104.12765 31.61)', 4326);
SET @h = geography::STGeomFromText('POINT(114.132179 22.547010)', 4326);
SELECT @g.STDistance(@h);

查询某点经纬度距离1000米范围内的记录

SELECT * FROM [GpsData] WHERE 
geography::STGeomFromText('POINT(' + cast([Longitude] as varchar(20)) + ' ' 
+ cast([Latitude] as varchar(20)) +')', 4326).STDistance(
geography::STGeomFromText('POINT(114.132179 22.547010)', 4326))<1000

STDistance也可以计算点到面的最短距离。        

利用的是 geography::STPointFromText()。

第一个参数 POINT() 里面再跟经度、纬度。

第二个参数 4326 代表 GCS-WGS-1984 坐标系。具体本文后面会列出相关链接。

参考：

空间引用标识符

​​https://technet.microsoft.com/zh-cn/library/bb964707(v=sql.105)​​

STDistance（geometry 数据类型）
​​​ https://msdn.microsoft.com/zh-cn/library/bb933952(v=sql.100).aspx​​

STDistance的使用及geometry和geography的区别

参考方案二

​​SQL SERVER 根据地图经纬度计算距离函数 - 深南大道 - 博客园前些天客户提出一个这样的要求：一个手机订餐网，查询当前所在位置的5公里范围的酒店，然后客户好去吃饭。 拿到这个请求后，不知道如何下手，静静地想了一下，在酒店的表中增加两个字段，用来存储酒店所在的经

前些天客户提出一个这样的要求：一个手机订餐网，查询当前所在位置的5公里范围的酒店，然后客户好去吃饭。
拿到这个请求后，不知道如何下手，静静地想了一下，在酒店的表中增加两个字段，用来存储酒店所在的经度和纬度，当订餐的时候，要求手机得到当前客户所在的经度和纬度传过来，再与数据库中酒店的经度和纬度计算一下，就查出来。

为了在数据库中查询两点之间的距离，所以这个函数需要在数据库中定义。

我网上找了很久，却没有找到这个函


--计算地球上两个坐标点（经度，纬度）之间距离sql函数
--作者：lordbaby
--整理：www.aspbc.com 
CREATE FUNCTION [dbo].[fnGetDistance](@LatBegin REAL, @LngBegin REAL, @LatEnd REAL, @LngEnd REAL) RETURNS FLOAT
  AS
BEGIN
  --距离(千米)
  DECLARE @Distance REAL
  DECLARE @EARTH_RADIUS REAL
  SET @EARTH_RADIUS = 6378.137  
  DECLARE @RadLatBegin REAL,@RadLatEnd REAL,@RadLatDiff REAL,@RadLngDiff REAL
  SET @RadLatBegin = @LatBegin *PI()/180.0  
  SET @RadLatEnd = @LatEnd *PI()/180.0  
  SET @RadLatDiff = @RadLatBegin - @RadLatEnd  
  SET @RadLngDiff = @LngBegin *PI()/180.0 - @LngEnd *PI()/180.0   
  SET @Distance = 2 *ASIN(SQRT(POWER(SIN(@RadLatDiff/2), 2)+COS(@RadLatBegin)*COS(@RadLatEnd)*POWER(SIN(@RadLngDiff/2), 2)))
  SET @Distance = @Distance * @EARTH_RADIUS  
  --SET @Distance = Round(@Distance * 10000) / 10000  
  RETURN @Distance
END



--经度 Longitude 简写Lng,  纬度 Latitude 简写Lat
--跟坐标距离小于5公里的数据
SELECT * FROM 商家表名 WHERE dbo.fnGetDistance(121.4625,31.220937,longitude,latitude) < 5
 
这里的longitude,latitude分别是酒店的经度和纬度字段，而121.4625,31.220937是手机得到的当前客户所在的经度，后面的5表示5公里范围之内。
JS版本
 
function toRadians(degree) {
  return degree * Math.PI / 180;
}
function distance(latitude1, longitude1, latitude2, longitude2) {
  // R is the radius of the earth in kilometers
  var R = 6371;
  var deltaLatitude = toRadians(latitude2-latitude1);
  var deltaLongitude = toRadians(longitude2-longitude1);
  latitude1 =toRadians(latitude1);
  latitude2 =toRadians(latitude2);
  var a = Math.sin(deltaLatitude/2) *
  Math.sin(deltaLatitude/2) +
  Math.cos(latitude1) *
  Math.cos(latitude2) *
  Math.sin(deltaLongitude/2) *
  Math.sin(deltaLongitude/2);
  var c = 2 * Math.atan2(Math.sqrt(a),
  Math.sqrt(1-a));
  var d = R * c;
  return d;
}


标签：REAL,distance,经纬度,Server,SQL,lat,myLocation,lng,dis
From： https://blog.51cto.com/cplvfx/5928921

相关文章

  • MySQL
    MySQL连接的使用在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。本章节我们将向大家介绍如何使用......
  • SQL Server添加序号列
    https://www.cnblogs.com/cailinaaa/archive/2006/10/20/534988.htmlSQLServer添加序号列 selectROW_NUMBER()OVER(ORDERBYConvey_Feedesc)asz,rank()OVER(OR......
  • windows下QT5.9连接MYSQL
    首先，按照教程尝试连接数据库：QSqlDatabase:availabledrivers:QSQLITEQMYSQLQMYSQL3QODBCQODBC3QPSQLQPSQL7连接失败意思大概就是：“QMYSQL驱动加载失败”......
  • 《MySQL必知必会》之快速入门游标和触发器
    第二十四章使用游标本章将介绍什么是游标以及如何使用游标游标之前的select语句检索出来的数据，没有办法得到第一行或者下一行有时，需要在检索出来的行中前进或后退一行......
  • [BUUCTF][WEB][极客大挑战 2019]BabySQL 1
    靶机打开url界面上显示，它做了更严格的过滤。看来后台是加了什么过滤逻辑老规矩先尝试时候有sql注入的可能，密码框输入123'爆出sql错误信息，说明有注入点构造万能密码注......
  • MAYSQL 2 DAY
    MySQL1、关于查询结果集的去重？mysql>selectdistinctjobfromemp;//distinct关键字去除重复记录。+-----------+|job|+-----------+|CLERK||SA......
  • 静态文件相关配置、request请求方法、pycharm连接MySQL、orm
    静态文件相关配置、request请求方法、pycharm连接MySQL、orm风得意马蹄疾，一日看尽长安花一、静态文件相关配置1.编写一个登录功能1.创建一个django项目并创建一个app......
  • SQL_2_查询语句
     selcet语句：指定要查看的列（字段）信息，设计结果集样式，可以使用*表示显示所有列信息。selectdistinct'id='||id,name,ageas'年龄'1、列可以进行运算。2、......
  • Ubuntu20.04安装python3-pip后安装sqlalchemy报错AttributeError: module 'platform'
    解决方法如下：1.首先卸载已经安装python3-pipsudoaptremovepython3-pip2.安装python3.8-pipsudopython3.8-measy_installpip3.安装sqlalchemywang@wang:~$sudopip3.......
  • mysql安装
    1.要看linux中有没有安装，如果有就删除rpm-qa|grepmysqlrpm-qa|grepmariadb  (自带的会和mysql冲突)rpm-e--nodeps 软件名称    （卸载软件）2.安装r......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99