Searching nearby locations with DISTINCT? (Maps API)

Question

I have a separate table in MYSQL with user addresses containing location information (latitude and longitude), and each user can have more than one registered address. My goal is that when performing a search for a location, be listed all the users closest to that location.

The problem is I can’t do the DISTINCT so that each user is listed only once on SELECT. I researched further sinking and saw that the GROUP BY could solve the problem. It actually groups users preventing repetition, but it does not maintain the correct order that would be of lesser distance.

This case can be easily seen in the Maps documentation but DISTINCT is not done. https://developers.google.com/maps/articles/phpsqlsearch_v3

Result without GROUP BY (This is what I need, but without repeating users):

ID_PLACE | ID_USER | NAME_USER | DISTANCE
   2         1        MARIA         5
   3         2        KEVIN         6
   1         1        MARIA         8
   4         2        KEVIN         10

Result with GROUP BY:

ID_PLACE | ID_USER | NAME_USER | DISTANCE
    1         1        MARIA         8
    3         2        KEVIN         6

When in fact it had to be so ...

ID_PLACE | ID_USER | NAME_USER | DISTANCE
   2         1        MARIA         5
   3         2        KEVIN         6

With GROUP BY, distance ordering seems to be being ignored and is apparently being done by PLACE_ID.

Man SELECT that’s the one:

SELECT place_id, id_user,
(6371 * acos(
 cos( radians(-30.053831) )
 * cos( radians( lat ) )
 * cos( radians( lng ) - radians(-51.191810) )
 + sin( radians(-30.053831) )
 * sin( radians( lat ) ) 
 )
) AS distancia
FROM enderecos
GROUP BY id_user
HAVING distancia < 25
ORDER BY distancia ASC;

Answer 1

The problem with your code is that you have more than one address for each user. Actually this is not a problem, but part of the application.

One possible solution to your problem is to continue running your query and remove duplicate records later. One way to do this is by using a temporary table:

/* PRIMEIRO CALCULAMOS A DISTANCIA DE CADA USUARIO */
DROP TEMPORARY TABLE IF EXISTS `tmp_distancia`;
CREATE TEMPORARY TABLE `tmp_distancia` (
  place_id INT NOT NULL,
  id_user INT NOT NULL,
  distancia INT NOT NULL,
  PRIMARY KEY (id_user, distancia)
) ENGINE = MYISAM;

INSERT INTO tmp_distancia (place_id, id_user, distancia)
SELECT place_id, id_user,
(6371 * acos(
 cos( radians(-30.053831) )
 * cos( radians( lat ) )
 * cos( radians( lng ) - radians(-51.191810) )
 + sin( radians(-30.053831) )
 * sin( radians( lat ) ) 
 )
) AS distancia
FROM enderecos
HAVING distancia < 25;

/* AGORA BUSCAMOS A DISTANCIA MINIMA INDIVIDUALMENTE */
DROP TEMPORARY TABLE IF EXISTS `tmp_distancia_minima`;
CREATE TEMPORARY TABLE `tmp_distancia_minima`(
  id_user INT NOT NULL,
  distancia INT NOT NULL,
  PRIMARY KEY (id_user, distancia)
)ENGINE = MYISAM;

INSERT INTO tmp_distancia_minima (id_user, distancia)
SELECT
  id_user,
  MIN(distancia)
FROM tmp_distancia
GROUP BY id_user;

/* FAZEMOS A BUSCA FINAL, ELIMINANDO RESULTADOS DUPLICADOS */
SELECT
  A.id_user,
  A.place_id,
  A.distancia
FROM tmp_distancia AS A
INNER JOIN tmp_distancia_minima AS B
  ON (A.id_user = B.id_user AND A.distancia = B.distancia)
ORDER BY A.distancia;

Important: In the above query I am assuming that the distance will always be an integer value. If it is not, change the data type accordingly.