How to find Steemians who live close to you in seconds!

in #steemsql7 years ago (edited)

Want to find users of the platform who live near you? This simple SteemSQL query will do the trick!

find users by location using steemsql.png

If you've ever wanted to find out who are the Steemians who live near you, this is the way to do it using SteemSQL.

This is super useful if you are planning a meetup or #promo-steem event, or just want to prove to yourself that you're not the only lonely Steemian in your local area.

Now obviously, not everyone in an area puts their location in. Some people I guess don't want to be found, for whatever reason, and of course that's perfectly cool.

But for those of us who do want to be found (and as one of the #promo-steem core team, this is a very useful tool for finding local supporters), this method is beautifully simple, easy to set up even for a complete newbie, and I got a full list of the 12000 plus Steemers in the London area in seconds!


How to run a SteemSQL query to find users in a particular location

STEP 1: Get a subscription to SteemSQL


You will need to get a subscription from SteemSQL. This costs 10SBD every month, and for the data you get from it, it's absolutely brilliant. In the few posts where I've used this data, I've already made more back than the cost of the monthly subscription in upvotes, so it's well worth it if you can get on with it. I'm no SQL expert. In fact, I'm a bit of a novice at it, and if I can work it out, well probably anyone can!

STEP 2: Download an IDE to do the query


I use the free LinqPad to do my queries. It's a free download ( you just need the community edition, not the premium one). It's dead easy to set up and available for Windows, Mac and Linux.

STEP 3: Set up your IDE


In LinqPad, set up a new SQL environment. You will need to enter the SQL server name, your password and the Database Name (you will get sent these when you subscribe to SteemSQL as a MEMO in your wallet. Don't worry, nobody else can see them!)

STEP 4: Run your query


In the query box, simply paste the following and hit RUN:

SELECT name as username
FROM dbo.Accounts
WHERE json_metadata LIKE '%london%'
order by
username asc

You can use this yourself and simply replace the word "london" with whatever city, country or region you like. For example if you want to see how many people are in Nigeria just replace %london% with %nigeria%.

The reason I've used the % sign is that this looks for all variants around the word between the %'s. For example, if someone has put "London, UK" in as their location, it will still return the result despite having more than just the word "London" in the location

The query is not case sensitive, so it doesn't matter if you put "london" or "London".


For newbies to this sort of thing, like me, I will be posting up new queries as I figure them out, and you can try them too!


MY VALUE ADDED OFFER


Don't want to set this up yourself? Let me know and I'll run a query for you. Limited to the first 10 people who ask for one (or maybe more if you choose to make a small donation to @steem-ambassador to help us support more promoters)

Hope this helps!
Love, light and laughter.
Anarcotech x

promo-steem team are official media partners at the London Cryptocurrency Show 2018

Sort:  

This is really amazing
Though I'm new to this but from subsequent posts I will be updated on it.
Cant wait for more blogs on this.

They will be coming. Thanks for the comment Sami.

Thank you sir
I look forward to it

This is great. Thankyou. Could you check Adelaide please?
Include account names, and I'll send you 10 SBD

Loading...

If you want them in a nice excel format, just bob me your email address and I'll send that over to you too.

Thanks mate. Just like that is fine. Great to see so many. I was only expecting half that :)

Thank you so much! I'm glad I could help.

@laminesonko doesn't appear to have any relationship with 'Adelaide' (all the others look good so far)

Yes, I just had a look. Funny that. Still, that's the data it came back with. How very bizarre!

It is really awesome...why didnt you made this post earlier....it is really helpful ...gonna make a try ....guess i can post something about the meet ups too jow...heheh😂😂

Yep. That's the idea. I didn't post it earlier because nobody had asked me to do it. @starkerz wanted a list earlier on for London users. I don't think he was quite ready for how many of them there were. hehe.

More simple Steem tricks to come....

Very informative and usefull article, thanks a lot, greeting friendship from me @abialfatih in aceh indonesia

Where there are a whopping 7241 users with Aceh on their profile!!!!

Brilliant - what a great tool. Can you do Manchester at some point for me - would you DM me the data > then> I'll blog about it, reference you and split the SBD earnings 50:50 with you?

Oh go on then Chris, you master negotiator you! :)

There are 194 in Manchester. I'll email them over to you. If I haven't done by tomorrow, hassle me until I do it.

Wow! More than I thought. Thank you.

of course, there may be manchesters in other countries too. We might have to filter a few countries out. I know there is one in the US, so when I excluded "us" from the query, it went down to 141

Thanks - yes lots of Manchester's in US. Happy to take the cut at 141. Cheers

This is great, I could do with finding Steemians in the Sheffield area for the Easter promo I'm doing. I feel cheeky asking but could you run it for me and let me know. it would be great for that week.
up-voted resteemed

Ok. So here they are:
artonmysleeve
chrisdutton
cryptohubuk
cryptophile-uk
epcdedication
flaybur7
fulcanelli
ijazlikelocals
j-bex
johleen
justinriley
l8bit
magnusbuda
marekvej
mikewilks
ooaldridge
pjhoughton
quintbritish
thomw
zhoudi1220

I ran two queries, because the first came up with someone in Sheffield, New Zealand. So I've added another line that removes New Zealand from the search. There may be other countries with a sheffield too. But with a list of 20 they will be easier to find.

Cheers for doing this for me mate. just checked them all and there is no active users in sheffield, some have not been on for months, some haven't even made a post or comment.
It was great of you to do this for me though. cheers once again :)

The social media marketing has started and its going crazyyy! This is all thanks to your support @anarcotech i will livestream the event with dlive so we can talk and you guys can see all the results. Thank you so much!
U5duEEUxFg4Ypttgu7T8YXNcXZAzr1k_1680x8400.png

Looks like its gonna be an amazing event!

Thanks @anarcotech for this one; then i can get to know how many steemians are in my Country Uganda.

I can tell you right now. It's 84. Well, thats a query just on the word uganda. It might be worth giving a list of major city names, and I can run those in parallel. Likelihood is that this will return many more.

Woow..thanks so much! Now i see that i need to do more promos this side. We are atleast growing now.

I'd work on the basis that most dont just put Uganda. It might be trying the big cities return more hits. For example, a query on Kampala as well as uganda returns 97.

This is mind blowing and outrightly brilliant.
Can you tell the number of Steemians in my country, Nigeria?

Yes. 4874. Again, this is only on the search term 'Nigeria'. If you added city names (because some will just have put something like Port Harcourt in, this will probably return many more.

Wow!!!
Really need to bring more people into the platform

This is like a deliverance.. I've been thinking on how to connect with Steemians in my city. Thanks for the info @anarcotech. I will sure give it a try.

Good news. Hope my instructions help you to figure it out.

Always educating the mass. That's brilliant

I do my best. I found it difficult to get the information I wanted, so I'm putting this out, with a good view on the SEO of the post, so that someone else might be able to find it quicker. A lot of techies who already know how to do this are likely to not care, but it's more for people who aren't like those clever devs to quickly find out how to do it....like me!

nature will surely pay you someday. its karma!

good tools and we need to try

Thanks for the update

up-vote from @steem-bd community

up-vote from @steem-bd community

up-vote from @steem-bd community

up-vote from @steem-bd community

up-vote from @steem-bd community

up-vote from @steem-bd community

up-vote from @steem-bd community

Incredibly helpful. this is a great news I just found here. And I will try it in some future time