How to load OpenStreetMap in Hive?
In a previous article, I examined reverse geocoding using Spark. Now imagine that we faced the challenge of direct geocoding of mail addresses. That is, receiving for the address recorded by the text of some geographical coordinates.
The addresses for definiteness are Russian, and most importantly - they are often written crookedly, that is, with errors, ambiguities and other delights. And these addresses are in the Hive database, on the Hadoop cluster.
Well, it would seem - we take the Google Maps Geocoding API (or, if you are a supporter of import substitution, then the Yandex Maps API), and we work. But here we, as well as with reverse geocoding, are waiting for a small ambush.
Or big, it's like a look. The fact is that this time we need to process about 5 million addresses. And maybe 50 - it was not immediately clear. As you know, Google will ban your IP after about 10 thousand addresses, Yandex will do the same with you, although it is possible a little later (25 thousand requests a day, like). And besides, both APIs are REST, which means it is relatively slow. And even if you buy a paid subscription, the speed from this will not increase by a penny.
And yet - we ran out of ammo (s) anecdote.
I forgot the most important thing - our Hadoop cluster is located on the intranet, and Google Maps, for the company with Yandex Maps, and everyone else, are generally inaccessible to us from the cluster. That is, we needed an autonomous solution.
I will say right away - you will not find a ready-made solution here. I will only describe the approach that we plan to apply, and in a little more detail - one of the steps on a long way to a solution.
Of course, we had something in reserve. There was an internal ArcGIS server that I already mentioned. We were not allowed to steer them, but were allowed to use its REST services.
The first thing we did was screw it to the task. He did not ban us, but simply sometimes turned off for maintenance. And what’s nice - it had batch mode of geocoding, when you submit a packet of addresses to the input (after setting up the server, the packet size was 1000 pieces, by default it seems something is an order of magnitude or two smaller). All this was not easy either, and we, and ArcGIS support, were engaged in sumo wrestling with the server for a long time, but this is a different story.
After all the tricks and twists and turns, we were able to process our five million in about a day. It was necessary to move on, and try to accelerate yet.
At the same time, it became clear that any geocoder with REST is most likely not suitable for us. In addition, we looked at Nominatim, and at Pelias, and at Photon, and at gisgraphy, and in general were not satisfied with any. Quality and performance (or both) was far from ideal.
For example, no one knows how to geocode packages (and this greatly accelerated work with ArcGIS).
Or quality - go to the gisgraphy.com demo server and try to find Moscow. You will receive a couple of dozen answers, including: Moscow (a city in the Russian Federation), Kansas City (a city in the USA), Khimki, Kaluga, Vykhino-Zhulebino, and many other objects that I would not want to see in the geocoder’s answer when search for Moscow.
Well, the last (but not important for us) problem is that far from all geocoders the API is as well thought out as, say, Google Maps. Let's say the ArcGIS API is already much more inconvenient, and the rest is for the most part even worse. If you geocode addresses for the UI, then as a rule a person is engaged in choosing the best option. And he does it better than the program. And in the case of mass geocoding, as we have, assessing the quality of the result for a particular address is one of the important components of success.
As a result, options like “Expand your own Nominatim”, for example, have disappeared too.
A rather obvious solution was this: since the addresses are not taken from anywhere and do not disappear, the houses are not built every day, and the streets are not built, you just need to add a database of officially existing addresses to our process. Better immediately with the coordinates, and if this does not turn out - then geocode it once. In this case, it will be enough for us to update our base with the same frequency as new houses or streets appear, that is, not often.
The first and main candidate for the base of existing addresses is FIAS . Wait a minute, you say, but FIAS has just a few million addresses — and you have as many as 50 million? Yes, there really are only a few million homes. And our 50 is 50 million addresses of our users, that is, these are the addresses of people, and they suddenly have an apartment in the address. Five million houses of 1-100 apartments, several people live in each apartment ... well, you understand everything. And the second option is the addresses of offices, where also one office center has up to hundreds of premises, which are sometimes rented out.
In this case, we obviously do not need an address with the number of the apartment (or office) - firstly, this is personal data with all the consequences, and secondly, we still are not interested in how the apartments are located in a particular house, and what their coordinates are . Only a house is needed. For offices, this is not entirely true, but the location of offices in a building by floors is still not determined by coordinates.
Ultimately, having a base of, say, 5 million (conditionally) existing houses, we can solve the geocoding problem of 50 or 100 million addresses by simply throwing the apartment or office out of the address and matching it with the base.
And where to get the coordinates of the houses? There is only one obvious open source - OpenStreetMap, there are houses there, with geometries, and various other attributes such as number of storeys or even the color of the roof.
After all the discussions, we had a Napoleonic plan. Here is one:
We get the base of houses with latitude and longitude. Enjoy. Reaping the benefits. Drink bonuses (joke).
In this article I will tell you how we implemented the first point of this plan.
If you look at OSM from a data point of view, then the cards can be imagined in the form of three tables:
Real schemes of this data will be given below.
Only points have coordinates (latitude and longitude, in degrees). Lines are an ordered sequence of points. Relations are a set of points and lines, each of which has a role .
Everything else is the so-called tags. That is, for example, an ATM, or a store, or an entrance to the metro - it can be a point equipped with the tag amenity = atm, or shop = sells something, or something else. There is a directory of officially recommended tags (for each applicable language and country they can be partially their own), and the practice of inventing non-standard ones.
In addition to tags, each element of the map has a unique numeric id, as well as some attributes related to the history - who edited when, edit number, etc.
The database with the card comes in several formats:
- pbf is Google Protobuf, a portable data serialization format.
- xml is obviously XML. A lot more in volume.
You need to understand that the database is updated daily. Therefore, unloadings are complete and incremental.
We chose PBF as more compact.
To read it in Hadoop, there is a Java API specifically made for OSM called this osmosis project. In principle, working with it is simple - you upload a file and cycle through the map elements. Add the points in one place, the lines in another, the relations in the third. In principle, osmosis and for example Spark are already enough to download all the data.
Fortunately, in the process of implementing my bike, it somehow occurred to me to search the Internet for converting OSM into the formats accepted in Hadoop - Parquet (parquet) and Avro. In a sense, both are analogues of PBF, so there was a chance to find a converter. And he was found, but not one.
Look what I found!
For lazy people - right in the readme of the project in the first line it says: Telenav publishes weekly downloads of the planet at .
For very lazy people: get ready to ship about 700 gigabytes;) Well, if you certainly need a planet. You can usually get by with, say, Europe.
If you don’t want to load, then the process looks like this: download a map in PBF format, for example from a geofactory . This is 2.5 gigabytes if you need Russia, and 19 if Europe. Also not a little, but you can find more finely chopped samples. Next, put the file on the disk, and run the program:
After a few minutes or even seconds, depending on the performance of your machine, you will receive three files in parquet format. Here is what the author looks like (he is from Romania):
Schemes of the received .parquet files: As you can see, everything is simple here. Then we do the following:
A small nuance: in our version of Hive (and possibly yours as well), it is not able to create tables based on the scheme from Parquet. You need to either convert the above to CREATE TABLE (which, in general, is not difficult, and I will leave this as a home exercise for readers), or to do a little trickier: Spark can read the diagram and data from the floor, and create temporary tables on their basis . So we can read the data in Spark Shell like this:
Then you can already create tables in Hive using LIKE nodes.
Another remark for lazy people: the author has such a wonderful example , from which in general everything becomes clear (well, if you own Spark). This is of course not Spark Shell, but the Databricks Notebook, but it took me about 15 minutes to tap one keyboard to translate one into another. And in 30-40 minutes it was possible to convert it all into queries for Hive using some analogues that are slightly different from spark.
What can we get from this database in its current form? In general, quite a lot. If you have a Hive or Spark, Spatial Framework, Geometry API, or one of the alternatives, which are GeoSpark or for example GeoMesa, you can solve many different problems on this basis.
Let's see an example. The easiest way to work with points. For example, a query to get a list of ATMs with their coordinates looks like this:
How to build such a query, you can guess by reading the page on the wiki . There you will find what other tags are, and some of them can be included in your request instead of *, in the form of tags ['operator'], for example, to show the name of the bank.
From the same page it follows that ATM markup is possible in the form of tags amenity = bank and atm = yes. Alas, such ambiguities are everywhere in OSM.
If you are a beginner, and just get acquainted with OSM, I highly recommend mastering (by good examples on the wiki) overpass-turbo . This is a tool that allows you to perform various kinds of searches on map data, both with geometric conditions and with conditions for tags.
Good question. Addresses in OSM are map elements provided with addr: * tags, i.e. starting with addr. Description you will find here . In principle, knowing everything that I have stated above, you can already write some working request:
What problems await us here? Firstly, addresses are placed both on points (for example, building entrances), and on polygons, i.e. on ways. So we have to duplicate the request at least. And secondly, on the page mentioned above, the wiki is written in plain text that it is not recommended to put tags indicating the city, region, region and country, but this must be calculated geometrically. How to do it? In general, this is practically the task of reverse geocoding, with light modifications, and it was described in a previous post.
That is, in general, you need to find administrative boundaries, and for all addresses that are inside them, add the address to the area and everything above. How the boundaries of administrative entities are arranged is described here .
In general, this task is not too simple, but quite solvable, and it is not solved by geocoding, but by downloading OSM updates to our database, in a relaxed atmosphere.
In principle, you can already work with the nodes, ways and relations tables that we have, but it’s better to change the scheme a little, making it more suitable for Hive and Spark. The fact is that the OSM scheme is completely normalized, ways and relations do not contain coordinates at all. To build a polygon for the way, you need to join with nodes. I would recommend doing this operation right away, saving the polygons either as an array of structures (Hive can work with composite types array, map and struct), or immediately as a serialized representation of, say, the Geometry class. How to do this is in the example of the author parquetizer.
You can repeat a similar operation at the level of relations, if you want, but hardly worth it. Firstly, you will not always need all the elements of a relationship, and secondly, the relations themselves in OSM are much smaller.
Here is another converter, this time to the Avro format. And here it is described where to get the finished files. I did not measure the sizes, but I think that approximately 15-20 files per planet should be comparable to PBF. That is, these are gigabytes, and a lot.
And where is geocoding, you ask? Yes, downloading maps and extracting addresses is only part of the overall task. I hope it comes to this.
The addresses for definiteness are Russian, and most importantly - they are often written crookedly, that is, with errors, ambiguities and other delights. And these addresses are in the Hive database, on the Hadoop cluster.
Well, it would seem - we take the Google Maps Geocoding API (or, if you are a supporter of import substitution, then the Yandex Maps API), and we work. But here we, as well as with reverse geocoding, are waiting for a small ambush.
Or big, it's like a look. The fact is that this time we need to process about 5 million addresses. And maybe 50 - it was not immediately clear. As you know, Google will ban your IP after about 10 thousand addresses, Yandex will do the same with you, although it is possible a little later (25 thousand requests a day, like). And besides, both APIs are REST, which means it is relatively slow. And even if you buy a paid subscription, the speed from this will not increase by a penny.
And yet - we ran out of ammo (s) anecdote.
I forgot the most important thing - our Hadoop cluster is located on the intranet, and Google Maps, for the company with Yandex Maps, and everyone else, are generally inaccessible to us from the cluster. That is, we needed an autonomous solution.
I will say right away - you will not find a ready-made solution here. I will only describe the approach that we plan to apply, and in a little more detail - one of the steps on a long way to a solution.
Of course, we had something in reserve. There was an internal ArcGIS server that I already mentioned. We were not allowed to steer them, but were allowed to use its REST services.
The first thing we did was screw it to the task. He did not ban us, but simply sometimes turned off for maintenance. And what’s nice - it had batch mode of geocoding, when you submit a packet of addresses to the input (after setting up the server, the packet size was 1000 pieces, by default it seems something is an order of magnitude or two smaller). All this was not easy either, and we, and ArcGIS support, were engaged in sumo wrestling with the server for a long time, but this is a different story.
After all the tricks and twists and turns, we were able to process our five million in about a day. It was necessary to move on, and try to accelerate yet.
At the same time, it became clear that any geocoder with REST is most likely not suitable for us. In addition, we looked at Nominatim, and at Pelias, and at Photon, and at gisgraphy, and in general were not satisfied with any. Quality and performance (or both) was far from ideal.
For example, no one knows how to geocode packages (and this greatly accelerated work with ArcGIS).
Or quality - go to the gisgraphy.com demo server and try to find Moscow. You will receive a couple of dozen answers, including: Moscow (a city in the Russian Federation), Kansas City (a city in the USA), Khimki, Kaluga, Vykhino-Zhulebino, and many other objects that I would not want to see in the geocoder’s answer when search for Moscow.
Well, the last (but not important for us) problem is that far from all geocoders the API is as well thought out as, say, Google Maps. Let's say the ArcGIS API is already much more inconvenient, and the rest is for the most part even worse. If you geocode addresses for the UI, then as a rule a person is engaged in choosing the best option. And he does it better than the program. And in the case of mass geocoding, as we have, assessing the quality of the result for a particular address is one of the important components of success.
As a result, options like “Expand your own Nominatim”, for example, have disappeared too.
What to do?
A rather obvious solution was this: since the addresses are not taken from anywhere and do not disappear, the houses are not built every day, and the streets are not built, you just need to add a database of officially existing addresses to our process. Better immediately with the coordinates, and if this does not turn out - then geocode it once. In this case, it will be enough for us to update our base with the same frequency as new houses or streets appear, that is, not often.
The first and main candidate for the base of existing addresses is FIAS . Wait a minute, you say, but FIAS has just a few million addresses — and you have as many as 50 million? Yes, there really are only a few million homes. And our 50 is 50 million addresses of our users, that is, these are the addresses of people, and they suddenly have an apartment in the address. Five million houses of 1-100 apartments, several people live in each apartment ... well, you understand everything. And the second option is the addresses of offices, where also one office center has up to hundreds of premises, which are sometimes rented out.
In this case, we obviously do not need an address with the number of the apartment (or office) - firstly, this is personal data with all the consequences, and secondly, we still are not interested in how the apartments are located in a particular house, and what their coordinates are . Only a house is needed. For offices, this is not entirely true, but the location of offices in a building by floors is still not determined by coordinates.
Ultimately, having a base of, say, 5 million (conditionally) existing houses, we can solve the geocoding problem of 50 or 100 million addresses by simply throwing the apartment or office out of the address and matching it with the base.
And where to get the coordinates of the houses? There is only one obvious open source - OpenStreetMap, there are houses there, with geometries, and various other attributes such as number of storeys or even the color of the roof.
After all the discussions, we had a Napoleonic plan. Here is one:
- loading map data from OSM into Hadoop
- upload FIAS data with addresses
- build a list of unique complete addresses with house numbers
- we geocode it by searching for addresses in OSM, and what we didn’t find is through ArcGIS
We get the base of houses with latitude and longitude. Enjoy. Reaping the benefits. Drink bonuses (joke).
In this article I will tell you how we implemented the first point of this plan.
What is OpenStreetMap
If you look at OSM from a data point of view, then the cards can be imagined in the form of three tables:
- points
- lines (ways)
- relations
Real schemes of this data will be given below.
Only points have coordinates (latitude and longitude, in degrees). Lines are an ordered sequence of points. Relations are a set of points and lines, each of which has a role .
Everything else is the so-called tags. That is, for example, an ATM, or a store, or an entrance to the metro - it can be a point equipped with the tag amenity = atm, or shop = sells something, or something else. There is a directory of officially recommended tags (for each applicable language and country they can be partially their own), and the practice of inventing non-standard ones.
In addition to tags, each element of the map has a unique numeric id, as well as some attributes related to the history - who edited when, edit number, etc.
The database with the card comes in several formats:
- pbf is Google Protobuf, a portable data serialization format.
- xml is obviously XML. A lot more in volume.
You need to understand that the database is updated daily. Therefore, unloadings are complete and incremental.
We chose PBF as more compact.
To read it in Hadoop, there is a Java API specifically made for OSM called this osmosis project. In principle, working with it is simple - you upload a file and cycle through the map elements. Add the points in one place, the lines in another, the relations in the third. In principle, osmosis and for example Spark are already enough to download all the data.
Fortunately, in the process of implementing my bike, it somehow occurred to me to search the Internet for converting OSM into the formats accepted in Hadoop - Parquet (parquet) and Avro. In a sense, both are analogues of PBF, so there was a chance to find a converter. And he was found, but not one.
Meet OSM Parquetizer
Look what I found!
For lazy people - right in the readme of the project in the first line it says: Telenav publishes weekly downloads of the planet at .
For very lazy people: get ready to ship about 700 gigabytes;) Well, if you certainly need a planet. You can usually get by with, say, Europe.
If you don’t want to load, then the process looks like this: download a map in PBF format, for example from a geofactory . This is 2.5 gigabytes if you need Russia, and 19 if Europe. Also not a little, but you can find more finely chopped samples. Next, put the file on the disk, and run the program:
java -jar ./osm-parquetizer.jar russia-latest.osm.pbf
After a few minutes or even seconds, depending on the performance of your machine, you will receive three files in parquet format. Here is what the author looks like (he is from Romania):
-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.parquetSchemes of the received .parquet files: As you can see, everything is simple here. Then we do the following:
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
- we put files on the Hadoop cluster with the hdfs dfs -put command
- let's go say in Hue and create a schema / base, and three tables for it, based on the above data
- execute select * from osm.nodes, and enjoy the result.
A small nuance: in our version of Hive (and possibly yours as well), it is not able to create tables based on the scheme from Parquet. You need to either convert the above to CREATE TABLE (which, in general, is not difficult, and I will leave this as a home exercise for readers), or to do a little trickier: Spark can read the diagram and data from the floor, and create temporary tables on their basis . So we can read the data in Spark Shell like this:
val nodeDF = sqlContext.read.parquet("file:/tmp/osm/romania-latest.osm.pbf.node.parquet")
nodeDF.createOrReplaceTempView("nodes")
Then you can already create tables in Hive using LIKE nodes.
Another remark for lazy people: the author has such a wonderful example , from which in general everything becomes clear (well, if you own Spark). This is of course not Spark Shell, but the Databricks Notebook, but it took me about 15 minutes to tap one keyboard to translate one into another. And in 30-40 minutes it was possible to convert it all into queries for Hive using some analogues that are slightly different from spark.
Real Request Example
What can we get from this database in its current form? In general, quite a lot. If you have a Hive or Spark, Spatial Framework, Geometry API, or one of the alternatives, which are GeoSpark or for example GeoMesa, you can solve many different problems on this basis.
Let's see an example. The easiest way to work with points. For example, a query to get a list of ATMs with their coordinates looks like this:
select * from nodes where tags['amenity']='atm'How to build such a query, you can guess by reading the page on the wiki . There you will find what other tags are, and some of them can be included in your request instead of *, in the form of tags ['operator'], for example, to show the name of the bank.
From the same page it follows that ATM markup is possible in the form of tags amenity = bank and atm = yes. Alas, such ambiguities are everywhere in OSM.
If you are a beginner, and just get acquainted with OSM, I highly recommend mastering (by good examples on the wiki) overpass-turbo . This is a tool that allows you to perform various kinds of searches on map data, both with geometric conditions and with conditions for tags.
And where are the addresses?
Good question. Addresses in OSM are map elements provided with addr: * tags, i.e. starting with addr. Description you will find here . In principle, knowing everything that I have stated above, you can already write some working request:
select * from nodes where tags['addr:housenumber']!=nullWhat problems await us here? Firstly, addresses are placed both on points (for example, building entrances), and on polygons, i.e. on ways. So we have to duplicate the request at least. And secondly, on the page mentioned above, the wiki is written in plain text that it is not recommended to put tags indicating the city, region, region and country, but this must be calculated geometrically. How to do it? In general, this is practically the task of reverse geocoding, with light modifications, and it was described in a previous post.
That is, in general, you need to find administrative boundaries, and for all addresses that are inside them, add the address to the area and everything above. How the boundaries of administrative entities are arranged is described here .
In general, this task is not too simple, but quite solvable, and it is not solved by geocoding, but by downloading OSM updates to our database, in a relaxed atmosphere.
What is useful to do next
In principle, you can already work with the nodes, ways and relations tables that we have, but it’s better to change the scheme a little, making it more suitable for Hive and Spark. The fact is that the OSM scheme is completely normalized, ways and relations do not contain coordinates at all. To build a polygon for the way, you need to join with nodes. I would recommend doing this operation right away, saving the polygons either as an array of structures (Hive can work with composite types array, map and struct), or immediately as a serialized representation of, say, the Geometry class. How to do this is in the example of the author parquetizer.
You can repeat a similar operation at the level of relations, if you want, but hardly worth it. Firstly, you will not always need all the elements of a relationship, and secondly, the relations themselves in OSM are much smaller.
Converter to Avro
Here is another converter, this time to the Avro format. And here it is described where to get the finished files. I did not measure the sizes, but I think that approximately 15-20 files per planet should be comparable to PBF. That is, these are gigabytes, and a lot.
Some conclusions
And where is geocoding, you ask? Yes, downloading maps and extracting addresses is only part of the overall task. I hope it comes to this.
