GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Importing geonames into MsSQL Server  XML
Forum Index -> FAQ - frequently asked questions
Author Message
marc



Joined: 08/12/2005 07:39:47
Messages: 3993
Offline

Russ has written a small how-to import geonames into MsSQL Server :

http://tinisles.blogspot.com/2006/08/importing-geo-names-database-into.html

[WWW]
rballman



Joined: 17/03/2007 23:18:30
Messages: 6
Offline

Can someone upload the 'allCountries.txt' in UTF-16 format? I am having trouble figuring out how to use NANT to convert it from UTF-8 to UTF-16.

PLEASE HELP ME!
marc



Joined: 08/12/2005 07:39:47
Messages: 3993
Offline

Hi

I have converted todays dump with the 'iconv' command line tool :
http://dev.geonames.org/allCountries.utf16.gz

Hope this helps

Cheers

Marc

[WWW]
rballman



Joined: 17/03/2007 23:18:30
Messages: 6
Offline

Thanks, but when I import the file into MsSQL there seems to be some corruptness in the file or something. The import preview step shows vertical bars and it adds 16 columns. Not sure what is going on here. Any help would be much appreciated.

Thanks.
rballman



Joined: 17/03/2007 23:18:30
Messages: 6
Offline

nevermind i think it all works fine
bemall



Joined: 12/11/2008 20:34:31
Messages: 11
Offline

My 2 questions at this point:
Where can I download the input file associate with the table below and what are the correct mappings:
create table geoname (
geonameid int,
name varchar(200),
asciiname varchar(200),
alternatenames varchar(4000),
latitude float,
longitude float,
fclass char(1),
fcode varchar(10),
country varchar(2),
cc2 varchar(60),
admin1 varchar(20),
admin2 varchar(80),
admin3 varchar(20),
admin4 varchar(20),
population int,
elevation int,
gtopo30 int,
timezone varchar(40),
moddate date
);

Secondly, the reademe.txt doc mentions "The main 'geoname' table has the following fields :
"

What is the right input file for this genome table and what are the correct mappings.
marc



Joined: 08/12/2005 07:39:47
Messages: 3993
Offline

I have the impression you have already found the solution or are you pulling our leg?

Marc

[WWW]
bemall



Joined: 12/11/2008 20:34:31
Messages: 11
Offline

No I am honestly not pulling anyone's leg on this one and earnestly looking for a solution. AllCountries.txt has 11 columns yet the create genome's table script has 19. These do not match and will not work.

similarly, the procedure outlined in, http://tinisles.blogspot.com/2006/08/importing-geo-names-database-into.html, will not work becasue the provided create table script has less columns.

I have looked at all the links and suggestions and honestly I cannot get anyone of them to make sense in terms of simly matching the columns in the AllCountries.txt with the created tables. Additionall I came across another database "geonames_dd_dms_date_20081022" which does seem to match the columns for the create geonomes table, however I need some assistance with figring out the mapping since the column names are not intuitive..

Thank you
russau


[Avatar]
Joined: 26/01/2009 12:44:48
Messages: 14
Offline

Re: bemall's trouble with my blog article. The original create table is now outdated as the import now has extra columns.

I've updated my article at: http://tinisles.blogspot.com/2006/08/importing-geo-names-database-into.html

There's also a good article at: http://blogs.msdn.com/edkatibah/archive/2009/01/13/loading-geonames-data-into-sql-server-2008-yet-another-way.aspx
 
Forum Index -> FAQ - frequently asked questions
Go to:   
Powered by JForum 2.1.5 © JForum Team