I am trying to use the database on geonames.org, i inserted all the database files into an sql database.
Now i have a couple of database tables, one with country names, one the provinces/states, and one table with all the city's. The files on the geoname database are named as: countryinfo.txt, admin1codesascii.txt, admin2codes.txt, allcountries.txt.
Something i don't understand, is how do i use all these tables together to get a hierachy like country->province/states->city's? Because if i don't use the hierachy, the allcountries database is far to huge, to just select a city by name.. It takes 10 minutes.
What i am wanting to do is creating a box, where all the countries are in. If they select a country, then i want to show in a second selectbox the provinces and states. But how do i select the provinces states, if there is nothing in common like an id, or a name in the admin1codes database.
That totally depends on what you want with the data, and what exactly you want to query.
Have you taken a look at the readme.txt? You can find it here: http://download.geonames.org/export/dump/readme.txt
Also, this question is related, and should give you the correct answer: Geonames database: getting a full hierarchy (country -> admin1 -> admin2 -> city) with one only mysql query