Introduction

In my previous posting I mentioned that there are business listings in Virtual Earth and that enterprise customers can additionally leverage the point of interest databases in the MapPoint Web Service. However, there are a lot of other interesting sources on the web of which some are even available for free. One of them is GeoNames.

"The GeoNames geographical database is available for download free of charge under a creative commons attribution license. It contains over eight million geographical names and consists of 6.5 million unique features whereof 2.2 million populated places and 1.8 million alternate names. All features are categorized into one out of nine feature classes and further subcategorised into one out of 645 feature codes. The data is accessible free of charge through a number of webservices and a daily database export."

In this example I want to look at the GeoNames database as a source for additional points of interest. Let's take the beaches which are neither in Virtual Earth nor in the MapPoint Web Service. In GeoNames there are ~ 7,000 beaches.

Importing the GeoNames database into SQL Server

Start by downloading the allCountries.zip from the GeoNames download site. Although the data dump is available as a tab-delimited text-file in UTF-8 encoding the import into SQL Server is not straight forward since the SQL Server Integration Service expects the data in UTF-16 formats. Now you could just open the file in Visual Studio and save with a different encoding but since the data file for all countries has a size ~750 MB that wouldn't be a lot of fun. Fortunately there is a great tool which can help us out here: NAnt.

After you downloaded the binaries:

• Create a file called nant.bat in a directory that is included in the system environment variable "PATH" e.g. "C:\WINDOWS"
• Add the following to nant.bat:
  @echo off
  "[Your Path]\NAnt.exe" %*
• To convert the geonames file allCountries.txt from UTF-8 to UTF-16 create a simple XML-file as NAnt build-file like this

  xml version="1.0"?>
   <project name="geoname" default="build" basedir=".">
   <target name="build">
   <copy file="allCountries.txt" 
   todir="Converted" inputencoding="UTF-8" 
   outputencoding="UNICODE"/>
   target>
   project>
  

  
  
• Then run the command "nant -buildfile:NantBuild.xml"

Now that we have the GeoNames-file in a format that SQL Server understands create a new table in your SQL Server database:

CREATE TABLE geoname ( 

    geonameid int PRIMARY KEY, 

    name nvarchar(200), 

    asciiname nvarchar(200), 

    alternatenames nvarchar(4000), 

    latitude decimal(18,15), 

    longitude decimal(18,15), 

    fclass nchar(1), 

    fcode nvarchar(10), 

    country nvarchar(2), 

    cc2 nvarchar(60), 

    admin1 nvarchar(20), 

    admin2 nvarchar(80), 

    admin3 nvarchar(20), 

    admin4 nvarchar(20), 

    population int, 

    elevation int, 

    gtopo30 int, 

    timezone nvarchar(40), 

    moddate date) 

Now bring up the "Import Data" Wizard in the SQL Server Management Studio and select the "Flat File Source".

Select "Tab" as the column delimiter.

In the advanced options click on "Suggest Types" and make sure that the types in the data source correspond with the SQL data types. The click "Next".

Select the database you want to import into and click "Next again".

Now select the table you created before and click "Next" until the import starts.

Now that we have the data in our own database we can create a Virtual Earth application with database access as described in one of my previous posting.

You will fin the sample application here:

This sample application also contains some SQL statements and the sample code to spatially enable the database if you have a SQL Server 2008. The making of has been described in one of my previous postings.