Home / Data: Planet, Coastlines, Buildings, Admins, Roads / Attribution

What are the Daylight OSM Parquet Files?

While the Daylight Map Distribution of OpenStreetMap has always been openly available in the OSM PBF format, these new Daylight OSM Features parquet files are optimized for loading into AWS Athena and contain fully resolved geometries and additional metadata including length of LineString features. In total, the OSM features files contain 803M ways, 5M relations, and 176M nodes. I refer to these as the “renderable features” in OSM, meaning they have geometries that can be rendered on a map. These files therefore do not include untagged nodes or relations without basic geometries (such as turn restrictions).

The quickest way to get started is to run the following CREATE TABLE command to add the daylight_osm_features table to your AWS database catalog:

CREATE EXTERNAL TABLE `daylight_osm_features`(
  `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)
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_features/'

This query creates the table, next, load the partitions by running: MSCK REPAIR TABLE daylight_osm_features. Now you should be ready to go!

Basic Examples

1. Total kilometers of different highway features in OSM?

SELECT tags [ 'highway' ] AS highway_tag,
    count(id) AS osm_features,
    count(distinct(changeset)) AS total_changesets,
	sum(linear_meters) / 1000 AS total_km
FROM daylight_osm_features
WHERE linear_meters > 0 AND tags['highway'] IS NOT NULL
  AND release='v1.9'
GROUP BY tags [ 'highway' ]
ORDER BY total_km DESC

2. Extract point geometries for all hospital features in OSM?

SELECT 
    tags['name'] as name,
    ST_CENTROID(ST_GEOMETRYFROMTEXT(wkt)) as center,
    tags['amenity'] as amenity
FROM daylight_osm_features
WHERE tags['amenity'] LIKE 'hospital'
  AND release='v1.9'
Current Downloads