Download Tutorial Materials
Download Demo Application

Steps

1. Prepare Files

Unzip the GeocodeSample.zip file to C:\GeocodeSample. Please note that the included GeocodeUSA.dll is from the free evaluation edition of Geocode USA; if you are a licensed user of the full version, you will want to use your own copy of GeocodeUSA.dll instead.

2. Configure Database Environment

Open the SQL Server 2005 Surface Area Configuration tool and select the Surface Area Configuration for Features.

Enable the CLR Integration so that you can CREATE ASSEMBLY.

Open SQL Server Management Studio and create a new database. In this example we used: GeocodeSample. (The Database Owner must be a SQL server account, not a domain account.)

Once the database has been created, use the following SQL command to alter the database and set TRUSTWORTHY to ON, so that you can use UNSAFE PERMISSION SET.

ALTER DATABASE GeocodeSample SET TRUSTWORTHY ON

Create a new assembly and choose the SQLGeocodeUSA.dll from the Path to Assembly file browser. Be sure to set the Permission Set as Unsafe. Click OK. Now you will see two assemblies: GeoCodeUSA and SQLGeocodeUSA. Double-check that the Permission Set is set to Unsafe.

Now you can create a stored procedure from the assembly. For example:

CREATE PROCEDURE ReverseGeocode
    -- Set default value, idx files directory
    @GeocodeIdxFolder nvarchar(200)='C:\GeocodeIdx2006fe',
    @Longitude float = 0.0,
    @Latitude float = 0.0
AS
    EXTERNAL NAME
        SQLGeocodeUSA.SQLGeocodeUSAEngine.ReverseGeoCode

Then, you can use this procedure as common stored procedure:

EXECUTE [GeocodeSample].[dbo].[ReverseGeoCode];
     @Longitude = -95.2808458305725,
     @Latitude = 38.9553176794061

Which gives you these results:

3. Instantly Configuring the Database Environment

First, start SQL Server Management Studio. Open the GeocodeSample.sql file, then execute the SQL statement.

4. Client Demonstration

Demonstration code is also provided. The SQLGeocodeUSA project is a class library project, which acts as a bridge between the Geocode USA core and SQL Server 2005. You can use the SQLGeocodeClientDemo to demonstrate how to call the stored procedure in client code.