Search DB (of businesses) by location

I'm designing a MySQL database and a corresponding RoR app that will hold various businesses, each business will have an address.

A requirement of this application is to search the database by City/Country (this will hold businesses across Europe/UK). The search results will be returned by the nearest city in that country.

Here 开发者_如何学Cis a rough ERD:

What is the best way to organize the DB? Should I move the city field into its own table? Should I store GPS coordinates for each business?

Thanks!

I would store the City in a separate table with latitude/longitude of city hall (or city center). Then for each store I would store latitude/longitude.

This will allow you to calculate the distance from the store to city centre, and rank by distance, so that you can show the top N.

If later you store users' locations, then it is easy top moify your query to show distance from the user's location.

You don't need to link to countryID from your Businesses table, since the PostalCodes table already has a FK to the Countries table.

I think having Countries and PostalCodes is overkill, would not bother. In your Countries table, have address, city, state, postalcode, country, lat, and lng.

Then use GeoKit to geocode your Address into Lat/Lng if your address is updated before_save.

Then you can be awesome and say things like (find all businesses within 10 miles of this address)

Business.within.find(:all, :origin=>'100 Spear st, San Francisco, CA', :within=>10)

Resources:

• Geokit Gem
• Geokit Rails Plugin