Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.