I'm developing a real estate web catalogue and want to geocode every ad using geokit gem.
My question is what would be the best database layout from the performance point if i want to make search by country, city of the selected country, administrative area or nearest metro station of the selected city. Available countries, cities, administrative areas and metro sations should be defined by the administrator of catalogue and must be validated by geocoding.
I came up with single table:
create_table "geo_locations", :force => true do |t|
t.integer "geo_location_id" #parent geo location (ex. country is parent geo location of city
t.string "country", :null => false #necessary for any geo location
t.string "city", #not null for city geo location and it's children
t.string "administrative_area" #not null for administrative_area geo location and it's children
t.string "thoroughfare_name" #not null for metro station or street name geo location and it's children
t.string "premise_number" #house number
t.float "lng", :null => false
t.float "lat", :null => false
t.float "bound_sw_lat", :null => false
t.float "bound_sw_lng", :null => false
t.float "bound_ne_lat", :null => false
t.float "bound_ne_lng", :null => false
t.integer "mappable_id"
t.string "mappable_type"
t.string "type" #country, city, administrative area, metro station or address
end
Final geo location is address it contains all neccessary information to put marker of the real estate ad on the map. But i'm still stuck on search functionality.
Any help would be highly appreciated.
