本篇内容主要讲解“SQL最近位置查询语句是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“SQL最近位置查询语句是什么”吧!
我已经浪费了太多的时间在寻找定位软件上了,因此这值得我去写下如何去做。当然,在地球表面计算距离意味着计算大圆距离,可以通过半正矢公式计算,也称之为球面余弦定律公式。问题是:
给出一个具有经纬度的位置表,其中哪个位置最靠近给出的定位?
<!--more-->
你是否想问在哪里我可以找到一张具有经纬度的位置表?你可以在互联网上搜索“邮政编码免费下载”或者“免费邮编下载”。然后将其加载到MySQL表中。有很多不同类型的地理数据可以下载,附带经纬度位置。
这是SQL Server数据的美国邮政编码数据包,如果你正好需要的话。
本文中的逻辑适用于MySQL,MariaDB, PostgreSQL,和微软的SQL Server。Oracle的工作方式有一点不同;这里有一篇文章讲述了如何在Oracle中实现。
请慎重的使用邮政编码数据作为确定位置的方法。邮政编码仅被设计用于帮助优化邮政投递。他们的数据用途有限,并且可能带来错误的结果。例如,这是一篇地理学家写的关于美国密西根州弗林特市水危机的文章。在很长一段时间,弗林特市的孩子似乎没有铅中毒,因为研究员只看他们家的邮政编码去找出他们住在哪里。但是他们铅中毒了,别和密歇根州政府犯相同的错误。
纬度和经度用度数表示。纬度描述了一个点在赤道以北或以南的距离。赤道上的点的纬度是零。北极的正(北)纬度为90度,并且南极是负(南)纬度-90度。相应的,北半球的位置有着正纬度,并且南半球的位置有着负的纬度。
经度描述了一个点从本初子午线向东的距离:地球表面从一个点到另一个点的任意直线。位于美国纽约市的帝国大厦的经度为负(西),具体来说为-73.9857。印度阿格拉的泰姬陵经度为正(东经),具体为78.0422。英国伦敦附近的格林威治天文台,根据定义,经度为零。
因此,纬度是范围内的值[-90,90]。经度是范围(-180,180)内的值。这些值有时以度、分和秒表示,而不是以度和小数表示。如果你打算做计算,先把分和秒转换成小数。
在拿破仑时代,米是最早被定义的,所以从赤道到两极有一千万米。原来纬度上的米数是10000000/90或111.111公里。但是地球有点凸起,因此111.045公里/度被认为是一个更好的近似值。
在这里我们为了方便计算,我们假设地球是一个球体。虽然这不是真的。它在赤道上有点凸起,但是定位问题,我们假设是球体就足够了。
这个公式(111.045公里/度)在你向北或者向南移动的时候很好用。如果你在改变你的纬度而不是经度。如果你在向东或者向西移动、在改变你的经度、在赤道上,它也能起作用。但是在赤道的南北边,经度线越来越接近,所以如果你向西或向东移动一个刻度,你移动的距离就会小于111.045千米。当你往东或往西走一度时,你实际移动的距离实际上是公里数。
111.045 * cos(latitude)
我们在一些英国殖民地里使用英里。海里是指纬度的一分钟(1/60度)。所以每度有69法定英里或每度60海里。如果你正在处理这样的应用,如GPS控制耕牛队,你可能会发现它有助于知道有552浪(长度单位,相当于220码、201米或⅛英里)每度。一些以美国为中心的应用程序扰乱了经度。对西半球的位置来说,它们是正的而不是负的。如果你在调试什么东西,要注意这个
任意两点沿(球面)地球表面的距离是多少?用度数表示,用他们的经纬度表示?这是由球余弦定理,或者半正矢公式决定的。这是MySQL语法中的:
DEGREES(ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(long1) - RADIANS(long2)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))
它是地球表面的距离。当这些地方是你的公寓和当地超市,或者是澳大利亚悉尼和冰岛雷克雅未克的机场时,它也同样适用。注意,这个结果是以度为单位的。这意味着如果我们想要以公里为单位的距离,我们必须将它乘以111.045,即每度公里的数值。
请注意MS SQL Server需要使用一个float或double来表示RADIANS。RADIANS(30) 返回的是有问题的值,但是RADIANS(30)能正常工作。一般来说,MS SQL Server不会可靠的强制整integer类型的值转换为float或者double类型,所有请小心,不要在你需要使用float的时候使用integer类型。此外,请记住美国邮政编码虽然看起来像数字,但是其实是字符串。我住的地方邮政编码是'01950',这和1950是不一样的。
为了在数据库中找到与给定点的最近的点,我们可以这样写查询。让我们使用经度为-70.81、纬度为42.81的点。这个MySQL查询按照距离的顺序查找离给定点最近的15个点。 可以在这边测试:http://sqlfiddle.com/#!9/21e06/1
SELECT zip, primary_city, latitude, longitude, 111.045* DEGREES(ACOS(COS(RADIANS(latpoint)) * COS(RADIANS(latitude)) * COS(RADIANS(longpoint) - RADIANS(longitude)) + SIN(RADIANS(latpoint)) * SIN(RADIANS(latitude)))) AS distance_in_km FROM zip JOIN ( SELECT 42.81 AS latpoint, -70.81 AS longpoint ) AS p ON 1=1 ORDER BY distance_in_km LIMIT 15
注意使用连接将latpoint和longpoint放入查询中。这样编写查询很方便,因为公式中多次引用了latpoint和longpoint。(MySQL不需要使用
ON 1=1
,但是PostgreSQL需要) (在SQL Server中, 使用SELECT TOP(15) zip …
来替换LIMIT 15.
)
非常好,我们做到了,对吧?别着急!这个查询虽然是正确的,但是他很慢。
查询速度很慢是因为它必须为每个可能的点对计算半正矢公式。因此,它使你的MySQL服务器做了很多数学运算,并强制它扫描整个位置表。如何优化?如果我们能在表中的纬度和经度列上使用索引,那就太好了。为此,我们引入一个约束。假设我们只关心邮政编码表中距离(latpoint,longpoint)50公里以内的点。让我们找出如何使用索引来消除更远的点。
请记住,根据本文前面的背景信息,纬度是111.045公里。所以,如果纬度列上有一个索引,我们可以使用类似这样的SQL子句来消除太北或太南的点,这些点可能不在50公里之内。
latitude BETWEEN latpoint - (50.0 / 111.045) AND latpoint + (50.0 / 111.045)
这个WHERE
语句允许MySQL在计算半正矢距离公式之前使用索引省略许多纬度点。它允许MySQL对纬度索引执行范围扫描。
最后,我们可以使用一个类似但更复杂的SQL子句来消除太东或太西的点。这个条款更复杂,因为经度是离我们移动的赤道越远的距离越小。请看下面公式:
longitude BETWEEN longpoint - (50.0 / (111.045 * COS(RADIANS(latpoint)))) AND longpoint + (50.0 / (111.045 * COS(RADIANS(latpoint))))
因此,将所有这些放在一起,这个查询将查找(latpoint,longpoint)50公里范围内的最东边15个点。
尽管这个查询有点复杂,但它利用了纬度和经度索引,并且工作效率很高。
请注意,作为整个查询的一部分,我们加入了这个子查询。
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS radius, 111.045 AS distance_unit
这样做的目的是使应用软件更容易提供查询所需的参数。Latpoint和Longpoint是您需要附近位置的特定位置。radius指定搜索应该走多远。最后,如果你想用公里表示距离,距离单位应该是111.045。如果你想用英里表示距离,应该是69.0。
但是,这个边界查询有可能返回距离(latpoint,longpoint)对角线超过50km的一些点:它只检查一个边界矩形,而不是对角线距离。让我们增强查询以消除超过50公里的点。
最后,许多人需要用英里而不是公里来计算他们的距离。这很简单。只需将距离单位的值更改为69.0。
这是一个基于经纬度的典型商店查找程序或位置查找程序的查询。应该能够适应你的使用,没有太多的麻烦。
当然,这个查询是用一个特定的ZIP表定义(一个美国邮政编码表)编写的。该zip
表包含名为zip
、primary_city
、latitude
和longitude
等字段。请注意,该表在查询中由 FROM zip AS z
引用。所以它的别名是z
。
你的位置表很可能有不同的列。重写此查询来适应你的查询应该很简单。在查询中查找称为z.something
的字段,并用表中的字段名替换这些字段。例如,如果你的表名为shop
,并且有shopname
、shoplat
和shoplong
字段,那么你把z.shopname
替换为z.primary_city
,以此类推。你将通过在查询中包含FROM SHOP as z
来引用表。
到此,相信大家对“SQL最近位置查询语句是什么”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。