美文网首页
【翻译】SQL最近位置查询语句(MySQL、PostgreSQL

【翻译】SQL最近位置查询语句(MySQL、PostgreSQL

作者: Thans | 来源:发表于2019-10-23 09:47 被阅读0次

    前言

    我已经浪费了太多的时间在寻找定位软件上了,因此这值得我去写下如何去做。当然,在地球表面计算距离意味着计算大圆距离,可以通过半正矢公式计算,也称之为球面余弦定律公式。问题是:

    给出一个具有经纬度的位置表,其中哪个位置最靠近给出的定位?

    位置数据表

    你是否想问在哪里我可以找到一张具有经纬度的位置表?你可以在互联网上搜索“邮政编码免费下载”或者“免费邮编下载”。然后将其加载到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表包含名为zipprimary_citylatitudelongitude等字段。请注意,该表在查询中由FROM zip AS z引用。所以它的别名是z

    你的位置表很可能有不同的列。重写此查询来适应你的查询应该很简单。在查询中查找称为z.something的字段,并用表中的字段名替换这些字段。例如,如果你的表名为shop,并且有shopnameshoplatshoplong字段,那么你把z.shopname替换为z.primary_city,以此类推。你将通过在查询中包含FROM SHOP as z来引用表。

    原文地址:http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc
    博客地址:https://thans.cn

    相关文章

      网友评论

          本文标题:【翻译】SQL最近位置查询语句(MySQL、PostgreSQL

          本文链接:https://www.haomeiwen.com/subject/wwyhvctx.html