I needed to load spatial data for Canadian places into a SQL Server 2008 R2 database. Good thing there is geonames.org, which is a geographic database that contains millions of placenames and their corresponding geolocations (latitude and longitude).

Here are the steps I took to create this spatial database.

Download the geospatial data

So I first downloaded the Canadian geolocation database from http://download.geonames.org/export/dump/ called CA.zip (according to the geonames readme).

This export file contains the following fields (excerpt from the readme file)

Create a table in your SQL Server database that maps to all these columns

I then created the following table

CREATE TABLE [dbo].[CAGeoNames] 
(
	[geonameid] INT PRIMARY KEY,
	[name] VARCHAR(200),
	[asciiname] VARCHAR(200),
	[alternatenames] VARCHAR(5000),
	[latitude] DECIMAL(38,10),
	[longitude] DECIMAL(38,10),
	[featureclass] VARCHAR(10),
	[featurecode] VARCHAR(10),
	[countrycode] VARCHAR(2),
	[cc2] VARCHAR(60),
	[admin1code] VARCHAR(20),
	[admin2code] VARCHAR(80),
	[admin3code] VARCHAR(20),
	[admin4code] VARCHAR(20),
	[population] bigINT,
	[elevation] INT,
	[gtopo30] INT,
	[timezone] VARCHAR(100),
	[modificationdate] DATETIME
)
GO

Import the data

I initially used BULK IMPORT with a ROWTERMINATOR of n because I know each line is terminated by a linefeed. But for some reason, n didn’t work. Neither did rn.

Baffled, I know that n should have worked, but I conceded I should consult Books Online. Turns out Books Online has an exact sample for importing a file that was produced by UNIX .. and for some reason it uses dynamic SQL. Not wanting to waste any more time with the import, I just used the Books Online sample as a template, and true enough, it worked.

One of these days though I will come back to this issue and figure out why a bare n ROWTERMINATOR doesn’t work.

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT CAGeoNames
FROM ''C:CACA.txt'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)
GO

Add a GEOGRAPHY type column

Next up, we’ll use SQL Server 2008’s new GEOGRAPHY data type.

ALTER TABLE [dbo].[CAGeoNames]
ADD [GeoLocation] GEOGRAPHY
GO

Create GEOGRAPHY data based on the existing longitude and latitude values

And last but not least, we are going to derive the GEOGRAPHY value based on the longitude and latitude values that we just imported. We are going to use the Parse function that comes with the GEOGRAPHY data type.

UPDATE [dbo].[CAGeoNames]
SET [GeoLocation] = Geography::Parse('POINT(' + 
                    CAST([longitude] AS VARCHAR(20)) + ' ' + 
                    CAST([latitude] AS VARCHAR(20)) + ')')
GO

Ta-Daa!

That’s it. Now I have my own Canadian geolocation database. I can start using this database now for my SSRS R2/Bing Maps presentations!

Be Sociable, Share!

• 
• 

Related posts:

1. How to Import Shapefile (.shp) Spatial Data into SQL Server 2008 …
2. Transfer data from MySQL to SQL Server Using SSIS …
3. Generating Random Data for SQL Server and MySQL …
4. Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas …
5. Downloading Vancouver Weather Data Using PowerShell …
6. Calling Students for Imagine Cup Canada 2012 …