Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Geocode Addresses in T-SQL Expand / Collapse
Author
Message
Posted Monday, May 24, 2010 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 7:52 AM
Points: 2, Visits: 16
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.
Post #927045
Posted Monday, May 24, 2010 3:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405
@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

Post #927101
Posted Monday, May 24, 2010 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 7:52 AM
Points: 2, Visits: 16
Thank you for the info and the caveats.
Post #927123
Posted Friday, June 4, 2010 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #932829
Posted Wednesday, October 13, 2010 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 8:55 AM
Points: 19, Visits: 38
Dear Shawn
I've got the same result when running the sample of Geocoding. Have you got any update about this issue?

Thx

Jesus
Post #1003575
Posted Friday, October 22, 2010 3:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 4:17 AM
Points: 82, Visits: 209
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

Post #1009035
Posted Friday, February 11, 2011 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:53 AM
Points: 13, Visits: 47
Procedure uses Geocoding API ver. 2 but can be updated in order to use Geocoding API ver. 3.

Regards,
M.R.
Post #1062686
Posted Tuesday, August 23, 2011 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 1:27 PM
Points: 1, Visits: 16
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


  Post Attachments 
geocode-function-yahoo.txt (39 views, 3.41 KB)
Post #1164314
Posted Saturday, December 3, 2011 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 12:50 PM
Points: 1, Visits: 11
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
Post #1215860
Posted Friday, June 8, 2012 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 2, 2012 4:46 PM
Points: 1, Visits: 9
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. . . .
Post #1313392
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse