select within 20 kilometers based on latitude/longitude
SELECT *FROM mytable mJOIN mytable mnON 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.0WHERE 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 mytableSET position = POINT(latitude, longitude);ALTER TABLE mytable MODIFY position NOT NULL;CREATE SPATIAL INDEX sx_mytable_position ON mytable (position);SELECT *FROM mytable mJOIN mytable mnON 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.0WHERE m.name = 'grey river'
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 DESCThis 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.