scarhand - 1 year ago 81
MySQL Question

# select within 20 kilometers based on latitude/longitude

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

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

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.

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.

any help would be much appreciated.

``````SELECT  *
FROM    mytable m
JOIN    mytable mn
WHERE   m.name = 'grey river'
``````

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
)