Comment charger OpenStreetMap dans Hive?

Dans un article précédent, j'ai examiné le géocodage inversé à l'aide de Spark. Imaginez maintenant que nous avons été confrontés au défi du géocodage direct des adresses e-mail. Autrement dit, recevoir pour l'adresse enregistrée par le texte de certaines coordonnées géographiques.

Les adresses pour la précision sont russes, et plus important encore - elles sont souvent écrites de manière tordue, c'est-à-dire avec des erreurs, des ambiguïtés et d'autres délices. Et ces adresses se trouvent dans la base de données Hive, sur le cluster Hadoop.


Eh bien, il semblerait - nous prenons l'API Google Maps Geocoding (ou, si vous êtes partisan de la substitution des importations, alors l'API Yandex Maps), et nous travaillons. Mais ici, comme pour le géocodage inversé, nous attendons une petite embuscade.

Ou gros, c'est comme un regard. Le fait est que cette fois, nous devons traiter environ 5 millions d'adresses. Et peut-être 50 - ce n'était pas immédiatement clair. Comme vous le savez, Google bannira votre IP après environ 10 000 adresses, Yandex fera de même avec vous, bien que ce soit possible un peu plus tard (25 000 demandes par jour, par exemple). Et d'ailleurs, les deux API sont REST, ce qui signifie qu'il est relativement lent. Et même si vous achetez un abonnement payant, sa vitesse n'augmentera pas d'un sou.

Et pourtant - nous avons manqué d'anecdote de munitions.

J'ai oublié la chose la plus importante - notre cluster Hadoop est situé sur l'intranet, et Google Maps, pour l'entreprise avec Yandex Maps et tout le monde, nous sont généralement inaccessibles depuis le cluster. Autrement dit, nous avions besoin d'une solution autonome.

Je dirai tout de suite - vous ne trouverez pas de solution toute faite ici. Je ne décrirai que l'approche que nous prévoyons d'appliquer, et de manière un peu plus détaillée - l'une des étapes d'un long chemin vers une solution.

Bien sûr, nous avions quelque chose en réserve. Il y avait un serveur ArcGIS interne que j'ai déjà mentionné. Nous n'étions pas autorisés à les diriger, mais nous étions autorisés à utiliser ses services REST.

La première chose que nous avons faite a été de le visser à la tâche. Il ne nous a pas bannis, mais a simplement parfois désactivé pour l'entretien. Et ce qui est bien - il y avait un mode de géocodage par lots, lorsque vous soumettez un paquet d'adresses à l'entrée (après avoir configuré le serveur, la taille du paquet était de 1000 pièces, par défaut, il semble que quelque chose soit un ordre de grandeur ou deux de moins). Tout cela n'a pas été facile non plus, et nous, et le support d'ArcGIS, avons été engagés dans une lutte de sumo avec le serveur pendant longtemps, mais c'est une autre histoire.

Après toutes les astuces et rebondissements, nous avons pu traiter nos cinq millions en une journée environ. Il fallait avancer et essayer encore d'accélérer.

Dans le même temps, il est devenu clair que tout géocodeur avec REST ne nous convient probablement pas. De plus, nous avons examiné Nominatim, Pelias, Photon et gisgraphy, et en général nous n'en étions pas satisfaits. La qualité et les performances (ou les deux) étaient loin d'être idéales.

Par exemple, personne ne sait géocoder les packages (et cela accélère considérablement le travail avec ArcGIS).

Ou qualité - rendez-vous sur le serveur de démonstration gisgraphy.com et essayez de trouver Moscou. Vous recevrez une douzaine de réponses, notamment: Moscou (une ville de la Fédération de Russie), Kansas City (une ville des États-Unis), Khimki, Kaluga, Vykhino-Zhulebino et bien d'autres objets que je ne voudrais pas voir dans la réponse du géocodeur quand recherchez Moscou.

Eh bien, le dernier problème (mais pas important pour nous) est que loin de tous les géocodeurs, l'API est aussi bien pensée que, disons, Google Maps. Disons que l'API ArcGIS est déjà beaucoup plus gênant, et le reste est pour la plupart encore pire. Si vous géocodez des adresses pour l'interface utilisateur, en règle générale, une personne s'engage à choisir la meilleure option. Et il le fait mieux que le programme. Et dans le cas du géocodage de masse, comme nous l'avons fait, l'évaluation de la qualité du résultat pour une adresse particulière est l'un des éléments importants du succès.

