Como carregar o OpenStreetMap no Hive?

Em um artigo anterior, examinei a geocodificação reversa usando o Spark. Agora imagine que enfrentamos o desafio de geocodificação direta de endereços de correio. Ou seja, recebendo pelo endereço registrado pelo texto de algumas coordenadas geográficas.

Os endereços para definição são russos e, o mais importante - geralmente são escritos de forma torta, ou seja, com erros, ambiguidades e outras delícias. E esses endereços estão no banco de dados Hive, no cluster Hadoop.


Bem, ao que parece - pegamos a API de geocodificação do Google Maps (ou, se você é um defensor da substituição de importação, depois a API do Yandex Maps) e trabalhamos. Mas aqui nós, assim como com a geocodificação reversa, aguardamos uma pequena emboscada.

Ou grande, é como uma olhada. O fato é que desta vez precisamos processar cerca de 5 milhões de endereços. E talvez 50 - não ficou claro de imediato. Como você sabe, o Google banirá seu IP após cerca de 10 mil endereços, o Yandex fará o mesmo com você, embora isso seja possível um pouco mais tarde (25 mil solicitações por dia, como). Além disso, as duas APIs são REST, o que significa que é relativamente lento. E mesmo se você comprar uma assinatura paga, a velocidade não aumentará em um centavo.

E, no entanto - ficamos sem munição (s).

Esqueci a coisa mais importante - nosso cluster Hadoop está localizado na intranet e o Google Maps, para a empresa com o Yandex Maps e todos os outros, geralmente são inacessíveis para nós a partir do cluster. Ou seja, precisávamos de uma solução autônoma.

Eu direi imediatamente - você não encontrará uma solução pronta aqui. Descreverei apenas a abordagem que planejamos aplicar e, com um pouco mais de detalhes - uma das etapas de uma solução longa.

Claro, tínhamos algo em reserva. Havia um servidor interno do ArcGIS que eu já mencionei. Não tínhamos permissão para orientá-los, mas estávamos autorizados a usar seus serviços REST.

A primeira coisa que fizemos foi estragar a tarefa. Ele não nos proibiu, mas às vezes simplesmente desligava para manutenção. E o que é legal - ele tinha o modo de lote de geocodificação, quando você envia um pacote de endereços para a entrada (depois de configurar o servidor, o tamanho do pacote era de 1000 peças, por padrão, parece que algo é uma ordem de magnitude ou dois a menos). Tudo isso também não foi fácil, e nós e o ArcGIS apoiamos a luta de sumô com o servidor por um longo tempo, mas isso é outra história.

Depois de todos os truques e reviravoltas, conseguimos processar nossos cinco milhões em cerca de um dia. Era necessário seguir em frente e tentar acelerar ainda.

Ao mesmo tempo, ficou claro que qualquer geocoder com REST provavelmente não é adequado para nós. Além disso, vimos Nominatim, Pelias, Photon e gisgrafia, e em geral não ficamos satisfeitos com nada. Qualidade e desempenho (ou ambos) estavam longe do ideal.

Por exemplo, ninguém sabe como codificar geograficamente os pacotes (e esse trabalho muito acelerado com o ArcGIS).

Ou qualidade - acesse o servidor de demonstração gisgraphy.com e tente encontrar Moscou. Você receberá algumas dezenas de respostas, incluindo: Moscou (cidade da Federação Russa), Kansas City (cidade dos EUA), Khimki, Kaluga, Vykhino-Zhulebino e muitos outros objetos que eu não gostaria de ver na resposta do geocoder quando procure por Moscou.

Bem, o último problema (mas não importante para nós) é que, longe de todos os geocodificadores, a API é tão bem pensada quanto, por exemplo, o Google Maps. Digamos que a API do ArcGIS já seja muito mais inconveniente, e o resto é na maior parte ainda pior. Se você codificar geograficamente os endereços para a interface do usuário, normalmente a pessoa estará envolvida na escolha da melhor opção. E ele faz isso melhor do que o programa. E no caso da geocodificação em massa, como temos, avaliar a qualidade do resultado para um endereço específico é um dos componentes importantes do sucesso.

Como resultado, opções como “Expanda seu próprio Nominatim”, por exemplo, também desapareceram.

O que fazer?

Uma solução bastante óbvia foi a seguinte: como os endereços não são retirados de nenhum lugar e não desaparecem em nenhum lugar, casas não são construídas todos os dias e ruas não são construídas, você só precisa adicionar um banco de dados de endereços oficialmente existentes ao nosso processo. Melhor imediatamente com as coordenadas e, se isso não acontecer, geocodifique-o uma vez. Nesse caso, será suficiente atualizarmos nossa base com a mesma frequência que novas casas ou ruas aparecem, ou seja, não com frequência.

O primeiro e principal candidato para a base de endereços existentes é o FIAS . Espere um minuto, você diz, mas o FIAS tem apenas alguns milhões de endereços - e você tem até 50 milhões? Sim, existem realmente apenas alguns milhões de casas . E nossos 50 são 50 milhões de endereços de nossos usuários, ou seja, esses são os endereços das pessoas e, de repente, eles têm um apartamento no endereço. Cinco milhões de casas de 1 a 100 apartamentos, várias pessoas moram em cada apartamento ... bem, você entende tudo. E a segunda opção são os endereços dos escritórios, onde também um centro de escritórios tem até centenas de instalações, que às vezes são alugadas.

Ao mesmo tempo, obviamente não precisamos de um endereço com o número do apartamento (ou escritório) - em primeiro lugar, trata-se de dados pessoais com todas as consequências e, em segundo lugar, ainda não estamos interessados ​​em saber como os apartamentos estão localizados em uma casa específica e quais são suas coordenadas. . Apenas uma casa é necessária. Para escritórios, isso não é totalmente verdade, mas a localização dos escritórios em um prédio por pisos ainda não é determinada por coordenadas.

Por fim, com uma base de, digamos, 5 milhões (condicionalmente) de casas existentes, podemos resolver o problema de geocodificação de 50 ou 100 milhões de endereços simplesmente jogando o apartamento ou escritório fora do endereço e combinando-o com a base.

E onde conseguir as coordenadas das casas? Existe apenas um código aberto óbvio - o OpenStreetMap, existem casas com geometrias e vários outros atributos, como número de andares ou mesmo a cor do telhado.

Depois de todas as discussões, tínhamos um plano napoleônico. Aqui está um:

• carregando dados do mapa do OSM no Hadoop
• carregar dados FIAS com endereços
• crie uma lista de endereços completos exclusivos com números de casa
• nós o codificamos geograficamente procurando endereços no OSM, e o que não encontramos é através do ArcGIS

Chegamos à base de casas com latitude e longitude. Aprecie. Colhendo os benefícios. Beba bônus (piada).

Neste artigo, mostrarei como implementamos o primeiro ponto deste plano.

O que é o OpenStreetMap

Se você observar o OSM do ponto de vista dos dados, os cartões poderão ser imaginados na forma de três tabelas:

• pontos
• linhas (caminhos)
• relações

Esquemas reais desses dados serão apresentados abaixo.

Somente pontos têm coordenadas (latitude e longitude, em graus). Linhas são uma sequência ordenada de pontos. Relações são um conjunto de pontos e linhas, cada um dos quais tem um papel .

Tudo o resto são as chamadas tags. Ou seja, por exemplo, um caixa eletrônico, uma loja ou uma entrada para o metrô - pode ser um ponto equipado com a tag amenity = atm, ou shop = vende alguma coisa ou outra coisa. Há um diretório de tags oficialmente recomendadas (para cada idioma e país aplicáveis, elas podem ser parcialmente próprias) e a prática de inventar tags não padronizadas.

Além das tags, cada elemento do mapa possui um ID numérico exclusivo, além de alguns atributos relacionados ao histórico - quem editou quando, editou o número etc.

O banco de dados com o cartão vem em vários formatos:
- pbf é o Google Protobuf, um formato portátil de serialização de dados.
- xml é obviamente XML. Muito mais em volume.

Você precisa entender que o banco de dados é atualizado diariamente. Portanto, as descargas são completas e incrementais.

Escolhemos o PBF como mais compacto.

Para lê-lo no Hadoop, existe uma API Java criada especificamente para o OSM, denominada projeto de osmose. Em princípio, trabalhar com ele é simples - você carrega um arquivo e percorre os elementos do mapa. Adicione os pontos em um lugar, as linhas em outro, o relacionamento no terceiro. Em princípio, a osmose e, por exemplo, o Spark já são suficientes para baixar todos os dados.

Felizmente, no processo de implementação da minha bicicleta, ocorreu-me, de alguma forma, pesquisar na Internet a conversão do OSM para os formatos aceitos no Hadoop - Parquet (parquet) e Avro. Em certo sentido, ambos são análogos do PBF, então houve uma chance de encontrar um conversor. E ele foi encontrado, mas não um.

Conheça OSM Parquetizer

Veja o que eu encontrei!

Para pessoas preguiçosas - bem no leia-me do projeto, na primeira linha, diz: Telenav publica downloads semanais do planeta no endereço .

Para pessoas muito preguiçosas: prepare-se para transportar cerca de 700 gigabytes;) Bem, se você certamente precisa de um planeta. Geralmente, você pode conviver com, digamos, a Europa.

Se você não deseja carregar, o processo é assim: faça o download de um mapa no formato PBF, por exemplo, de uma geofábrica . São 2,5 gigabytes se você precisar da Rússia e 19 se a Europa. Também não é um pouco, mas você pode encontrar amostras mais finamente picadas. Em seguida, coloque o arquivo no disco e execute o programa:

java -jar ./osm-parquetizer.jar russia-latest.osm.pbf 

Após alguns minutos ou até segundos, dependendo do desempenho da sua máquina, você obtém três arquivos no formato parquet. Aqui está a aparência do autor (ele é da Romênia):

 -rw-r--r-- 1 adrianbona adrianbona 145M Apr 3 19:57 romania-latest.osm.pbf -rw-r--r-- 1 adrianbona adrianbona 372M Apr 3 19:58 romania-latest.osm.pbf.node.parquet -rw-r--r-- 1 adrianbona adrianbona 1.1M Apr 3 19:58 romania-latest.osm.pbf.relation.parquet -rw-r--r-- 1 adrianbona adrianbona 123M Apr 3 19:58 romania-latest.osm.pbf.way.parquet 

Esquemas dos arquivos .parquet recebidos:

node
|-- id: long
|-- version: integer
|-- timestamp: long
|-- changeset: long
|-- uid: integer
|-- user_sid: string
|-- tags: array
| |-- element: struct
| | |-- key: string
| | |-- value: string
|-- latitude: double
|-- longitude: double

way
|-- id: long
|-- version: integer
|-- timestamp: long
|-- changeset: long
|-- uid: integer
|-- user_sid: string
|-- tags: array
| |-- element: struct
| | |-- key: string
| | |-- value: string
|-- nodes: array
| |-- element: struct
| | |-- index: integer
| | |-- nodeId: long

relation
|-- id: long
|-- version: integer
|-- timestamp: long
|-- changeset: long
|-- uid: integer
|-- user_sid: string
|-- tags: array
| |-- element: struct
| | |-- key: string
| | |-- value: string
|-- members: array
| |-- element: struct
| | |-- id: long
| | |-- role: string
| | |-- type: string


Como você pode ver, tudo é simples aqui. Em seguida, fazemos o seguinte:

• colocamos arquivos no cluster Hadoop com o comando hdfs dfs -put
• digamos no Hue e crie um esquema / base e três tabelas para ele, com base nos dados acima
• execute select * from osm.nodes e aproveite o resultado.

Uma pequena nuance: em nossa versão do Hive (e possivelmente na sua também), não é possível criar tabelas com base no esquema do Parquet. Você precisa converter o item acima em CREATE TABLE (que, em geral, não é difícil, e deixarei isso como um exercício doméstico para os leitores), ou fazer um pouco mais complicado: o Spark pode ler o diagrama e os dados do chão e criar tabelas temporárias com base neles. . Então, podemos ler os dados no Spark Shell assim:

 val nodeDF = sqlContext.read.parquet("file:/tmp/osm/romania-latest.osm.pbf.node.parquet") nodeDF.createOrReplaceTempView("nodes") 

Então você já pode criar tabelas no Hive usando nós LIKE.

