Introduction détaillée de la fonction mysql pour le traitement géographique de la localisation géographique (avec code)

Ce que cet article vous apporte est une introduction détaillée à la fonction mysql pour le traitement géographique de la localisation (avec code). Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Actuellement, de plus en plus d'entreprises sont basées sur LBS, les personnes à proximité, les lieux de plats à emporter, les entreprises à proximité, etc. Nous allons maintenant discuter de la solution pour le scénario commercial le plus proche de moi.

Les solutions actuellement connues sont :

calcul de fonction personnalisée mysql index géo mysql index géo mongodb postgresql index PostGis redis geoElasticSearch

Cet article teste les performances du fonctionnement de la fonction mysql

Travail de préparation

Créer un tableau de données

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;

Créer des données de test

Comprendre les connaissances géographiques de base avant de créer des données :

• La plage de valeurs de La longitude et la latitude mondiales sont : Latitude -90~90, longitude -180~180
• La plage de longitude et de latitude de la Chine est d'environ : Latitude 3,86~53,55, longitude 73,66 ~ 135,05
• La latitude du centre administratif de Pékin est 39,92 et la longitude est 116,46
• Plus on est au nord, plus la valeur de latitude est grande, plus on est à l'est, plus la valeur de longitude est grande
• degrés Conversion des minutes : convertissez les données d'unités de degré et de minute en données d'unité de degré, formule : degré = degré + minute/60
• Conversion de minute et de seconde : convertissez les données d'unité de degré, de minute et de seconde en données d'unité de degré, formule : degré = degré + minutes / 60 + secondes / 60 / 60

Lorsque les latitudes sont égales :

• La longitude est tous les 0,00001 degrés, la distance diffère d'environ 1 mètre

En cas d'égalité de longitude :

• Chaque 0,00001 degré de latitude, la différence de distance est d'environ 1,1 mètre

calcul de la fonction 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 ;

Créer un script python de données

# 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()

Le script ci-dessus crée 10 threads, et 10 threads insèrent 40 000 éléments de données. L'exécution a pris 150,18 secondes et un total de 400 000 éléments de données ont été insérés

Test

• Environnement de test

Système : Mac OS

Mémoire ：16G

CPU : Intel Core i5

Disque dur : disque SSD de 500 g

Sous le test, trouvez les 10 pilotes les plus proches du point de coordonnées (134.38753,18.56734)

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

• Prend du temps : 18,0 s
• expliquer : analyse complète de la table

J'ai testé l'intervalle de 10 000 à 100 000 et l'intervalle de 100 000 à 100 000 Modifications des résultats des tests à des intervalles de 100 000 à 900 000

Conclusion

• Cette solution prendra moins de temps pour interroger lorsque le volume de données atteint 30 000 Plus d'1 seconde
• Environ tous les 10 000 éléments supplémentaires augmenteront le temps de 0,4 seconde

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!