Many people (whether influenced more by Einstein’s special theory of relativity or the TARDIS) think of space and time as intricately linked dimensions; we describe the position of an object in space using three dimensions, and time represents the fourth dimension. Doctor Who, in his multi-dimensional policebox, is capable both of travelling throughout the universe and throughout the ages.
This month’s MSDN magazine has an article describing how to create curved lines on the Bing Maps AJAX control. While I don’t want to criticise the author at all, there are two comments I would make on the article:
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.
