Integrating Virtual Earth and GeoNames

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".

image

Select "Tab" as the column delimiter.

image

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".

image

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

image

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

image

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.

image

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.

Advertisements
This entry was posted in Virtual Earth. Bookmark the permalink.

3 Responses to Integrating Virtual Earth and GeoNames

  1. Adam Hill says:

    The sample app link is missing from the post.

  2. Johannes says:

    The embedded links to my SkyDrive are broken. I have now created a href.

  3. Tor says:

    Johannes,
     
    I am trying to compile the following Stored procedure in SQL Server 2008. It gives me two errors.
    Here is the Stored Procedure.
    CREATE PROCEDURE GetBeachGml @ulLat nvarchar(10), @ulLong nvarchar(10), @brLat nvarchar(10), @brLong nvarchar(10)
    AS
    — Create a rectangle geography instance based on bounding box of the Virtual Earth map
    DECLARE @SearchRectangleString VARCHAR(MAX);
    SET @SearchRectangleString = ‘POLYGON((‘ + @ulLat + ‘ ‘ + @ulLong + ‘,’ + @brLat + ‘ ‘ + @ulLong + ‘,’ + @brLat + ‘ ‘ + @brLong + ‘,’ + @ulLat + ‘ ‘ + @brLong + ‘,’ + @ulLat + ‘ ‘ + @ulLong + ‘))’;
    DECLARE @SearchRectangle geography;
    SET @SearchRectangle = geography::STPolyFromText(@SearchRectangleString, 4326)
    –Return all Beaches in the search rectangle
    SELECT name, Geom.AsGml() As PoiGML
    FROM geoname
    WHERE (@SearchRectangle.STIntersects(GEOM) = 1) AND (fcode LIKE ‘BCH’)
    The first error reads:

    Msg 207, Level 16, State 1, Procedure GetBeachGml, Line 13
    Invalid column name ‘GEOM’.
    When I hold the cursor over the Geom.AsGml()  which is underlined in red, the tool tip error term it reads:
    Cannot find either column "Geom" or the user-defined function or aggregate "Geom.AsGml" or the name is ambiguous.
    It also complains about the
    DECLARE @SearchRectangle geography;
    It states that it is the wrong datatype.
    Here is the table I created before I tried to compile the Stored Procedure:
    USE [VirtualEarthDB]GO
    /****** Object:  Table [dbo].[geoname]    Script Date: 06/24/2008 06:58:57 ******/SET ANSI_NULLS ONGO
    SET QUOTED_IDENTIFIER ONGO
    CREATE TABLE [dbo].[geoname]( [geonameid] [int] NOT NULL, [name] [nvarchar](200) NULL, [asciiname] [nvarchar](200) NULL, [alternatenames] [nvarchar](4000) NULL, [latitude] [decimal](18, 15) NULL, [longitude] [decimal](18, 15) NULL, [fclass] [nchar](1) NULL, [fcode] [nvarchar](10) NULL, [country] [nvarchar](2) NULL, [cc2] [nvarchar](60) NULL, [admin1] [nvarchar](20) NULL, [admin2] [nvarchar](80) NULL, [admin3] [nvarchar](20) NULL, [admin4] [nvarchar](20) NULL, [population] [int] NULL, [elevation] [int] NULL, [gtopo30] [int] NULL, [timezone] [nvarchar](40) NULL, [moddate] [date] NULL,PRIMARY KEY CLUSTERED ( [geonameid] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
    GO
     
    Any Suggestions would be gratly appreciated.
    By the way. Excellent Tutorials. Keep them comming.
    Thanks
    Tor.
     
     

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