Par conséquent, des options comme «Développez votre propre Nominatim», par exemple, ont également disparu.

Que faire?

Une solution assez évidente était la suivante: étant donné que les adresses ne proviennent de nulle part et ne disparaissent nulle part, les maisons ne sont pas construites tous les jours et les rues ne sont pas construites, il vous suffit d'ajouter une base de données d'adresses officiellement existantes à notre processus. Mieux immédiatement avec les coordonnées, et si cela ne se produit pas, géocodez-le une fois. Dans ce cas, il nous suffira de mettre à jour notre base avec la même fréquence d'apparition de nouvelles maisons ou rues, c'est-à-dire pas souvent.

Le premier et principal candidat pour la base d'adresses existantes est FIAS . Attendez une minute, dites-vous, mais la FIAS n'a que quelques millions d'adresses - et vous en avez jusqu'à 50 millions? Oui, il n'y a vraiment que quelques millions de foyers . Et nos 50, c'est 50 millions d'adresses de nos utilisateurs, c'est-à-dire, ce sont les adresses de personnes, et ils ont soudainement un appartement à l'adresse. Cinq millions de maisons de 1 à 100 appartements, plusieurs personnes vivent dans chaque appartement ... enfin, vous comprenez tout. Et la deuxième option est l'adresse des bureaux, où un centre de bureaux dispose également de centaines de locaux, parfois loués.

Dans le même temps, nous n'avons évidemment pas besoin d'une adresse avec le numéro de l'appartement (ou du bureau) - d'une part, ce sont des données personnelles avec toutes les conséquences, et d'autre part, nous ne sommes toujours pas intéressés par la façon dont les appartements sont situés dans une maison particulière, et quelles sont leurs coordonnées . Seule une maison est nécessaire. Pour les bureaux, ce n'est pas tout à fait vrai, mais l'emplacement des bureaux dans un immeuble par étages n'est toujours pas déterminé par les coordonnées.

En fin de compte, ayant une base de, disons, 5 millions de maisons (conditionnellement) existantes, nous pouvons résoudre le problème de géocodage de 50 ou 100 millions d'adresses en jetant simplement l'appartement ou le bureau hors de l'adresse et en l'associant à la base.

Et où trouver les coordonnées des maisons? Il n'y a qu'une seule source ouverte évidente - OpenStreetMap, il y a des maisons là-bas, avec des géométries, et toutes sortes d'autres attributs comme le nombre d'étages ou même la couleur du toit.

Après toutes les discussions, nous avions un plan napoléonien. En voici un:

• chargement des données cartographiques d'OSM dans Hadoop
• télécharger des données FIAS avec des adresses
• construire une liste d'adresses complètes uniques avec des numéros de maison
• nous le géocodons en recherchant des adresses dans OSM, et ce que nous n'avons pas trouvé est via ArcGIS

Nous obtenons la base de maisons avec latitude et longitude. Profitez-en. Profiter des avantages. Boissons bonus (blague).

Dans cet article, je vais vous expliquer comment nous avons mis en œuvre le premier point de ce plan.

Qu'est-ce qu'OpenStreetMap

Si vous regardez OSM du point de vue des données, vous pouvez imaginer les cartes sous la forme de trois tableaux:

• des points
• lignes (voies)
• les relations

Les schémas réels de ces données seront donnés ci-dessous.

Seuls les points ont des coordonnées (latitude et longitude, en degrés). Les lignes sont une séquence ordonnée de points. Les relations sont un ensemble de points et de lignes, chacun ayant un rôle .

Tout le reste, ce sont les soi-disant balises. C'est, par exemple, un guichet automatique, ou un magasin, ou une entrée du métro - il peut s'agir d'un point équipé de la balise amenity = atm, ou shop = vend quelque chose, ou autre chose. Il existe un répertoire de balises officiellement recommandées (pour chaque langue et pays applicable, elles peuvent être partiellement les leurs), et la pratique d'inventer des balises non standard.

En plus des balises, chaque élément de la carte a un identifiant numérique unique, ainsi que certains attributs liés à l'historique - qui a modifié quand, modifier le numéro, etc.

La base de données avec la carte se présente sous plusieurs formats:
- pbf est Google Protobuf, un format de sérialisation de données portable.
- xml est évidemment XML. Beaucoup plus en volume.

Vous devez comprendre que la base de données est mise à jour quotidiennement. Par conséquent, les déchargements sont complets et incrémentiels.

Nous avons choisi le PBF comme plus compact.

Pour le lire dans Hadoop, il existe une API Java spécialement conçue pour OSM appelée ce projet d'osmose. En principe, travailler avec est simple: vous téléchargez un fichier et parcourez les éléments de la carte. Ajoutez les points à un endroit, les lignes à un autre, la relation au troisième. En principe, l'osmose et par exemple Spark suffisent déjà pour télécharger toutes les données.

Heureusement, dans le processus de mise en œuvre de mon vélo, il m'est apparu en quelque sorte de rechercher sur Internet pour convertir OSM dans les formats acceptés dans Hadoop - Parquet (parquet) et Avro. Dans un sens, les deux sont des analogues du PBF, il y avait donc une chance de trouver un convertisseur. Et il a été retrouvé, mais pas un.

Rencontrez OSM Parquetizer

Voyez ce que j'ai trouvé!

Pour les paresseux - en plein dans le readme du projet dans la première ligne, il est dit: Telenav publie des téléchargements hebdomadaires de la planète à l'adresse .

Pour les gens très paresseux: préparez-vous à expédier environ 700 gigaoctets;) Eh bien, si vous avez certainement besoin d'une planète. Vous pouvez généralement vous débrouiller avec, disons, l'Europe.

Si vous ne voulez pas charger, le processus ressemble à ceci: téléchargez une carte au format PBF, par exemple à partir d'une géofactory . C'est 2,5 gigaoctets si vous avez besoin de la Russie et 19 si l'Europe. Pas un peu non plus, mais vous pouvez trouver des échantillons plus finement hachés. Ensuite, placez le fichier sur le disque et exécutez le programme:

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

Après quelques minutes voire quelques secondes, selon les performances de votre machine, vous obtenez trois fichiers au format parquet. Voici à quoi ressemble l'auteur (il est de Roumanie):

 -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 

Schémas des fichiers .parquet reçus:

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


Comme vous pouvez le voir, tout est simple ici. Ensuite, nous faisons ce qui suit:

• nous mettons des fichiers sur le cluster Hadoop avec la commande hdfs dfs -put
• allons dire dans Hue et créons un schéma / base, et trois tables pour cela, sur la base des données ci-dessus
• exécutez select * dans osm.nodes et profitez du résultat.

Une petite nuance: dans notre version de Hive (et peut-être aussi la vôtre), il n'est pas en mesure de créer des tables basées sur le schéma de Parquet. Vous devez soit convertir ce qui précède en CREATE TABLE (ce qui, en général, n'est pas difficile, et je laisserai cela comme un exercice à domicile pour les lecteurs), soit faire un peu plus compliqué: Spark peut lire le diagramme et les données à partir du sol, et créer des tables temporaires sur leur base . Nous pouvons donc lire les données dans Spark Shell comme ceci:

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

Ensuite, vous pouvez déjà créer des tables dans Hive en utilisant des nœuds LIKE.

Une autre remarque pour les paresseux: l'auteur a un si bel exemple , à partir duquel en général tout devient clair (enfin, si vous possédez Spark). Ce n'est bien sûr pas Spark Shell, mais le Databricks Notebook, mais il m'a fallu environ 15 minutes pour appuyer sur un clavier pour le traduire en un autre. Et en 30 à 40 minutes, il a été possible de tout convertir en requêtes pour Hive en utilisant des analogues légèrement différents de l'étincelle.

Exemple de demande réelle

Que pouvons-nous obtenir de cette base de données dans sa forme actuelle? En général, beaucoup. Si vous avez une ruche ou une étincelle, un cadre spatial, une API de géométrie ou l'une des alternatives, qui sont GeoSpark ou par exemple GeoMesa, vous pouvez résoudre de nombreux problèmes différents sur cette base.

