我有一个mysql表,其结构如下例所示:
POSTAL_CODE_ID|PostalCode|City|Province|ProvinceCode|CityType|Latitude|Longitude
7|A0N 2J0|Ramea|Newfoundland|NL|D|48.625599999999999|-58.9758
8|A0N 2K0|Francois|Newfoundland|NL|D|48.625599999999999|-58.9758
9|A0N 2L0|Grey River|Newfoundland|NL|D|48.625599999999999|-58.9758
现在我要做的是创建一个查询,该查询将在搜索位置的选定公里内选择结果
因此,假设他们搜索"灰色河流"并选择"在20公里内找到所有结果">
它显然应该选择"格雷河",但也应该根据纬度和经度选择格雷河20公里以内的所有位置。
我真的不知道该怎么做。我读过haversine公式,但不知道如何将其应用于mysqlSELECT。
任何帮助都将不胜感激。
SELECT *
FROM mytable m
JOIN mytable mn
ON ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE m.name = 'grey river'
如果您的表是MyISAM
,您可能希望以原生几何体格式存储点,并在其上创建SPATIAL
索引:
ALTER TABLE mytable ADD position POINT;
UPDATE mytable
SET position = POINT(latitude, longitude);
ALTER TABLE mytable MODIFY position NOT NULL;
CREATE SPATIAL INDEX sx_mytable_position ON mytable (position);
SELECT *
FROM mytable m
JOIN mytable mn
ON MBRContains
(
LineString
(
Point
(
X(m.position) - 0.009 * 20,
Y(m.position) - 0.009 * 20 / COS(RADIANS(X(m.position)))
),
Point
(
X(m.position) + 0.009 * 20,
Y(m.position) + 0.009 * 20 / COS(RADIANS(X(m.position))
)
),
mn.position
)
AND ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE m.name = 'grey river'
SELECT `s`.suburb_id,`s`.suburb_name,`s`.lat,`s`.long, (((acos(sin(($lat*pi()/180)) * sin((s.lat*pi()/180))+cos(($lat*pi()/180)) * cos((s.lat*pi()/180)) * cos((($long - s.long)*pi()/180))))*180/pi())*60*1.1515*1.609344) AS distance FROM (`mst_suburbs` as s) HAVING distance <= 20 ORDER BY `s`.suburb_id DESC
这个查询适用于我获取12公里之间的所有纬度、经度。我有mst_suburbs
可以是具有lat
和long
列的表。$lat
和$long
是我的两个php变量。我正在传递所需的lat,long,以从mst_suburb
中获得最近的12km lat-long列表。您只需要根据表更改列的名称,并将lat,long传递给查询即可。
这是一个有点复杂的算法,但这里有一个到一个解决方案的链接
您只需采用您的haversine公式,并像这样应用它:
SELECT *,
6371 * ACOS(SIN(RADIANS( $lat1 )) * SIN(RADIANS(`Latitude`)) +
COS(RADIANS( $lat1 )) * COS(RADIANS(`Latitude`)) * COS(RADIANS(`Longitude`) -
RADIANS( $lon1 ))) AS `distance`
FROM `table`
WHERE `distance` <= 20
ORDER BY `distance` ASC
将$lat1
和$lon1
替换为要进行比较的纬度和经度。