地理的位置地理処理のための mysql 関数の詳細な紹介 (コード付き)

この記事では、地理的位置情報を処理するための mysql 関数 (コード付き) を詳しく紹介します。これには一定の参考値があります。必要な友人は参照できます。お役に立てれば幸いです。

現在、LBS、近くの人、テイクアウトの場所、近くのビジネスなどに基づいたビジネスが増えています。ここでは、私に最も近いビジネス シナリオのソリューションについて説明します。

現在知られている解決策は次のとおりです。

mysql カスタム関数の計算 mysql geo インデックス mongodb geo インデックス pos​​tgresql PostGis インデックス redis geoElasticSearch

この記事では、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 個のスレッドで 40,000 個のデータを挿入します。実行には 150.18 秒かかり、合計 400,000 個のデータが挿入されました

テスト

• テスト環境

システム: mac os

メモリ：16G

cpu: intel core i5

ハードディスク: 500g ソリッド ステート ドライブ

テスト中、座標点に最も近い 10 台のドライバーを見つけます。 (134.38753、18.56734)

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

• 所要時間: 18.0秒
• 説明: フル テーブル スキャン

10,000 間隔で 10,000 から 100,000 までテストしました。 100,000 から 90 まで 100,000 テストごとの結果の変化

##結論

このソリューションでは、データ量が次の値に達すると、クエリに 1 回以上かかります30,000 秒
• 追加エントリが約 10,000 件ごとに、消費時間は 0.4 秒増加します

以上が地理的位置地理処理のための mysql 関数の詳細な紹介 (コード付き)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。