問題描述

我有一個(gè)按照以下示例構(gòu)建的 mysql 表:

i have a mysql table structured as per the example below:

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

現(xiàn)在我要做的是創(chuàng)建一個(gè)查詢，該查詢將選擇搜索位置選定公里范圍內(nèi)的結(jié)果

now what i am trying to do is create a query that will select results within selected kilometers of a searched location

假設(shè)他們搜索灰色河流"并選擇查找 20 公里內(nèi)的所有結(jié)果"

so lets say they search for "grey river" and select "find all results within 20 kilometers"

顯然應(yīng)該選擇灰河"，但也應(yīng)該根據(jù)經(jīng)緯度選擇灰河20公里范圍內(nèi)的所有位置.

it should obviously select "grey river", but it should also select all locations within 20 kilometers of grey river based on the latitudes and longitudes.

我真的不知道該怎么做.我已經(jīng)閱讀了半正弦公式，但不知道如何將其應(yīng)用于 mysql SELECT.

i really have no idea how to do this. i've read up on the haversine formula but have no idea how to apply this to a mysql SELECT.

任何幫助將不勝感激.

推薦答案

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，您可能希望以原生幾何格式存儲(chǔ)您的點(diǎn)并在其上創(chuàng)建一個(gè) SPATIAL 索引:

If your table is MyISAM you may want to store your points in a native geometry format and create a SPATIAL index on it:

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'

這篇關(guān)于根據(jù)緯度/經(jīng)度在20公里內(nèi)選擇的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！