Sunday, August 20, 2006 

Importing the Geo-Names database into MsSql Server

 The article you are reading has moved! It is now available at: http://blog.tinisles.com/2006/08/importing-the-geo-names-database-into-mssql-server/
Update (26-Jul-2009): This article is now a little outdated. The table now has some extra columns. Have a look at Loading GeoNames Data Into SQL Server 2008 by EdKatibah for an up-to-date approach. I've successfully imported the newest allCountries.txt with the following steps:
  1. UNIX2DOS to update the line endings of AllCountries.txt
  2. Same nant script below to convert AllCountries.txt to UNICODE
  3. CREATE TABLE now looks like this: 
    CREATE TABLE [GeoNames] (
    [GeonameID] [int] NOT NULL ,
    [Name] [nvarchar] (200) NOT NULL ,
    [AnsiName] [varchar] (200) NULL ,
    [AlternateNames] [nvarchar] (MAX) NULL ,
    [Latitude] [float] NOT NULL ,
    [Longitude] [float] NOT NULL ,
    [FeatureClass] [char] (1) NULL ,
    [FeatureCode] [varchar] (10) NULL ,
    [CountryCode] [char] (2) NULL ,
    [CC2] [varchar] (60) NULL ,
    [Admin1Code] [varchar] (20) NULL ,
    [Admin2Code] [varchar] (80) NULL ,
    [Admin3Code] [varchar] (20) NULL ,
    [Admin4Code] [varchar] (20) NULL ,
    [Population] [bigint] NOT NULL ,
    [Elevation] [int] NULL ,
    [GTopo30] [int] NULL ,
    [Timezone] [varchar] (50) NULL ,
    [ModificationDate] [datetime] NULL 
)
  4. EdKatibah's BULK INSERT works nicely: 
    BULK INSERT GeoNames
FROM 'D:\Developer\Geonames\allCountries_unicode.txt'
WITH(
   DATAFILETYPE = 'widechar',
   FIELDTERMINATOR = '\t',
   ROWTERMINATOR = '\n'
)
GO
Using BULK INSERT to load the data is MUCH nicer than using BCP. As BULK INSERT will actually tell you where and WHY and import failed. I was originally trying to do this with BCP. I had DECIMAL types for latitude and longitude - which resulted in an infuriating error because a handful of rows have scientific notation representation.

Geo-Names "provides free geo-data such as geographical names and postal codes. The database contains over 6 million entries for geographical names whereof 2.2 million cities and villages". The offer their content via a webservice, and as a database download.

I'm using a subset of this data for my 'learn ASP.NET 2.0' project, so I've imported the database export into my SQL Server. Here's the steps I followed:

  • Create a table that looks like this (note the nvarchar columns for the extendend characters): 
    
CREATE TABLE [CityNames] (
    [GeonameID] [int] NOT NULL ,
    [Name] [nvarchar] (200) NOT NULL ,
    [AnsiName] [varchar] (200) NOT NULL ,
    [Latitude] [decimal](18, 15) NOT NULL ,
    [Longitude] [decimal](18, 15) NOT NULL ,
    [FeatureCode] [varchar] (10) NULL ,
    [CountryCode] [char] (2) NULL ,
    [Admin1Code] [varchar] (20) NULL ,
    [Population] [int] NOT NULL 
)
  • The database export is a UTF8 file - MSSQL DTS import wants a UTF16 file. Easy to convert a file by opening it in Textpad/VS.Net and using 'Save As' to change the encoding. Not so easy with the 500meg 'allCountries' file. The nant copy task works well for this: 
    <target name="build">
    <copy file="allCountries.txt" 
    todir="Converted" inputencoding="UTF-8" 
    outputencoding="UNICODE"/>
</target>
  • Import the converted file via the DTS Import/Export Wizard. In Enterprise Manager right-click the database > All Tasks > Import Data...
  • Follow the prompts to import the converted file. On the file format screen select the following options: File Type = Unicode, Row Delimeter = {LF}, Text Qualifer = .
  • If all the names look neatly lined up on the next screen your file has imported okay.
  • In the next two steps select the destination database / table created in the first step.
  • Just to make sure all the UTF-16 content has come across okay - jump into Query Analyzer and run something like this: 
    SELECT AlternateNames FROM GeoNames WHERE GeonameID=2147714

Labels:

8:25 pm | |