GeoNames Forum

Hi there,

I tried (in vain) to import the cities data (> 15000) into a postgres database.

Created first the table (guessed the structure):

Code:

create table geonames_cities (
          geonameid int,
          countrycode varchar(3),
          fcode varchar(5),
          fcodeName varchar(200),
          fcl varchar(2),
          fclName varchar(100),
          name varchar(200),
          wikipedia varchar(100),
          lng float,
          lat float,
          population int
  );

Then, tried to import the txt file:

Code:

\copy geonames_cities (population, name, fclName, lat, lng, fcl, fcode, countrycode, geonameID) from 'cities15000.txt' null as ''

But it doesn't work....

Can anyone give me the correct command our a hint what I am doing wrong?

Thanks a lot!

____________________________________________________________________

Lean Back and Relax - Enjoy some Nature Photography
http://photoblog.la-famille-schwarzer.de

Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
____________________________________________________________________

Hi

There is an faq entry about loading the data into postgres:
http://forum.geonames.org/gforum/posts/list/67.page

Marc

Yes, I've seen that and tried to let me guide as much as I can. But there is no mentioning of cities data/format. And the \copy doesn't work... Any "restore" way of importing the data?

The 'cities' files are just excerpts from the geoname table. The dataformat is identical.

Marc

But then I don't understand why the field names are different.

fcl (cities) equals fclass (geoname) ?
fcodename (cities) equals ?
fclname (cities) equals ?
wikipedia (cities) equals ?

This is perhaps a bit dumb from my side, but I find it strange that it needs hours from the users' side to understand format and structure of the tables, before being able to import the stuff. You're doing a great job, right! Just to hint that perhaps one could add the CREATE TABLE etc. stuff for MySQL and Postgres somewhere in the download directory or directly in the dump file.

Sorry for any signs of incompetence from my side...

Great work!