Geocode Addresses in T-SQL

Question

Geocode Addresses in T-SQL

Viewing 15 posts - 31 through 45 (of 70 total)

You must be logged in to reply to this topic. Login to reply

rainermrilke SSC Enthusiast Points: 108 More actions · Answer 1

Thanks for the article. How difficult would it be to pass table values to this stored procedure?

I have been trying for a couple days and not getting it done. My attempt was to modify the procedure with a WHILE loop around a SELECT statement.

Is there a way to check what the end result of the request looks like that was sent to the geocoder? This might help me with error checking.

David Rueter SSCrazy Points: 2650 More actions · Answer 2

@rainermrilke:

This geocoding web service can only process a single request at a time, so to process multiple rows you would need to loop through the rows to process (such as with a cursor): see below for a sample.

Note that:

1) you should check out Google's terms of use

2) beware that there is a cap of 2,500 lookups per day from a single IP address (unless you make arangements with Google)

3) you would want to modify spGeocode so that it did not return a resultset with each execution (i.e. remove the SELECT @GPSLatitude AS GPSLatitude.... statement)

4) If you are thinking of updating your address data with the formatted address returned by the service, beware that

the geocoding service may not succeed in encoding a specific address--and in such a case you may not want to overwrite your existing data, and
you should probably review / enhance error handling to avoid any overwriting of data in the event of an error

SET NOCOUNT ON
CREATE TABLE #temp (
RecordID int identity,
Address varchar(50),
City varchar(30),
State varchar(5),
GPSLat numeric(9,6),
GPSLong numeric(9,6),
MapURL varchar(255))
INSERT INTO #temp (Address, City, State)
VALUES ('1150 Magic Way', 'Anaheim', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('8039 Beach Boulevard', 'Buena Park', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('500 SeaWorld Drive', 'San Diego', 'CA')
INSERT INTO #temp (Address, City, State)
VALUES ('1 Legoland Drive', 'Carlsbad', 'CA')
DECLARE curGeo CURSOR LOCAL STATIC FOR
SELECT RecordID, Address, City, State
FROM #temp
DECLARE @RecordID int
DECLARE @Address varchar(50)
DECLARE @City varchar(30)
DECLARE @State varchar(5)
DECLARE @GPSLatitude numeric(9, 6)
DECLARE @GPSLongitude numeric(9, 6)
DECLARE @MapURL varchar(255)
OPEN curGeo
FETCH curGeo INTO
@RecordID,
@Address,
@City,
@State
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
EXEC opsstream.sputilGeocode
@Address = @Address OUTPUT,
@City = @City OUTPUT,
@State = @State OUTPUT,
@GPSLatitude = @GPSLatitude OUTPUT,
@GPSLongitude = @GPSLongitude OUTPUT,
@MapURL = @MapURL OUTPUT
UPDATE #temp
SET
GPSLat = @GPSLatitude,
GPSLong = @GPSLongitude,
MapURL = @MapURL
WHERE
RecordID = @RecordID
END TRY
BEGIN CATCH
PRINT 'Warning: RecordID ' + CAST(@RecordID AS varchar(100)) + ' could not be geocoded.'
END CATCH
FETCH curGeo INTO
@RecordID,
@Address,
@City,
@State
END
SELECT * FROM #temp

rainermrilke SSC Enthusiast Points: 108 More actions · Answer 3

rainermrilke

SSC Enthusiast

Points: 108

May 24, 2010 at 3:54 pm

#1171797

Thank you for the info and the caveats.

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 4

I always love to see alternatives ....

But going back to use the unstable sp_oa... is at least one bridge to far.

There has been a nice article with a CLR alternative on SQL2005: http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/geocodingwithsqlserver2005/2373/

It should still be a valid staring point for SQL2008 (R2) on this matter.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Chusky Old Hand Points: 322 More actions · Answer 5

Dear Shawn

I've got the same result when running the sample of Geocoding. Have you got any update about this issue?

Thx

Jesus

raj.thakur SSC Enthusiast Points: 124 More actions · Answer 6

The article is excellent. I appreciate the way it is explained.

It work flawlessly for me.

I want to mention that when I try this for bulk Geo coding passing the addresses by loop.Google block the IP for one day after the 2500 requestper day per IP or less.

If the IPs are from same domain and if the per daylimit is reached from any perticular IP inside the that domain.

it will be applicable to all the ip of that domain.

Also we have we have incropoate a delay of 60 sec. after per request to get this 2500 Geocode per day .

otherwise we will not get any data back.

Thanks ,

Raj Thakur

mr-967650 Mr or Mrs. 500 Points: 504 More actions · Answer 7

Procedure uses Geocoding API ver. 2 but can be updated in order to use Geocoding API ver. 3.

Regards,

M.R.

briandmorgan SSC Rookie Points: 47 More actions · Answer 8

I know I'm a little late in the conversation, but in case anyone stumbles on it and finds this helpful . . .

First of all, thanks for the article. It was very helpful. It very much simplifies the task I was preparing for pulling latitude/longitude data. I ended up reworking your code into a function (for ease of multiple record updates without cursors), and chose to use Yahoo's Placefinder API instead of Google's API, for the reasons already mentioned by others in this thread. I also was having an issue with several records coming back with NULL values from google, even though I KNEW the urls were valid, as was the XML. The problem I was encountering had to do with the number of bytes returned in the XML (>4000). In these cases, the sp_OAGetProperty proc is not able to handle results >4000 bytes. This appears to be a hard limit, and no workaround or fix is available, at least for SQL 2008. The Yahoo XML file that's returned contains much less data than the Google API, and therefore processes all of my addresses just fine.

The function code is attached. Note that you'll have to get a key from Yahoo (http://developer.yahoo.com/geo/placefinder/). Takes about 30 seconds to apply.

Sample query using the function applied to an address table:

select *

from addr as a

cross apply fnGeocode(a.addr,a.city, a.state, a.country,a.zip,null, null, null, null) as b --passes arguments to geocode function

payasitofeliz SSC Rookie Points: 29 More actions · Answer 9

I tried to change the "address" parameter in URL to "latlng", and pass a latitude, long value to stored procedure, but dont obtain nothing. anybody knows how to solve this?

bsalazar@sksuministros.com

jeff_ott_95132 Valued Member Points: 59 More actions · Answer 10

Great article. very useful for geocoding data. Unfortunately we are limited in our use of the data. Yahoo appears the have the same "use of data" limitation as google.

From Yahoo's terms of use page.

(viii) store or allow end users to store map imagery, map data or geocoded location information from the Yahoo! Maps APIs for any future use;

(ix) use the stand-alone geocoder for any use other than displaying Yahoo! Maps or displaying points on Yahoo! Maps;

(x) publish or display, or allow other users to publish or display, any geocoded location information using any Yahoo! Maps APIs; or

Oh well. Still a great article. . . .

DeusExDatum Ten Centuries Points: 1144 More actions · Answer 11

Yeah, I've been through this POC before and what I've discovered is that even if you have the Google Maps Premier license, you still can't use it for this sort of potential "Batch Geo-Coding" without running the risk of getting your licenses disabled. That being said, if you have a funded project, there are companies like Melissa Data or Telogys (sp) that can provide web or local implementations for you to Geocode with. Personally my experience is with Melissa Data; they will provide SSIS wrappers around their GeoCoding API, with unlimited Geo Lookups, and at least Quarterly updates of their files. Being that we are .NET savvy based on this discussion, it should be easy enough to write a CLR wrapper around their API to deliver GeoCoding from T-SQL.

Josh Lewis