-1

At the moment I have a web app that uses Google Maps APIs to get distance between one location (store) and a list of other locations (techs), as its all cached in a DB, it only has to run once. But its very slow that first time....

What im needing to do now is make a front end for customers, where they can enter in their address and find the closest store. As there is a tone of stores, trying to get addresses of each store and comparing it with the customers address using Google Maps API is going to take forever.

So what techniques are available to make this faster? Is it possible to filter out most of the addresses with the difference of lat and long? Or is their an API method that I can give Google Maps with a long list of addresses and have it return the closest address?

Mattisdada
  • 916
  • 2
  • 12
  • 19
  • How are you calling the Google Maps API? If you're not using cURL you might want to consider it - it might make your outbound requests faster. – Alfo Aug 18 '12 at 07:26
  • Yeah using CURL, but a few thousend HTTP requests doesn't matter how you cut it, is going to take a while – Mattisdada Aug 19 '12 at 09:22

2 Answers2

3

What you're supposed to do is when adding the stores to the database also include their lat/lngs. This way when the user enters their address, you just need to geocode their address(get the lat/lng), and use the haversine formula (which is just math so it runs fast) to get the closest locations.

https://developers.google.com/maps/articles/phpsqlsearch_v3

You can use the google geocoding api to get/insert the lat/lng of the stores already in your database.

I currently have a locator that searches tens of thousands of stores and it executes in under a second using this method.

Galen
  • 29,976
  • 9
  • 71
  • 89
  • That is exactly the kind of thing i was looking for! Thanks! I imagined it was possible to figure out distances through math + lat/long, but I had no idea how to it effectively. – Mattisdada Aug 18 '12 at 03:22
  • Great stuff here: http://www.codecodex.com/wiki/Calculate_Distance_Between_Two_Points_on_a_Globe – Teson Mar 11 '15 at 10:18
  • Even more great stuff here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ – Teson Mar 11 '15 at 10:37
1

Unfortunately there isn't such a function in Google maps API and you have to implement it yourself. A good start is the mysql point data type to store the locations geo location and a spatial index. A spatial index reduces the dimension complexity and makes nearest-neighbor lookup simple. A spatial index is a r-tree and a space filling curve, for example a hilbert curve. Here is a nice article about hilbert curves: http://blog.notdot.net/2009/11/Damn-Cool-Algorithms-Spatial-indexing-with-Quadtrees-and-Hilbert-Curves. Here is an example of a spatial index with mysql: Selecting nearest geo coordinates using spatial index on mysql table not working. Here is an example of mysql distance function Correct way of finding distance between two coordinates using spatial function in MySql. Here is an example of MySql 5.1 http://www.elevatedcode.com/articles/2009/03/06/speeding-up-location-based-searches/.

Community
  • 1
  • 1
Micromega
  • 12,486
  • 7
  • 35
  • 72
  • I don't see any example of a spatial index. This page is about the harvesine formula? – Micromega Aug 18 '12 at 04:19
  • GIS support, while present in MySQL, isn't as good as Postgres with PostGIS. It's trailing in features by at least a few years. While MySQL is a very good general purpose database, it is significantly weaker in this regard. – tadman Aug 18 '12 at 05:39