The Flateau dataset contains over 29M Japanese buildings along with natural disaster risk rankings.
These were originally sourced from Japan's national open 3D city data, which is distributed in CityGML format. Flateau has converted these files into spatially sorted Parquet format and is hosting them via Cloudflare. This makes analysing the data quicker as well as presenting it with a much smaller disk footprint.
Below are the bounding boxes of the 303 settlement-year datasets.
Zooming into Tokyo, you can see some bounding boxes overlap one another. The datasets are updated on an ongoing basis. As of this writing, the oldest dataset is from 2020.
Since Flateau is hosted via HTTPS, its contents can be downloaded surgically via the GeoParquet Downloader plug-in for QGIS. This plug-in will only download the data that is needed, even from a multi-TB dataset, into a local Parquet file and into the active project. I walk through how to install and run it using OpenStreetMap data in this post.
The Flateau project is the work of Hiroo Imaki, who is the President of Pacific Spatial Solutions. Until recently, he was based in Seattle, serving primarily Japanese clients, but is now based in Okinawa. He had previously worked for NOAA as a Geospatial Data Analyst.
In this post, I'll analyse Flateau's refreshed Japanese building footprints dataset.
My Workstation
I'm using a 5.7 GHz AMD Ryzen 9 9950X CPU. It has 16 cores and 32 threads and 1.2 MB of L1, 16 MB of L2 and 64 MB of L3 cache. It has a liquid cooler attached and is housed in a spacious, full-sized Cooler Master HAF 700 computer case.
The system has 96 GB of DDR5 RAM clocked at 4,800 MT/s and a 5th-generation, Crucial T700 4 TB NVMe M.2 SSD which can read at speeds up to 12,400 MB/s. There is a heatsink on the SSD to help keep its temperature down. This is my system's C drive.
The system is powered by a 1,200-watt, fully modular Corsair Power Supply and is sat on an ASRock X870E Nova 90 Motherboard.
I'm running Ubuntu 24 LTS via Microsoft's Ubuntu for Windows on Windows 11 Pro. In case you're wondering why I don't run a Linux-based desktop as my primary work environment, I'm still using an Nvidia GTX 1080 GPU which has better driver support on Windows and ArcGIS Pro only supports Windows natively.
Installing Prerequisites
I'll use jq to help format data in this post.
$ sudo apt update
$ sudo apt install \
jq
I'll use DuckDB, along with its H3, JSON, Lindel, Parquet and Spatial extensions in this post.
$ cd ~
$ wget -c https://github.com/duckdb/duckdb/releases/download/v1.5.4/duckdb_cli-linux-amd64.zip
$ unzip -j duckdb_cli-linux-amd64.zip
$ chmod +x duckdb
$ ~/duckdb
INSTALL h3 FROM community;
INSTALL lindel FROM community;
INSTALL json;
INSTALL parquet;
INSTALL spatial;
I'll set up DuckDB to load every installed extension each time it launches.
$ vi ~/.duckdbrc
.timer on
.width 180
LOAD h3;
LOAD lindel;
LOAD json;
LOAD parquet;
LOAD spatial;
The maps in this post were rendered with QGIS version 4.2.3. QGIS is a desktop application that runs on Windows, macOS and Linux. The application has grown in popularity in recent years and has ~22M application launches from users all around the world each month.
I used QGIS' HCMGIS plug-in to add vector-based basemaps from Esri to this post.
303 Settlements-Year Pairs
The following will download a 176 KB manifest file containing 303 records. These describe both an area, often a single settlement, and a year of surveying.
$ wget -O by-city.parquet \
https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/items.parquet
These are the top 20 datasets by building count listed in the above manifest file.
$ ~/duckdb
SELECT title
FROM 'by-city.parquet'
ORDER BY "plateau:building_count" DESC
LIMIT 20;
┌─────────────────────┐
│ title │
│ varchar │
├─────────────────────┤
│ Yokohama Shi 2024 │
│ Nagoya Shi 2022 │
│ Sapporo Shi 2020 │
│ Osaka Shi 2025 │
│ Okayama Shi 2025 │
│ Hamamatsu Shi 2023 │
│ Kyoto Shi 2025 │
│ Saitama Shi 2025 │
│ Sendai Shi 2024 │
│ Niigata Shi 2023 │
│ Hiroshima Shi 2024 │
│ Sakai Shi 2024 │
│ Fukuoka Shi 2024 │
│ Shizuoka Shi 2023 │
│ Toyota Shi 2023 │
│ Himeji Shi 2023 │
│ Fukuyama Shi 2020 │
│ Nagano Shi 2024 │
│ Kitakyushu Shi 2020 │
│ Kawasaki Shi 2022 │
└─────────────────────┘
The Parquet-formatted assets of the 303 datasets are just over 2 GB.
SELECT gb: SUM("file:size") / 1024 ** 3
FROM (SELECT UNNEST(assets, recursive := true)
FROM 'by-city.parquet');
┌────────────────────┐
│ gb │
│ double │
├────────────────────┤
│ 2.0805980963632464 │
└────────────────────┘
There are over 29M buildings described in Flateau.
SELECT SUM("plateau:building_count")
FROM 'by-city.parquet';
29,290,201
There are several fields of metadata within the manifest. Below is an example record.
$ echo "FROM 'by-city.parquet'
ORDER BY \"plateau:building_count\" DESC
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"assets": {
"building_centroid_lod0": {
"file:size": 69363985,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_centroid_lod0.parquet",
"proj:code": "EPSG:6668",
"roles": [
"data"
],
"table:primary_geometry": "GEOM",
"table:row_count": 883008,
"title": "Building centroids (LOD0 representative points) with the same attributes",
"type": "application/vnd.apache.parquet"
},
"building_data_quality": {
"file:size": 62121734,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_data_quality.parquet",
"roles": [
"data"
],
"table:row_count": 883008,
"title": "Source and data-quality attributes",
"type": "application/vnd.apache.parquet"
},
"building_detail": {
"file:size": 62257595,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_detail.parquet",
"roles": [
"data"
],
"table:row_count": 883008,
"title": "Detailed building attributes (usage, structure, vacancy, ...)",
"type": "application/vnd.apache.parquet"
},
"building_large_customer_facility": null,
"building_lod0": {
"file:size": 134381836,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_lod0.parquet",
"proj:code": "EPSG:6668",
"roles": [
"data"
],
"table:primary_geometry": "GEOM",
"table:row_count": 883008,
"title": "Building footprints (LOD0) with attributes and geometry",
"type": "application/vnd.apache.parquet"
},
"building_risk_flooding": {
"file:size": 12137704,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_risk_flooding.parquet",
"roles": [
"data"
],
"table:row_count": 344233,
"title": "Flood (river) risk per building",
"type": "application/vnd.apache.parquet"
},
"building_risk_high_tide": {
"file:size": 4683823,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_risk_high_tide.parquet",
"roles": [
"data"
],
"table:row_count": 63239,
"title": "Storm-surge (high tide) risk per building",
"type": "application/vnd.apache.parquet"
},
"building_risk_inland_flooding": null,
"building_risk_land_slide": {
"file:size": 4789759,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_risk_land_slide.parquet",
"roles": [
"data"
],
"table:row_count": 67220,
"title": "Landslide risk per building",
"type": "application/vnd.apache.parquet"
},
"building_risk_reservoir_flooding": null,
"building_risk_tsunami": {
"file:size": 4603414,
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100_building_risk_tsunami.parquet",
"roles": [
"data"
],
"table:row_count": 64915,
"title": "Tsunami risk per building",
"type": "application/vnd.apache.parquet"
}
},
"bbox": {
"xmax": 139.72409601721222,
"xmin": 139.4652213272838,
"ymax": 35.592639692999235,
"ymin": 35.313164982867015
},
"collection": "flateau-v2-by-city",
"created": "2026-10-27 08:44:20+02",
"datetime": null,
"description": "PLATEAU building data for yokohama-shi (JIS 14100), fiscal year 2024: 8 GeoParquet/Parquet tables joined on gml_id.",
"end_datetime": "2025-04-01 02:59:59+03",
"geometry": "POLYGON ((139.4652213272838 35.313164982867015, 139.72409601721222 35.313164982867015, 139.72409601721222 35.592639692999235, 139.4652213272838 35.592639692999235, 139.4652213272838 35.313164982867015))",
"id": "yokohama-shi-14100",
"links": [
{
"href": "https://data.source.coop/pacificspatial/flateau-2-0/catalog.json",
"rel": "root",
"title": null,
"type": "application/json"
},
{
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/collection.json",
"rel": "parent",
"title": null,
"type": "application/json"
},
{
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/collection.json",
"rel": "collection",
"title": null,
"type": "application/json"
},
{
"href": "https://data.source.coop/pacificspatial/flateau-2-0/flateau-v2-by-city/yokohama-shi-14100/yokohama-shi-14100.json",
"rel": "self",
"title": null,
"type": "application/json"
},
{
"href": "https://www.geospatial.jp/ckan/dataset/plateau-14100-yokohama-shi-2024",
"rel": "via",
"title": "PLATEAU dataset page (G-spatial Information Center)",
"type": "text/html"
},
{
"href": "https://assets.cms.plateau.reearth.io/assets/04/96d45a-a30b-4c9f-88c0-c1323b5a0f86/14100_yokohama-shi_city_2024_citygml_2_op.zip",
"rel": "via",
"title": "Source CityGML zip (14100_yokohama-shi_city_2024_citygml_2_op.zip)",
"type": "application/zip"
}
],
"plateau:area_ja": "神奈川県_横浜市",
"plateau:building_count": 883008,
"plateau:dataset_id": "plateau-14100-yokohama-shi-2024",
"plateau:fiscal_year": 2024,
"plateau:jis_code": "14100",
"plateau:spec_version": "v4",
"plateau:table_row_counts": {
"building_centroid_lod0": 883008,
"building_data_quality": 883008,
"building_detail": 883008,
"building_large_customer_facility": null,
"building_lod0": 883008,
"building_risk_flooding": 344233,
"building_risk_high_tide": 63239,
"building_risk_inland_flooding": null,
"building_risk_land_slide": 67220,
"building_risk_reservoir_flooding": null,
"building_risk_tsunami": 64915
},
"plateau:title_ja": "3D都市モデル（Project PLATEAU）横浜市（2024年度）",
"stac_extensions": [
"https://stac-extensions.github.io/file/v2.1.0/schema.json",
"https://stac-extensions.github.io/table/v1.2.0/schema.json",
"https://stac-extensions.github.io/projection/v2.0.0/schema.json"
],
"stac_version": "1.1.0",
"start_datetime": "2024-04-01 03:00:00+03",
"title": "Yokohama Shi 2024",
"type": "Feature",
"updated": "2026-10-27 08:44:20+02"
}
]
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
.maxrows 200
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
SELECT * EXCLUDE (
"plateau:table_row_counts",
assets,
links,
bbox),
"plateau:table_row_counts".*,
bbox.*
FROM 'by-city.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌──────────────────────────────────┬──────────────────┬───────────────┬────────────────────────────┬────────────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├──────────────────────────────────┼──────────────────┼───────────────┼────────────────────────────┼────────────────────────────┤
│ building_centroid_lod0 │ BIGINT │ 273 │ 214 │ 883008 │
│ building_data_quality │ BIGINT │ 273 │ 214 │ 883008 │
│ building_detail │ BIGINT │ 273 │ 214 │ 883008 │
│ building_large_customer_facility │ BIGINT │ 24 │ 1 │ 40453 │
│ building_lod0 │ BIGINT │ 273 │ 214 │ 883008 │
│ building_risk_flooding │ BIGINT │ 268 │ 4 │ 1763359 │
│ building_risk_high_tide │ BIGINT │ 68 │ 7 │ 330488 │
│ building_risk_inland_flooding │ BIGINT │ 28 │ 1461 │ 335338 │
│ building_risk_land_slide │ BIGINT │ 250 │ 2 │ 124527 │
│ building_risk_reservoir_flooding │ BIGINT │ 3 │ 144 │ 19784 │
│ building_risk_tsunami │ BIGINT │ 113 │ 3 │ 268134 │
│ description │ VARCHAR │ 276 │ PLATEAU building dat │ PLATEAU building dat │
│ end_datetime │ TIMESTAMP │ 6 │ 2021-04-01 02:59:59+ │ 2026-04-01 02:59:59+ │
│ geometry │ GEOMETRY(' │ 284 │ POLYGON ((139.612191 │ POLYGON ((139.749445 │
│ id │ VARCHAR │ 318 │ adachi-ku-13121 │ yoshimi-machi-11347 │
│ plateau:area_ja │ VARCHAR │ 308 │ 三重県_伊勢市 │ 鹿児島県_南さつま市 │
│ plateau:building_count │ BIGINT │ 273 │ 214 │ 883008 │
│ plateau:dataset_id │ VARCHAR │ 323 │ plateau-01100-sappor │ plateau-47201-naha-s │
│ plateau:fiscal_year │ BIGINT │ 6 │ 2020 │ 2025 │
│ plateau:jis_code │ VARCHAR │ 280 │ 01100 │ 47201 │
│ plateau:spec_version │ VARCHAR │ 3 │ v3 │ v5 │
│ plateau:title_ja │ VARCHAR │ 250 │ 3D都市モデル（Project PLAT │ 3D都市モデル（Project PLAT │
│ start_datetime │ TIMESTAMP │ 6 │ 2020-04-01 03:00:00+ │ 2025-04-01 03:00:00+ │
│ title │ VARCHAR │ 317 │ Adachi Ku 2025 │ Yoshimi Machi 2025 │
│ xmax │ DOUBLE │ 290 │ 127.7387703771321 │ 143.30417340927974 │
│ xmin │ DOUBLE │ 282 │ 127.63997333602606 │ 143.10471190579253 │
│ ymax │ DOUBLE │ 281 │ 26.24719547583986 │ 43.18427843196736 │
│ ymin │ DOUBLE │ 252 │ 26.17650865970848 │ 42.89608971019516 │
└──────────────────────────────────┴──────────────────┴───────────────┴────────────────────────────┴────────────────────────────┘
Yokohama's Datasets
I'll download each of the Parquet files for Yokohama.
$ KEYS=`echo "FROM 'by-city.parquet'
ORDER BY \"plateau:building_count\" DESC
LIMIT 1" \
| ~/duckdb -json \
| jq -r '.[0].assets|keys[]'`
$ for KEY in $KEYS; do
echo "FROM 'by-city.parquet'
ORDER BY \"plateau:building_count\" DESC
LIMIT 1" \
| ~/duckdb -json \
| jq -r ".[0].assets.$KEY.href" \
| grep https \
| xargs -P1 \
-I% \
wget -c "%"
done
These are the sizes of each file.
$ du -h yokohama-shi-14100_*.parquet
67M yokohama-shi-14100_building_centroid_lod0.parquet
60M yokohama-shi-14100_building_data_quality.parquet
60M yokohama-shi-14100_building_detail.parquet
129M yokohama-shi-14100_building_lod0.parquet
12M yokohama-shi-14100_building_risk_flooding.parquet
4.5M yokohama-shi-14100_building_risk_high_tide.parquet
4.6M yokohama-shi-14100_building_risk_land_slide.parquet
4.4M yokohama-shi-14100_building_risk_tsunami.parquet
Yokohama's Building Footprints
These are the building footprints for the above dataset. They've been coloured based on their size, with the brighter buildings having a larger area.
This is an example record from the main building footprint Parquet file. It contains the geometry for the building and will be used for joining within disaster risk datasets later on.
$ echo "FROM 'yokohama-shi-14100_building_lod0.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"GEOM": "POLYGON ((139.48632377560318 35.38250065087854, 139.48640557260586 35.38254644939928, 139.4863783675618 35.38257897712214, 139.4862966781064 35.38253324920214, 139.48632377560318 35.38250065087854))",
"address": null,
"branch_id": 1,
"building_id": "14100-bldg-909817",
"cal_area_m2": 39.358994947746396,
"cal_height_m": 3.5889999866485596,
"cal_xmax": 139.48640557260586,
"cal_xmin": 139.4862966781064,
"cal_ymax": 35.38257897712214,
"cal_ymin": 35.38250065087854,
"cal_zmax_m": 19.147842407226562,
"cal_zmin_m": 15.558842658996582,
"city": "神奈川県横浜市戸塚区",
"city_code": "14110",
"class": null,
"class_code": null,
"creation_date": "0001-01-01",
"gml_description": null,
"gml_id": "bldg_86f8a522-6076-4fe2-8808-f7810ec646cd",
"gml_name": null,
"matching_score": null,
"measured_height": 3.5999999046325684,
"measured_height_uom": "m",
"number_of_building_unit_ownership": null,
"number_of_real_estate_id_of_land": null,
"prefecture": "神奈川県",
"prefecture_code": "14",
"real_estate_id_of_building": null,
"real_estate_id_of_building_unit_ownership": null,
"real_estate_id_of_land": null,
"roof_type": null,
"roof_type_code": null,
"source_filename": "53390358_bldg_6697_op.gml",
"storeys_above_ground": null,
"storeys_below_ground": null,
"termination_date": null,
"usage": null,
"usage_code": null,
"year_of_construction": null,
"year_of_demolition": null
}
]
The records in the above dataset have been spatially sorted. I'll group them into 100 groups below to prove this.
$ ~/duckdb
COPY (
WITH a AS (
SELECT ST_XMIN(ST_EXTENT(geom)) AS xmin,
ST_YMIN(ST_EXTENT(geom)) AS ymin,
ST_XMAX(ST_EXTENT(geom)) AS xmax,
ST_YMAX(ST_EXTENT(geom)) AS ymax,
CEIL(ROW_NUMBER() OVER () / 8_830)::INT AS group_num
FROM READ_PARQUET('yokohama-shi-14100_building_lod0.parquet')
)
SELECT group_num,
geometry: {'min_x': MIN(xmin),
'min_y': MIN(ymin),
'max_x': MAX(xmax),
'max_y': MAX(ymax)}::BOX_2D::GEOMETRY
FROM a
GROUP BY group_num
ORDER BY group_num
) TO 'yokohama-shi-14100_building_lod0.bbox.parquet' (
FORMAT 'PARQUET',
CODEC 'ZSTD',
COMPRESSION_LEVEL 22,
ROW_GROUP_SIZE 15000);
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_lod0.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌───────────────────────────────────────────┬──────────────────┬───────────────┬────────────────────────────┬──────────────────────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├───────────────────────────────────────────┼──────────────────┼───────────────┼────────────────────────────┼──────────────────────────────────────┤
│ branch_id │ INTEGER │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 877667 │ 14100-bldg-1 │ 14100-bldg-99999 │
│ cal_area_m2 │ DOUBLE │ 782958 │ 0.012901990208774805 │ 392213.09524476435 │
│ cal_height_m │ FLOAT │ 24711 │ 2.0 │ 297.21 │
│ cal_xmax │ DOUBLE │ 927039 │ 139.465285218391 │ 139.72409601721222 │
│ cal_xmin │ DOUBLE │ 777207 │ 139.4652213272838 │ 139.72369334288805 │
│ cal_ymax │ DOUBLE │ 853474 │ 35.31340567780812 │ 35.592639692999235 │
│ cal_ymin │ DOUBLE │ 963650 │ 35.313164982867015 │ 35.592583613320826 │
│ cal_zmax_m │ FLOAT │ 451496 │ 2.629 │ 293.735 │
│ cal_zmin_m │ FLOAT │ 514645 │ -5.718926 │ 152.79584 │
│ city │ VARCHAR │ 20 │ 神奈川県川崎市中原区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 26 │ 14101 │ 14137 │
│ creation_date │ DATE │ 5 │ 0001-01-01 │ 2025-10-31 │
│ GEOM │ GEOMETRY(' │ 740302 │ POLYGON ((139.642262 │ MULTIPOLYGON (((139. │
│ gml_id │ VARCHAR │ 824638 │ bldg_0000141c-5b12-4 │ bldg_fffffaf3-c236-4 │
│ gml_name │ VARCHAR │ 1427 │ KDDI株式会社(南関東総支社) │ ＪＸＴＧエネルギー株式会社横浜製造所 │
│ matching_score │ VARCHAR │ 45 │ 50 │ 98 │
│ measured_height │ FLOAT │ 595 │ 0.1 │ 305.0 │
│ number_of_real_estate_id_of_land │ INTEGER │ 67 │ 1 │ 464 │
│ real_estate_id_of_building │ VARCHAR │ 113769 │ 0200000049298-0000 │ 0225001232935-0000 │
│ real_estate_id_of_building_unit_ownership │ VARCHAR │ 584 │ 0200001109630-0000 │ 0225001232331-0000 │
│ real_estate_id_of_land │ VARCHAR │ 128747 │ 0200000043085-0000 │ 0225001230735-0000 │
│ source_filename │ VARCHAR │ 514 │ 52397478_bldg_6697_o │ 53393413_bldg_6697_o │
└───────────────────────────────────────────┴──────────────────┴───────────────┴────────────────────────────┴──────────────────────────────────────┘
Building Details
This is an example record from the building detail dataset.
$ echo "FROM 'yokohama-shi-14100_building_detail.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"area_classification_type": null,
"area_classification_type_code": null,
"basement_first_usage": null,
"basement_first_usage_code": null,
"basement_second_usage": null,
"basement_second_usage_code": null,
"basement_usage": null,
"basement_usage_code": null,
"branch_id": 1,
"building_coverage_rate": null,
"building_footprint_area": null,
"building_footprint_area_uom": null,
"building_height": null,
"building_height_uom": null,
"building_id": "14100-bldg-768508",
"building_roof_edge_area": null,
"building_roof_edge_area_uom": null,
"building_structure_org_type": null,
"building_structure_org_type_code": null,
"building_structure_type": null,
"building_structure_type_code": null,
"city": "神奈川県横浜市金沢区",
"city_code": "14108",
"detail_usage": null,
"detail_usage2": null,
"detail_usage2_code": null,
"detail_usage3": null,
"detail_usage3_code": null,
"detail_usage_code": null,
"development_area": null,
"development_area_uom": null,
"districts_and_zones_type": null,
"districts_and_zones_type_code": null,
"eave_height": null,
"eave_height_uom": null,
"fireproof_structure_type": null,
"fireproof_structure_type_code": null,
"floor_area_rate": null,
"gml_id": "bldg_a82a61a6-adda-46f2-a45b-4cec53bf73da",
"ground_floor_usage": null,
"ground_floor_usage_code": null,
"implementing_body": null,
"land_use_type": null,
"land_use_type_code": null,
"major_usage": null,
"major_usage2": null,
"major_usage2_code": null,
"major_usage_code": null,
"note": null,
"org_usage": null,
"org_usage2": null,
"org_usage2_code": null,
"org_usage_code": null,
"prefecture": "神奈川県",
"prefecture_code": "14",
"second_floor_usage": null,
"second_floor_usage_code": null,
"serial_number_of_building_certicication": null,
"site_area": null,
"site_area_uom": null,
"source_filename": "52397478_bldg_6697_op.gml",
"specified_building_coverage_rate": null,
"specified_floor_area_rate": null,
"standard_floor_area_rate": null,
"survey_year": 2020,
"third_floor_usage": null,
"third_floor_usage_code": null,
"total_floor_area": null,
"total_floor_area_uom": null,
"urban_plan_type": null,
"urban_plan_type_code": null,
"vacancy": null,
"vacancy_code": null
}
]
For some reason, almost every field contains a NULL value. I'll need to look into this.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_detail.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌─────────────────┬──────────────────┬───────────────┬──────────────────────┬──────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├─────────────────┼──────────────────┼───────────────┼──────────────────────┼──────────────────────┤
│ branch_id │ INTEGER │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 877667 │ 14100-bldg-1 │ 14100-bldg-99999 │
│ city │ VARCHAR │ 20 │ 神奈川県川崎市中原区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 26 │ 14101 │ 14137 │
│ gml_id │ VARCHAR │ 824638 │ bldg_0000141c-5b12-4 │ bldg_fffffaf3-c236-4 │
│ source_filename │ VARCHAR │ 514 │ 52397478_bldg_6697_o │ 53393413_bldg_6697_o │
│ survey_year │ SMALLINT │ 3 │ 1 │ 2020 │
└─────────────────┴──────────────────┴───────────────┴──────────────────────┴──────────────────────┘
Flooding Risk
This is an example of a flood risk record.
$ echo "FROM 'yokohama-shi-14100_building_risk_flooding.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"admin_type": "都道府県",
"admin_type_code": "2",
"branch_id": 1,
"building_id": "14100-bldg-759408",
"city": "神奈川県横浜市金沢区",
"city_code": "14108",
"depth": 0.368,
"depth_uom": "m",
"description": "侍従川水系侍従川",
"description_code": "18",
"duration": 0.0,
"duration_uom": "hour",
"gml_id": "bldg_24269ef8-f30c-4916-ba38-c5976f11106d",
"prefecture": "神奈川県",
"prefecture_code": "14",
"rank": "0.5m未満",
"rank_code": 1,
"rank_org": null,
"rank_org_code": null,
"scale": "L2（想定最大規模）",
"scale_code": "2",
"source_filename": "52397488_bldg_6697_op.gml"
}
]
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_risk_flooding.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌──────────────────┬──────────────────┬───────────────┬──────────────────────┬──────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├──────────────────┼──────────────────┼───────────────┼──────────────────────┼──────────────────────┤
│ admin_type │ VARCHAR │ 2 │ 国 │ 都道府県 │
│ admin_type_code │ VARCHAR │ 2 │ 1 │ 2 │
│ branch_id │ INTEGER │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 148979 │ 14100-bldg-1 │ 14100-bldg-9999 │
│ city │ VARCHAR │ 17 │ 神奈川県川崎市中原区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 23 │ 14101 │ 14137 │
│ depth │ DOUBLE │ 5034 │ 0.001 │ 9.832 │
│ description │ VARCHAR │ 16 │ 侍従川水系侍従川 │ 鶴見川水系鶴見川 │
│ description_code │ VARCHAR │ 21 │ 1 │ 9 │
│ duration │ DOUBLE │ 4095 │ 0.0 │ 671.383 │
│ gml_id │ VARCHAR │ 154846 │ bldg_00005581-d486-4 │ bldg_ffffeb76-b6c0-4 │
│ rank │ VARCHAR │ 4 │ 0.5m以上3m未満 │ 5m以上10m未満 │
│ rank_code │ TINYINT │ 4 │ 1 │ 4 │
│ scale │ VARCHAR │ 2 │ L1（計画規模） │ L2（想定最大規模） │
│ scale_code │ VARCHAR │ 2 │ 1 │ 2 │
│ source_filename │ VARCHAR │ 219 │ 52397488_bldg_6697_o │ 53392571_bldg_6697_o │
└──────────────────┴──────────────────┴───────────────┴──────────────────────┴──────────────────────┘
I'll join this dataset to the building footprint dataset.
COPY (
FROM 'yokohama-shi-14100_building_risk_flooding.parquet' a
JOIN 'yokohama-shi-14100_building_lod0.parquet' b ON (a.building_id = b.building_id)
) TO 'yokohama-shi-14100_building_risk_flooding.footprints.parquet' (
FORMAT 'PARQUET',
CODEC 'ZSTD',
COMPRESSION_LEVEL 22,
ROW_GROUP_SIZE 15000);
These are the flooding risk ranks of various buildings in this dataset.
High Tide Risk
This is an example of a high-tide risk record.
$ echo "FROM 'yokohama-shi-14100_building_risk_high_tide.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"branch_id": 1,
"building_id": "14100-bldg-759408",
"city": "神奈川県横浜市金沢区",
"city_code": "14108",
"depth": 0.025,
"depth_uom": "m",
"description": "高潮浸水想定区域図",
"description_code": "1",
"gml_id": "bldg_24269ef8-f30c-4916-ba38-c5976f11106d",
"prefecture": "神奈川県",
"prefecture_code": "14",
"rank": "0.5m未満",
"rank_code": 1,
"rank_org": null,
"rank_org_code": null,
"source_filename": "52397488_bldg_6697_op.gml"
}
]
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_risk_high_tide.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌─────────────────┬──────────────────┬───────────────┬──────────────────────┬──────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├─────────────────┼──────────────────┼───────────────┼──────────────────────┼──────────────────────┤
│ branch_id │ INTEGER │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 60672 │ 14100-bldg-1 │ 14100-bldg-9999 │
│ city │ VARCHAR │ 10 │ 神奈川県川崎市川崎区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 12 │ 14101 │ 14132 │
│ depth │ DOUBLE │ 2685 │ 0.001 │ 4.697 │
│ gml_id │ VARCHAR │ 64610 │ bldg_0000db01-689a-4 │ bldg_fffffaf3-c236-4 │
│ rank │ VARCHAR │ 3 │ 0.5m以上3m未満 │ 3m以上5m未満 │
│ rank_code │ TINYINT │ 3 │ 1 │ 3 │
│ source_filename │ VARCHAR │ 109 │ 52397488_bldg_6697_o │ 53392552_bldg_6697_o │
└─────────────────┴──────────────────┴───────────────┴──────────────────────┴──────────────────────┘
These are the risk rankings for the buildings within this dataset.
COPY (
FROM 'yokohama-shi-14100_building_risk_high_tide.parquet' a
JOIN 'yokohama-shi-14100_building_lod0.parquet' b ON (a.building_id = b.building_id)
) TO 'yokohama-shi-14100_building_risk_high_tide.footprints.parquet' (
FORMAT 'PARQUET',
CODEC 'ZSTD',
COMPRESSION_LEVEL 22,
ROW_GROUP_SIZE 15000);
Landslide Risk
This is an example landslide risk record.
$ echo "FROM 'yokohama-shi-14100_building_risk_land_slide.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"area_type": "土砂災害特別警戒区域（指定済）",
"area_type_code": 2,
"branch_id": 1,
"building_id": "14100-bldg-746015",
"city": "神奈川県横浜市金沢区",
"city_code": "14108",
"description": "急傾斜地の崩落",
"description_code": 1,
"gml_id": "bldg_5fc6db0a-0568-439a-b4e6-dc278e0c0b2d",
"prefecture": "神奈川県",
"prefecture_code": "14",
"source_filename": "52397478_bldg_6697_op.gml"
}
]
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_risk_land_slide.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌──────────────────┬──────────────────┬───────────────┬────────────────────────────────┬────────────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├──────────────────┼──────────────────┼───────────────┼────────────────────────────────┼────────────────────────────┤
│ area_type │ VARCHAR │ 4 │ 土砂災害特別警戒区域（指定前） │ 土砂災害警戒区域（指定済） │
│ area_type_code │ TINYINT │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 72788 │ 14100-bldg-100003 │ 14100-bldg-99997 │
│ city │ VARCHAR │ 18 │ 神奈川県川崎市中原区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 21 │ 14101 │ 14137 │
│ description │ VARCHAR │ 2 │ 土石流 │ 急傾斜地の崩落 │
│ description_code │ TINYINT │ 2 │ 1 │ 2 │
│ gml_id │ VARCHAR │ 68080 │ bldg_0000748e-319b-4 │ bldg_ffffeb14-1043-4 │
│ source_filename │ VARCHAR │ 315 │ 52397478_bldg_6697_o │ 53393412_bldg_6697_o │
└──────────────────┴──────────────────┴───────────────┴────────────────────────────────┴────────────────────────────┘
These are the four distinct area type values found in this dataset, with their English translations below.
SELECT DISTINCT area_type
FROM 'yokohama-shi-14100_building_risk_land_slide.parquet';
┌────────────────────────────────┐
│ area_type │
│ varchar │
├────────────────────────────────┤
│ 土砂災害特別警戒区域（指定前） │
│ 土砂災害特別警戒区域（指定済） │
│ 土砂災害警戒区域（指定前） │
│ 土砂災害警戒区域（指定済） │
└────────────────────────────────┘
Special Warning Area for Sediment-Related Disasters (prior to designation)
Special Warning Area for Sediment-Related Disasters (already designated)
Sediment-related disaster alert area (prior to designation)
Sediment-related disaster alert area (already designated)
These are the area types of the various buildings within this dataset.
COPY (
FROM 'yokohama-shi-14100_building_risk_land_slide.parquet' a
JOIN 'yokohama-shi-14100_building_lod0.parquet' b ON (a.building_id = b.building_id)
) TO 'yokohama-shi-14100_building_risk_land_slide.footprints.parquet' (
FORMAT 'PARQUET',
CODEC 'ZSTD',
COMPRESSION_LEVEL 22,
ROW_GROUP_SIZE 15000);
Tsunami Risk
This is an example tsunami risk record.
$ echo "FROM 'yokohama-shi-14100_building_risk_tsunami.parquet'
LIMIT 1" \
| ~/duckdb -json \
| jq -S .
[
{
"branch_id": 1,
"building_id": "14100-bldg-759408",
"city": "神奈川県横浜市金沢区",
"city_code": "14108",
"depth": 0.5,
"depth_uom": "m",
"description": "津波浸水想定図",
"description_code": "1",
"gml_id": "bldg_24269ef8-f30c-4916-ba38-c5976f11106d",
"prefecture": "神奈川県",
"prefecture_code": "14",
"rank": "0.5m以上3m未満",
"rank_code": 2,
"source_filename": "52397488_bldg_6697_op.gml"
}
]
This is a breakdown of unique values and NULL coverage across each column.
$ ~/duckdb
SELECT column_name,
column_type[:10],
approx_unique,
min[:20],
max[:20]
FROM (SUMMARIZE
FROM 'yokohama-shi-14100_building_risk_tsunami.parquet')
WHERE approx_unique > 1
ORDER BY LOWER(column_name);
┌─────────────────┬──────────────────┬───────────────┬──────────────────────┬──────────────────────┐
│ column_name │ column_type[:10] │ approx_unique │ min[:20] │ max[:20] │
│ varchar │ varchar │ int64 │ varchar │ varchar │
├─────────────────┼──────────────────┼───────────────┼──────────────────────┼──────────────────────┤
│ branch_id │ INTEGER │ 4 │ 1 │ 4 │
│ building_id │ VARCHAR │ 66817 │ 14100-bldg-10 │ 14100-bldg-9999 │
│ city │ VARCHAR │ 7 │ 神奈川県川崎市川崎区 │ 神奈川県横浜市鶴見区 │
│ city_code │ VARCHAR │ 10 │ 14101 │ 14131 │
│ depth │ DOUBLE │ 44 │ 0.1 │ 6.8 │
│ gml_id │ VARCHAR │ 62940 │ bldg_0002457e-ba98-4 │ bldg_fffffaf3-c236-4 │
│ rank │ VARCHAR │ 4 │ 0.5m以上3m未満 │ 5m以上10m未満 │
│ rank_code │ TINYINT │ 4 │ 1 │ 4 │
│ source_filename │ VARCHAR │ 122 │ 52397488_bldg_6697_o │ 53392535_bldg_6697_o │
└─────────────────┴──────────────────┴───────────────┴──────────────────────┴──────────────────────┘
These are the risk ranks of the various buildings within this dataset.
COPY (
FROM 'yokohama-shi-14100_building_risk_tsunami.parquet' a
JOIN 'yokohama-shi-14100_building_lod0.parquet' b ON (a.building_id = b.building_id)
) TO 'yokohama-shi-14100_building_risk_tsunami.footprints.parquet' (
FORMAT 'PARQUET',
CODEC 'ZSTD',
COMPRESSION_LEVEL 22,
ROW_GROUP_SIZE 15000);
Thank you for taking the time to read this post. I offer both consulting and hands-on development services to clients in North America and Europe. If you'd like to discuss how my offerings can help your business please contact me via LinkedIn.