Voyons un exemple. La façon la plus simple de travailler avec des points. Par exemple, une requête pour obtenir une liste de distributeurs automatiques de billets avec leurs coordonnées ressemble à ceci:

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

Comment construire une telle requête, vous pouvez le deviner en lisant la page sur le wiki . Vous y trouverez ce que sont d'autres balises, et certaines d'entre elles peuvent être incluses dans votre demande au lieu de *, sous la forme de balises ['opérateur'], par exemple, pour afficher le nom de la banque.

De la même page, il s'ensuit que le balisage ATM est possible sous la forme de balises amenity = bank et atm = yes. Hélas, de telles ambiguïtés sont omniprésentes dans OSM.

Si vous êtes un débutant et que vous vous familiarisez avec OSM, je recommande fortement de maîtriser (par de bons exemples sur le wiki) overpass-turbo . Il s'agit d'un outil qui vous permet d'effectuer différents types de recherches sur les données cartographiques, à la fois avec des conditions géométriques et avec des conditions pour les balises.

Et où sont les adresses?

Bonne question. Les adresses dans OSM sont des éléments de carte fournis avec des balises addr: *, c'est-à-dire commençant par addr. Description que vous trouverez ici . En principe, sachant tout ce que j'ai dit ci-dessus, vous pouvez déjà rédiger une demande de travail:

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

Quels problèmes nous attendent ici? Premièrement, les adresses sont placées à la fois sur des points (par exemple, des entrées de bâtiments) et sur des polygones, c'est-à-dire sur les voies. Nous devons donc au moins dupliquer la demande. Et deuxièmement, sur la page mentionnée ci-dessus, le wiki est écrit en texte clair qu'il n'est pas recommandé de mettre des balises indiquant la ville, la région, la région et le pays, mais cela doit être calculé géométriquement. Comment faire En général, c'est pratiquement la tâche du géocodage inversé, avec de légères modifications, et cela a été décrit dans un post précédent.

C'est-à-dire, en général, vous devez trouver des limites administratives, et pour toutes les adresses qui s'y trouvent, ajoutez l'adresse à la zone et à tout ce qui précède. La façon dont les limites des entités administratives sont organisées est décrite ici .

En général, cette tâche n'est pas trop simple, mais tout à fait résoluble, et elle n'est pas résolue par le géocodage, mais en téléchargeant les mises à jour OSM dans notre base de données, dans une atmosphère détendue.

Que faut-il faire ensuite

En principe, vous pouvez déjà travailler avec les nœuds, les chemins et les tables de relations que nous avons, mais il vaut mieux changer un peu le schéma, le rendant plus adapté à Hive et Spark. Le fait est que le schéma OSM est complètement normalisé, les voies et les relations ne contiennent pas du tout de coordonnées. Pour construire un polygone pour le chemin, vous devez vous joindre à des nœuds. Je recommanderais de faire cette opération tout de suite, en enregistrant les polygones soit comme un tableau de structures (Hive peut fonctionner avec un tableau de types composites, une carte et une structure), soit immédiatement comme une représentation sérialisée de la classe Geometry, par exemple. Comment faire cela est dans l'exemple du parqueteur auteur.

Vous pouvez répéter une opération similaire au niveau des relations, si vous le souhaitez, mais cela ne vaut pas la peine. Premièrement, vous n'aurez pas toujours besoin de tous les éléments d'une relation, et deuxièmement, les relations elles-mêmes dans OSM sont beaucoup plus petites.

Convertisseur en Avro

Voici un autre convertisseur, cette fois au format Avro. Et ici, il est décrit où obtenir les fichiers finis. Je n'ai pas mesuré les tailles, mais je pense qu'environ 15-20 fichiers par planète devraient être comparables à PBF. Autrement dit, ce sont des gigaoctets, et beaucoup.

Quelques conclusions

Et où est le géocodage, demandez-vous? Oui, le téléchargement de cartes et l'extraction d'adresses ne sont qu'une partie de la tâche globale. J'espère qu'il en sera ainsi.