Home > Database > Mysql Tutorial > body text

Examples of mysql searching for data within N kilometers nearby

jacklove
Release: 2018-06-08 23:16:04
Original
2154 people have browsed it

Based on the pi ratio, the earth's radius coefficient and the longitude and latitude of the search point, search for data within N kilometers of the search point in the data table.

1.Create 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;
Copy after login

2.Insert test data

insert into location(name,longitude,latitude) values
('广州东站',113.332264,23.156206),
('林和西',113.330611,23.147234),
('天平架',113.328095,23.165376);mysql> select * from `location`;
+----+--------------+----------------+---------------+| id | name         | longitude      | latitude      |
+----+--------------+----------------+---------------+|  1 | 广州东站      | 113.3322640000 | 23.1562060000 |
|  2 | 林和西        | 113.3306110000 | 23.1472340000 ||  3 | 天平架        | 113.3280950000 | 23.1653760000 |
+----+--------------+----------------+---------------+
Copy after login

3.Search for data within 1 km

Search point coordinates: Times Square 113.323568, 23.146436

6370.996Kilometer is the radius of the earth

Formula for calculating the coordinate distance between two points on the 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;
Copy after login

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 | 林和西     | 113.3306110000 | 23.1472340000 |
+----+-----------+----------------+---------------+
Copy after login

This article explains the relevant content of mysql searching for data within N kilometers nearby. For more related knowledge, please pay attention to the PHP Chinese website.

Related recommendations:
Mysql connection interruption automatic reconnection method

php implements HTML entity numbering and non-ASCII String mutual conversion class

#php creates a unique number class based on the auto-incremented id

The above is the detailed content of Examples of mysql searching for data within N kilometers nearby. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template