Detailed introduction of mysql function for geographical location geo processing (with code)

This article brings you a detailed introduction to the mysql function for geographical location geo processing (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Currently, more and more businesses are based on LBS, nearby people, takeout locations, nearby businesses, etc. Now we will discuss the solution for the business scenario closest to me.

Currently known solutions are:

mysql custom function calculation mysql geo index mongodb geo index postgresql PostGis index redis geoElasticSearch

This article tests the performance of mysql function operation

Preparation Work

Create data table

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;

Create test data

Understand basic geographical knowledge before creating data:

• The value range of global longitude and latitude is:Latitude -90~90, longitude -180~180
• The range of longitude and latitude in China is approximately:Latitude 3.86~53.55, longitude 73.66~135.05
• The latitude of the administrative center of Beijing is 39.92, and the longitude is 116.46
• The farther north, the greater the latitude value, and the farther east, the greater the longitude value
• Conversion of degrees and minutes : Convert the unit data of degrees and minutes into data of the units of degrees, the formula: degrees = degrees and minutes/60
• Minutes and seconds conversion: Convert the data of the units of degrees, minutes and seconds into data of the units of degrees, the formula: degrees = degrees and minutes/60 Seconds / 60 / 60

In the case of equal latitude:

• The longitude is every 0.00001 degrees, and the distance differs by about 1 meter

In the case of equal longitude:

• Every 0.00001 degree latitude, the distance difference is about 1.1 meters

mysql function calculation

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 ;

Create data python script

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

The above script creates 10 threads, and 10 threads insert 40,000 pieces of data. It took 150.18s to execute, and a total of 400,000 pieces of data were inserted

Test

• Test environment

System: mac os

Memory ：16G

cpu: intel core i5

Hard disk: 500g solid state drive

Under the test, find the 10 closest drivers to the coordinate point (134.38753, 18.56734)

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

• Time consuming: 18.0s
• explain: full table scan

I tested from 10,000 to 100,000 at intervals of 10,000 and from 100,000 to 90 Changes in results every 100,000 tests

##Conclusion

This solution will take more than 1 time to query when the data volume reaches 30,000 Seconds
• Approximately every 10,000 additional entries will increase the time consumption by 0.4 seconds

The above is the detailed content of Detailed introduction of mysql function for geographical location geo processing (with code). For more information, please follow other related articles on the PHP Chinese website!