YAddress SQL Server UDF for Postal Address Correction, Validation, Standardization and Geocoding
YAddress SQL Server UDF (User Defined Function) calls YAddress Web API (http://www.yaddress.net) to correct, validate, standardize and geocode postal addresses. The UDF can be called from T-SQL queries and stored procedures or incorporated into ETL packages in SSIS.
Setup
- Download YAddress UDF Binaries at http://www.yaddress.net/Downloads/YAddressSqlServerUdfBinaries.zip
- Follow installation steps in "SQL Setup Script.sql".
Usage
ProcessAddress
ProcessAddress is a table-valued UDF (User Defined Function). It returns a single row of data with address fields in its columns.
ProcessAddress(@AddressLine1 nvarchar(255), @AddressLine2 nvarchar(255),
@UserKey nvarchar(255), @BaseUrl nvarchar(1024))AddressLine1: street address line.
AddressLine2: city, state, zip.
UserKey: YAddress Web API user key. Set to NULL if you do not have a YAddress account.
BaseUrl: Base URL for API calls. Set to NULL to use the standard base URL.
Example
SELECT * FROM ProcessAddress('506 Fourth Avenue Unit 1', 'Asbury Prk, NJ', NULL, NULL) Results:
ErrorCode: 0
ErrorMessage:
AddressLine1: 506 4TH AVE APT 1
AddressLine2: ASBURY PARK, NJ 07712-6086
Number: 506
PreDir:
Street: 4TH
Suffix: AVE
PostDir:
Sec: APT
SecNumber: 1
City: ASBURY PARK
State: NJ
Zip: 7712
Zip4: 6086
County: MONMOUTH
CountyFP: 25
CensusTract: 1015
CensusBlock: 8070.03
Latitude: 40.223571
Longitude: -74.005973
GeocodePrecision: 5
TimeZoneOffset: -5
DstObserved: 1
SalesTaxRate: 6.625
SalesTaxJurisdiction: State of NJ
SQL Spatial Geography
ProcessAddress returns address location as two floating point values of latitude and longitude. To convert them to SQL Server spatial type Geography:
SELECT Location = GEOGRAPHY::Point(Latitude, Longitude, 4326)
FROM ProcessAddress('506 Fourth Avenue Unit 1', 'Asbury Prk, NJ', NULL, NULL)