Bulk Bing Maps Geocode Service Encoding using Powershell


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.

Advertisements