Latest posts for the topic "How to import the cities data into postgres?"

How to import the cities data into postgres?

luftikus143 — GMT

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 ____________________________________________________________________

Re:How to import the cities data into postgres?

marc — GMT

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

Re:How to import the cities data into postgres?

luftikus143 — GMT

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?

Re:How to import the cities data into postgres?

marc — GMT

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

Re:How to import the cities data into postgres?

luftikus143 — GMT

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!