Looking for nearby places with DISTINCT? (Maps API)

Navigation
3

I have a separate table in MYSQL with the addresses of the users 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 a location, all the users closest to that location are listed.

The problem is that I can not do DISTINCT so that each user is listed only once in SELECT . I searched deeper and saw that GROUP BY could solve the problem. It does indeed group users by preventing repetition, however it does not maintain the correct order that would be the shortest distance.

This case can be easily seen in the Maps documentation but DISTINCT is not done. link

Result without GROUP BY (This is what I need, but without repeating the 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 this way ... 

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 .

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;
    
asked by anonymous 27.02.2017 / 03:12

1 answer

2

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.

One possible solution to your problem is to continue running your query and remove the 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 not, change the data type accordingly.

    
27.02.2017 / 12:20
Pull data to an input when typing in the field execute .GlobalEnv function in parallel processing