Analysis of data instances near mysql search
This article mainly introduces the analysis of data examples near mysql search, hoping to supplement and update some knowledge, if you have any other questions you need to know, you can continue to follow my updated article in the industry information.
1. Create a test table CREATE TABLE `location` (`id`location` (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (50) NOT NULL, `longitude` decimal (1313) NOT NULL, `latitude` decimal (13pc10) NOT NULL, PRIMARY KEY (`id`), KEY `long_lat_ index` (`longitude`, `latitude`) ENGINE=InnoDB DEFAULT CHARSET=utf8;2. Insert test data
Insert into location (name,longitude,latitude) values ('Guangzhou East Railway Station', 113.332264), (Lin Hexi, 113.330611, 23.147234), (Lianjia, 113.328095, 23.165376); mysql > select * from `location` +-+ | id | name | longitude | latitude | +-+- -- + | 1 | Guangzhou East Railway Station | 113.3322640000 | 23.1562060000 | | 2 | Lin Hexi | 113.3306110000 | 23.1472340000 | 3 | balance frame | 113.3280950000 | 23.1653760000 | +-+ 3. Search for data within 1 kilometer
Search point coordinates: times Square 113.323568, 23.146436
6370.996 km is the radius of the earth
A formula for calculating the coordinate distance between two points on a sphere
C = sin (MLatA) sin (MLatB) cos (MLonA-MLonB) + cos (MLatA) cos (MLatB)
Distance = RArccos (C) * Pi180
According to the calculation formula, the query statement is as follows:
Select * from `location` where (acos (sin ([# latitude#] * 3.1415) * sin ((latitude*3.1415) / 180) + cos (([# latitude#] * 3.1415) * cos ((latitude*3.1415) / 180) * cos (([# longitude#] * 3.1415) / 180-(longitude*3.1415) / 180)) * 6370.996) select * from `location` where (- > acos (- >) Sin ((23.146436 / 3.1415) / 180) * sin ((latitude*3.1415) / 180) +-> cos ((23.146436 / 3.1415) / 180) * cos ((latitude*3.1415) / 180) * cos ((113.323568 / 3.1415) / (longitude*3.1415) / 180)->) * 6370.996->)
