Openstreetmap h3

openstreetmap_h3 is a high-performance tool for importing OSM PBF files into PostGIS databases or into the Big Data ecosystem via Apache Arrow and Apache Parquet data formats. This project splits planet-dump geospatial data by H3 indexes into many partitions to simplify worldwide data geospatial analysis/aggregation and routing tasks. The H3 hexagonal hierarchical geospatial indexing system's attributes allow you to perform fast data partitioning/join/aggregation on a 3-8 scale. The resulting dataset contains the following objects: nodes, ways, and relations. There are no tables for historical data, only for the current data.

You can find more information in the project repository openstreetmap_h3

What is openstreetmap_h3

• How to put the whole world in a regular laptop: PostgreSQL and OpenStreetMap as overview
• Roads and building density in North America. 100GB geodata processing OSM data in PostgreSQL(with columnar storage provided by open source Citus extension ).
• «Divide and Conquer» for OpenStreetMap world inside PostgreSQL details about data partitioning approach\

Apache Parquet schemas

Node:

scala> spark.read.parquet("/home/geo/arrow/nodes/*.parquet").printSchema
root                                                                            
|-- id: long (nullable = true)
|-- h33: short (nullable = true)
|-- h38: integer (nullable = true)
|-- latitude: double (nullable = true)
|-- longitude: double (nullable = true)
|-- tags: map (nullable = true)
|    |-- key: string
|    |-- value: string (valueContainsNull = true)

Way:

scala> spark.read.parquet("/home/geo/arrow/ways/*.parquet").printSchema
root                                                                            
|-- id: long (nullable = true)
|-- h33: short (nullable = true)
|-- h38: integer (nullable = true)
|-- latitude: double (nullable = true)
|-- longitude: double (nullable = true)
|-- tags: map (nullable = true)
|    |-- key: string
|    |-- value: string (valueContainsNull = true)
|-- pointIdxs: array (nullable = true)
|    |-- element: long (containsNull = true)
|-- h33Center: short (nullable = true)
|-- closed: boolean (nullable = true)
|-- building: boolean (nullable = true)
|-- highway: boolean (nullable = true)
|-- scale: float (nullable = true)
|-- lineStringWkb: binary (nullable = true)
|-- bboxWkb: binary (nullable = true)
|-- h38Indexes: array (nullable = true)
|    |-- element: integer (containsNull = true)
|-- bboxMinX: double (nullable = true)
|-- bboxMaxX: double (nullable = true)
|-- bboxMinY: double (nullable = true)
|-- bboxMaxY: double (nullable = true)

Relation:

scala> spark.read.parquet("/home/geo/arrow/relations/*.parquet").printSchema
root
|-- id: long (nullable = true)
|-- tags: map (nullable = true)
|    |-- key: string
|    |-- value: string (valueContainsNull = true)
|-- memberId: array (nullable = true)
|    |-- element: long (containsNull = true)
|-- memberType: array (nullable = true)
|    |-- element: byte (containsNull = true)
|-- memberRole: array (nullable = true)
|    |-- element: string (containsNull = true)

Multipolygon:

scala> spark.read.parquet("/home/geo/arrow/multipolygon.parquet").printSchema
root
|-- id: long (nullable = true)
|-- wkb_hex: string (nullable = true)
|-- tags_json: string (nullable = true)

Alternatives to openstreetmap_h3

• Osm2pgsql
• Imposm
• OGR - OGR OSM driver with ogr2ogr
• Osm-parquetizer Transform PBF into Big Data friendly Apache Parquet format
• Osm2pgrouting
• Osmium, special fast C++ data processor
• Osmosis - can also do imports of osm file to postgres DB with postgis extension
• Apache Sedona provides a data source for Apache Spark to read PBF files directly in Spark to transform them or convert them into other formats, such as Apache Parquet