SQL Server Spatial Data for Canada

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)

geonameid integer id of record in geonames database
name name of geographical point (utf8) varchar(200)
asciiname name of geographical point in plain ascii characters, varchar(200)
alternatenames alternatenames, comma separated varchar(5000)
latitude latitude in decimal degrees (wgs84)
longitude longitude in decimal degrees (wgs84)
feature class see http://www.geonames.org/export/codes.html, char(1)
feature code see http://www.geonames.org/export/codes.html, varchar(10)
country code ISO-3166 2-letter country code, 2 characters
cc2 alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code code for third level administrative division, varchar(20)
admin4 code code for fourth level administrative division, varchar(20)
population bigint (4 byte int)
elevation in meters, integer
gtopo30 average elevation of 30’x30′ (ca 900mx900m) area in meters, integer
timezone the timezone id (see file timeZone.txt)
modification date date of last modification in yyyy-MM-dd format

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

I then created the following table
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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
[/sql]

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.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ‘BULK INSERT CAGeoNames
FROM ”C:CACA.txt”
WITH (ROWTERMINATOR = ”’+CHAR(10)+”’)’
EXEC(@bulk_cmd)
GO

[/sql]

Add a GEOGRAPHY type column

Next up, we’ll use SQL Server 2008’s new GEOGRAPHY data type.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
ALTER TABLE [dbo].[CAGeoNames]
ADD [GeoLocation] GEOGRAPHY
GO

[/sql]

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.
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

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

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s