GeoNames Forum

http://forum.geonames.org/gforum/posts/list/926.page does more automatically

Hi,

i have a question:
where i have to ADD "allCountries.txt" file in oder to copy it in postgres database

Thank you.

it's ok
we must define a path

exp: c:/name.txt

I'm completely new to postgres, but it appears to be a capitalization issue. The sample 'create.sql' file at the head of this thread quotes the field names for the countryInfo table, forcing the I in geonameId to uppercase. psql appears to lowercase field names typed in (I'm using 8.2.4 on debian testing), so it never matches the stored field name.

To fix, just remove the quotes round the field names before creating the table.

Updated countryinfo create table statement:

Code:

 CREATE TABLE countryinfo
 (
   iso_alpha2 character(2) NOT NULL,
   iso_alpha3 character(3),
   iso_numeric integer,
   fips_code character varying(3),
   name character varying(200),
   capital character varying(200),
   areainsqkm double precision,
   population integer,
   continent character(2),
   languages character varying(200),
   currencycode character(3),
   geonameid integer,
   neighbours character varying,
   tld character varying,
   currencyname character varying,
   postalcodeformat character varying,
   postalcoderegex character varying,
   phone character varying,
   equivalentfipscode character varying,
   CONSTRAINT countryinfo_pkey PRIMARY KEY (iso_alpha2)
 )

And the corresponding copy command is

Code:

 copy countryInfo  (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currencycode,currencyname,phone,postalcodeformat,postalcoderegex,languages,geonameId,neighbours,equivalentfipscode) 
 from 'countryInfo.txt' null as '';

To fix, just remove the quotes round the field names before creating the table. The sample 'create.sql' filemy sizegenetics customer reviews at the head of this thread quotes the field names for the countryInfo table, forcing the I in geonameId to uppercase. psql appears to lowercase field names typed in (I'm using 8.2.4 on debian testing), so it never matches the stored field name.

I'm completely new to postgres, but it appears to be a capitalization issue. The sample 'create.sql' https://signalscv.com/2021/09/how-to-lose-weight-fast-guide-how-to-get-skinny-fast-how-to-cut-weight/ how to lose weight fast how to get skinny fast how to be skinny fast how to get skinny how can i lose weight fast shark tank weight loss testogen gnc https://www.fingerlakes1.com/2021/10/05/sarms-for-sale-where-to-buy-sarms-online-review-guide/ sarms for sale buy sarms buy sarms online where to buy sarms sarms online https://www.fingerlakes1.com/2021/09/20/crazy-bulk-review-legal-steroids-for-sale-how-it-works-legal-steroids-gnc/ crazy bulk review legal steroids gn legal steroids review crazy bulk steroids for sale crazy bulk gnc zantrex black reviews modere trim reviews shark tank weight loss file at the head of this thread quotes the field names for the countryInfo table, forcing the I in geonameId to uppercase. psql appears to lowercase field names typed in (I'm using 8.2.4 on debian testing), so it never matches the stored field name.

To fix, just remove the quotes round the field names before creating the table.

I'd like to be able to do case-insensitive searches for place names. Is there any reason not to use the citext type for name, asciiname, etc?

Richard Tobin wrote:

I'd like to be able to do case-insensitive searches for place names. Is there any reason not to use the citext type for name, asciiname, etc?

I didn't get any reply to this, so I'm just reporting that it seems to work satisfactorily.

mapscheidt wrote:

And the corresponding copy command is

Code:

 copy countryInfo  (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currencycode,currencyname,phone,postalcodeformat,postalcoderegex,languages,geonameId,neighbours,equivalentfipscode) 
 from 'countryInfo.txt' null as '';

you can use the command shell ?

My take on this :

https://github.com/zedalaye/geonames_pg_import

This is the getgeo.sh script refactored and updated for recent dataset changes.

Changes :

- database naming normalization (camel_cased everything, primary and foreign keys where possible)
- extracted tables cleanup script into cleanup.sql
- extracted tables creation script into schema.sql
- extracted indexing and referential integrity updates into integrity.sql
- restored import of postal_codes (with a hard cleanup of duplicates and invalid rows)
- added hierarchy.txt
- added adminCode5.txt
- switched to alternateNamesV2.txt
- read arguments on command line (--from-scratch, --drop and --create)
- added a help message (--help)
- factored out dowload_file(), cleanup_header() and cleanup_comments()