A simple example of mysql searching for data within N kilometers

According to the coefficient of pi and the radius of the earth and the longitude and latitude of the search point, the distance between the search point and the search data table is within N kilometers.

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,longitude,latitude) values
('Guangzhou East Station',113.332264,23.156206),
('Lin Hexi',113.330611,23.147234),
('balance rack',113.328095,23.165376);

mysql> select * from `location`;
+----+--------------+----------------+---------------+
| id | name | longitude | latitude |
+----+--------------+----------------+---------------+
| 1 | Guangzhou East Railway Station | 113.3322640000 | 23.1562060000 |
| 2 | Linhexi | 113.3306110000 | 23.1472340000 |
| 3 | Balance frame | 113.3280950000 | 23.1653760000 |
+----+--------------+----------------+---------------+

3. Search for data within 1 km

Search point coordinates: Times Square 113.323568, 23.146436

6370.996 km is the radius of the Earth

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

Execute 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 - (longitude*3.1415)/180)
  -> )*6370.996
  -> )<=1;
+----+-----------+----------------+---------------+
| id | name | longitude | latitude |
+----+-----------+----------------+---------------+
| 2 | Linhexi | 113.3306110000 | 23.1472340000 |
+----+-----------+----------------+---------------+

The above simple example of MySQL searching for data within N kilometers is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

<<: Three ways to configure Nginx virtual hosts (based on domain names)

>>: vue uses Ele.me UI to imitate the filtering function of teambition