Importing GeoNames data into SQL Server

Problem

Import GeoNames data into SQL Server 2014.

Solution

Download GeoNames file from here. I’m using just USA data in my example (us.txt).
Create permanent table for storing data. We will only need specific columns from the original feed:

CREATE TABLE GeoNames(
	geonameid int NOT NULL PRIMARY KEY,
	name nvarchar(200) NULL,
	latitude float NULL,
	longitude float NULL,
	feature_class char(2) NULL,
	feature_code nvarchar(10) NULL,
	[state] nvarchar(20) NULL,
	geoLocation GEOGRAPHY NULL
)

Create temporary table for storing exact copy of the GeoNames data:

CREATE TABLE #GeoNames(
	geonameid int NOT NULL,
	name nvarchar(200) NULL,
	asciiname nvarchar(200) NULL,
	alternatenames nvarchar(max) NULL,
	latitude float NULL,
	longitude float NULL,
	feature_class char(2) NULL,
	feature_code nvarchar(10) NULL,
	country_code char(3) NULL,
	cc2 char(60) NULL,
	admin1_code nvarchar(20) NULL,
	admin2_code nvarchar(80) NULL,
	admin3_code nvarchar(20) NULL,
	admin4_code nvarchar(20) NULL,
	population int NULL,
	elevation int NULL,
	dem int NULL,
	timezone varchar(40) NULL,
	modification_date date NULL
)

Bulk import data into temp table:

BULK INSERT #GeoNames
FROM 'C:\temp\GeoNames\us.txt'
	WITH(
		FIELDTERMINATOR = '\t',
		ROWTERMINATOR = '0x0a'
	)
GO

In this example I’m only interested in school(SCH) and churches (CH) data for DE, PA and MD states. So drop the rest of the data:

DELETE FROM #GeoNames WHERE admin1_code NOT IN ('de','pa','md') OR admin1_code IS NULL OR feature_code IS NULL;
DELETE FROM #GeoNames WHERE feature_code NOT IN ('SCH','CH');

Now copy data from the temp table into permanent:

TRUNCATE TABLE GeoNames;
GO
INSERT 
	GeoNames SELECT geonameid, name, latitude, longitude, feature_class, feature_code, admin1_code,
	geography::Point(latitude, longitude, 4326)
FROM #GeoNames

Finally add a spatial index:

CREATE SPATIAL INDEX [inx_GeoNames_geoLocation]  ON  GeoNames(geoLocation);