April 5, 2018 at 9:22 am
Can someone please share their thoughts on how to best update the country information if I have the address + city. We have about 4 million contacts and out of which, close to 5000 contacts don't have country info. I mean the value in the country column is NULL and I need to update those. I hate the idea of manually putting in the address and the city on google map to see where that address is and update the country. Is there an API call I can make where I pass address + city and update country? I am not sure if I have provided all the details or should I provide more details?
April 5, 2018 at 9:46 am
NewBornDBA2017 - Thursday, April 5, 2018 9:22 AMCan someone please share their thoughts on how to best update the country information if I have the address + city. We have about 4 million contacts and out of which, close to 5000 contacts don't have country info. I mean the value in the country column is NULL and I need to update those. I hate the idea of manually putting in the address and the city on google map to see where that address is and update the country. Is there an API call I can make where I pass address + city and update country? I am not sure if I have provided all the details or should I provide more details?
Pretty sparse on details here. You can probably find some address validation APIs that can do this. I would check with the major shippers first, like UPS or FedEx. But what happens when the address is found in more than 1 country??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 5, 2018 at 10:43 am
NewBornDBA2017 - Thursday, April 5, 2018 9:22 AMCan someone please share their thoughts on how to best update the country information if I have the address + city. We have about 4 million contacts and out of which, close to 5000 contacts don't have country info. I mean the value in the country column is NULL and I need to update those. I hate the idea of manually putting in the address and the city on google map to see where that address is and update the country. Is there an API call I can make where I pass address + city and update country? I am not sure if I have provided all the details or should I provide more details?
try the following website which can provide you information based on Google API.
https://www.doogal.co.uk/BatchGeocoding.php
What you will notice is that if you put all of the 5000 contacts in a single batch, it would take a lot of time to process the last records.
Instead split up your record set into batches, say 10 batches each of 500. Open up 10 browser windows and paste 500 address and wait for the output. After that combine the data from 10 windows into an excel and use that to update your base table.
September 27, 2020 at 1:52 am
Hi, there are several steps you need to perform and I would walk you in details:
1. You need to use batch geocoder tool that will perform the conversion for you.
2. The tool needs to cover world wide maps as you did not specify what country these addresses are from and you may not know that either. The tool i listed above does that.
3. You need to understand that when you have just the street number, street name and city the outcome sometimes maybe too ambiguous, as it is theoretically possible same street number, street ,city tokens to appear in two different countries.
In that case you would get AI driven result.
4. Take a look this article that goes in details how to get latitude and longitude from an address and as a side effect will provide you the origin /country.
September 27, 2020 at 2:57 pm
Interesting.
First, the original post on this thread was from 2.5 years ago. I wonder if the OP ever resolved their problem.
The really interesting thing is that, although this is an older post, it's still applicable to what a lot of folks sometimes need to do (like the company that I work for) and so I'll be reading the article you provided the link for. Thanks for posting.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply