I am continuing my experiments after compiling the #BritList, Ukrainians and Germans I adapted my script to search across multiple countries to see where the big crowds are. It can easily be adapted to actually list individuals.
I checked up on the countries where visitors to Hive Blog ...
...and Peakd come from. I expanded the list to other obvious countries. Some of those did not get too many results, which may be down to the search terms I am using. I am just checking profile location with the obvious country name plus a city or alternative name. For the UK I have compiled a more comprehensive list manually over more than a year.
These numbers are for those posting or voting in the last month. There may well be some false positives.
Australia: 144
Austria: 56
Bangladesh: 149
Belgium: 38
Brasil: 142
Egypt: 4
Canada: 182
China: 41
El Salvador: 5
France: 78
Germany: 288
Hong Kong: 15
India: 223
Indonesia: 384
Iran: 23
Israel: 17
Italy: 125
Japan: 57
Malaysia: 94
Myanmar: 40
Netherlands: 59
Nigeria: 516
Norway: 38
Paraguay: 17
Philippines: 402
Poland: 86
Russia: 39
South Africa: 103
South Korea: 35
Spain: 87
Thailand: 62
Turkey: 45
Ukraine: 119
Venezuela: 1757
Vietnam: 16
USA: 342
UK: 231
Grand total: 6059
The big surprise was Venezuela, which is way ahead of anywhere else. I expect there are a lot more people in some of these countries, but their metadata is not making them obvious. This is why we need people to manually curate such lists. If anyone wants to work with me on this then please get in touch. I think this is valuable data, but it will take work to improve it.
Which countries have I missed that would have a lot of users?
I am using @hivesql to get the data. Perhaps someone has more reliable ways to query for location.
These are the queries I am using right now. I welcome suggestions on improvements, but as I say above I think having local people working on their own country we will get better results. I do not have time to maintain a list for every country.
'Australia':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%australia%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%sydney%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%tasmania%')"],
'Austria':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%austria%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%vienna%')"],
'Bangladesh':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%bangladesh%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%dhaka%')"],
'Belgium':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%belgium%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%belgique%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%brussels%')"],
'Brasil':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%brasil%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%brazil%')"],
'Egypt':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%egypt%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%cairo%')"],
'Canada':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%canada%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%montreal%')"],
'China':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%china%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%beijing%')"],
'El Salvador':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%el salvador%')"],
'France':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%france%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%paris%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%bordeaux%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%brittany%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%calais%')"],
'Germany':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%germany%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%deutschland%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%berlin%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%bavaria%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%bayern%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%hamburg%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%frankfurt%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%münchen%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%cologne%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%stuttgart%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%düsseldorf%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%leipzig%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%munich%')"],
'Hong Kong':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%hong kong%') "],
'India':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%india%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%delhi%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%mumbai%')"],
'Indonesia':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%indonesia%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%aceh%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%jakarta%')"],
'Iran':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%iran%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%tehran%')"],
'Israel':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%israel%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%jerusalem%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%tel aviv%')"],
'Italy':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%italy%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%italia%')"],
'Japan':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%japan%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%tokyo%')"],
'Malaysia':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%malaysia%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%Kuala Lumpur%')"],
'Myanmar':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%myanmar%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%burma%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%naypyidaw%')"],
'Netherlands':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%holland%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%netherlands%')"],
'Nigeria':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%nigeria%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%lagos%')"],
'Norway':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%norway%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%oslo%')"],
'Paraguay':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%paraguay%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%asunción%')"],
'Philippines':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%Philippines%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%manila%')"],
'Poland':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%poland%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%warsaw%')"],
'Russia':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%russia%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%moscow%')"],
'South Africa':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%south africa%')"],
'South Korea':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%korea%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%seoul%')"],
'Spain':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%spain%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%madrid%')"],
'Thailand':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%thailand%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%bangkok%')"],
'Turkey':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%turkey%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%ankara%')"],
'Ukraine':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%ukraine%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%lviv%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%mariupol%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%України%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%Ukrainy%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%kyiv%')"],
'Venezuela':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%venezuela%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%caracas%')"],
'Vietnam':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%vietnam%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%hanoi%')"],
'USA':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%U.S.A.%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%USA' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%united states%')"]
