Mais

Geocodificação reversa usando dados OSM importados para PostGIS

Geocodificação reversa usando dados OSM importados para PostGIS


Estou tentando reverter a geocodificação de alguns pontos usando uma importação OSM para PostGIS. Importei os dados OSM (para a Dinamarca, portanto, todos os endereços devem estar disponíveis) para PostGIS usando osm2pgsql habilitando "addr: street" e "addr: housenumber". Todos os endereços agora devem estar notabela planet_osm_point. Minha tabela de pontos que desejo reverter geocódigo é criada usando

torre de atualização definir the_geom = st_transform (st_setsrid (st_makepoint (xcoord, ycoord), 4326), 4326)

Ao tentar usar o código a seguir, obtenho alguns resultados muito estranhos onde, pelo que posso ver, os endereços com os quais acabo são aleatórios, mas ainda dentro da área geral ou talvez deslocados por até alguns km:

atualizar torre definir mais próximo_number = t. "addr: housenumber", próximo_street = t. "addr: rua" de (selecionar "addr: housenumber", "addr: rua", pk de planet_osm_point, torre onde st_dwithin (st_transform (planet_osm_point.way , 4326), torre.the_geom, 0,01) ordem por st_transform (planet_osm_point.way, 4326) <-> torre.the_geom ASC) t onde torre.pk = t.pk;

Na imagem anexa, rotulei os pontos com o resultado da geocodificação reversa e, como exemplo, destaquei um ponto que obteve um endereço que está localizado mais ao norte, um pouco além da fronteira. Alguém tem alguma ideia do que estou fazendo de errado?


É fácil obter efeitos inesperados executando umATUALIZARque envolve uma junção. Em profundidade na documentação do PostgreSQL para UPDATE, você pode encontrar o seguinte aviso:

Quando uma cláusula FROM está presente, o que essencialmente acontece é que a tabela de destino é unida às tabelas mencionadas em from_list e cada linha de saída da junção representa uma operação de atualização para a tabela de destino. Ao usar FROM, você deve garantir que a junção produza no máximo uma linha de saída para cada linha a ser modificada. Em outras palavras, uma linha de destino não deve se juntar a mais de uma linha da (s) outra (s) tabela (s). Em caso afirmativo, apenas uma das linhas de junção será usada para atualizar a linha de destino, mas qual delas será usada não é prontamente previsível.

Visto que sua consulta original está potencialmente gerando muitas correspondências de rua para cada ponto, você está atualizando o ponto com uma dessas correspondências aleatórias. O fato de você estar ordenando a subconsulta não ajuda, infelizmente.

Você pode resolver o problema colocando umDISTINTOna tuaA PARTIR DEcláusula para garantir que você obtenha apenas uma correspondência ou, talvez mais diretamente, colocando umLIMITE 1depois do seuORDENAR POR.

Uma boa maneira de evitar o problema (que não ajuda muito aqui) é retirar sua junção doA PARTIR DEe transformá-lo em uma expressão apósDEFINIR, ou seja:

ATUALIZAR torre SET near_id = (SELECIONAR id FROM planet_osm_point.way ONDE ST_DWithin (tower.geom, way.geom, 0,01) ORDENAR POR ST_Distance (tower.geom, way.geom) ASC LIMIT 1);

Este método tem a vantagem de que sua consulta falhará se sua subconsulta retornar mais de uma linha. Mas se você precisar buscar mais de uma coluna de sua subconsulta, não acho que haja uma maneira de tirar proveito disso.


Não tenho certeza de por que isso funciona e adoraria que alguém fornecesse uma resposta real com o raciocínio por trás, mas usar o código a seguir parece funcionar:

atualizar torre definir mais próximo_number = t. "addr: housenumber", próximo_street = t. "addr: street" de (selecionar distinto st_transform (planet_osm_point.way, 4326) <-> tower.the_geom, "addr: housenumber", "addr: rua ", pk de planet_osm_point, torre onde st_dwithin (st_transform (planet_osm_point.way, 4326), torre.the_geom, 0,01) ordenar por st_transform (planet_osm_point.way, 4326) <-> torre.the_geom ASC) t onde torre.pk = t.pk;

EDITAR

A declaração abaixo faz muito mais sentido, pois usa oselecione distinto emexpressão, na colunapk, que é um número inteiro exclusivo serializado. Então, para cada únicopkvalor eu obtenho o ponto mais próximo deplanet_osm_point. Inspirado por bostongis.

atualizar torre definir mais próximo_number = t. "addr: housenumber", próximo_street = t. "addr: street" de (selecionar distinto em (tower.pk) tower.pk, "addr: housenumber", "addr: street" de planet_osm_point, torre onde st_dwithin (st_transform (planet_osm_point.way, 4326), torre.the_geom, 0,01) ordenar por st_transform (planet_osm_point.way, 4326) <-> torre.the_geom ASC) t onde tower.pk = t.pk;


Assista o vídeo: Postgis - QGIS Stintersects Stdistance