Geocoding

[Maysam Ayoub]

Dears,

I need to generate the geographical coordinates of around 5000 companies so that I can calculate travel times. For most companies, there is data for street address, PO box, city, and the country.
Here's a subset of the date (just two companies):

* Example generated by -dataex-. For more info, type help dataex
clear
input str22 EntityName str34 HeadquarterAddressLine1 str35 HeadquarterAddressLine2 str6 HeadquarterCity str11 HeadquarterCountry float HeadquarterPostalCode
" Pierre & Vacances"     "L’Artois, Espace Pont de Flandre" ".11, rue de Cambrai, Paris Cedex 19" "Paris"  "France"      75947
"Edisun Power Europe AG" "Universitatstrasse 51"              ""                                    "Zurich" "Switzerland"  8006
end

------------------ copy up to and including the previous line ------------------

Your help is appreciated

Regards,

[Jared Greathouse]

Impossible (as far as I know). But, this sounds like a good Python/Stata problem.

Anyways, you don't give enough details on how you might start doing this, even theoretically. Where does this dataset come from? Why aren't the coordinates provided already? Who collected the data? And also, why is calculating travel time so necessary?

[Mike Lacy]

-search geocode- reveals 20+ relevant user-written Stata packages that include both geocoding and/pr travel time capacities. I have not recently used any of them, and my recollection is that there have been difficulties over the years with changes in e.g. Google Maps breaking such programs, but I'd definitely recommend trying some of what shows up there. Discussions of geocoding have appeared now and then on StataList, so feeding something like /site:statalist.org geocode/ to your favorite search engine should reveal past discussions.

[Jared Greathouse]

My fear isn't the travel time, my fear is that there's nothing that would allow for explicit geocoding of addresses (but I could be totally wrong on this front). Mike Lacy

I know Geonear is great (for Euclidean distance at least), but I've never seen a user written command allowing for coding of addresses, but again, I could be completely wrong.

[Mike Lacy]

To my recollection -- 5 years old or so old--- the geocoding packages used to work pretty decently, with occasional glitches. I believe they all function by issuing calls to some online service, such as Google Maps or MapQuest. From a quick experiment I just did, it appears that -geocode- (available at SSC) is broken now, but maybe one of the other package works or perhaps I misused -geocode-. I don't have much expertise here, but perhaps someone else can work this out or already knows which package to use.

[Iuri Gavronski]

I used a Google Script for that purpose. You have the addresses in a Google Sheets column and the output goes to two columns you set aside for the lat/long data.
See Geocoding With Google Sheets (willgeary.github.io)

[Jared Greathouse]

Iuri Gavronski This is fascinating. Is this Python or Java working under the hood?

[Iuri Gavronski]

Hi Jared Greathouse ,
As far as I know, it is a scripting language for Google Sheets. Looks like a JavaScript dialect.
You upload the dataset to Sheets, run the script, then download it as xlxs or csv and import it back to Stata.
Worked like a charm for me.

[Maysam Ayoub]

Iuri Gavronski thank you so much! Amazing

also, thanks everyone for your input