PHP / MySQL中的地理搜索(距离)(性能)?mysql-问答-阿里云开发者社区-阿里云

开发者社区> 保持可爱mmm> 正文

PHP / MySQL中的地理搜索(距离)(性能)?mysql

2020-05-17 11:58:48 156 1

我有一个MySQL表(MyISAM),其中包含我根据与另一对经纬度对之间的距离(大圆公式)从中选择的约20万经纬度对条目。(例如,在50.281852,2.504883附近的10公里半径内的所有条目)

我的问题是此查询大约需要0.28秒。仅针对这200k条目运行(每天仍在增加)。而0.28秒。通常情况下会很好,此查询会经常运行,因为它会增强我的Web应用程序的主要功能,而且通常是较大查询的一部分。

有什么办法可以加快速度吗?显然,MySQL每次必须遍历所有200k条目,并为每个条目执行大圈公式。我在stackoverflow上阅读了一些有关geohashing,R-Trees之类的内容,但我不认为这是我想要的方式。部分原因是我从来都不是数学的忠实拥护者,但主要是因为我认为这个问题已经由比我聪明的人在图书馆/扩展名等中解决了。已进行了广泛的测试,并定期进行更新。

MySQL似乎具有空间扩展性,但是没有提供距离功能。我是否应该查看另一个数据库来放置这些坐标对?PostgreSQL似乎具有相当成熟的Spatial扩展。你知道吗 还是PostgreSQL是否仅使用大圆公式来获取特定区域内的所有条目?

是否有专门的独立产品或mysql扩展已经可以满足我的需求?

还是有可能我可以使用PHP库进行计算?使用APC,我可以轻松地将经纬度对放入内存(200k条目需要约5MB),然后在PHP内部运行查询。但是,这种方法的问题是,然后我将有一个SELECT .. FROM .. WHERE id在(id1,id2,..)中的MySQL查询,因为所有结果最多可达几千个。MySQL如何处理此类查询?然后(由于这是一个数字运算任务)在PHP中执行此操作是否足够快?

还有其他想法我应该/不应该做什么?

对于completeneses,这里是示例查询,其中删除了所有不相关的部分(如我所说,通常这是我联接多个表的更大查询的一部分):

SELECT id, 6371 * acos( sin( radians( 52.4042924 ) ) * sin( radians( lat ) ) + cos( radians( 50.281852 ) ) * cos( radians( lat ) ) * cos( radians( 2.504883 ) - radians( lon ) ) ) AS dst FROM geoloc HAVING dst <10 ORDER BY dst ASC 谢谢!

取消 提交回答
全部回答(1)
  • 保持可爱mmm
    2020-05-17 12:11:20

    计算边界框以选择SQL查询的WHERE子句中的行的子集,以便仅对该行子集执行昂贵的距离计算,而不是对表中的全部200k记录执行。本文在Movable Type(带有PHP代码示例)中描述了该方法。然后,您可以针对该子集在查询中包含Haversine计算,以计算实际距离,并在该点处将其包含在HAVING子句中。

    这是帮助您提高性能的边界框,因为这意味着您仅对一小部分数据进行了昂贵的距离计算。实际上,这是与Patrick所建议的方法相同的方法,但是Movable Type链接对方法进行了详尽的解释,以及可用于构建边界框和SQL查询的PHP代码。

    编辑

    如果您认为Haversine不够准确,那么还有Vincenty公式。

    // Vincenty formula to calculate great circle distance between 2 locations expressed as Lat/Long in KM

    function VincentyDistance($lat1,$lat2,$lon1,$lon2){ $a = 6378137 - 21 * sin($lat1); $b = 6356752.3142; $f = 1/298.257223563;

    $p1_lat = $lat1/57.29577951;
    $p2_lat = $lat2/57.29577951;
    $p1_lon = $lon1/57.29577951;
    $p2_lon = $lon2/57.29577951;
    
    $L = $p2_lon - $p1_lon;
    
    $U1 = atan((1-$f) * tan($p1_lat));
    $U2 = atan((1-$f) * tan($p2_lat));
    
    $sinU1 = sin($U1);
    $cosU1 = cos($U1);
    $sinU2 = sin($U2);
    $cosU2 = cos($U2);
    
    $lambda = $L;
    $lambdaP = 2*M_PI;
    $iterLimit = 20;
    
    while(abs($lambda-$lambdaP) > 1e-12 && $iterLimit>0) {
        $sinLambda = sin($lambda);
        $cosLambda = cos($lambda);
        $sinSigma = sqrt(($cosU2*$sinLambda) * ($cosU2*$sinLambda) + ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda) * ($cosU1*$sinU2-$sinU1*$cosU2*$cosLambda));
    
        //if ($sinSigma==0){return 0;}  // co-incident points
        $cosSigma = $sinU1*$sinU2 + $cosU1*$cosU2*$cosLambda;
        $sigma = atan2($sinSigma, $cosSigma);
        $alpha = asin($cosU1 * $cosU2 * $sinLambda / $sinSigma);
        $cosSqAlpha = cos($alpha) * cos($alpha);
        $cos2SigmaM = $cosSigma - 2*$sinU1*$sinU2/$cosSqAlpha;
        $C = $f/16*$cosSqAlpha*(4+$f*(4-3*$cosSqAlpha));
        $lambdaP = $lambda;
        $lambda = $L + (1-$C) * $f * sin($alpha) * ($sigma + $C*$sinSigma*($cos2SigmaM+$C*$cosSigma*(-1+2*$cos2SigmaM*$cos2SigmaM)));
    }
    
    $uSq = $cosSqAlpha*($a*$a-$b*$b)/($b*$b);
    $A = 1 + $uSq/16384*(4096+$uSq*(-768+$uSq*(320-175*$uSq)));
    $B = $uSq/1024 * (256+$uSq*(-128+$uSq*(74-47*$uSq)));
    
    $deltaSigma = $B*$sinSigma*($cos2SigmaM+$B/4*($cosSigma*(-1+2*$cos2SigmaM*$cos2SigmaM)- $B/6*$cos2SigmaM*(-3+4*$sinSigma*$sinSigma)*(-3+4*$cos2SigmaM*$cos2SigmaM)));
    
    $s = $b*$A*($sigma-$deltaSigma);
    return $s/1000;
    

    }

    echo VincentyDistance($lat1,$lat2,$lon1,$lon2);来源:stack overflow

    0 0
相关问答

17

回答

【大咖问答】对话PostgreSQL 中国社区发起人之一,阿里云数据库高级专家 德哥

阿里ACE 彭飞 2019-07-10 09:36:10 968521浏览量 回答数 17

162

回答

惊喜翻倍:免费ECS+免费环境配置~!(ECS免费体验6个月活动3月31日结束)

豆妹 2014-10-29 17:52:21 223003浏览量 回答数 162

145

回答

【新手入门】云服务器linux使用手册

fanyue88888 2012-11-26 17:14:18 157085浏览量 回答数 145

8

回答

OceanBase 使用动画(持续更新)

mq4096 2019-02-20 17:16:36 333875浏览量 回答数 8

22

回答

爬虫数据管理【问答合集】

我是管理员 2018-08-10 16:37:41 146784浏览量 回答数 22

2

回答

mySQL数据库报错You have an error in your SQL syntax

落地花开啦 2016-02-14 16:09:24 126511浏览量 回答数 2

38

回答

安全组详解,新手必看教程

我的中国 2017-11-30 15:23:46 256872浏览量 回答数 38

21

回答

请教一下数据量有100万条左右要什么配置?

易网网络 2013-03-27 15:18:02 192338浏览量 回答数 21

249

回答

阿里云LNAMP(Linux + Nginx + Apache + MySQL + PHP)环境一键安装脚本

云代维 2014-02-14 15:26:06 302605浏览量 回答数 249

2

回答

区域选择帮助

fanyue88888 2012-12-07 15:54:30 204008浏览量 回答数 2
66
文章
12595
问答
推荐问答
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载