How to store all geographical locations around the world in Database?

Question

I am working for a travel site, where i need to store the tourist spots which tourists traveled to. I need the spots to be unique in the locations table so that i can know the popularity of a particular spot etc. I will also need also need all countries, states, cities stored with me because i cannot depend on user input. The database is MySQL. Seeing the data sets available for such locations i see there is a problem of nesting of cities across countries which may use provinces, states, counties etc.

So, my question is how to design the schema so that i can store all the locations. I was thinking about having tables for countries, states, cities, and spots. the spots table will contain spot_name, cityId, stateId, countryId, and some fields to have longitude and latitude bounds. This way i can identify same spots by their geopositions.

But again, this solution won't work because of the states/provinces/counties etc. problem. Can you please suggest how to build the schema and go about seeding it with correct data so that dependency on user input is minimum.

Answer 1

I might be a bit late for the response, but, the question was how to design the schema to store countries, states, cities, and spots.

As you had to design it for the world, you should be aware that each countries have different number of administrative levels, and that the names of those administrative levels are not always the same (there is more than 'state'). As I work daily in that kind of problems, I've opted for a hierarchical schema. So a location is included in a parent administrative division which is in turn in an other admin division. Your location table will have a 'parent_id' that will refer to the parent location in the same table.

That will be only the beginning of your nightmare, you will that have to populate the table, perhaps with a free database available (geonames, openstreetmap). Your next nightmare will be to update you data, because location names changes, admin division changes... Or you can also buy a good up-to-date database.

Answer 2

I think the schema part of your problem would be pretty simple. But the real problem is how you would get the data for your user to select - you are imagining the (almost) impossible! I don't think there is any database in existence which would translate a co-ordinate to a place name. Even Google can't (yet) do that for you - for example, a search for "Lat Long Taj Mahal" provides 27.1750, 78.0419 (Google have used their own and other people's experience to tell you that); but a search for "27.1750, 78.0419" just yields a pin on the map, and then our human eyes can see that the pin is 'pretty close' to a place named "Taj Mahal" (or ताज महल in Hindi, or તાજ મહેલ in Gujarati )...

Just imagine - how you would populate your schema? Think about how many co-ordinates you would need in your table if you wanted decent accuracy (needing at least 6 decimal places)! And who would be the authority on place names?

So I think your best approach might be to:

1. Use the publically available lists of country/city names translated to their co-ordinates,
2. Build your app so it pre-populates the closest co-ordinate to the user's precise location, and then
3. Allow the user to qualify the match with their own (more specific) chosen place name.

Then YOU could store the precise co-ordinate gathered by your app, along with the place name the user specified; and sell the data for $millions! (I suspect Google are already doing this ;)

Answer 3

you should use a geospatial database as then you can store your locations like countries and states as spatial entities and so can determine the nesting correctly.

If you can't use one you can simulate geospatial positions using strings in a normal table by dividing the world up into a grid, then subdividing each square of the grid recursively.

For example divide the world into 9 squares, numbered 1-9 from top left to bottom right. Anything which is in these large squares has only a single digit reference. Then divide each square into 9 and anything which is at this level has a 2 digit reference. so 11 is the top left square and 99 is the bottom right square.

Repeat this process until you have the precision that you need. a single feature might have a reference 10 digits long 5624357899 but you would know that this would be inside any larger feature which is fewer digits which starts with the same string like 5624357. So your countries would have fewer digits because they are larger, but your individual locations would have more because they are smaller and more accurately located.

This will only give you a course approximation of location (and will be bad for long thin features) but might be suitable enough

The first grid will look like this:

______________________________
|         |         |         |
|    1    |    2    |    3    |
|         |         |         |
|_________|_________|_________|
|         |         |         |
|    4    |    5    |    6    |
|         |         |         |
|_________|_________|_________|
|         |         |         |
|    7    |    8    |    9    |
|         |         |         |
|_________|_________|_________|

The second round looks like this (only first square completed for simplicity):

______________________________
|11|12 |13|         |         |
|---------|    2    |    3    |
|14|15 |16|         |         |
|---------|         |         |
|17|18 |19|         |         |
|_________|_________|_________|
|         |         |         |
|    4    |    5    |    6    |
|         |         |         |
|_________|_________|_________|
|         |         |         |
|    7    |    8    |    9    |
|         |         |         |
|_________|_________|_________|

you repeat this process until you have fine enough approximation for your purposes.