土砂災害防止法に基づいて都道府県が指定した土砂災害警戒区域・特別警戒区域のポリゴンを、急傾斜地の崩壊・土石流・地滑りの現象ごとに持ちます(国土数値情報 A33、2025 年度)。
SELECT prefecture_code, CASE WHEN ((prefecture_code = '01')) THEN ('北海道') WHEN ((prefecture_code = '02')) THEN ('青森県') WHEN ((prefecture_code = '03')) THEN ('岩手県') WHEN ((prefecture_code = '04')) THEN ('宮城県') WHEN ((prefecture_code = '05')) THEN ('秋田県') WHEN ((prefecture_code = '06')) THEN ('山形県') WHEN ((prefecture_code = '07')) THEN ('福島県') WHEN ((prefecture_code = '08')) THEN ('茨城県') WHEN ((prefecture_code = '09')) THEN ('栃木県') WHEN ((prefecture_code = '10')) THEN ('群馬県') WHEN ((prefecture_code = '11')) THEN ('埼玉県') WHEN ((prefecture_code = '12')) THEN ('千葉県') WHEN ((prefecture_code = '13')) THEN ('東京都') WHEN ((prefecture_code = '14')) THEN ('神奈川県') WHEN ((prefecture_code = '15')) THEN ('新潟県') WHEN ((prefecture_code = '16')) THEN ('富山県') WHEN ((prefecture_code = '17')) THEN ('石川県') WHEN ((prefecture_code = '18')) THEN ('福井県') WHEN ((prefecture_code = '19')) THEN ('山梨県') WHEN ((prefecture_code = '20')) THEN ('長野県') WHEN ((prefecture_code = '21')) THEN ('岐阜県') WHEN ((prefecture_code = '22')) THEN ('静岡県') WHEN ((prefecture_code = '23')) THEN ('愛知県') WHEN ((prefecture_code = '24')) THEN ('三重県') WHEN ((prefecture_code = '25')) THEN ('滋賀県') WHEN ((prefecture_code = '26')) THEN ('京都府') WHEN ((prefecture_code = '27')) THEN ('大阪府') WHEN ((prefecture_code = '28')) THEN ('兵庫県') WHEN ((prefecture_code = '29')) THEN ('奈良県') WHEN ((prefecture_code = '30')) THEN ('和歌山県') WHEN ((prefecture_code = '31')) THEN ('鳥取県') WHEN ((prefecture_code = '32')) THEN ('島根県') WHEN ((prefecture_code = '33')) THEN ('岡山県') WHEN ((prefecture_code = '34')) THEN ('広島県') WHEN ((prefecture_code = '35')) THEN ('山口県') WHEN ((prefecture_code = '36')) THEN ('徳島県') WHEN ((prefecture_code = '37')) THEN ('香川県') WHEN ((prefecture_code = '38')) THEN ('愛媛県') WHEN ((prefecture_code = '39')) THEN ('高知県') WHEN ((prefecture_code = '40')) THEN ('福岡県') WHEN ((prefecture_code = '41')) THEN ('佐賀県') WHEN ((prefecture_code = '42')) THEN ('長崎県') WHEN ((prefecture_code = '43')) THEN ('熊本県') WHEN ((prefecture_code = '44')) THEN ('大分県') WHEN ((prefecture_code = '45')) THEN ('宮崎県') WHEN ((prefecture_code = '46')) THEN ('鹿児島県') WHEN ((prefecture_code = '47')) THEN ('沖縄県') ELSE NULL END AS prefecture_name, phenomenon_code, CASE WHEN ((phenomenon_code = 1)) THEN ('急傾斜地の崩壊') WHEN ((phenomenon_code = 2)) THEN ('土石流') WHEN ((phenomenon_code = 3)) THEN ('地滑り') ELSE NULL END AS phenomenon, zone_code, CASE WHEN ((zone_code = 1)) THEN ('土砂災害警戒区域') WHEN ((zone_code = 2)) THEN ('土砂災害特別警戒区域') WHEN ((zone_code = 3)) THEN ('土砂災害警戒区域') WHEN ((zone_code = 4)) THEN ('土砂災害特別警戒区域') ELSE NULL END AS zone_type, CASE WHEN ((zone_code = 1)) THEN (CAST('t' AS BOOLEAN)) WHEN ((zone_code = 2)) THEN (CAST('t' AS BOOLEAN)) WHEN ((zone_code = 3)) THEN (CAST('f' AS BOOLEAN)) WHEN ((zone_code = 4)) THEN (CAST('f' AS BOOLEAN)) ELSE NULL END AS is_designated, zone_number, zone_name, address, notice_date, special_zone_unspecified, geometry FROM {DUCKLAKE_CATALOG}.hazard.stg_landslide_hazard_area