I have an attribute that contains location information (text-based) but it will say something like “Lake View, Chicago, USA”. I want to create a column that just tells me “USA”. Note, the data isn’t regular, sometimes there are many locality details, sometimes just a country, and sometimes no country. How do I just pull out the countries?

How do I pick country names out of an attribute?

Userlevel 6

+33

nielsgerrits
Celebrity
2347 replies
1 year ago
8 February 2023

Is the country always the last item, divided by a comma? Then you can use an AttributeSplitter to split the attribute to list elements. Then use a ListIndexer to get the last list element value(index = -1) and set it to an attribute.

C

+1

Is the country always the last item, divided by a comma? Then you can use an AttributeSplitter to split the attribute to list elements. Then use a ListIndexer to get the last list element value(index = -1) and set it to an attribute.

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

Userlevel 6

+33

nielsgerrits
Celebrity
2347 replies
1 year ago
8 February 2023
Best Answer

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

Ah I see :) one thing what comes to mind is to use a list of all possible countries and a StringSearcher to find a match. For this you have to merge all countries to all features and then search if attribute country is in attribute location.

Userlevel 5

+25

redgeographics
Influencer
3345 replies
1 year ago
8 February 2023

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

All possible countries and common alternative spellings... 😅 USA, US, U.S.A., United States, United States of America...

Or misspellings... Did you know it's "The Bahamas" and not "Bahama's"? I found out after a high profile cartography project was finished 😕

Userlevel 6

+33

nielsgerrits
Celebrity
2347 replies
1 year ago
8 February 2023

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

Aye, this can get very nasty very quick. It all depends on the data. Is it one time only or automated? 1000 or 1000's of records? Etc .etc.

C

+1

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

One time only but 23,000 records

C

+1

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

Yep, and half of them are in German. I figured I can probably figure out a second language once I figure out how to deal with the first but I am concerned with different spellings. AND there are historical names.

Userlevel 6

+33

nielsgerrits
Celebrity
2347 replies
1 year ago
8 February 2023

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

If it was easy, you would not have got this question 🙂 Just get started with a base list, then iterate down the remnants.

Userlevel 5

+25

redgeographics
Influencer
3345 replies
1 year ago
8 February 2023

No unfortunately it's not. Sometimes there is other information after or there is no country, it just has a continent.

That sounds like a challenge. You can try the method @nielsgerrits suggested, off the top of my head the Natural Earth dataset has both English and German country names (and a load of other languages too for that matter). It will most likely not match everything but hopefully what's left is a relatively small number (and also hopefully a relatively small number of unique countries). At that point you can try and refine your list and rerun the process, or fix the remainders manually.

M

+2

@caitlin.thorn what about using a geocoder? Try the FME Geocoder (OpenStreet map if free)

Userlevel 3

+26

dustin
Influencer
589 replies
1 year ago
9 February 2023

@caitlin.thorn Luckily for you, a similar problem was addressed in a recent webinar about OpenAI....see what I did there 🙂 I'd imagine you could submit a prompt that says something like "What country is @Value(att) in?" If you do try this, I'd be interested in the results. The webinar can be found in the link below, specifically the 26:30 mark in the video.

Just out of curiosity, here is the response I got with your example....which was a bit of a softball question for ChatGPT. If you can get it to work, you could parse the return to get everything after "is located in"

Unleashing the Power of OpenAI GPT-3 in FME Data Integration Workflows (safe.com)

EDIT: Ok, I had to try this out since this just seems too cool not to. 🙂 Using the OpenAICompletionsConnector I was able to elicit a correct response when I asked it where Lake View, Chicago. Seems very promising.

C

+1

So I actually ended up doing this by using a StringSearcher to match a list of country names using a RegEx. I created the list using Notion AI, then when there were matches I created a new attribute with those. This was only for those which just had the country name, which was actually more common than I initially thought, making it a bit easier.

Here is the country list if anyone needs it in the future. No alternative spellings but you could add those. Notion AI was a great tool here!

How do I pick country names out of an attribute?

12 replies

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded