Originally posted on Alastair Aitchison:

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.

The concept of four-dimensional spacetime was challenged recently by a group of scientists who argue that time, in itself, is not a fundamental entity – it is merely a measure of the numerical order of changes in space. Nevertheless, whether dimension or not, time is a crucial factor to consider when interpreting spatial data. We not only need to know where something is, but when it was there.

Time and Maps

One commonquestionasked on the MSDN Bing Maps forum concerns the timeliness of the base map data…

View original 1,248 more words


The Architect Evangelist:

Essential reading for every Bing map developer.

Originally posted on Alastair Aitchison:

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:

  • Firstly, it’s written using v6.3 of the AJAX control – v7.0 has been available for well over 6 months now and (despite some teething problems) this latest version is recommended for all new development.
  • Secondly, the article describes how to draw arbitrary Bezier curves on the projected plane of the map. Whilst this is an interesting exercise (and the author goes on to describe important concepts such as how to test the routine), it’s not actually that useful. More often, when we see curved lines on a map, we expect them to represent geodesics – the shortest path between two points on the surface of the earth. Although this was never…

View original 1,659 more words

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.