地理位置geo处理之mysql函数

qiangdada 发布于2019-08-01 10:40 / 1131人阅读

摘要：目前越来越多的业务都会基于，附近的人，外卖位置，附近商家等等，现就讨论离我最近这一业务场景的解决方案。大概度差距米创建日志对象开启线程以上脚本创建个线程，个线程插入万条数据。

目前越来越多的业务都会基于LBS，附近的人，外卖位置，附近商家等等，现就讨论离我最近这一业务场景的解决方案。

目前已知解决方案有:

mysql 自定义函数计算

mysql geo索引

mongodb geo索引

postgresql PostGis索引

redis geo

ElasticSearch

本文测试下mysql 函数运算的性能

准备工作 创建数据表

CREATE TABLE `driver` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lng` float DEFAULT NULL,
  `lat` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建测试数据

在创建数据之前先了解下基本的地理知识:

全球经纬度的取值范围为: 纬度-90~90，经度-180~180

中国的经纬度范围大约为： 纬度3.86~53.55，经度73.66~135.05

北京行政中心的纬度为39.92，经度为116.46

越北面的地方纬度数值越大，越东面的地方经度数值越大

度分转换：将度分单位数据转换为度单位数据，公式：度=度+分/60

分秒转换：将度分秒单位数据转换为度单位数据，公式：度 = 度 + 分 / 60 + 秒 / 60 / 60

在纬度相等的情况下：

经度每隔0.00001度，距离相差约1米

在经度相等的情况下：

纬度每隔0.00001度，距离相差约1.1米

mysql函数计算

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `getDistance`(
    `lng1` float(10,7) 
    ,
    `lat1` float(10,7)
    ,
    `lng2` float(10,7) 
    ,
    `lat2` float(10,7)

) RETURNS double
    COMMENT "计算2坐标点距离"
BEGIN
    declare d double;
    declare radius int;
    set radius = 6371000; #假设地球为正球形，直径为6371000米
    set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2)   
        *SIN((lat1-lat2)*PI()/180/2)+   
        COS(lat2*PI()/180)*COS(lat1*PI()/180)   
        *SIN((lng1-lng2)*PI()/180/2)   
        *SIN((lng1-lng2)*PI()/180/2)),   
        SQRT(1-SIN((lat1-lat2)*PI()/180/2)   
        *SIN((lat1-lat2)*PI()/180/2)   
        +COS(lat2*PI()/180)*COS(lat1*PI()/180)   
        *SIN((lng1-lng2)*PI()/180/2)   
        *SIN((lng1-lng2)*PI()/180/2))))*radius;
    return d;
END//
DELIMITER ;

创建数据python脚本

# coding=utf-8
from orator import DatabaseManager, Model
import logging
import random
import threading

""" 中国的经纬度范围 纬度3.86~53.55，经度73.66~135.05。大概0.00001度差距1米 """

# 创建 日志 对象
logger = logging.getLogger()
handler = logging.StreamHandler()
formatter = logging.Formatter(
    "%(asctime)s %(name)-12s %(levelname)-8s %(message)s")
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.setLevel(logging.DEBUG)

# Connect to the database

config = {
    "mysql": {
        "driver": "mysql",
        "host": "localhost",
        "database": "dbtest",
        "user": "root",
        "password": "",
        "prefix": ""
    }
}

db = DatabaseManager(config)
Model.set_connection_resolver(db)


class Driver(Model):
    __table__ = "driver"
    __timestamps__ = False
    pass


def ins_driver(thread_name,nums):
    logger.info("开启线程%s" % thread_name)
    for _ in range(nums):
        lng = "%.5f" % random.uniform(73.66, 135.05)
        lat = "%.5f" % random.uniform(3.86, 53.55)

        driver = Driver()
        driver.lng = lng
        driver.lat = lat
        driver.save()

thread_nums = 10
for i in range(thread_nums):
    t = threading.Thread(target=ins_driver, args=(i, 400000))
    t.start()

以上脚本创建10个线程，10个线程插入4万条数据。耗费150.18s执行完,总共插入40万条数据

测试

测试环境

系统：mac os

内存：16G

cpu: intel core i5

硬盘: 500g 固态硬盘

测试下查找距离(134.38753,18.56734)这个坐标点最近的10个司机

select *,`getDistance`(134.38753,18.56734,`lng`,`lat`) as dis from driver ORDER BY dis limit 10

耗时：18.0s

explain:全表扫描

我测试了从1万到10万间隔1万和从10万到90万每间隔10万测试的结果变化

结论

此方案在数据量达到3万条查询耗时就会超过1秒

大约每增加1万条就会增加0.4秒的耗时

云数据库MySQL js 函数位置函数处理数据 php文件处理函数 java异常处理函数

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48601.html

地理位置geo处理之mysql函数

摘要：目前越来越多的业务都会基于，附近的人，外卖位置，附近商家等等，现就讨论离我最近这一业务场景的解决方案。大概度差距米创建日志对象开启线程以上脚本创建个线程，个线程插入万条数据。目前越来越多的业务都会基于LBS，附近的人，外卖位置，附近商家等等，现就讨论离我最近这一业务场景的解决方案。目前已知解决方案有: mysql 自定义函数计算 mysql geo索引 mongodb geo索引...

Pines_Cheng 2019-07-31 10:09 评论0 收藏0
【redis专题(10)】命令语法介绍之GEO

摘要：获取指定元素范围的地理信息位置集合使用命令即可查询附近的位置例如查找距离杭州以内的城市的个城市按距离排序在返回位置元素的同时，将位置元素与中心之间的距离也一并返回。距离的单位和用户给定的范围单位保持一致。简述移动互联网时代LBS应用越来越多，交友软件中附近的小姐姐、外卖软件中附近的美食店铺、打车软件附近的车辆等等，那这种附近各种形形色色的XX是如何实现的呢我么你都知道地球上的地理...

BicycleWarrior 2019-07-24 16:33 评论0 收藏0
【戴嘉乐】基于IPFS和GeoHash构建具有地理位置价值服务的DDApp（理论篇）

摘要：数据将具有如下个特点将二维的经纬度转换成字符串，比如下图展示了北京个区域的字符串，分别是，等等，每一个字符串代表了某一矩形区域。例如，坐标对，位于北京安定门附近，后形成的值为。作者简介：戴嘉乐( Mr.Maple ) | 前百度高级研发工程师 | IPFS应用实践者&布道师|个人网站：https://www.daijiale.cn联系方式：微信号：daijiale6239。 show...

lmxdawn 2019-06-27 18:54 评论0 收藏0
Redis GEO 特性在 LBS 中的应用总结

摘要：的是版本的新特性。在处理社交这种关系复杂的数据存储时，依然还是需要用这种关系型数据库，并不能完全替代。根据给定地理位置获取指定范围内的地理位置集合。表示单位为英里。传入参数，可以返回指定数量的结果。什么是LBS LBS(Location Based Service)，基于位置的服务。 Redis和GEO Redis 是最热门的 nosql 数据库之一，它的最大特点就是快。所以在 LB...

EscapedDog 2019-07-24 15:02 评论0 收藏0
Docker监控及日志采集神器

摘要：因此，另一种解决办法像这样的工具，则只是将和进行了结合，其功能尤其关注日志管理，比如格式检查，日志语法分析，数据改进地址地理位置信息，元数据标签等以及日志路由。由Rancher社区维护的应用商店最近迎来了两个明星项目——SPM 和 Logsene，来自Sematext的监控与日志工具。如果你已经熟悉Logstash，Kibana，Prometheus，Grafana这些监控或日志解决...

Tony_Zby 2019-05-29 19:11 评论0 收藏0