Wednesday, May 19, 2010

Geocode Addresses in T-SQL


This article shows how you can "geocode" street address information natively in T-SQL with MS SQL 2005, and as a byproduct, how you can validate, normalize, and parse address information.
Note that this approach will work in SQL 2008 as well, though SQL 2008 provides geospatial types and functions that could be used in lieu of the numeric data type used here.
Geocoding is the process of converting a geographical address into latitude and longitude (GPS coordinates). Geocoding address information has a number of benefits:
  • Means to interoperate with web-based mapping services
  • Means to interoperate with GPS devices
  • Makes it possible to calculate distance between addresses
  • Makes geospatial analysis possible (i.e. identifying geographically similar addresses, etc.
  • Compact way to represent a geographical location
  • And more...