locked
possible to have stored procedure update lat/long settings in a dabase using bing maps RRS feed

  • Question

  • User371369287 posted

    to all --

    We have a couple tables in a MS 2005 db that contain latitude and longitude information.   The trouble is the users complain if they have to reach out to another website to retrieve the lat/long settings.   So we are looking to automate this some how.   We performed a batch update on all rows, but as new contractors or customers records are added, we set the lat/long to 0/0 and then the user must find the lat/long on their own and then re-updated the records.  Seems a little clunky.   

    What we would like to do is create a stored procedure to run every hour.  It would select all new records that have a 0/0 for lat/long and then connect to the Bing Maps API somehow and retrieve the lat/long coordinates and update the row accordingly.  We have a Bing map key - so we were hoping this is possible?  

    Any help would be appreciated.

    e

    Tuesday, January 12, 2016 2:35 PM

Answers

  • User1124521738 posted

    You'll probably want to build a console app (run hourly scheduled) or a simple web gui which does ajax calls to the api when the user types in an address and lets the use confirm that the coordinates shown are correct. I doubt you'd be able to do everything you are looking staying solely in the confines of SQL Server.

    I did something like what you are looking to do a while back for a local credit union as they had an ATM finder on their website (ironically, using google maps, since we had a yahoo api key for lat/long lookups).  They had 3 different ATM networks they participated in and they sent us spreadsheets for each one, once a quarter.  Only one of the networks provided lat/long and it was the smallest list.  I had to load all three systems into a database, and then run a simple script which did REST calls to yahoo's map API to turn street addresses into LAT/LONG coordinates.  There were always a few addresses that couldn't get resolved, but they were less than a dozen and so we could easily update those manually (and add to the data script) for the next quarter.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2016 4:45 PM