Início / dba / Perguntas / 182839
Accepted
Randomize
Asked: 2017-08-08 01:42:29 +0800 CST

Encontrar os pontos geográficos mais próximos em duas tabelas?

  • 772

Eu tenho duas tabelas, ambas têm colunas lat/lon no mesmo país (Reino Unido). Os tamanhos das tabelas são aproximadamente 80M e 50M.

Junto com as colunas lat/lon, criei um geo-indexes para ambas as tabelas desta maneira:

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);
UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

Para encontrar os pontos mais próximos da tabela com 80M em relação a 50M em 0,1 milhas, corro algo como:

   SELECT A.latitude, A.longitude, B.latitude, B.longitude,
   FROM my_table_50 AS A, my_table_80 AS B
   where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10
   ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;

A consulta é muito lenta para ser executada (praticamente é cartesiana 50M X 80M).

Existe uma maneira de acelerá-lo?

Além disso, é realmente útil usar a "indexação geográfica do postgis" para um problema como esse? Usar "teorema de Pitágoras" pode ser suficiente (como na resposta escolhida aqui https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem ), pois estou esperando distâncias muito menor que o raio da terra ou pode levar a algum erro?

postgresql spatial

1 respostas

  1. Best Answer

    Não use ST_Distance. Ele nunca usará um índice. Em vez disso, use a distância KNN com <-> e use ST_DWithinquando possível.

    Para encontrar os pontos mais próximos da tabela com 80M em relação a 50M em 0,1 milhas, corro algo como:

    Para encontrar o ponto mais próximo, você pode fazer..

    SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
ORDER BY A.geom <-> B.geom
LIMIT 1

    Para encontrar o ponto mais próximo dentro de uma milha,

    SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
WHERE ST_DWithin(A.geom, B.geom, 1609.34)
ORDER BY A.geom <-> B.geom
LIMIT 1

    Além disso, a menos que seu postgis seja antigo, nunca faça

    SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);

    Dos documentos,

    Alterado: 2.0.0 Esta função não atualiza mais geometry_columns, pois geometry_columns é uma visão que lê os catálogos do sistema. Por padrão, ele também não cria restrições, mas usa o comportamento do modificador de tipo embutido do PostgreSQL. Então, por exemplo, construir uma coluna wgs84 POINT com esta função agora é equivalente a:ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

    Certifique-se de que você tenha um índice espacial/gist em A.geom e B.geom. E considere agrupar ambos nesses índices.

    • 3

relate perguntas