Calculate nearest store for a database

iaingibbons
iaingibbons used Ask the Experts™
on
I have a database of 800 stores and a consumer database of 800,000 customers. I want to allocate a nearest store to the customer database, along with the distance in miles, to each of consumer record. Anyone know of a solution to do this at all?

Many Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Sorry, I don't understand.
Do you mean something like this: http://jan.ucc.nau.edu/~cvm/latlongdist.html
or Google distance: http://www.daftlogic.com/projects-google-maps-distance-calculator.htm
Or just trivial distance between two points: sqrt(x * x + y * y)
Do you have coordinates of this stores and customers? or it's set as state, city (zip), street, house?
 

Author

Commented:
Hi thanks for replying. Both those links would suffice for the purpose of calculating the distance between the consumer and the outlet, however what I need to do is automate a routine that took the long and lat of the consumer (using postcode to generate the long and lat) and then checked their distance against all 800 stores and selected the nearest store. I don't have the long and lat but I am sure there must be a way of auto generating them too.

Any help, as always, much appeciated! Thank you.

Commented:
In the navigation application we have a map from map vendors and there is longitude and latitude for each zip. I don't know your requirements and circumstances to recommend something. I think, if you don't have such info as longitude and latitude for the zip codes, you can use, for example, Google Map API or MSN:
http://code.google.com/apis/maps/documentation/
http://www.microsoft.com/maps/developers/
 I've sent you the links where it is possible to convert zip to coordinates:
http://www.batchgeocode.com/lookup/
http://www.nearby.org.uk/conversions.cgi
Here is even a component doing it: http://labs.mike-obrien.net/Document.aspx?id=zip2coords
 
Commented:
you could create a graph with a vertex as a zipcode, and an edge representing the distance between them.
when a user enters a zip code, check all that vertex's edges and return the closest ones

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial