CODEWITHSUNDEEP
mysqlruby-on-railsdatabasedatabase-designgeolocation
christo16
christo16

Reputation: 4843

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 is a rough ERD: alt text

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!

Upvotes: 0

Views: 293

Answers (3)

Jesse Wolgamott
Jesse Wolgamott

Reputation: 40277

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:

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

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.

Upvotes: 2

dotariel
dotariel

Reputation: 1594

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

Upvotes: 1

Related Questions