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
'
'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://qa.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://qa.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005.asptry
{
// 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 responseXmlTextReader xmlread = new XmlTextReader(URL);
// Create a Dataset to hold the resultsDataSet ds = new DataSet();
// Fill the DSds.ReadXml(xmlread);
// Return rows of datareturn 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