0

My question is rather simple. I am currently researching and learning how to use PHP and MySQL along with Google API to allow users on my site to search specific cities, and then also get nearby results included. I will continue to learn this on my own, but I would like to future-proof my database so that it can be implemented later on.

So if I have users submit their zip code when registering, I know that I can use the API or a function to get the latitude and longitude, and then I can store that info in its own column, such as 'lat_lng' (also zip code will have its own column).

BIG QUESTION: How can I format the lat/long column so that later on I can implement the proximity searching ability. Example: One user's lat_lng column may read as "41.854301,-71.193893" with just a comma separating the latitude and longitude. Other users will have different numbers, but is this enough for me to setup the proximity searching at a later date?

Jbreen37
  • 65
  • 1
  • 6
  • 1
    I would store each element in its own column. – Jay Blanchard Oct 14 '14 at 16:55
  • Check this out. Use a spatial index on a column with the type POINT. You should also keep the latitude and longitude in their own columns if you'd like to access them easily. http://stackoverflow.com/a/4726072/4099592 – slapyo Oct 14 '14 at 16:57
  • @Jay Blanchard Ok, so you mean that if I store the latitude (as listed above) in its own 'lat' column, and stored the longitude (as above) in its own 'lng' column, then I would be all set down the road? – Jbreen37 Oct 14 '14 at 16:58
  • It would be preferable @Jbreen37 – Jay Blanchard Oct 14 '14 at 16:59
  • @slapyo Thank you for that link. It seems that it will work efficiently, also seems complicated. Do you think just separate columns could future-proof my DB or no? – Jbreen37 Oct 14 '14 at 17:07
  • @Jbreen37 It wouldn't hurt. If for whatever reason you wanted to do something with just the latitude you wouldn't have to pull the whole string and then parse it. You would just update the one column. – slapyo Oct 14 '14 at 17:09
  • I am thinking with this setup when querying the database I will have to do some intense stuff to pull out the rows that have 'lat' and 'lng' columns with similar mathematics. However that is the part I said I would learn myself. – Jbreen37 Oct 14 '14 at 17:13

1 Answers1

0

You are looking for a topological, or distance sort on the points. You want to be able to calculate the distance between two points or locations. The formulae below yield distance with different accuracy. Pick one that yields your desired accuracy. You will find each need separate lat and lon, so keep them in separate columns to simplify SQL query statements.

Finding 'nearby' points given a geolocation (lat,lon) uses one of two well-know formulas, the haversine formula (popular because it yields accurate results with fewer significant digits) and the law of cosines formula ( see moveable-type.org fpr explanation ).

The haversine formula,

a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)

c = 2 ⋅ atan2( √a, √(1−a) )

d = R ⋅ c

where φ is latitude, λ is longitude, E is Earth’s radius (6371.010km); note that trignometric functions need angles converted to radians.

The Law of cosines:

d = acos( sin φ1 ⋅ sin φ2 + cos φ1 ⋅ cos φ2 ⋅ cos Δλ ) ⋅ E

An approximation can be found using Pythagoras' theorem (for small lat,lon differences), using fewer transcedentals (trignometric functions),

x = Δλ ⋅ cos φm

y = Δ

d = E ⋅ √x² + y²

Yet a coarser approximation uses Manhattan distance (N-S street distance),

d = E * ( abs(x) + abs(y) ) / √2

ChuckCottrill
  • 4,360
  • 2
  • 24
  • 42
  • Wow this answer looks great thank you! Can't wait to read it after work tonight and get back to you. Thanks Chuck. – Jbreen37 Oct 14 '14 at 20:00