GeoNames Forum

Hi There,

I know there are some details regarding this topic in FAQ section but sadly i was unable to find a clear set of instructions out of those each thread or reply add new details to existing ones also most of the posts seems to be from 2008 so here im possibly requesting the same information again but it really really would be a lot help ful if any one can point me to a latest document explaining details clearly.

any help would be appriceated.

Thank you,
bala.

If you think there is something wrong with the faq then let me know what it is and we can correct it.

Marc

Hi Marc,

I didnt mean there are issues in the details but i thought it would be more easy to follow if it was compiled into a single document.

Regards,
bala.

When I run the FAQ I get errors in my data import. The Tables build fine (takes from the FAQ thread on how to build the tables)

allCountries.txt:
Incorrect int value '' for column elevation

alternateNames.txt:
Incorrect int value '' for column isPreferredName

iso-languagecodes.txt
Data too long for column iso_639_3

admin1Codes.txt
Data too long for column `code` at row 1366

admin1CodesAscii.txt
Data too long for column `code` at row 1062

countryInfo-n.txt
Incorrect int value for column 'Dirham' for column geonameId

Do the tables match the populating data? I was getting errors with my lat/long data because the table decimal size was too small and the admin1Codes tables were erroring because `code` CHAR(5) didn't match the input where some rows had 6 chars.

I think we shd use the updated table definition which is listed in the faq section.

so after the table creation part i updated the sql-mode="" from sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" otherwise the loading is not working after this change i was able to load "allCountries.txt"

LOAD DATA INFILE 'D:/geonames/allCountries.txt' INTO TABLE geoname (geonameid,name,ansiname,alternatenames,latitude,longitude,feature_class,feature_code,country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,gtopo30,timezone,modification_date);

but when i try to load the "alternateNames.txt" its alway showing as process is progressing and i have to close the querybrowser and my MySql wont respond unless i restart the machine. i was using the following

LOAD DATA INFILE 'D:/geonames/alternateNames.txt' INTO TABLE alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName);

not sure how to fix this. please let me know if im doing anything wrong here than what is suggested.

any help on this would be apperciated.

Thank you,
bala.

I ended up making my own tables then linking everything via id columns instead of using the US.AZ.18323 type columns. The table definitions need to be rewritten, they're not optimized and need a dba touch.

Hi cancausecancer,

Were you able to recreat the database after changing the table definitions. could you share the updated definitions you have with you please.

Thank you,
bala

I didn't end up keeping the ones from the sql, nor fixing them so they'd be useful to just import the data. Mine are too different to make sense and are structured quite differently so the data wouldn't be able to import.

If you really want to use their data in the form they supply then you'll need to take their table structure, put it into notepad, delete it and then close notepad. Maybe that's a bit harsh but I believe without rewriting it yourself you'll just walk into walls.

One solution which may be useful is to import the data into excel so you can see which/what columns are being used, then work with the create table statements to match them. Also start by increasing the various data type sizes to help get the data in, then optimize them after.

cancausecancer

Who is 'their' in your posting? If you don't want to or cannot post scripts or cannot improve the scripts provided by others why should anyone else? This is YOUR job. Don't expect others to do YOUR job.

Marc