Geocoding with SQL Server 2005

I recoded it as a TVF function. I modified the logic to return multiple matches, and did some code changes.

I used it to geocode 500 addresses, and it found 4 that returned multiple matches (3 of them at the address level). While it doesn't happen often, and the accuracy of the first match is probably good enough for most applications, if you want high accuracy, you should analyze all the matches returned.

select * from dbo.fncGeocodeByAddress ( '245 Union Ave', 'Pueblo', 'CO', '81003', 'YahooDemo' )
select * from dbo.fncGeocodeByAddress ( '7920 Stone Creek Dr.', 'Chanhassen', 'MN', '55317', 'YahooDemo' )
select * from dbo.fncGeocodeByLocation ( 'Springfield', 'YahooDemo' )
select * from dbo.fncGeocodeByAddress ( '100 East 42nd Street', 'new york', 'NY', Null, 'YahooDemo' )
select * from dbo.fncGeocodeByAddress ( '701 First Street', 'Sunnyvale', 'CA', Null, 'YahooDemo' )

By making it a TVF, it is more flexible, and you can call it in a select using CROSS APPLY

insert tmpAddressGeocode
(
      [AddressGuid]
      ,[Latitude]
      ,[Longitude]
      ,[Address]
      ,[City]
      ,[State]
      ,[Zip]
      ,[Country]
      ,[Precision]
      ,[Warning]
)
select top 100
addr.AddressGuid,
gc.*
from
dbo.AddressTbl addr with (NoLock)
left join tmpAddressGeocode alldone with (NoLock)
    on alldone.AddressGuid = addr.AddressGuid
cross apply dbo.fncGeocodeByAddress (
    addr.StreetAddress1 + coalesce (' ' + StreetAddress2,''),
    addr.CityName,
    addr.State,
    addr.Zip,
    'YahooDemo' ) gc
where
alldone.AddressGuid is Null

CREATE FUNCTION [dbo].[fncGeocodeByLocation](@Location nvarchar(4000), @appid nvarchar(100))
RETURNS TABLE (
[Latitude] float Null,
[Longitude] float Null,
[Address] nvarchar(80) Null,
[City] nvarchar(35) Null,
[State] nchar(2) Null,
[Zip] nvarchar(10) Null,
[Country] nvarchar(35) Null,
[Precision] nvarchar(10) Null,
[Warning] nvarchar(1024) Null
)
AS EXTERNAL NAME [YahooGCSQL].[sqlservercentral.Geocoding.YGeocode].[GeocodeByLocation]
GO

CREATE FUNCTION [dbo].[fncGeocodeByAddress](
@Address [nvarchar](4000),
@City [nvarchar](4000),
@State [nvarchar](4000),
@Zip [nvarchar](10),
@appid nvarchar(100)
)
RETURNS TABLE (
[Latitude] float Null,
[Longitude] float Null,
[Address] nvarchar(80) Null,
[City] nvarchar(35) Null,
[State] nchar(2) Null,
[Zip] nvarchar(10) Null,
[Country] nvarchar(35) Null,
[Precision] nvarchar(10) Null,
[Warning] nvarchar(1024) Null
)
AS EXTERNAL NAME [YahooGCSQL].[sqlservercentral.Geocoding.YGeocode].[GeocodeByAddress]
GO

    /// <summary>
    /// Query Yahoo Geocode API, and return an enumeration of results. Have to provide City and State or Zip or Location. Data is extracted for SQL by FillRow
    /// </summary>
    /// <param name="AppId">Yahoo application ID</param>
    /// <param name="StreetAddress">Street Address</param>
    /// <param name="City">City name</param>
    /// <param name="State">US State 2 letter abbreviation</param>
    /// <param name="Zip">Zip Code</param>
    /// <param name="Location">Free form address/location</param>
    /// <returns>Enumeration of addresses found</returns>
    private static IEnumerable GeocodeStart(SqlString AppId, SqlString StreetAddress, SqlString City, SqlString State, SqlString Zip, SqlString Location)
    {
      // Based on http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005.asp
      try
      {
        // Build the URL
        StringBuilder sb = new StringBuilder();
        sb.Append(AppId);
        if (!StreetAddress.IsNull)
        {
          sb.Append("&street=");
          sb.Append(StreetAddress);
        }
        if (!City.IsNull)
        {
          sb.Append("&city=");
          sb.Append(City);
        }
        if (!State.IsNull)
        {
          sb.Append("&state=");
          sb.Append(State);
        }
        if (!Zip.IsNull)
        {
          sb.Append("&zip=");
          sb.Append(Zip);
        }
        if (!Location.IsNull)
        {
          sb.Append("&location=");
          sb.Append(Location);
        }
        // Replace spaces and # with +
        // string Address = System.Web.HttpUtility.UrlEncode(StreetAddress);
        // It seems that HttpUtility.UrlEncode is not available here (Compact Framework?), so we have to do it "manually"
        // If need be, could use the implementation available at http://www.devx.com/vb2themax/Tip/19352 (just check for the = and & )
        sb.Replace('#', '+');
        sb.Replace(' ', '+');
        // Prepend the API URL
        sb.Insert(0, "http://api.local.yahoo.com/MapsService/V1/geocode?appid=");

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters")]
    public static void FillRow(object obj,
      out SqlDouble Latitude, out SqlDouble Longitude,
      out string Address, out string City, out string State, out string Zip, out string Country,
      out string Precision, out string Warning )
    {
      // XML elements and attributes are case sensitive.
      // Access by name to protect against API changes. It is slower, but not so bad compared with the cost of calling an Internet API
      DataRow row = (DataRow)obj;
      if (!String.IsNullOrEmpty(row["Latitude"].ToString()))
        Latitude = Convert.ToDouble(row["Latitude"].ToString());
      else
        Latitude = SqlDouble.Null;
      if (!String.IsNullOrEmpty(row["Longitude"].ToString()))
        Longitude = Convert.ToDouble(row["Longitude"].ToString());
      else
        Longitude = SqlDouble.Null;
      Address = (!String.IsNullOrEmpty(row["Address"].ToString())) ? row["Address"].ToString() : null;
      City = (!String.IsNullOrEmpty(row["City"].ToString())) ? row["City"].ToString() : null;
      State = (!String.IsNullOrEmpty(row["State"].ToString())) ? row["State"].ToString() : null;
      Zip = (!String.IsNullOrEmpty(row["Zip"].ToString())) ? row["Zip"].ToString() : null;
      Country = (!String.IsNullOrEmpty(row["Country"].ToString())) ? row["Country"].ToString() : null;
      // Better way to detect precision/warning columns w/o going back up to the columns collection? w/o resorting to exception trapping? w/o assuming indexes?
      // Alternate way = Precision = (row.ItemArray.Length >= 8) && !String.IsNullOrEmpty(row["precision"].ToString()) ? row["precision"].ToString() : null;
      Precision = (row.Table.Columns.Contains("precision")) && !String.IsNullOrEmpty(row["precision"].ToString()) ? row["precision"].ToString() : null;
      Warning = (row.Table.Columns.Contains("warning")) && !String.IsNullOrEmpty(row["warning"].ToString()) ? row["warning"].ToString() : null;
    }
}
};