Recently I had the need to geo-encode millions of addresses, after reviewing multiple technologies I found PowerShell provided excellent results. Here is the example solution.
You will need a Bing Maps Maps Account API Key and Microsoft SQL Server
The first step is to create a database with sample data, below is the script:
USE [master] GO /****** Object: Database [LocationDB] Script Date: 2012-10-20 12:42:43 AM ******/ CREATE DATABASE [LocationDB] GO ALTER DATABASE [LocationDB] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [LocationDB].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [LocationDB] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [LocationDB] SET ANSI_NULLS OFF GO ALTER DATABASE [LocationDB] SET ANSI_PADDING OFF GO ALTER DATABASE [LocationDB] SET ANSI_WARNINGS OFF GO ALTER DATABASE [LocationDB] SET ARITHABORT OFF GO ALTER DATABASE [LocationDB] SET AUTO_CLOSE OFF GO ALTER DATABASE [LocationDB] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [LocationDB] SET AUTO_SHRINK OFF GO ALTER DATABASE [LocationDB] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [LocationDB] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [LocationDB] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [LocationDB] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [LocationDB] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [LocationDB] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [LocationDB] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [LocationDB] SET DISABLE_BROKER GO ALTER DATABASE [LocationDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [LocationDB] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [LocationDB] SET TRUSTWORTHY OFF GO ALTER DATABASE [LocationDB] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [LocationDB] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [LocationDB] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [LocationDB] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [LocationDB] SET RECOVERY FULL GO ALTER DATABASE [LocationDB] SET MULTI_USER GO ALTER DATABASE [LocationDB] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [LocationDB] SET DB_CHAINING OFF GO ALTER DATABASE [LocationDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [LocationDB] SET TARGET_RECOVERY_TIME = 0 SECONDS GO EXEC sys.sp_db_vardecimal_storage_format N'LocationDB', N'ON' GO USE [LocationDB] GO /****** Object: StoredProcedure [dbo].[usp_GetLocations] Script Date: 2012-10-20 12:42:44 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nathan Storms -- Create date: 2012-10-20 -- Description: Get Location that need encoding. -- ============================================= CREATE PROCEDURE [dbo].[usp_GetLocations] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT TOP 1000 Id, ([Address] + ', ' + [City] + ', ' + [Region] + ', ' + [Country] + ', ' + [PostalCode]) AS Address FROM [dbo].[Locations] WITH (NOLOCK) WHERE SRID IS NULL END GO /****** Object: StoredProcedure [dbo].[usp_UpdateLocation] Script Date: 2012-10-20 12:42:44 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Nathan Storms -- Create date: 2012-10-20 -- Description: Update locations with geocode -- ============================================= CREATE PROCEDURE [dbo].[usp_UpdateLocation] -- Add the parameters for the stored procedure here @ID int = 0, @Longitude float = 0, @Latitude float = 0, @SRID int = 4326 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here UPDATE [dbo].[Locations] SET [Latitude] = @Latitude ,[Longitude] = @Longitude ,[SRID] = 4326 WHERE [ID] = @ID END GO /****** Object: Table [dbo].[Locations] Script Date: 2012-10-20 12:42:44 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Locations]( [Id] [int] IDENTITY(1,1) NOT NULL, [Address] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [Region] [char](2) NOT NULL, [Country] [varchar](50) NOT NULL, [PostalCode] [char](15) NOT NULL, [Latitude] [float] NULL, [Longitude] [float] NULL, [SRID] [int] NULL, [Point] AS ([geography]::Point([Latitude],[Longitude],[SRID])), CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Locations] ADD CONSTRAINT [DF_Locations_Latitude] DEFAULT ((0)) FOR [Latitude] GO ALTER TABLE [dbo].[Locations] ADD CONSTRAINT [DF_Locations_Longitude] DEFAULT ((0)) FOR [Longitude] GO INSERT INTO [dbo].[Locations] ([Address],[City],[Region],[Country],[PostalCode]) VALUES ('6258 Amesbury St','San Diego','CA','United States','92114-6717'), ('8308 Fenway Rd','Bethesda','MD','United States','20817-2733'), ('10 Wall St','Burlington','MA','United States','01803-4749'), ('3315 W Greenway Rd','Phoenix','AZ','United States','85053-380'), ('1635 Camile Pl','Santa Ana','CA','United States','92703-4401') GO
Lastly here is the PowerShell Script to geo-encode the addresses in the database:
# Setup Connection To Bing $key = "{Add Bing Map API Key}" $ws = New-WebServiceProxy -uri http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl $wsgr = New-Object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.GeocodeRequest $wsc = New-Object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.Credentials $wsc.ApplicationId = $key $wsgr.Credentials = $wsc # Setup Connection To SQL Database function ConnectSQL { Param ($server, $query, $database) $conn = new-object ('System.Data.SqlClient.SqlConnection') $connString = "Server=$server;Integrated Security=SSPI;Database=$database" $conn.ConnectionString = $connString $conn.Open() $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlCmd.CommandText = $query $sqlCmd.Connection = $conn $Rset = $sqlCmd.ExecuteReader() ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset } function QuerySQL { Param ($server, $query, $database = "master") $data = ConnectSQL $server $query $database while ($data.read() -eq $true) { $max = $data.FieldCount -1 $obj = New-Object Object For ($i = 0; $i -le $max; $i++) { $name = $data.GetName($i) $obj | Add-Member Noteproperty $name -value $data.GetValue($i) } $obj } } Write-Host "Getting Records From Database..." $Locations = QuerySQL "localhost" "EXECUTE [dbo].[usp_GetLocations]" "LocationDB" $Locations | ForEach-Object {
$wsr = 0
$Longitude = 0
$Latitude = 0$ID = $_.ID $Address = $_.Address Write-Host "Processing record $LocationID for the address: $Address" $wsgr.Query = $Address $wsr = $ws.Geocode($wsgr) $Longitude = $wsr.Results[0].Locations[0].Longitude $Latitude = $wsr.Results[0].Locations[0].Latitude Write-Host "Location found for record $ID the location is $Longitude, $Latitude" QuerySQL "localhost" "EXECUTE [dbo].[usp_UpdateLocation] $ID, $Longitude, $Latitude" "LocationDB" }
That’s it.
The Architect Evangelist 