Geocoding with SQL Server 2005

  • scb

    SSCommitted

    Points: 1859

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/geocodingwithsqlserver2005.asp

  • Martin Vrieze

    SSCrazy

    Points: 2760

    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

    SSCommitted

    Points: 1859

    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

    SSC Eights!

    Points: 845

    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

    SSCommitted

    Points: 1859

    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

    SSC Guru

    Points: 125444

    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

    SSCommitted

    Points: 1859

    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.

    SSC Enthusiast

    Points: 182

    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

    SSCommitted

    Points: 1859

    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.

    SSC Enthusiast

    Points: 182

    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

     

  • scb

    SSCommitted

    Points: 1859

    Thanks for the feedback. Good advice on the warning column length.

  • pl-272086

    SSC Journeyman

    Points: 81

    From what I understand from the Yahoo page, this is only for US addresses ? Seem to require "US State"and there is no Country parameter when sending the query. 

  • scb

    SSCommitted

    Points: 1859

    I took a quick look on their yahoo developer group, and looks like they have some city level geocoding available for some locations. But unfortunately, you're right, the really precise geocodes may only be US. Trying this link returns a city centre for London.

    http://api.local.yahoo.com/MapsService/V1/geocode?appid=YahooDemo&city=London

    Search "internation" on the yahoo group, and you may have some more luck. Not sure...

    http://groups.yahoo.com/group/yws-maps/

  • Allan M.

    SSC Enthusiast

    Points: 182

    Addresses with apartment numbers have some issues...

    The following work: Apartment B32 or apt. B32

    The following don't work: Apartment #B32, apt B32, apt #B32 (in fact, the pound (#) sign causes no results to be returned)

    It seems like I will have to scan each address for an apartment number before sending it to the stored procedure, but there are lots of variations that people use 

    PO Boxes are also causing some issues.

    Any thoughts?

    Allan

  • scb

    SSCommitted

    Points: 1859

    P.O. Boxes are of course not tied to a physical location, so there's nothing you can do about those. As far as the other dirty data, some of the other not-free geocoders that I've worked with, mainly Microsoft Mappoint Web Service, can handle a little more garbage (although I think they don't like # signs either). But to use the Yahoo one, you may have to do some string filtering, etc. as you mentioned. Sorry! That's a product of Yahoo's functionality/flexibility.

Viewing 15 posts - 1 through 15 (of 59 total)

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