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%')"]
~~~ embed:1539942448612491266 twitter metadata:RW9pbkNhcnJvbGw1MHx8aHR0cHM6Ly90d2l0dGVyLmNvbS9Fb2luQ2Fycm9sbDUwL3N0YXR1cy8xNTM5OTQyNDQ4NjEyNDkxMjY2fA== ~~~
The rewards earned on this comment will go directly to the people( @demotruk ) sharing the post on Twitter as long as they are registered with @poshtoken. Sign up at https://hiveposh.com.
There are lots of Venezuelans that are here that I engage with so that does surprise me.
The Philippines is a good market for Hive.
I hope more projects can be directly be implemented for the Philippine Community in terms of promotion and curation.
I had been using the 'spanish' post tag as a proxy for measuring posters from Venezuela, but it seems like I vastly underestimated how many active Venezuelan users we have based on your methodology.
They seem keen to have 'Venezuela' in their profile details. Over 1200 are actively posting. I would hope word of mouth is spreading Hive there. We need to reach similar penetration in other countries.
I used to interact with people from Indonesia, but many of them seemed to stay on Steemit. Some of the Nigerians are here on Hive and I like to see how things are down there. That is something I wasn't even close to achieving when I was on Facebook, and the federated social media also seems to be mostly Western upper middle-class, left-leaning and tech savvy.
I'd say that one of the best things about Hive is the diversity.
I agree the diversity is wonderful. I hear from people all over the world on Hive. I did check Denmark too, but there were not many showing there. That may be down to limitations of my query. This was just a quick exercise to see how the numbers looked, but I really want to see people compiling better national lists and I will help where I can.
It is pretty much in line with my experience. The Danes here are mostly gone as far as I can tell.
This is almost the same as my comment lol
Hi, I'm samostically. I organize a weekend engagement for Nigerians every week where I give hive prizes. I would love if you could throw some upvotes our way or support in anyway that you can.
Here is a link to the last engagement post.
I create one every friday. Thanks.
I didn’t realize we were this much from nigeria 516...not bad at all
Thanks for the update
Hive has really taken off there. I hope we can do as well elsewhere. It just takes a few dedicated people to spread the word.
I looooove hive naija but cross culture has been hard cause only naija got involved for a while. Maybe I get along with everyone there too well 😂
😂😂Maybe
I am surprised too by the Vene numbers. Although, to be honest, I shouldn't be given the number of ENG/ESP posts that I see
Wish I had some Spanish speakers around me IRL so I felt motivated to learn and chill with hive Venezuela
That's the good thing about the combined language posts, you can learn from them!
Great job @steevc, it's quite pleasant to know these facts. I'm shocked at the low numbers Nigeria is doing, though, I would have really expected more because Nigeria is a really populated country and back in 2017 there was a really strong Nigerian presence.
There may be more in Nigeria, but some will not have set their profile location. In most countries the Hive population is tiny, but that gives us lots of potential for growth.
!BEER
Oh, yeah. Cool 👍
View or trade
BEER.Hey @nevies, here is a little bit of
BEERfrom @steevc for you. Enjoy it!Learn how to earn FREE BEER each day by staking your
BEER.Neat bit of data!
I'd probably approach this differently, myself. You're looking for location data matching certain locations which is fine, but I think a more comprehensive way to capture this would be to scrape the location data out and then using a script, match those users to countries and throw away any non-location data that is present.
Leveraging the google maps API you could pretty easily get country from pretty much any scrap of location data, or you could manually match through a list (or separate db table) of country/city locations.
Great work though regardless, super interesting to see some of these numbers! Fun little project for sure!
This was a pretty simple script to test the idea. The metadata on Hive is really messy, so it needs some manual processing. Given how few active users we have it could be done by a few people taking a region each. I manage the UK list.
Nice work. A disproportionate amount of my interactions are with Nigerians and Filipinos. I can’t figure out if it’s cause I get along with them best or if there are just so many from there. I used to see more indonesian and venezuelan in my comments but I guess there are more language barriers there
I get responses from all around the world. Hive tends to be more diverse than other platforms and I love it for that.
!BEER
Me too! I'm getting back to Cross Culture community cause I kept meeting amazing people through it. It was tiring to run but worth it!
!pizza
View or trade
BEER.Hey @selfhelp4trolls, here is a little bit of
BEERfrom @steevc for you. Enjoy it!Learn how to earn FREE BEER each day by staking your
BEER.Could the users browser & VPN's also effect those numbers? I know a lot of people, including myself use Brave browser and user agents with Firefox. Along with do not track plugins.
The numbers I use are from blockchain transactions and account metadata, so the browser is irrelevant. What Hypestat shows is from browsers, but based on people having the Alexa extension, which most people do not have anyway. That data will not be available any more. We can get some data on dapp usage from Dapp Radar, but that does not show locations. I am just playing with the data that is available and Hivers can decide how much they give away about themselves.
Ahhh, ok. Didn't know if those things came into play on the Metadata or not.
I'm not a stats guy but I do find it interesting to know where on the world the users are. It's just git a really coolness ratio to it. Lol!
The profile metadata is really messy as people can type in anything they want. @demotruk has done more analysis in his posts.
I hope some others will step up to compile local Hiver lists. I do this for Brits and I have found a lot whose metadata would not make their location obvious. This will be useful for meetups.
Awesome data! Thanks for sharing. I'm actually surprised we don't have more regular users. I knew Venezuela was a dominant community in Hive. I'm actually surprised how low US numbers were. Loving these posts, thanks for sharing!
!LUV
There will be a lot more in the US, but my query was pretty crude. Do you fancy working on building a comprehensive list? Would be great to map people by state.
That could be fun. Maybe this winter? My plates full right now.
@steevc, @the-bitcoin-dood(1/1) sent you LUV.
wallet | market | tools | discord | community | <>< daily
question: when you type " 'Spain':["(JSON_VALUE(posting_json_metadata,'$.profile.location') like '%spain%' or " +
"JSON_VALUE(posting_json_metadata,'$.profile.location') like '%madrid%')"]
you get only people from Madrid or also from other parts of Spain?
I will get anyone with either 'spain' or 'madrid'. I can add more cities, but this was just a first run. You are welcome to take on building the list for Spain. I can give you what I have so far to start you off. With the Brits there are a lot who do not have an obvious location on their profile, but people will know each other and there will be clues. I have a text file with well over 600 Brits, but many are inactive for now.
I see, maybe I will do in the future, I just need some time for doing so
I didn't realize there are that much Venezuelan Hivers. I thought there are more from my country the Philippines lol! Way to go Venezuela!
I expect there are more in the Philippines, but it needs someone to work on finding them. I concentrate on the UK, but this was an interesting exercise.
Oh, I see. It would be interesting to see the numbers on a wider search :)
Anyone is welcome to do more detailed searching. I can provide the list of users I have found as a starter. I make my scripts available too. Those are in Python and you need access to HiveSQL. I want others to manage other countries.
I wish I could but those are alien to me lol!
It can also be done manually. Just need to keep adding people you find to a list.
Ah, got it :D
Great post, in the next one we will try to see Cuba there
Yooo we need some more cuba! Nice!
Yeah!!!! Check our community @hive-10053 to see the progress
It looks like Cuba has around 60 active users based on profile location, but you may know of more.
Around that number, i think a few more. We are trying to increase it and take Hive to every cuban knowledge
So cool to see the countries with some numbers… thanks for these stats, very interesting.
Great to see so many countries represented.
I've turned your data into a chart. BTW, you should add an 'unknown' for users who have no location data at all, and 'uncategorized' for users who have location data but don't match your filter.
You should also post it to Hive Statistics community :-P
There are lots of countries I have not even checked. I can look at adding more, but it means researching what names might be used as there can be variations and people are not consistent in what they use. For the Brits I check UK, GB, England/Scotland/Wales/N. Ireland and variations on those. Even that does not find everyone. This is why we need local knowledge from people with the time to maintain their local list. We could coordinate to combine the data into charts like yours. It's no simple job.
It doesn't need to be comprehensive. By adding 'unknown' and 'uncategorized' you can start narrowing down what is missing. Can you send me your query and I'll make a few additions?
I am running SQL queries, so I only get what matches. I don't have a query for the non-matches. The code is here. You need a HiveSQL key to use it.
You can get the total number of active accounts in the time period with this:
select count(*) as count
from Accounts a
WHERE (a.last_vote_time >= '2022-05-23 00:00:00.000' or a.last_post >= '2022-05-23 00:00:00.000')
And the number of the same with location data set with this:
select count(*) as count
from Accounts a
where a.posting_json_metadata like '%"location":%'
and (a.last_vote_time >= '2022-05-23 00:00:00.000' or a.last_post >= '2022-05-23 00:00:00.000')
That amounts to 25458 and 13804 respectively. The rest is just subtraction.
Worth noting that if you're not joining on anything, you don't need to alias Accounts or provide the a. on your Where statements since the only table it's referencing is Accounts and therefore the identifier is unique by default.
This query gives some useful results but they need cleaning up (lots of variations that mean the same thing)
select count(*) as count, JSON_VALUE(posting_json_metadata,'$.profile.location') as location
from Accounts a
WHERE a.posting_json_metadata like '%"location":%'
AND (a.last_vote_time >= '2022-05-23 00:00:00.000' or a.last_post >= '2022-05-23 00:00:00.000')
GROUP BY JSON_VALUE(posting_json_metadata,'$.profile.location')
ORDER BY count DESC
That will get a lot of results, but could make a list we could work from. Queries need to exclude false results, e.g. 'Northern Ireland' contains 'Ireland'. Various city names exist in multiple countries.
I just ran that query. We have 18 each on the Moon and Mars :) Lots of cities listed without a country.
Compiling a comprehensive list will involve a fair bit of manual work. I check each person I add for the Brit List. I have a query that looks at lots of locations, but excludes any I already have.
I'm doing some manual cleanup of the data as we speak. It won't be perfect, I will not check every account manually.
Here is the chart when we factor in users with no location data + users with uncategorized location data.
This is the real insight IMO! Very very interesting :)
Congratulations @steevc! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s):
Your next target is to reach 61000 replies.
You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOP