<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
	<channel>
		<title><![CDATA[Latest posts for the topic "Table Creation Scripts"]]></title>
		<link>http://forum.geonames.org/gforum/posts/list/4.page</link>
		<description><![CDATA[Latest messages posted in the topic "Table Creation Scripts"]]></description>
		<generator>JForum - http://www.jforum.net</generator>
			<item>
				<title>Table Creation Scripts</title>
				<description><![CDATA[ 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',6255148)
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]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/7802.page#16348</guid>
				<link>http://forum.geonames.org/gforum/posts/list/7802.page#16348</link>
				<pubDate><![CDATA[Sat, 19 Apr 2014 10:17:50]]> GMT</pubDate>
				<author><![CDATA[ geoDave]]></author>
			</item>
			<item>
				<title>Re:Table Creation Scripts</title>
				<description><![CDATA[ Hi geoDave,

Why you want to have locations and cities table with same column names? Is typo?

Thanks,
SMK]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/7802.page#19752</guid>
				<link>http://forum.geonames.org/gforum/posts/list/7802.page#19752</link>
				<pubDate><![CDATA[Mon, 22 Sep 2014 10:46:19]]> GMT</pubDate>
				<author><![CDATA[ SMK]]></author>
			</item>
	</channel>
</rss>