根据经度/纬度在20公里内选择 [英] select within 20 kilometers based on latitude/longitude

问题描述

我有一个按照以下示例结构的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.

我真的不知道该怎么做.我已经阅读了haversine公式，但不知道如何将其应用于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公里内选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！