Switch to Daylight and add AI Disconnected Streets + enhanced coastline

[nmarchio]

Planet file: https://daylightmap.org/planet.html
AI Streets: https://daylightmap.org/roads.html
Enhanced Coastline: https://daylightmap.org/coastlines.html

[nmarchio]

Recommendation on new sources:

• Try Daylight admin data to see if its flush with the coastline (osc file requires osmium). Otherwise use GADM, snap admin areas to coastline then subtract both water polys (from OSM) and (coastlines poly) from Daylight.
• Use Daylight Planet file and parse out to countries or use the Geofabrik (perhaps change names to codes).
• Add the OSC file of ML roads vis Osmium following this description https://daylightmap.org/roads.html

[nmarchio]

https://gist.github.com/jenningsanderson/3e42a99dcb8f760038ad8aa47ea38ce8?permalink_comment_id=4068042#gistcomment-4068042

[nmarchio]

AWS Athena spatial function: https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list-v2.html

[nmarchio]

CREATE EXTERNAL TABLE `daylight_osm_elements`( `id` bigint, `version` int, `changeset` bigint, `created_at` timestamp, `tags` map<string,string>, `wkt` string, `min_lon` double, `max_lon` double, `min_lat` double, `max_lat` double, `quadkey` string, `linear_meters` double, `square_meters` double, `node_refs` array<bigint>, `node_roles` array<string>, `way_refs` array<bigint>, `way_roles` array<string>, `rel_refs` array<bigint>, `rel_roles` array<string>)
PARTITIONED BY ( `release` string, `type` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://daylight-openstreetmap/parquet/osm_elements'
TBLPROPERTIES ( 'has_encrypted_data'='false', 'parquet.compression'='GZIP')

MSCK REPAIR TABLE daylight_osm_elements

Poly file for all of Africa: https://download.geofabrik.de/africa.poly
https://download.geofabrik.de/africa.html

[nmarchio]

Make an Athena query with a hardcoded polygon of Africa and include that in the query of the OSM Daylight table? And try to better understand is the Daylight table a pbf.osm file format (in which case it would need to be converted via osmium before we could query a geometry) but possible we could use the xmin xmax ymin ymax wit the hardcoded polygon

[nmarchio]

Experimental OSM Data from Daylight

• Download Planet OSM via AWS Athena Query: https://gist.github.com/jenningsanderson/3e42a99dcb8f760038ad8aa47ea38ce8?permalink_comment_id=4068042#gistcomment-4068042

In Athena's query editor for us-west-2, run:

CREATE EXTERNAL TABLE `daylight_osm_elements`( `id` bigint, `version` int, `changeset` bigint, `created_at` timestamp, `tags` map<string,string>, `wkt` string, `min_lon` double, `max_lon` double, `min_lat` double, `max_lat` double, `quadkey` string, `linear_meters` double, `square_meters` double, `node_refs` array<bigint>, `node_roles` array<string>, `way_refs` array<bigint>, `way_roles` array<string>, `rel_refs` array<bigint>, `rel_roles` array<string>)
PARTITIONED BY ( `release` string, `type` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://daylight-openstreetmap/parquet/osm_elements'
TBLPROPERTIES ( 'has_encrypted_data'='false', 'parquet.compression'='GZIP')

Partition the data

MSCK REPAIR TABLE daylight_osm_elements;

Query the relevant features data (try to restrict to polygon of Africa https://download.geofabrik.de/africa.poly)

https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list-v2.html

SELECT * FROM daylight_osm_elements WHERE 
tags['natural','barrier','route','railway','highway','waterway','boundary','water']
AND release='v1.9'; 

Download the query locally, then add Admin and ML road extensions to PBF

osmium apply-changes planet.osm.pbf admins.osc.bz2 -o everything.osm.pbf
osmium renumber everything.osm.pbf -o ordered-everything.osm.pbf
osmium apply-changes everything.osm.pbf fb_roads.osc.bz2 -o everything.osm.pbf

Option 1: pass polygon to query and parse down in athena
Option 2: provide bounding box and just parse down via osmium-tool

https://daylightmap.org/daylight-osm-distribution-parquet.html

[nmarchio]

Linked: #71