Home / Data: Planet, Coastlines, Buildings, Admins, Roads / Attribution
The earth table is a new data schema that classifies OpenStreetMap-style tags into a 3-level ontology: theme, class, and subclass. A small fishpond, for example, would be found in the water theme, pond class, and fishpond subclass. A kindergarten school is found in the building theme, education class, kindergarten subclass.
The Daylight Earth Table is the result of running the earth table classification over the latest release (v1.55) of the Daylight Map Distribution. The daylight earth table is available as parquet files on Amazon S3. The instructions on this page will create the table inside your AWS account so that you can access the features with Amazon Athena.
Theme | Classes | Subclasses | Features in v1.55 |
building |
20
|
223
|
617,830,276
|
building_detail** |
1
|
1
|
3,246,702
|
infrastructure |
7
|
48
|
46,394,135
|
land |
10
|
19
|
54,647,126
|
landuse |
25
|
97
|
47,113,656
|
placename |
3
|
11
|
3,746,903
|
poi |
22
|
233
|
48,454,865
|
road |
10
|
32
|
249,500,710
|
transit |
6
|
32
|
7,565,617
|
water |
10
|
29
|
49,260,726
|
** The building detail theme includes all building:part=yes
features in OSM. They do not currently have different class/subclass values. Instead, they contain the IDs of the building footprint(s) with which they overlap.
Name | Type | Description |
---|---|---|
geometry_id |
string | unique identifier of the form (n/w/r) + osm id + @ (osm version). Example, version 22 of node id=1 : n1@22 |
theme |
string | theme of the feature (one of the 10 themes above) |
class |
string | class of the feature |
subclass |
string | subclass of the feature |
names |
string | JSON-formatted key/value pairs containing the placename in different languages. Keys include local (the common name used in the place) and ISO language codes like en , en-US , and de |
metadata |
string | JSON-formatted key/value pairs of feature metadata. To retrieve height of a feature, for example: JSON_EXTRACT_SCALAR(metadata, '$.height_m') |
original_source_tags |
string | JSON-formatted key/value pairs of original OSM tags |
source |
string | The daylight release, currently v1.18 |
quadkey |
string | The zoom level 15 quadkey that contains the centroid of the feature. |
wkt |
string | well-known text of the feature Coordinate system: EPSG 4326 |
Run the following query in the Athena query editor to create the daylight_earth
table
CREATE EXTERNAL TABLE `daylight_earth`(
`geometry_id` string,
`class` string,
`subclass` string,
`metadata` string,
`original_source_tags` string,
`names` string,
`quadkey` string,
`wkt` string)
PARTITIONED BY (
`release` varchar(5),
`theme` string)
STORED AS PARQUET
LOCATION
's3://daylight-openstreetmap/earth'
TBLPROPERTIES (
'has_encrypted_data'='false',
'parquet.compression'='GZIP')
run MSCK REPAIR TABLE daylight_earth
to load the partitions.
Now you have access to nearly 1B OSM features each with theme, class, and subclass attributes. metadata
, original_source_tags
, and names
are stored JSON-formatted strings. These attributes can be accessed with the JSON_EXTRACT_SCALAR
function.
For example, let’s extract all of the parks in Minneapolis / St. Paul:
SELECT
geometry_id,
class,
subclass,
JSON_EXTRACT_SCALAR(names, '$.local') as name,
CAST(JSON_EXTRACT_SCALAR(metadata, '$.surface_area_sq_m') AS double) as area,
wkt
from daylight_earth
WHERE release = 'v1.55'
AND theme = 'landuse'
AND class = 'park'
AND subclass <> 'grass'
AND ST_CONTAINS(
ST_GEOMETRYFROMTEXT(
'POLYGON((-93.67167390882966 44.88741416375285,-93.29662598669525 45.24265977467033,-92.84310065209863 44.96670966763787,-93.21197278797622 44.64095920238205,-93.67167390882966 44.88741416375285))'),
ST_GEOMETRYFROMTEXT(wkt)
)