Início / dba / Perguntas / 158349
Accepted
xangr
Asked: 2016-12-17 02:40:01 +0800 CST

Como posso agilizar minha consulta em processos de geolocalização

  • 772

Tenho uma tabela que contém 10.301.390 registros de GPS, cidades, países e blocos de endereços IP. Eu tenho a localização atual do usuário com latitude e longitude. Eu criei esta consulta:

SELECT
  *, point(45.1013021, 46.3021011) <@> point(latitude, longitude) :: point AS distance
FROM
  locs
WHERE
  (
    point(45.1013021, 46.3021011) <@> point(latitude, longitude)
  ) < 10 -- radius
ORDER BY
  distance LIMIT 1;

Esta consulta me deu com sucesso o que eu quero, mas é lento. Demorou 2 a 3 segundos para obter um registro por determinada latitude e longitude.

Eu tentei um índice B-Tree nas colunas latitudee , também tentei, mas ainda assim a consulta é lenta.longitudeGIST( point(latitude, longitude));

Como posso acelerar esta consulta?

Atualizar:

Parece que a lentidão é causada pelo ORDER BYmas eu quero obter a distância mais curta, então a questão permanece.

postgresql index

2 respostas

  1. Best Answer

    Você pode considerar o uso de um índice GIST com base no uso da função ll_to_earth. Este índice permitirá pesquisas "próximas" rápidas.

    CREATE INDEX 
   ON locs USING gist (ll_to_earth(lat, lng));

    Depois de ter esse índice, sua consulta deve ser feita de uma maneira diferente.

    Seus pares (lat, lng) precisam ser convertidos para o earthtipo e comparados com os valores indexados (que são do mesmo tipo). Sua consulta precisará ter duas condições, uma para o resultado "aproximado" e outra para o "preciso". O primeiro poderá usar o índice anterior:

    SELECT
    *
FROM
    locs
WHERE
    /* First condition allows to search for points at an approximate distance:
       a distance computed using a 'box', instead of a 'circumference'.
       This first condition will use the index.
       (45.1013021, 46.3021011) = (lat, lng) of search center. 
       25000 = search radius (in m)
    */
    earth_box(ll_to_earth(45.1013021, 46.3021011), 25000) @> ll_to_earth(lat, lng) 

    /* This second condition (which is slower) will "refine" 
       the previous search, to include only the points within the
       circumference.
    */
    AND earth_distance(ll_to_earth(45.1013021, 46.3021011), 
             ll_to_earth(lat, lng)) < 25000 ;

    Para usar este código, você precisa de duas extensões (incluídas na maioria das distribuições do PostgreSQL):

    CREATE EXTENSION IF NOT EXISTS cube ;
CREATE EXTENSION IF NOT EXISTS earthdistance;

    Esta é a documentação para eles:

    • cubo . Você deve dar uma olhada na descrição do operador @>. Este módulo é necessário para o próximo.
    • EarthDistance . Você encontrará aqui informações sobre earth_boxe earth_distance. Este módulo assume que a Terra é esférica, o que é uma aproximação boa o suficiente para a maioria das aplicações.

    Um teste com uma tabela composta por 2,2 milhões de linhas retiradas do Free World Cities Database me dá a seguinte resposta para a consulta anterior (que não é exatamente a mesma que a sua):

    "ru","andra-ata","Andra-Ata","24",,44.9509,46.3327
"ru","andratinskiy","Andratinskiy","24",,44.9509,46.3327
"ru","chernozemelskaya","Chernozemelskaya","24",,44.9821,46.0622
"ru","gayduk","Gayduk","24",,44.9578,46.5244
"ru","imeni beriya","Imeni Beriya","24",,45.0208,46.3906
"ru","imeni kirova","Imeni Kirova","24",,45.2836,46.4847
"ru","kumskiy","Kumskiy","24",,44.9821,46.0622
"ru","kumskoy","Kumskoy","24",,44.9821,46.0622
"ru","lopas","Lopas","17",,44.937,46.1833
"ru","pyatogo dekabrya","Pyatogo Dekabrya","24",,45.1858,46.1656
"ru","svetlyy erek","Svetlyy Erek","24",,45.0079,46.4408
"ru","ulan tuk","Ulan Tuk","24",,45.1542,46.1097

    Para ter uma ideia de "ordem de grandeza" sobre temporizações: pgAdmin III está me dizendo que o tempo para obter esta resposta é 22 ms. (PostgreSQL 9.6.1 com parâmetros "out-of-the-box", em um Mac com Mac OS 10.12, Core i7, SSD)

    • 11

  2. Resposta alternativa com PostGIS

    Se você estiver usando 10 milhões de linhas. Você provavelmente precisará intensificar e atualizar para o PostGIS.

    1. Converta seus pontos em tipos de geografia. Presumo que estejam no SRID 4326 de qualquer maneira, se vierem do GPS. Para isso você pode usargeometery(point)::geography , ou se você armazenar em lat/long você pode usarST_MakePoint
    2. Crie um índice na nova coluna geom (de ST_Points)
    3. Então você quer usar ST_DWithin. Esta função irá um índice (se você criar um).
    4. Em seguida, calcule apenas os ST_Distancepontos na caixa delimitadora

    Aqui está o sinal para ST_DWithin,

    boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

    Ele pode medir sua distância ao longo do esferóide ou esfera.

    SELECT geom, ST_Distance(geom, point)
WHERE ST_DWithin( geom, pointgiven, limit in meters )
ORDER BY geom <=> point ASC
LIMIT 1;
    • 3

relate perguntas