GeoNames Forum

It would be nice if you provided table creation scripts for different database systems on the surface of your website (so people don't have to dig and search through the forums for something that may or may not be there and they may not know how to search to find).

To get started, here is an example sql server script for tables to hold the geonames data:

--create schema geonamesdotorg
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.locations') AND type in (N'U'))
DROP TABLE geonamesdotorg.locations
GO
create table geonamesdotorg.locations
(
geonameid int,
name nvarchar(200),
asciiname varchar(200),
alternatenames nvarchar(max),
latitude float(53),
longitude float(53),
feature_class char(1),
feature_code varchar(10),
country_code char(2),
cc2 varchar(60),
admin1_code varchar(20),
admin2_code varchar(80),
admin3_code varchar(20),
admin4_code varchar(20),
population bigint,
elevation int,
dem int,
timezone varchar(40),
modification_date datetime
)

--select count(*) from geonamesdotorg.locations
--select top 1000 * from geonamesdotorg.locations
---------------------------------------------------------------------------------------------
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.cities') AND type in (N'U'))
DROP TABLE geonamesdotorg.cities
GO
create table geonamesdotorg.cities
(
geonameid int,
name nvarchar(200),
asciiname varchar(200),
alternatenames nvarchar(max),
latitude float(53),
longitude float(53),
feature_class char(1),
feature_code varchar(10),
country_code char(2),
cc2 varchar(60),
admin1_code varchar(20),
admin2_code varchar(80),
admin3_code varchar(20),
admin4_code varchar(20),
population bigint,
elevation int,
dem int,
timezone varchar(40),
modification_date datetime
)

--select count(*) from geonamesdotorg.locations
--select top 1000 * from geonamesdotorg.locations
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.alternate_names') AND type in (N'U'))
DROP TABLE geonamesdotorg.alternate_names
GO
create table geonamesdotorg.alternate_names
(
alternateNameId int,
geonameid int,
isolanguage varchar(7),
alternate_name nvarchar(200),
isPreferredName bit,
isShortName bit,
isColloquial bit,
isHistoric bit
)

--select count(*) from geonamesdotorg.alternate_names
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.continent_codes') AND type in (N'U'))
DROP TABLE geonamesdotorg.continent_codes
GO
create table geonamesdotorg.continent_codes
(
continent_codes_id smallint identity primary key,
abbreviation varchar(2),
name varchar(20),
geoname_id int
)
GO
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('AF','Africa',6255146)
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('AS','Asia',6255147)
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('EU','Europe',625514

insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('NA','North America',6255149)
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('OC','Oceania',6255151)
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('SA','South America',6255150)
insert into geonamesdotorg.continent_codes(abbreviation,name,geoname_id) values ('AN','Antarctica',6255152)

--select count(*) from geonamesdotorg.continent_codes
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.UserTags') AND type in (N'U'))
DROP TABLE geonamesdotorg.UserTags
GO
create table geonamesdotorg.UserTags
(
geoname_id int,
tag nvarchar(75)
)

--select count(*) from geonamesdotorg.UserTags

---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.admin1Codes') AND type in (N'U'))
DROP TABLE geonamesdotorg.admin1Codes
GO
create table geonamesdotorg.admin1Codes
(
concatentated_id varchar(40),
subdivision_name nvarchar(100),
subdivision_name_ascii varchar(100),
geoname_id int
)

--select count(*) from geonamesdotorg.admin1Codes
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.admin2Codes') AND type in (N'U'))
DROP TABLE geonamesdotorg.admin2Codes
GO
create table geonamesdotorg.admin2Codes
(
concatentated_id varchar(40),
subdivision_name nvarchar(100),
subdivision_name_ascii varchar(100),
geoname_id int
)
--select count(*) from geonamesdotorg.admin2Codes
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.featureCodes') AND type in (N'U'))
DROP TABLE geonamesdotorg.featureCodes
GO
create table geonamesdotorg.featureCodes
(
feature_code varchar(10),
short_description nvarchar(100),
long_description nvarchar(500)
)

--select count(*) from geonamesdotorg.featureCodes
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.timeZones') AND type in (N'U'))
DROP TABLE geonamesdotorg.timeZones
GO
create table geonamesdotorg.timeZones
(
CountryCode varchar(2),
TimeZoneId nvarchar(50),
GMT_Offset float,
DST_Offset float,
Raw_Offset float,
)

--select count(*) from geonamesdotorg.timeZones

---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.iso_languagecodes') AND type in (N'U'))
DROP TABLE geonamesdotorg.iso_languagecodes
GO
create table geonamesdotorg.iso_languagecodes
(
Language_Code_ISO_639_3 varchar(4),
Language_Code_ISO_639_2 varchar(15),
Language_Code_ISO_639_1 varchar(4),
description nvarchar(100)
)

--select count(*) from geonamesdotorg.iso_languagecodes
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.countryInfo') AND type in (N'U'))
DROP TABLE geonamesdotorg.countryInfo
GO
create table geonamesdotorg.countryInfo
(
ISO varchar(2),
ISO3 varchar(3),
ISO_Numeric varchar(3),
fips varchar(2),
Country nvarchar(max),
Capital nvarchar(max),
Area_in_sq_km float(53),
Population bigint,
Continent varchar(2),
top_level_domain_name varchar(15),
CurrencyCode varchar(10),
CurrencyName nvarchar(max),
Phone_Code varchar(20),
Postal_Code_Format varchar(15),
Postal_Code_Regex varchar(60),
Languages nvarchar(max),
geonameid int,
neighbours nvarchar(max),
EquivalentFipsCode varchar(60)
)
---------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'geonamesdotorg.countryInfo') AND type in (N'U'))
DROP TABLE geonamesdotorg.countryInfo
GO
create table geonamesdotorg.countryInfo
(
ISO varchar(2),
ISO3 varchar(3),
ISO_Numeric varchar(3),
fips varchar(2),
Country nvarchar(50),
Capital nvarchar(30),
Area_in_sq_km float(53),
Population bigint,
Continent varchar(2),
top_level_domain_name varchar(3),
CurrencyCode varchar(3),
CurrencyName nvarchar(20),
Phone_Code varchar(20),
Postal_Code_Format varchar(70),
Postal_Code_Regex varchar(200),
Languages nvarchar(150),
geonameid int,
neighbours nvarchar(41),
EquivalentFipsCode varchar(2)
)

--select count(*) from geonamesdotorg.countryInfo

Hi geoDave,

Why you want to have locations and cities table with same column names? Is typo?

Thanks,
SMK