Geocoding with SQL Server 2005

  • This article came just in time. I used to work with address normalization and geocoding with a prior employer that had premium geocoding libraries available (like Group1 Centrus aka Sagent Centrus), so I know what is possible. Now I'm having to do similar work with less resources at a new place, and I'm also trying to give it a SQL 2005 spin. This article hits the mark, and saves me some work. Thank you.

    Normally, for this type of service, you want to normalize/standarize the addresses before you geocode them (which fixes the apartment number issue). You can develop your own code to do this, or you can use an address normalization package. For the US and Canada, low cost alternatives include Semaphore Corp ZP4, and ZipInfo Perfect Address.

    I found this site http://www.batchgeocode.com that allows you to geocode multiple addresses also using the Yahoo API, if you only need to do a one time run. You can optionally map the first 100 (using Google).

    If you don't need high accuracy, need to geocode a large number of addresses quickly, and can live with zip level geocodes, a free alternative is to use the US Census ZCTAs (Zip Code Tabulation Areas). You can download the 2000 ZCTA file, and query it by zip code (or by major Zip Code). Not all zip codes are available, and some zip codes have changed since then, but it gets you pretty close. There is supposed to be a ZCTA 2003 file, but I haven't been able to find it.

  • I like the article and the example.

    I've tried to install it on my local sql2005STD instance wich has clr enabled and my testdb is trustworthy.

    The vb.net version as well as the c#.net version do not work

    I did add //Catch any errors here

                throw new Exception("mynewexception", ex);

    When I execute the url in internet explorer I receive the correct results.

    When executed with the sproc it says :

    Msg 6522, Level 16, State 1, Procedure spclr_GeocodingC, Line 0

    A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_GeocodingC':

    System.Exception: mynewexception---> System.Net.WebException: The remote name could not be resolved: 'api.local.yahoo.com'

    System.Net.WebException:

    at System.Net.HttpWebRequest.GetResponse()

    at System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)

    at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)

    at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)

    at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)

    at System.Threading.CompressedStack.runTryCode(Object userData)

    at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

    at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)

    at System.Xml.XmlTextReaderImpl.OpenUrl()

    at System.Xml.XmlTextReaderImpl.Read()

    at System.Xml.XmlTextReader.Read()

    at System.Xml.XmlReader.MoveToContent()

    at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)

    at System.Data.DataSet.ReadXml(XmlReader reader)

    at StoredProcedures.spclr_GeocodingC(String Address, String City, String State, String appid)

    System.Exception:

    at StoredProcedures.spclr_GeocodingC(String Address, String City, String State, String appid)

    .

    This server is running with the same account I am logged in

    Any hints ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I haven't seen this before & I'm somewhat confused. The error would indicate that you can't resolve the URL, so maybe a DNS issue upstream from you. But then you said that it works in IE, which means your machine is indeed resolving the URL. Do you have any firewalls turned on on your local PC? Or anything that might be blocking http traffic coming from things other than IE? Not sure.......Could you try pulling some of the Yahoo API call out into a Windows form, and go into debug and watch as you step through?

  • Here it is ... my vbform :

    form fields : streettxt , citytxt, statetxt , datagridview Resultsdgv

    form button : CallWebServicebtn

    here's the code :

    Imports

    System

    Imports

    System.Data

    'Imports System.Data.SqlClient

    'Imports System.Data.SqlTypes

    'Imports Microsoft.SqlServer.Server

    Imports

    System.Net

    Imports

    System.Xml

    Imports

    System.Xml.XPath

    Imports

    System.Text

    Public

    Class FormStartup

    Private Sub CallWebServicebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CallWebServicebtn.Click

    Dim ds As New DataSet

    Try

    ds = SPCLR_Geocode(Streettxt.Text.ToString, Citytxt.Text.ToString, Statetxt.Text.ToString,

    "myyahootestappid")

    If ds.Tables.Count > 0 Then

    Resultdgv.DataSource = ds.Tables(0)

    Else

    Resultdgv.DataSource =

    Nothing

    End If

     

    Catch ex As Exception

    MessageBox.Show(ex.ToString)

    End Try

    End Sub

    Public Function SPCLR_Geocode(ByVal Address As String, ByVal City As String, ByVal State As String, ByVal appid As String) As DataSet

    'Declare string for URL

    Dim URL As String = "_empty_"

    'Yahoo test appid "myyahootestappid"

    Try

    'Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo

    Address = Address.Replace(

    " ", "+")

    'Build the URL

    URL =

    "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State

    ' http://api.local.yahoo.com/MapsService/V1/geocode?appid=myyahootestappid&street=8&city=seattle&state=wa

    '

    'Get the XML response

    Dim xmlread As New XmlTextReader(URL)

    ' Create the record and specify the metadata for the columns.

    'Dim record As New SqlDataRecord(New SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2), New SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100))

    'Create a Dataset to hold the results

    Dim ds As New DataSet()

    'Fill the DS

    ds.ReadXml(xmlread)

    ' Mark the begining of the result-set.

    ' SqlContext.Pipe.SendResultsStart(record)

    'If the response included an error, then the array length is different, 0-7

    'If ds.Tables(0).Rows(0).ItemArray.Length.Equals(9) Then

    ' record.SetString(0, ds.Tables(0).Rows(0).ItemArray(0).ToString())

    ' record.SetString(1, ds.Tables(0).Rows(0).ItemArray(1).ToString())

    ' record.SetString(2, ds.Tables(0).Rows(0).ItemArray(2).ToString())

    ' record.SetString(3, ds.Tables(0).Rows(0).ItemArray(3).ToString())

    ' record.SetString(4, ds.Tables(0).Rows(0).ItemArray(4).ToString())

    ' record.SetString(5, ds.Tables(0).Rows(0).ItemArray(5).ToString())

    ' record.SetString(6, ds.Tables(0).Rows(0).ItemArray(6).ToString())

    ' record.SetString(7, ds.Tables(0).Rows(0).ItemArray(7).ToString())

    ' record.SetString(8, ds.Tables(0).Rows(0).ItemArray(8).ToString())

    ' 'If the response did not include an error, then the array length is 0-6

    'Else

    ' record.SetString(0, ds.Tables(0).Rows(0).ItemArray(0).ToString())

    ' record.SetString(1, ds.Tables(0).Rows(0).ItemArray(1).ToString())

    ' record.SetString(2, ds.Tables(0).Rows(0).ItemArray(2).ToString())

    ' record.SetString(3, ds.Tables(0).Rows(0).ItemArray(3).ToString())

    ' record.SetString(4, ds.Tables(0).Rows(0).ItemArray(4).ToString())

    ' record.SetString(5, ds.Tables(0).Rows(0).ItemArray(5).ToString())

    ' record.SetString(6, ds.Tables(0).Rows(0).ItemArray(6).ToString())

    ' record.SetString(7, "Precision Good")

    ' record.SetString(8, "No Error")

    'End If

     

    'SqlContext.Pipe.SendResultsRow(record)

    ' Mark the end of the result-set.

    'SqlContext.Pipe.SendResultsEnd()

    Return ds

    Catch ex As Exception

    Throw New Exception(String.Format("AT_Error with url [{0}]", URL.ToString), ex)

    End Try

    End Function

    End

    Class

    it also gives an error, but for some proxi thing ???

    System.Net.WebException: The remote server returned an error: (407) Proxy Authentication Required.

    at System.Net.HttpWebRequest.GetResponse()

    at System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)

    at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)

    at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)

    at System.Xml.XmlTextReaderImpl.OpenUrlDelegate(Object xmlResolver)

    at System.Threading.CompressedStack.runTryCode(Object userData)

    at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

    at System.Threading.CompressedStack.Run(CompressedStack compressedStack, ContextCallback callback, Object state)

    at System.Xml.XmlTextReaderImpl.OpenUrl()

    at System.Xml.XmlTextReaderImpl.Read()

    at System.Xml.XmlTextReader.Read()

    at System.Xml.XmlReader.MoveToContent()

    at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)

    at System.Data.DataSet.ReadXml(XmlReader reader)

    at GeocodingWIN.FormStartup.SPCLR_Geocode(String Address, String City, String State, String appid) in P:\Documents\Visual Studio 2005\Projects\Geocoding\GeocodingWIN\FormStartup.vb:line 56"

    Being a dba that's trying to code a bit ... HEEELLLLPP

    http://api.local.yahoo.com/MapsService/V1/geocode?appid=mytestcode&street=8&city=Seattle&state=WA

    once again, internet exporer with the same url works fine

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Check in IE - go to Tools, Options, Connections tab, Lan Settings button..., and see if you have a proxy server specified. My guess is that you do. You may need to specify the same proxy server settings for this outgoing request from .NET. I did a quick search and found these....

    http://www.thescripts.com/forum/thread425824.html

    http://www.codeproject.com/vb/net/web_service_by_proxy.asp?df=100&forumid=16041&exp=0&select=1119964

    Let us know how it goes.

  • Thank you for your time.

    It took me a while, but finaly I got the winform app to work ...

    Address = Address.Replace(

    " ", "+")

    Dim myWebSvc As New System.Net.WebClient

    Dim myWebProxy As New System.Net.WebProxy("xxx.yyy.zzz.aaa", myport)

    'use current users credentials

    myWebProxy.Credentials = System.Net.CredentialCache.DefaultCredentials

    myWebSvc.Proxy = myWebProxy

    'Build the URL

    URL = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State

    'Get the XML response

    Dim xmlread As New XmlTextReader(myWebSvc.OpenRead(URL))

     

    Now, can I just port this code into the CLR-proc for sql2005 ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Theoretically.....we'll see. Give it a try.

  • It works just fine now !!

    Imports

    System

    Imports

    System.Data

    Imports

    System.Data.SqlClient

    Imports

    System.Data.SqlTypes

    Imports

    Microsoft.SqlServer.Server

    Imports

    System.Net

    Imports

    System.Xml

    Imports

    System.Xml.XPath

    Imports

    System.Text

    'Zie http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005_printversion.asp

    Partial

    Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub SPCLR_Geocode(ByVal Address As String, ByVal City As String, ByVal State As String, ByVal appid As String)

    'Declare string for URL

    Dim URL As String = "_empty_"

    'Yahoo test appid ""

    Try

    'Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo

    Address = Address.Replace(

    " ", "+")

    'Provide webservice and proxy and credentials

    Dim myWebSvc As New System.Net.WebClient

    Dim myWebProxy As New System.Net.WebProxy("xxx.yyy.zzz.aaa", myport)

    'use current users credentials

    myWebProxy.Credentials = System.Net.CredentialCache.DefaultCredentials

    myWebSvc.Proxy = myWebProxy

     

    'Build the URL

    URL =

    "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State

    ' http://api.local.yahoo.com/MapsService/V1/geocode?appid=myyahooappid&street=8&city=seattle&state=wa

    '

    'Get the XML response

    Dim xmlread As New XmlTextReader(myWebSvc.OpenRead(URL))

    ' Create the record and specify the metadata for the columns.

    Dim record As New SqlDataRecord(New SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2), New SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10), New SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100), New SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100))

    'Create a Dataset to hold the results

    Dim ds As New DataSet()

    'Fill the DS

    ds.ReadXml(xmlread)

    ' Mark the begining of the result-set.

    SqlContext.Pipe.SendResultsStart(record)

    For Each Rij As DataRow In ds.Tables(0).Rows

    If Rij.ItemArray.Length.Equals(9) Then

    record.SetString(0, Rij.ItemArray(0).ToString())

    record.SetString(1, Rij.ItemArray(1).ToString())

    record.SetString(2, Rij.ItemArray(2).ToString())

    record.SetString(3, Rij.ItemArray(3).ToString())

    record.SetString(4, Rij.ItemArray(4).ToString())

    record.SetString(5, Rij.ItemArray(5).ToString())

    record.SetString(6, Rij.ItemArray(6).ToString())

    record.SetString(7, Rij.ItemArray(7).ToString())

    record.SetString(8, Rij.ItemArray(8).ToString())

    'If the response did not include an error, then the array length is 0-6

    Else

    record.SetString(0, Rij.ItemArray(0).ToString())

    record.SetString(1, Rij.ItemArray(1).ToString())

    record.SetString(2, Rij.ItemArray(2).ToString())

    record.SetString(3, Rij.ItemArray(3).ToString())

    record.SetString(4, Rij.ItemArray(4).ToString())

    record.SetString(5, Rij.ItemArray(5).ToString())

    record.SetString(6, Rij.ItemArray(6).ToString())

    record.SetString(7,

    "Precision Good")

    record.SetString(8,

    "No Error")

    End If

     

    SqlContext.Pipe.SendResultsRow(record)

    Next

    ' Mark the end of the result-set.

    SqlContext.Pipe.SendResultsEnd()

    Catch ex As Exception

    Throw New Exception(String.Format("AT_Error with url [{0}]", URL.ToString), ex)

    End Try

    End Sub

    End

    Class

     

     

    Deploy and

    EXECUTE SPCLR_Geocode '8','Seattle','WA','MyYahooAppId'

     

    Thank your for your support !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

    Some examples of how to call it (the first 3 return multiple matches)

    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

    SQL Installation

    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

    Function Assembly Code

    using System;

    using System.Collections;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using System.Web;

    using System.Xml;

    using System.Xml.XPath;

    using System.Text;

    using Microsoft.SqlServer.Server;

    namespace sqlservercentral.Geocoding

    {

      public sealed class YGeocode

      {

        /// <summary>

        /// Prevent instances by making constructor private

        /// </summary>

        private YGeocode()

        {

        }

        /// <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=");

            String URL = sb.ToString();
            // Get the XML response

            XmlTextReader xmlread = new XmlTextReader(URL);

            // Create a Dataset to hold the results

            DataSet ds = new DataSet();

            // Fill the DS

            ds.ReadXml(xmlread);

            // Return rows of data

            return ds.Tables[0].Rows;

          }

          catch (Exception ex)

          {

            // Catch any errors here

            ;

          }

          return null;

        }

        [SqlFunction(FillRowMethodName = "FillRow",

          TableDefinition="[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(15) Null, [Warning] nvarchar(1024) Null")]

        public static IEnumerable GeocodeByAddress(SqlString StreetAddress, SqlString City, SqlString State, SqlString Zip, SqlString AppId)

        {

          return GeocodeStart(AppId, StreetAddress, City, State, Zip, null);

        }

        [SqlFunction(FillRowMethodName = "FillRow",

          TableDefinition = "[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(15) Null, [Warning] nvarchar(1024) Null")]

        public static IEnumerable GeocodeByLocation(SqlString Location, SqlString AppId)

        {

          return GeocodeStart(AppId, null, null, null, null, Location);

        }

        [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;

        }

      }

    };

     
  • Great addition! Thanks!

  • I've just found this tutorial today, and it's awesome! I've contacted Corey directly, but I'm also throwing this one out there for anyone that might catch it. My DBA is out of town, and if I wanted to execute the Assembly and immediately stick the lat/lon into a table from within an Insert trigger for a newly created row, what would that look like?

  • - it would look like bad code !

    - keep in mind you have no control over the availability of the counterparty !

    - If you put this clr-routine into a trigger, it will execute "in transaction", meaning if the clr-routine fails, your transaction will fail, so your actual insert statement will be rolled back !!!

    - You might want to put this in an assynchrone process (e.g. a sqlagent job).

       maybe within the insert trigger just feedin the pk into a "trigger"-table

       on which your sqlagent job will start from to get all the geo-info

       and will report bad info.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • alzdba - This is exactly what we do on our production system. We have another table where a bit gets set on or off based on business rules, and then a sql job comes & picks up the bits=1 and runs the CLR process. This is what I would recommend as well.

  • I also agree that making a CLR call from within a Trigger is a bit risky (your DBA probably won’t appreciate you doing this while he is away). With this assembly, I've encountered a sporadic error, presumably due to connectivity issues, that locks the assembly, and forces you to have to reinstall it. I haven't been able to reproduce it in a controlled environment to try and handle it within the assembly.

    Msg 6260, Level 16, State 1, Line 1

    An error occurred while getting new row from user defined Table Valued Function :

    System.InvalidOperationException: Handle is not initialized.

    System.InvalidOperationException:

       at System.Runtime.InteropServices.GCHandle.FromIntPtr(IntPtr value)

    .

    The statement has been terminated.

    That aside, to handle multiple matches by averaging the geocodes, using the TSV function above, the code would look like

    select

      addr.AddressGuid,

      gc.*

    into #tmpAddressGeocode

    from

      dbo.AddressTbl addr with (NoLock)

      cross apply dbo.fncGeocodeByAddress (

        addr.StreetAddress1 + coalesce (' ' + StreetAddress2,''),

        addr.CityName,

        addr.State,

        addr.Zip,

        'YahooDemo' ) gc

     

    update addr set

      LatitudeRad = LatAvg

      , LongitudeRad = LongAvg

      , LatestGeocodeAttemptDate = getutcdate()

      , GeocodePrecision = Prec

      , GeocodeMultiCnt = GeoCnt

    from (

      select

        AddressGuid,

        LatMin = Min (LatitudeRad),

        LongMin = Min (LongitudeRad),

        LatMax = Max (LatitudeRad),

        LongMax = Max (LongitudeRad),

        LatAvg = Avg (LatitudeRad),

        LongAvg = Avg (LongitudeRad),

        Prec = dbo.Concatenate (distinct geos.[Precision] ),

        GeoCnt = count (*)

      from #tmpAddressGeocode geos with (NoLock)

      group by AddressGuid

    ) geos

    join dbo.AddressTbl addr on addr.AddressGuid = geos.AddressGuid

    where

      -- 5 miles or less when multiple points

      dbo.DistanceGreatArc ( LatMin, LongMin, LatMax, LongMax ) * dbo.EarthRadiusCalc ( 1, null ) <= 10.0

     

    drop table #tmpAddressGeocode

     

    I’m storing other information, like when the geocode was obtained, a list of the precision levels, and how many matches were found. I'm also using a couple other CLR functions. dbo.Concatenate is included in the SQL CLR programming samples, and dbo.DistanceGreatArc is used to calculate the distance (there are multiple implementations you can find on the Internet, either as a CLR or SQL function). The check for the radius to be within 5 miles is a bit redundant, and you could omit it.

     

    If you’re geocoding a considerable number of records, you probably want to mange the temp table a bit more, and use a top to limit the number of geocodes you perform on each run (see earlier post).

  • I have followed the tutorial and everything went according to plan but when i execute the SP, it says Command Completed Successfully but I dont get any rows returned. I'm using the same test data as in the tutorial and I've tried other addresses too.

    Any ideas whats wrong or where I should start troubleshooting?

    Thanks

Viewing 15 posts - 16 through 30 (of 58 total)

You must be logged in to reply to this topic. Login to reply