Easy geocoding with Google API

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Easy geocoding with Google API

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 07:27:55

[code]-- Prepare sample data
SET NOCOUNT ON

DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Postal VARCHAR(50) NOT NULL,
ZipCode VARCHAR(50) NOT NULL,
City VARCHAR(50) NOT NULL,
Country VARCHAR(50) NOT NULL,
Status SMALLINT,
Accuracy TINYINT,
Lat DECIMAL(9, 6),
Lon DECIMAL(9, 6),
CreDate DATETIME
)

INSERT @Sample
SELECT 'One Microsoft Way', '98052-6399', 'Redmond, WA', 'USA' UNION ALL
SELECT '170 W. Tasman Dr.', '95134', 'San Jose, CA', 'USA' UNION ALL
SELECT '500 Oracle Parkway', '94065', 'Redwood Shores, CA', 'USA'

-- Initialize
DECLARE @url VARCHAR(300),
@win INT,
@hr INT,
@Text VARCHAR(8000),
@RowID int,
@Status smallint,
@Accuracy tinyint,
@Lon decimal(9, 6),
@Lat decimal(9, 6)

SELECT @RowID = MIN(RowID)
FROM @Sample
WHERE Status IS NULL

WHILE @RowID IS NOT NULL
BEGIN
SELECT @url = 'q=' + Postal + '+' + ZipCode + '+' + City + '+' + Country
FROM @Sample
WHERE RowID = @RowID

SET @url = 'http://maps.google.com/maps/geo?' + @url
SET @url = @url + '&output=csv&key={your google api key here}'

EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

SET @Text = REPLACE(REPLACE(@Text, '.', '#'), ',', '.')

SELECT @Status = PARSENAME(@Text, 4),
@Accuracy = PARSENAME(@Text, 3),
@Lat = REPLACE(PARSENAME(@Text, 2), '#', '.'),
@Lon = REPLACE(PARSENAME(@Text, 1), '#', '.')

UPDATE @Sample
SET Accuracy = @Accuracy,
Lat = @Lat,
Lon = @Lon,
Status = @Status,
CreDate = GETDATE()
WHERE RowID = @RowID

WAITFOR DELAY '00:00:00.010'

SELECT @RowID = MIN(RowID)
FROM @Sample
WHERE Status IS NULL
END

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 07:29:56

You may want to increase the delay to avoid to many status 620 records in sequence...

WAITFOR DELAY '00:00:00.500' seems like a good candidate.
With this value, you can fetch about 7000 addresses per hour, for two and a half hour per day.

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-25 : 07:36:45

or you may not do this in sql at all

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 07:37:33

Yes, you can do this with CLR-procedure too.
Too bad it is not available in SQL Server 2000.

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-25 : 07:39:29

no, i mean why would you want to do this in sql server at all?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 07:42:23

Just an idea I had.
We are running a CRM database where customers are assigned to a store depending on two critierias

1) Quantity - Most number of buys
2) Quality - Often most spent money

I wanted to add a third criteria

3) Nearest store

Using the Haversine formula reversed, I can assign the nearest store as default store to any customer in a jiffy!

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-25 : 07:45:23

cool!

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 08:27:05

One drawback and a thing to remember for this free service is that there seems to be two limits

1) About 12,000 requests per day
2) About 120 per minute (2 per second).

E 12°55'05.63"
N 56°04'39.26"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-25 : 08:47:12

I would never design critical application functionality around a free service such as Google's APIs.
It may be fine for personal applications, but what happens to your Enterprise application when the service changes or is rescinded?

Boycotted Beijing Olympics 2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 09:21:52

Consider buying a commercial application?
This can be a starter to see if the geocoding functioanlity meet your expectations.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources