Início / dba / Perguntas / 71509
Accepted
andrewniesen
Asked: 2014-07-15 17:13:22 +0800 CST

Pesquisa eficiente de geolocalização - LAT/LNG e DECIMALs

  • 772

Eu tenho um banco de dados com > 7,5 milhões de linhas (e crescendo), cada uma com um registro de imagem que possui uma geolocalização LAT/LNG específica representando onde a foto foi tirada, armazenada como valores DECIMAIS -

 mysql> describe image_meta;
 +---------------------+------------------+------+-----+-------------------+----------------+
 | Field               | Type             | Null | Key | Default           | Extra          |
 +---------------------+------------------+------+-----+-------------------+----------------+
 | id                  | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
 | media_id            | varchar(255)     | YES  | UNI | NULL              |                |
 | user_id             | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_unix    | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_zulu    | datetime         | YES  |     | NULL              |                |
 | latitude            | decimal(12,10)   | YES  | MUL | NULL              |                |
 | longitude           | decimal(13,10)   | YES  | MUL | NULL              |                |

Quero pesquisar dentro de uma área retangular, definida por LAT/LNG, e as consultas são muito lentas.

Estou usando esta consulta:

SELECT user_id FROM image_meta WHERE (latitude BETWEEN 40.779769 AND 40.792399 AND longitude BETWEEN -73.988457 AND -73.963308);

com o resultado:

123569 rows in set (8 min 28.99 sec)

Qual é a melhor maneira de fazer isso ir mais rápido? Devo pesquisar uma área mais ampla com menos algarismos significativos e, em seguida, usar o PHP para restringir os resultados pelos limites LAT/LNG mais precisos?

Sou um novato em SQL (sempre tive DBAs para me ajudar no passado), mas estou fazendo este projeto sozinho. Desde já, obrigado...

mysql

1 respostas

  1. Best Answer

    Os índices "padrão" do MySQL usam B-Trees , que são horríveis para pesquisar intervalos em 2 ou mais dimensões (eles podem usar apenas a primeira coluna). Você deseja usar R-Trees , disponível, como em muitos outros RDBMS, ao usar as extensões GIS/espaciais para MySQL.

    Eles exigem tipos de dados específicos . POINT é o que você quer - você terá que converter a latitude e a longitude para este formato. Em seguida, crie um índice espacial . Em seguida, usando a função Contains()ouMBRContains() .

    Observe que esse recurso é normalmente criticado no MySQL pela falta de recursos (por exemplo, não estava disponível para InnoDB antes de 5.7, sem função de distância, sem suporte de projeção, ...). Ironicamente, a falta desses recursos tornou a implementação muito rápida - quando funcionou. E deve ser o suficiente para o que você quer fazer.

    Se você não pode usar a indexação espacial no MySQL, um índice em (latitude, longitude) pode ser um pouco mais rápido em 5.6 com ICP, ou você pode alternativamente tentar usar alguns truques de indexação . Se isso não funcionar para você e ainda for muito lento, você precisará de uma solução de indexação externa ou de uma mudança completa do servidor de banco de dados.

    • 1

relate perguntas