Início / dba / Perguntas / 68076
Accepted
andrewniesen
Asked: 2014-06-12 13:00:11 +0800 CST

Fazendo um join para buscar pontos de geolocalização em zonas

  • 772

Tenho uma série de pontos de geolocalização em uma tabela e uma série de "zonas" em outra; Eu gostaria de fazer uma junção para identificar a qual zona cada ponto pertence (se houver).

A tabela de pontos fica assim:

+--+--------+---------+
|id|Latitude|Longitude|
+--+--------+---------+
| 1|37.23221|-81.32323|
+--+--------+---------+    
| 2|36.92321|-81.12423|
+--+--------+---------+
| etc                 |
+--+--------+---------+

A tabela de zonas se parece com:

+--+------+------+------+-----+-----+
|id|Name  |LONG_E|LONG_W|LAT_N|LAT_S|
+--+------+------+------+-----+-----+
| 1|Zone 1| -81.0| -82.0| 38.0| 37.0|
+--+------+------+------+-----+-----+
| 2|Zone 2| -81.0| -82.0| 37.0| 36.0|
+--+------+------+------+-----+-----+
| etc                               |
+--+------+------+------+-----+-----+

Eu gostaria de poder consultar a tabela de pontos e ingressar na tabela de zonas, para obter um resultado parecido com:

+----+----------+-----------+----+------+
|p.id|p.latitude|p.longitude|z.id|z.name|
+----+----------+-----------+----+------+
|   1|  37.23221|  -81.32323|   1|Zone 1|
+----+----------+-----------+----|------+    
|   2|  36.92321|  -81.12423|   2|Zone 2|
+----+----------+-----------+----+------+
|etc                                    |
+----+----------+-----------+----+------+

Estou muito enferrujado em minha sintaxe SQL, então estou me perguntando se isso é possível... Estou usando o MySQL BTW. Obrigado!

mysql join

1 respostas

  1. Best Answer

    Isso deve servir:

    SELECT p.id, latitude, longitude, z.id, z.name
  FROM points p
  LEFT JOIN zones z
    ON ( latitude BETWEEN LAT_S AND LAT_N
          AND
         longitude BETWEEN LONG_W AND LONG_E ) ;

    [EDITAR]

    De acordo com seu comentário, suponha que você queira adicionar uma coluna FK à sua pointstabela que associe o ponto ao seu id de zona. Existem algumas razões pelas quais isso não seria recomendado, como se os limites de sua zona pudessem mudar, você teria dados incorretos/inconsistentes. No entanto, para desempenho bruto, talvez você queira que o id da zona seja desnormalizado na pointstabela, tendo em mente que é como um id de zona em cache. O MIN()operador está lá para protegê-lo caso você tenha duas zonas sobrepostas. Nesse caso, você tem problemas maiores, mas pelo menos UPDATEfunciona, e pega o id da zona inferior por fiat.

    ALTER TABLE points ADD (zone_id int references zone(id));

UPDATE points SET zone_id = 
( SELECT MIN(z.id)
  FROM zones z
  WHERE latitude BETWEEN LAT_S AND LAT_N
          AND
        longitude BETWEEN LONG_W AND LONG_E
);
    • 3

relate perguntas