問題描述

我有一個按照以下示例構建的 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

現在我要做的是創建一個查詢，該查詢將選擇搜索位置選定公里范圍內的結果

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

假設他們搜索灰色河流"并選擇查找 20 公里內的所有結果"

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

顯然應該選擇灰河"，但也應該根據經緯度選擇灰河20公里范圍內的所有位置.

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.

我真的不知道該怎么做.我已經閱讀了半正弦公式，但不知道如何將其應用于 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，您可能希望以原生幾何格式存儲您的點并在其上創建一個 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'

這篇關于根據緯度/經度在20公里內選擇的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！