SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Geocoding with SQL Server 2005


Geocoding with SQL Server 2005

Author
Message
scb
scb
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 175
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005.asp
Martin Vrieze
Martin Vrieze
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 125

Corey,

Very interesting article. Do you know if Yahoo places restrictions on the number of records passed to their system for Geocoding before they would require a license?

I make extensive use of geocoding already using licensed software. It would be very nice to append lat/long on the database to speed things up. The cost of passing over 10 million customer records through this type of tool may make it impractical for myself to use.

Any thoughts?


scb
scb
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 175
Well, initially when I prepared the article, it was 50,000 hits per day. But I just checked now, and they have changed it to 5,000 hits per day . Their popularity must have gone up. Yes - 10 million would be a problem. But See the rate limiting area on this page......

http://developer.yahoo.com/maps/rest/V1/geocode.html

Also, the Terms of Use would probably restrict you from doing anything too high powered.
Robert Sterbal
Robert Sterbal
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 1

This is really neat.

What is the easiest way to process the XML if you just write a script to capture the URL's to a file?


scb
scb
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 175
I don't profess to be a C# wiz, so don't have an exact answer. I would start with the XmlTextReader object. In my code I put it in the Dataset, just b/c I was used to working with the dataset.
Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11484 Visits: 780
The only issue is what geocoding method you need. There are more than a half dozen which all work for areas in say Florida, but for locations in New York you end up with pointers to the opposite street corner. I forget what version we use here, but you want to be sure you are geocoding correctly, especially if the Government is over your sholder. The only issue I have with many of these site is they all use the same images that in many cases are 5+ years old.



scb
scb
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 175
Definitely something to consider - if your company or business relies on you being accurate down to a tolerance of feet, or the density of the location (NY) is such that this accuracy would be absolutely necessary, then these free tools may not be the best.
Allan M.
Allan M.
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 2

Corey,

This is a very interesting article, but how do you create the DLL with Visual Studio 2005 Standard Edition? I do not have the option of making a "SQL Server Project".

Thanks,

Allan


scb
scb
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 175
I think this should work if you don't have the SQL Server project option....

1 - open VS 2005, and choose new project, type "Class Library". Name it the same as in the article (YahooGCSQL), just to keep it straightforward.
2 - erase everything in Class1.cs and replace with the code included in the article
3 - rename Class1.cs to YahooGCSQL.cs & rename the project as well

I think this should work for you. Give it a try. If there are C# experts out here that know of a better way, just let us know.
Allan M.
Allan M.
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 2

The "Class Library" method worked. Since I don't have clean addresses to start with, I changed the code to take advantage of the "Location" feature in Yahoo (http://developer.yahoo.com/maps/rest/V1/geocode.html):

Microsoft.SqlServer.Server.SqlProcedure]

public static void SPGeocode(string Address)

...

//Build the URL

URL = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=MYAPPID&location=" + Address;

I also modified the Stored Procedure to get rid of the extra parameters. Some warning messages are pretty long, so you might consider setting the length of the "warning" column to at least 250.

This is a very handy program. Thanks for posting it, Corey!

Allan


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search