Mysql searches for simple instances of data within N kilometers, and mysql searches for instances.

Mysql searches for simple instances of data within N kilometers, and mysql searches for instances.

Based on the circumference rate, the earth's radius coefficient, and the latitude and longitude of the search point, the data that is located N kilometers away from the search point in the data table is searched.

1. Create a test table

CREATE TABLE `location` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `longitude` decimal(13,10) NOT NULL, `latitude` decimal(13,10) NOT NULL, PRIMARY KEY (`id`), KEY `long_lat_index` (`longitude`,`latitude`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Insert Test Data

Insert into location (name, longpolling, latitude) values ('guangzhou East Station ', 113.332264, 23.156206), ('lin and West', 113.330611, 23.147234, 23.165376); mysql> select * from 'location '; + ---- + -------------- + ---------------- + ------------- + | id | name | longpolling | latitude | + ---- + ---------------- + -------------- + --------------- + | 1 | Guangzhou East Station | 113.3322640000 | 23.1562060000 | 2 | lin and Xi | 113.3306110000 | 23.1472340000 | 3 | tianyao platform | 113.3280950000 | 23.1653760000 | + ---- + -------------- + ----------------- +

3. Search for data within 1 kilometer

Search coordinates: Times Square 113.323568, 23.146436

6370.996 km is the earth's radius

Formula for Calculating the distance between two points in the Sphere

C = sin(MLatA)sin(MLatB)cos(MLonA-MLonB) + cos(MLatA)cos(MLatB) Distance = RArccos(C)*Pi180

The query statement obtained based on the formula is as follows:

select * from `location` where (acos(sin(([#latitude#]*3.1415)/180) * sin((latitude*3.1415)/180) + cos(([#latitude#]*3.1415)/180) * cos((latitude*3.1415)/180) * cos(([#longitude#]*3.1415)/180 - (longitude*3.1415)/180))*6370.996)<=1;

Run the query:

Mysql> 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)/180-(longpolling * 3.1415) /180)->) * 6370.996->) <= 1; + ---- + ----------- + ---------------- + ------------- + | id | name | longpolling | latitude | + ---- + ----------- + ---------------- + ------------- + | 2 | lin and Xi | 113.3306110000 | 23.1472340000 | + ---- + ----------- + ---------------- + --------------- +

The simple example of mysql search for data within N kilometers above is all the content that I have shared with you. I hope to give you a reference and support for the customer's house.