Category: SQL Server

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

TruncationException: Trying to convert return value or output parameter of size XXX bytes to a T-SQL type with a smaller size limit of 8000 bytes.

Problem

You receive “TruncationException: Trying to convert return value or output parameter of size XXX bytes to a T-SQL type with a smaller size limit of 8000 bytes” error while trying to execute CLR stored procedure in SQL Server 2008.

Explanation

CLR stored procedure is trying to return via output parameter string that exceeds 8000 bytes. Declaring output parameter as nvarchar(MAX) is not enough to overcome 8000 bytes limit.

Solution

In order to return strings via CLR procedures that are more than 8000 bytes you need to apply a special attribute called SqlFacet(MaxSize) to the output parameter.

Example in VB.NET:
Public Shared Sub MySP(<SqlFacet(MaxSize:=-1)> ByRef ret As SqlString)

Example in C#:
public static SqlString MySP([param: SqlFacet(MaxSize=-1)] SqlString ret)