Hi, i would like to thank you for this amazing and useful database.
however, i have a simple question, what is the diference between admin1Codes.txt and admin1CodesASCII.txt because the number of recods is not the same!!

OK, thank you very much, and also thank you very much for this amazing database

2 last questions please:
1- is it normal the 'geonameid' field in the admin1CodesASCII table has no correspondance in the geoname table?

2- i'm trying to redesign our clients/members database, but i'm a little lost regarding addresses. i don't know if i should always use the content of admin1Codes or if the internet user should enter his region.
however i know i have to use it for the US states.
do you have a list of countries that require a state or area information??

It would be nice if the provided files included the latest mysql table structure, excluding the data. This can be achieved using this command, if your database is called geonames:

Code:

mysqldump5 -u root --no-data geonames > geonames.sql

I have attached a dump of my database (excluding data) using this command. Note that the table structures include the corrections submitted through-out this thread. Hopefully this should help avoid people needing to piece together the instructions and corrections to create the empty database.

Edit: I have just tried importing the latest countryinfo data and I am seeing a difference in data structure, with regards to what was posted at the start of this thread. I will revise the table creation data, unless someone does so before me.

I have also updated the database creation script to reflect the adjusted table format. At this point I haven't tested this with any existing applications, so I can't say whether it will break anything.

I tried using the code given (with the modification that I made all tables MyISAM, and in the geoname table added FULLTEXY (name) ), but get the following errors when loading the data into the tables (I'm using Mac OSX running on macbook pro, with mysql 5):

# here I inserted the character-set property that works for me
LOAD DATA INFILE '/tmp/geonames/allCountries.txt' INTO TABLE `geonames`.`allCountries` CHARACTER SET utf8 (geonameId,name,ansiName,alternateNames,latitude,longitude,featureClass,featureCode,countryCode,cc2,admin1Code,admin2Code,admin3Code,admin4Code,population,elevation,gtopo30,timezone,modificationDate);

# shoud be over 7 million entries (2010, January)
SELECT count(*) FROM `geonames`.`allCountries`;

# now you could use the MySQL geo spatial extensions and add the new (geo) column "coordinate"
ALTER TABLE `geonames`.`allCountries` ADD COLUMN `coordinate` POINT NOT NULL AFTER `longitude`;

So, I do have also a question. I want to get the closest/nearest point to a point in that table. Yes, I could build a bounding box with a rectangle of e.g. 1 mile. But, is there not an more easier way to find the closest point by a coordinate?