mysql – Searching for nearby locations with DISTINCT? (API Maps)

Question:

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

The problem is that I can't make the DISTINCT so that each user is listed only once in the SELECT . I looked further and saw that GROUP BY could solve the problem. It does group users preventing repetition, however it does not maintain the correct order which would be the shortest distance.

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

Result without GROUP BY (That's 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 would have to be like this…

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

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

My SELECT is this:

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:

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

A possible solution to your problem is to continue running your query and remove the duplicate records later. One way to do this is to use 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 not, change the data type accordingly.

Scroll to Top