SQL query: Which users are x distance from me (or some other user)?

Documentation Discourse Locations
1

If you have the Discourse Chatbot Plugin installed as well, you can simply ask it this.

The alternative is this Data Exploer query. It takes a username and distance, and returns all users who are located within that distance from the user.

It defaults to km, but miles can be used if you are in one of the backward countries in the world that are still imperial…

The use-case is to easily enable the identification of users in a certain area for a local event or grouping.

-- [params]
-- string :username
-- int :distance
-- string :unit = km

-- Define the username and distance in kilometres or miles
WITH target_user AS (
  SELECT lu.latitude, lu.longitude
  FROM users u
  JOIN locations_user lu ON u.id = lu.user_id
  WHERE u.username = :username
),
conversion_factor AS (
  SELECT CASE WHEN :unit = 'km' THEN 111.045
              WHEN :unit = 'miles' THEN 69.0
         END AS factor
)

-- Select users within the defined distance
SELECT u.username, 
       ROUND(((point(lu.latitude, lu.longitude) <-> point((SELECT latitude FROM target_user), (SELECT longitude FROM target_user))) * (SELECT factor FROM conversion_factor))::numeric,1) AS distance_in_unit
FROM users u
JOIN locations_user lu ON u.id = lu.user_id
WHERE (point(lu.latitude, lu.longitude) <-> point((SELECT latitude FROM target_user), (SELECT longitude FROM target_user))) * (SELECT factor FROM conversion_factor) <= :distance
ORDER BY distance_in_unit
1 Like
2

Note that with both this plugin and Discourse Chatbot installed you can get his information from using a natural language question to the bot.

You can ask it things like:

  • “List the five nearest users to my location” … (if you’ve registered a location) … or even
  • “List all users within 500km of my location.”
  • “List the users within 200km of Berlin.”
  • “What is my address?”

I aim to add Topic location support soon.

1 Like