Outra observação para pessoas preguiçosas: o autor tem um exemplo tão maravilhoso, do qual, em geral, tudo fica claro (bem, se você possui o Spark). Obviamente, não é o Spark Shell, mas o Databricks Notebook, mas levei cerca de 15 minutos para tocar em um teclado para traduzir um no outro. E em 30 a 40 minutos foi possível converter tudo isso em consultas para o Hive usando alguns análogos ligeiramente diferentes do spark.

Exemplo de solicitação real

O que podemos obter desse banco de dados em sua forma atual? Em geral, bastante. Se você tiver Hive ou Spark, Spatial Framework, Geometry API ou uma das alternativas, que são GeoSpark ou GeoMesa, por exemplo, poderá resolver muitos problemas diferentes com base nisso.

Vamos ver um exemplo. A maneira mais fácil de trabalhar com pontos. Por exemplo, uma consulta para obter uma lista de caixas eletrônicos com suas coordenadas é assim:

 select * from nodes where tags['amenity']='atm' 

Como criar essa consulta, você pode adivinhar lendo a página no wiki . Lá você encontrará o que outras tags são e algumas delas podem ser incluídas na sua solicitação em vez de *, na forma de tags ['operator'], por exemplo, para mostrar o nome do banco.

Na mesma página, segue-se que a marcação ATM é possível na forma de tags amenity = bank e atm = yes. Infelizmente, essas ambigüidades estão por toda parte no OSM.

Se você é iniciante e se familiariza com o OSM, recomendo dominar (através de bons exemplos no wiki) o overpass-turbo . Essa é uma ferramenta que permite realizar vários tipos de pesquisas nos dados do mapa, tanto com condições geométricas quanto com condições para tags.

E onde estão os endereços?

Boa pergunta Os endereços no OSM são elementos do mapa fornecidos com tags addr: *, ou seja, começando com addr. Descrição que você encontrará aqui . Em princípio, sabendo tudo o que afirmei acima, você já pode escrever uma solicitação de trabalho:

 select * from nodes where tags['addr:housenumber']!=null 

Que problemas nos esperam aqui? Em primeiro lugar, os endereços são colocados tanto em pontos (por exemplo, entradas de edifícios) quanto em polígonos, ou seja, em caminhos. Portanto, temos que duplicar a solicitação pelo menos. Em segundo lugar, na página mencionada acima, o wiki é escrito em texto simples, não sendo recomendado colocar tags indicando a cidade, região, região e país, mas isso deve ser calculado geometricamente. Como fazer isso? Em geral, essa é praticamente a tarefa de geocodificação reversa, com modificações leves, e foi descrita em um post anterior.

Ou seja, em geral, você precisa encontrar limites administrativos e, para todos os endereços que estão dentro deles, adicionar o endereço à área e tudo mais. Como os limites das entidades administrativas são organizados é descrito aqui .

Em geral, essa tarefa não é muito simples, mas bastante solucionável, e não é resolvida pela geocodificação, mas pelo download de atualizações do OSM em nosso banco de dados, em um ambiente descontraído.

O que é útil fazer a seguir

Em princípio, você já pode trabalhar com os nós, formas e tabelas de relações que possuímos, mas é melhor alterar um pouco o esquema, tornando-o mais adequado para o Hive e o Spark. O fato é que o esquema OSM é completamente normalizado, formas e relações não contêm coordenadas. Para criar um polígono para o caminho, você precisa se juntar aos nós. Eu recomendaria fazer essa operação imediatamente, salvando os polígonos como uma matriz de estruturas (o Hive pode trabalhar com tipos compostos matriz, mapa e estrutura) ou imediatamente como uma representação serializada da classe Geometry, por exemplo. Como fazer isso está no exemplo do parquetizador do autor.

Você pode repetir uma operação semelhante no nível das relações, se quiser, mas dificilmente vale a pena. Primeiro, você nem sempre precisará de todos os elementos de um relacionamento e, segundo, as próprias relações no OSM são muito menores.

Conversor para Avro

Aqui está outro conversor, desta vez para o formato Avro. E aqui está descrito onde obter os arquivos finalizados. Não medi os tamanhos, mas acho que aproximadamente 15 a 20 arquivos por planeta devem ser comparáveis ​​ao PBF. Ou seja, são gigabytes e muito.

Algumas conclusões

E onde está o geocoding, você pergunta? Sim, fazer o download de mapas e extrair endereços é apenas parte da tarefa geral. Espero que chegue a isso.