MySQL summation of data depending on distance tables

Question

I have two tables, user and places, with latitude and longitude. For each place, I want to sum users who are within x distance from it.

One possible way of doing this is by looping. Is there any other way to do it? How can I achieve this in plain SQL?

Edit:

User(id, name, latitude, longitude)

Place(id, name, latitude, longitude)

You should provide your table structure so we understand how they relate, but no, you shouldn't need a loop. `join` and `count` are what you're looking for. — sgeddes, Aug 12 '16 at 14:54
https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html — Nick, Aug 12 '16 at 14:54
@sgeddes tables' structure is very simple. I have edited the post. — jsan, Aug 12 '16 at 15:04
http://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql — StanislavL, Aug 12 '16 at 15:40
http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points — StanislavL, Aug 12 '16 at 15:40

spencer7593 · Answer 1 · 2016-08-12T16:22:02.573

Seems like one approach would be a cross join of "user" and "place" (with each "user" matched to each place. Incorporate an expression that calculates the distance between the "user" lat/long and the "place" lat/long (GCD), and compare the result from the expression to the given distance "x", and based on that comparison return a 0 or 1. And use a SUM aggregate of the return from the comparison.

For example, something like this:

SELECT p.id
     , p.name
     , IFNULL( SUM( [gcd_expr] <= x ) ,0) AS cnt
  FROM place p
 CROSS
  JOIN user u
 GROUP BY p.id

With every user matched to every place, that could be really big set. And that's a lot of distances to be calculated, which could be prohibitively expensive.

For improved performance, we could include join predicates that restrict the deltas of latitude and/or longitude ... say you only want to check users that are within 5 degrees of latitude ...

SELECT p.id
     , p.name
     , IFNULL( SUM( [gcd_expr] <= x ) ,0) AS cnt
  FROM place p
  LEFT
  JOIN user u
    ON u.lat-p.lat BETWEEN -5.00 AND 5.00
 GROUP BY p.id

The [gcd_expr] represents the expression that performs the Great Circle Distance calculation. (There's implementations of that available; search for existing questions on StackOverflow, I know that question has been answered several times.)

MySQL summation of data depending on distance tables

1 Answers1