GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Import GeoNames dump into Postgres  XML
Forum Index -> FAQ - frequently asked questions Go to Page: 1, 2 Next 
Author Message
bwakkie



Joined: 25/11/2008 12:49:52
Messages: 7
Offline

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



Joined: 26/03/2011 15:24:41
Messages: 6
Offline

Hi,

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

Thank you.

snrt



Joined: 26/03/2011 15:24:41
Messages: 6
Offline

it's ok
we must define a path

exp: c:/name.txt
Darrius



Joined: 24/03/2016 21:14:32
Messages: 1
Offline

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.

People always strongly suggest these probiotics for women to me.
mpscheidt



Joined: 25/03/2016 12:48:43
Messages: 2
Offline

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)
 )
 
mpscheidt



Joined: 25/03/2016 12:48:43
Messages: 2
Offline

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 '';
 
Sharoun



Joined: 14/02/2017 18:17:38
Messages: 1
Offline

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.
Qeuinton



Joined: 06/03/2019 11:19:21
Messages: 1
Offline

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 will be using v tight gel this summer
Richard Tobin



Joined: 06/02/2008 16:22:48
Messages: 7
Offline

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



Joined: 06/02/2008 16:22:48
Messages: 7
Offline

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.
DerrickAntoine



Joined: 17/10/2020 00:05:31
Messages: 1
Offline

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 ?
pierrey



Joined: 27/07/2022 08:46:01
Messages: 1
Offline

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()
 
Forum Index -> FAQ - frequently asked questions Go to Page: 1, 2 Next 
Go to:   
Powered by JForum 2.1.5 © JForum Team