5

I'm building a directory that lets you view business around you, based on your current location. Right now, these business are stored in the database as address1, address2, city, province, postal_code.

If I'm wanting to do distances, should I be storing lat/long as well? What's the best way to go about this?

I'm using PHP, HTML5 geolocation and Google Maps.

dallen
  • 2,621
  • 7
  • 37
  • 47

3 Answers3

2

Unless you have other ways to narrow your database search, you probably do want to keep lat/long, as it will help you filter your database search for nearby addresses. It's complicated to accurately compute exact distances between two points using lat/long, but it's at least a starting point for narrowing down the number of addresses to look at in more detail.

Also, if you want to be able to run when you don't have a data connection, you need some data to use in lieu of a web-lookup. It's possible to get a reasonably accurate distance from lat-long without having to worry too much about the convergence of longitude lines, at least for an around-me kind of an accuracy.

strings42
  • 543
  • 3
  • 9
0

You don't "have to" if you look at the distance service in google maps you can pass an address or lat, long:

https://developers.google.com/maps/documentation/javascript/distancematrix

David Nguyen
  • 8,368
  • 2
  • 33
  • 49
  • But if I have a form that says "type a keyword here" and they check "show businesses close to me", do I then have to go through every single business in the database, get the addresses, find out which are closer, then sort by closest? Seems expensive. What am I missing here? – dallen Apr 16 '12 at 20:48
  • Yes, you have to do that for every entry. Are you using MySQL? You can search this site for spatial search, or several other methods. You can look here if you for the best type: http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-for-latitude-longitude – SativaNL Apr 16 '12 at 21:00
  • I'm not familiar with HTML5 Geolocation at all, but if you have the postal code of the user, you can do the calculation once and store the distance in a distance table for the businesses keyed by the postal code (as it will be shared by numerous individuals). Subsequent lookups with that PC will just recall the previously calculated businesses. Eventually, you would get rid of the calculations after sometime of non-use. I believe you can use part of the IP Address as well although it may not be as accurate. –  Apr 16 '12 at 21:06
0

You don't want to do a distance calculation for every entry in your database, you need to narrow it down. The postal_code may be sufficient for this. If it isn't, you can quickly narrow the search with lat/lon by restricting each to a range around the location; this provides a nearly square trapezoid region which will probably be good enough as-is.

Mark Ransom
  • 299,747
  • 42
  • 398
  • 622