scarhand - 10 months ago 47

MySQL Question

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.

Answer Source

```
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'
```

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'
```