1

I have a MySQL database that looks as such:

Postcode int(4),
City varchar, 
State varchar,
Latitude decimal(7,4),
Longitude decimal(7,4)

I want the user to enter their post code (1660 for example) and a radius of x (lets say 10) miles or kilometers. When they hit search, I want to return a list of all the cities within that radius. I have a database that contains all of the post codes, cities, latitudes, longitudes etc. of all areas within Australia.

Dan Is Fiddling By Firelight
  • 5,981
  • 17
  • 77
  • 128
user764226
  • 21
  • 1
  • 9
  • 2
    Closely related (if not duplicate): [Formulas to Calculate Geo Proximity](http://stackoverflow.com/q/2096385) – Pekka Jun 01 '11 at 22:43
  • 1
    A similar question, not specific to implementations in SQL: http://stackoverflow.com/q/27928/238688 – Dan J Jun 01 '11 at 22:48
  • Yup, still confused. Forumla still doesn't make any sense as it doesn't specificaly tell me what is coming back within 10 kilometers of point a. Only that point a and b are x kilometers apart. To add salt to the wounds, they are saying this "As you can see there is no noticeable difference between the Haversine Formula and the Spherical Law of Cosines, however both have distance offsets as high as 22 kilometers compared to the Vincenty Formula because it uses an ellipsoidal approximation of the earth instead of a spherical one." 22km offset? Well that doesn's seem accurate at all!!???? – user764226 Jun 02 '11 at 19:33
  • 22 km is negligible for customers willing to travel 1000+ km. Use the Google Maps API to get latitude and longitude from a zip code. – Cees Timmerman Apr 05 '12 at 13:25
  • If r is greater than "x kilometres apart" then "x kilometres apart" is within r!! – Strawberry Jul 15 '14 at 16:44

1 Answers1

1

If you could sacrifice some precisions (by selecting cities within a rectangle area 10 miles from post code 1660), then the solution can be as simple as:

  1. Find out the latitude and longitude of post code 1660
  2. Calculate the top left and bottom right of the rectangle area (10 miles to the left and top, 10 miles to the bottom and right)
  3. Use the query like: select city from table where latitude between bottom_right_latitude and top_left_latitude and longitude between top_left_longitude and bottom_right_longitude

If it has to be precisely radius 10 miles (which means the area is a circle), then the solution will be very complicated, couldn't do it in a single query I afraid. You need to think about some helper columns and use the distance and bearing calculations formulas to do it.

Reference: Calculate distance, bearing and more between Latitude/Longitude points