# Geolocation (country) from affiliation or address string **URL:** \ **Category:** Support and Helpdesk\ **Created:** [December 16, 2022, 5:22pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273 "2022-12-16T17:22:13Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Chris\_Erdmann](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/chris_erdmann/32/45_2.png) [@Chris\_Erdmann](https://forum.openrefine.org/u/Chris_Erdmann)\ **Post date:** [December 16, 2022, 5:22pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/1 "2022-12-16T17:22:13Z")
Can anyone recommend a service that can pull country names from address or affiliation strings? Either an API where you can feed it the address/affiliation string via Fetch URL and parse the response JSON or via reconciliation service? Also one that pulls lat/long and a map service that is easy to use for visualizing the results? ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [December 16, 2022, 6:08pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/2 "2022-12-16T18:08:32Z")
I haven’t tried it recently but there’s a wikipage on using Google Maps to get lat/lon for a street address [Geocoding · OpenRefine/OpenRefine Wiki · GitHub](https://github.com/OpenRefine/OpenRefine/wiki/Geocoding) ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [December 16, 2022, 8:18pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/3 "2022-12-16T20:18:05Z")
Hi Chris, > Can anyone recommend a service that can pull country names from address or affiliation strings? Either an API where you can feed it the address/affiliation string via Fetch URL and parse the response JSON or via reconciliation service? If the address actually contains the country, you might try Named Entity Recognition. For affiliation strings (organization names), I'd probably try to reconcile them against a data source like Wikidata and then fetch the country name from there. > Also one that pulls lat/long and a map service that is easy to use for visualizing the results? The lookup piece is called "geocoding" and is commercially valuable, so the APIs tend to have relatively low free tiers (if they have them at all). For visualization, Google Maps, Tableau Public, and [Leaflet](https://leafletjs.com/) are possibilities, depending on what you're trying to do. Tom ---
**Author:** ![Chris\_Erdmann](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/chris_erdmann/32/45_2.png) [@Chris\_Erdmann](https://forum.openrefine.org/u/Chris_Erdmann)\ **Post date:** [December 17, 2022, 8:51am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/4 "2022-12-17T08:51:48Z")
Thanks @ostephens @tfmorris 😃 I tested the Wikidata service and had only 8% matches on the data I was working with. Though, I chose research institute as the entity type to reconcile against and these can be businesses, university departments, etc as well. This open street map API seemed promising [Overview - Nominatim 4.2.0](https://nominatim.org/release-docs/latest/api/Overview/) but also had difficulty making a number of matches. There is Google Maps but requires a credit card and like you say @tfmorris commercial services are available. ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [December 17, 2022, 10:18am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/5 "2022-12-17T10:18:41Z")
The Geonames web services may be worth looking at. There are a lot of options but this maybe one to try [GeoNames Addresses GeoCoding and Reverse GeoCoding](https://www.geonames.org/maps/addresses.html#address) @tfmorris also mentioned NER and I agree that’s worth a look. Dandelion has a free allowance (see the thread on the Dandelion API in this forum) and you can run NERs locally as well for completely free use (I’ve used Stanford NLP in the past [Download - CoreNLP](https://stanfordnlp.github.io/CoreNLP/download.html)). I don’t think The NER extension works with the latest OpenRefine release but if you download the 3.4.1 release you could use that [GitHub - stkenny/Refine-NER-Extension: Named-Entity Recognition extension for OpenRefine](https://github.com/stkenny/Refine-NER-Extension) ---
**Author:** ![antonin\_d](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/antonin_d/32/32_2.png) [@antonin\_d](https://forum.openrefine.org/u/antonin_d)\ **Post date:** [December 17, 2022, 11:47am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/6 "2022-12-17T11:47:27Z")
Some years ago I needed something similar for academic affiliations. I put together a small NER system for Wikidata, [OpenTapioca](https://opentapioca.org/), specifically trained on a dataset of affiliations manually annotated with Wikidata. It’s far from a production-grade service but because it’s trained on this exact domain, perhaps that can help? It can be used with the development version of the NER extension, as a NIF service. By the way, the development version of the NER extension is developed for OpenRefine 3.6, and I assume it should also work with 3.7 (and likely some earlier versions too): > ---
**Author:** ![thadguidry](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/thadguidry/32/653_2.png) [@thadguidry](https://forum.openrefine.org/u/thadguidry)\ **Post date:** [December 18, 2022, 11:02am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/7 "2022-12-18T11:02:16Z")
@Chris_Erdmann are the affiliations in one column and addresses in another column? You could use a bit of Python to handle some of the looking up. I’ve used `pycountry` for something like that before to have on hand your own database to search values against with python functions for the expression. This returns an array for example: `results = pycountry.countries.search_fuzzy(u'New')` ---
**Author:** ![Chris\_Erdmann](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/chris_erdmann/32/45_2.png) [@Chris\_Erdmann](https://forum.openrefine.org/u/Chris_Erdmann)\ **Post date:** [December 19, 2022, 2:44am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/8 "2022-12-19T02:44:19Z")
@thadguidry in this case affiliations/addresses are in the same column. The example from @antonin_d might be the best way to go. ---
**Author:** ![Joseph\_Anderson](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/joseph_anderson/32/82_2.png) [@Joseph\_Anderson](https://forum.openrefine.org/u/Joseph_Anderson)\ **Post date:** [December 19, 2022, 3:09pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/9 "2022-12-19T15:09:29Z")
I’ve been using mapbox’s geocoding for a project with address strings: [Geocoding | API | Mapbox](https://docs.mapbox.com/api/search/geocoding/) You just use it through the api fetch url in openrefine. I’ve heard that google maps geocoding is actually a bit better but there’s no longer any free option, which is a bummer ☹ ---
**Author:** ![ahagstro](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ahagstro/32/185_2.png) [@ahagstro](https://forum.openrefine.org/u/ahagstro)\ **Post date:** [December 31, 2022, 9:13am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/10 "2022-12-31T09:13:01Z")
I’ve been using Google’s Geocoding API for several years. I’d say the accuracy is \>99%. Yes, it requires an API key (and a credit card), but the [first US$ 200 every month are free](https://mapsplatform.google.com/pricing/), which translates into 40’000 coding requests. So I get a monthly 0$ bill, which seems a very fair deal. ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [January 5, 2023, 6:45pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/11 "2023-01-05T18:45:19Z")
> > ![](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ahagstro/45/185_2.png "ahagstro") | [ahagstro](https://forum.openrefine.org/u/ahagstro) > > December 31 | > > > > - | - | > > I’ve been using Google’s Geocoding API for several years. I’d say the accuracy is \>99%. > Yes, it requires an API key (and a credit card), but the [first US$ 200 every month are free](https://mapsplatform.google.com/pricing/), which translates into 40’000 coding requests. So I get a monthly 0$ bill, which seems a very fair deal. The promotional $200 credit is nice (while it lasts), but the biggest problem with the Google service isn't the price, but the terms of service since you're only allowed to use the geocoding results on Google Maps and not save them (short term caching allowed for use on Google Maps only). If your use case fits these terms of service, it's a great option, but otherwise a non-starter. A lot of people want to save the geocode results for later use or use them to calculate distances or plot them on an OpenStreetMap map, etc., none of which is permitted. Tom ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [February 28, 2023, 10:14pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/12 "2023-02-28T22:14:44Z")
Perhaps of interest to those following this thread is the video “[Strategies for Matching Affiliation Strings to ROR IDs](https://youtu.be/Tx5y7lX030U)” from the [2023 ROR Annual Meeting](https://ror.org/blog/2023-02-13-ror-turns-four/), especially the [presentation](https://youtu.be/Tx5y7lX030U?t=1720) by the US Department of Energy (DOE) Office of Scientific and Technical Information (OSTI) describing their OpenRefine reconciliation service for organizations which is currently in testing and scheduled to be available for public use in Q1 2023 (“hopefully by March”). More generally, the continued trend of increasing adoption of persistent identifiers is a good thing for users with reconciliation needs. ---
**Author:** ![Martin](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/martin/32/7_2.png) [@Martin](https://forum.openrefine.org/u/Martin)\ **Post date:** [February 6, 2025, 1:59pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/13 "2025-02-06T13:59:38Z")
Many people continue to visit this thread over time, making it one of the most viewed topics on the forum. So for those still searching for the right option, I found a set of recipes from [HERE](https://www.here.com/docs/bundle/geocoding-and-search-api-developer-guide/page/topics/quick-start.html) and [Nominatim](https://nominatim.org/release-docs/latest/api/Search/) developed by Peter Aldhous. I haven't tried them myself, but I'm pleased to see something that leverages OpenStreetMap data. > **[GitHub - paldhous/refine-geocoder: JSON to geocode list of addresses in OpenRefine,...](https://github.com/paldhous/refine-geocoder)** > > JSON to geocode list of addresses in OpenRefine, using HERE and OpenStreetMap Nominatim APIs ---
**Author:** ![Michael\_Markert](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/michael_markert/32/88_2.png) [@Michael\_Markert](https://forum.openrefine.org/u/Michael_Markert)\ **Post date:** [February 7, 2025, 1:31pm UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/14 "2025-02-07T13:31:08Z")
One could use the free [OpenStreetMap Nominatim service](https://nominatim.openstreetmap.org/ui/search.html) (but be aware of the [usage policy](https://operations.osmfoundation.org/policies/nominatim/)!). For affiliations/addresses like `British Museum, Great Russell St, London` in a column you could add a column by fetching urls with a throttle of 1000 ms (because of the policy) with `"https://nominatim.openstreetmap.org/search.php?q=" + escape(value, "url") + "&format=jsonv2&limit=1&addressdetails=1&email=YOUR_MAIL_ADDRESS"` and than add another column by parsing the JSON response for the country value with `value.parseJson()[0]["address"]["country"]` ---
**Author:** ![b2m](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/b2m/32/139_2.png) [@b2m](https://forum.openrefine.org/u/b2m)\ **Post date:** [February 10, 2025, 7:37am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/15 "2025-02-10T07:37:13Z")
Last year I wrote a tutorial that discusses several approaches for [loading geo coordinates using OpenRefine 🇩🇪](https://fdmlab.landesarchiv-bw.de/workshop/openrefine-fortgeschrittene/18-nachladen-von-geokoordinaten/) using Wikipedia, GeoNames, OpenStreetMap/Nominatim, OpenStreetMap/Photon, GND, Wikidata and Getty TGN. But the language is German and it also focuses on several issues regarding municipal structures in Germany. ---
**Author:** ![b2m](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/b2m/32/139_2.png) [@b2m](https://forum.openrefine.org/u/b2m)\ **Post date:** [February 10, 2025, 7:45am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/16 "2025-02-10T07:45:21Z")
> [@Michael\_Markert](#): > > One could use the free [OpenStreetMap Nominatim service](https://nominatim.openstreetmap.org/ui/search.html) (but be aware of the [usage policy](https://operations.osmfoundation.org/policies/nominatim/)!). There is also a policy to [add your email to the request](https://nominatim.org/release-docs/develop/api/Search/#other). Otherwise you might just get blocked if you are doing large numbers of requests (no definition in the docs what "large numbers" means). ---
**Author:** ![Michael\_Markert](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/michael_markert/32/88_2.png) [@Michael\_Markert](https://forum.openrefine.org/u/Michael_Markert)\ **Post date:** [February 10, 2025, 8:15am UTC](https://forum.openrefine.org/t/geolocation-country-from-affiliation-or-address-string/273/17 "2025-02-10T08:15:38Z")
Thanks for the tipp! I added the mail address to my demo query.