select within 20 kilometers based on latitude/longitude

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.

Quassnoi · Accepted Answer · 2011-08-02T23:07:00.417

10

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'

edited Aug 02 '11 at 23:07

answered Aug 02 '11 at 22:59

Quassnoi

413,100
91
616
614

Why did you deleted all your comments? – Micromega Aug 05 '11 at 09:55
1

@Jitamaro: I moved them to chat because they're irrelevant to the question. – Quassnoi Aug 05 '11 at 09:56
in your second code block, when you use MySQL spatial, why don't you use the DISTANCE function? – Tomas Apr 16 '12 at 06:07
@Tomas: where is such a function defined? – Quassnoi Apr 16 '12 at 10:05
[In MySQL spatial](http://dev.mysql.com/doc/refman/4.1/en/functions-that-test-spatial-relationships-between-geometries.html#function_distance)! Already present in version 4.1. – Tomas Apr 16 '12 at 12:40
@Tomas: this one only works on planar surface. Also, are they implemented in any `GA` version? `Distance` is not present in `5.5.22`. – Quassnoi Apr 16 '12 at 13:22
*"This one only works on planar surface"* - can you please cite the docs? They support the different spatial systems so Distance should be real distance. Don't know about 5.5. – Tomas Apr 16 '12 at 14:08
1

@Tomas: http://dev.mysql.com/doc/refman/5.5/en/gis-class-geometry.html: *In `MySQL`, the `SRID` value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry*. This function is not supported in `5.0` or above anyway. – Quassnoi Apr 16 '12 at 15:45
Aha, so the MySQL spatial support is even worse than I thought. Not only MBR limitation but also this... ugggh... escape to Postgress. Thanks for clarification. – Tomas Apr 16 '12 at 16:05

score 1 · Answer 2 · answered Nov 28 '16 at 10:41

SELECT `s`.suburb_id,`s`.suburb_name,`s`.lat,`s`.long, (((acos(sin(($lat*pi()/180)) * sin((s.lat*pi()/180))+cos(($lat*pi()/180)) * cos((s.lat*pi()/180)) * cos((($long - s.long)*pi()/180))))*180/pi())*60*1.1515*1.609344) AS distance FROM (`mst_suburbs` as s) HAVING distance <= 20 ORDER BY `s`.suburb_id DESC

This query works for me to get all the lat,long between 12 km distance.I have mst_suburbs is may table which having the lat and long column.$lat and $long are my two php variable .and I am passing the desired lat,long to get the nearest 12km lat long list from the mst_suburb. You just need to change the name of the column according to your table and pass the lat,long to query.

score 0 · Answer 3 · answered Aug 02 '11 at 22:54

0

It's a little complicated algorithm, but here's a link to one solution

answered Aug 02 '11 at 22:54

Brian Hoover

7,861
2
28
41

What about a spatial index or a space-filling-curve? – Micromega Aug 02 '11 at 23:03

score 0 · Answer 4 · answered Aug 02 '11 at 22:58

0

You simply take your haversine formula and apply it like this:

SELECT   *,
         6371 * ACOS(SIN(RADIANS( $lat1 )) * SIN(RADIANS(`Latitude`)) +
         COS(RADIANS( $lat1 )) * COS(RADIANS(`Latitude`)) * COS(RADIANS(`Longitude`) -
         RADIANS( $lon1 ))) AS `distance`
FROM     `table`
WHERE    `distance` <= 20
ORDER BY `distance` ASC

Replace $lat1 and $lon1 with the latitude and longitude you want to compare against.

answered Aug 02 '11 at 22:58

deceze

510,633
85
743
889

What about a spatial index or a space-filling-curve? – Micromega Aug 02 '11 at 23:03
@Jitamaro What about it? Was that a part of the question I missed? – deceze Aug 02 '11 at 23:11
It would be nice if the down voters would leave a comment. This works fine if you have your lat and lon and want to formulate an SQL query that searches within the vicinity. – deceze Aug 30 '11 at 06:32

select within 20 kilometers based on latitude/longitude

4 Answers4

Linked

Related