select id, parse_json(names):primary as name, parse_json(categories):main as main_category, parse_json(addresses):list[0].element.country as country, parse_json(addresses):list[0].element.locality as locality, parse_json(addresses):list[0].element.postcode as postcode, parse_json(addresses):list[0].element.freeform as freeform, st_aswkt(geometry)as wkt from OVERTURE_MAPS__PLACES.CARTO.PLACE where parse_json(addresses):list[0].element.country ='JP' limit 1000 ; select id, parse_json(names):primary as name, parse_json(categories):main as main_category, parse_json(addresses):list[0].element.country as country, parse_json(addresses):list[0].element.locality as locality, parse_json(addresses):list[0].element.postcode as postcode, parse_json(addresses):list[0].element.freeform as freeform, st_aswkt(geometry)as wkt from OVERTURE_MAPS__PLACES.CARTO.PLACE where parse_json(addresses):list[0].element.country ='JP' limit 1000 ;
-- 東京都離島を除くポリゴンを利用 select a.id, parse_json(names):primary as name, parse_json(categories):main as main_category, parse_json(addresses):list[0].element.country as country, parse_json(addresses):list[0].element.locality as locality, parse_json(addresses):list[0].element.postcode as postcode, parse_json(addresses):list[0].element.freeform as freeform, st_aswkt(geometry)as wkt from OVERTURE_MAPS__PLACES.CARTO.PLACE a join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b on st_within(a.geometry, b.polygon_jp_pref_3_light)-- 東京都ポリゴン内のポイントだけを抽出 where b.pref_name ='東京都' limit 1000 ; -- 東京都離島を除くポリゴンを利用 select a.id, parse_json(names):primary as name, parse_json(categories):main as main_category, parse_json(addresses):list[0].element.country as country, parse_json(addresses):list[0].element.locality as locality, parse_json(addresses):list[0].element.postcode as postcode, parse_json(addresses):list[0].element.freeform as freeform, st_aswkt(geometry)as wkt from OVERTURE_MAPS__PLACES.CARTO.PLACE a join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b on st_within(a.geometry, b.polygon_jp_pref_3_light)-- 東京都ポリゴン内のポイントだけを抽出 where b.pref_name ='東京都' limit 1000 ;
結果はこんな感じです! お店から観光スポット、史跡など、幅広くカバーされています。
Buildingsデータセット
次に、buildingsで試してみましょう! 件数はこんな感じです。(Placesと二桁違う)
select count(id) from overture_maps__buildings.carto.building ; /* COUNT(ID) 2354376929 */ select count(id) from overture_maps__buildings.carto.building ; /* COUNT(ID) 2354376929 */
select a.id, st_aswkt(geometry)as wkt from overture_maps__buildings.carto.building a join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b on st_within(a.geometry, b.polygon_jp_pref_3_light) and b.pref_name ='東京都' limit 10 ; select a.id, st_aswkt(geometry)as wkt from overture_maps__buildings.carto.building a join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b on st_within(a.geometry, b.polygon_jp_pref_3_light) and b.pref_name ='東京都' limit 10 ;
with target_pref as( select st_xmax(polygon_jp_pref_3_light)as xmax, st_xmin(polygon_jp_pref_3_light)as xmin, st_ymax(polygon_jp_pref_3_light)as ymax, st_ymin(polygon_jp_pref_3_light)as ymin from PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 where pref_name ='東京都' ) select id, st_aswkt(geometry)as wkt from overture_maps__buildings.carto.building where parse_json(bbox):xmax <=(select xmax from target_pref) and parse_json(bbox):xmin >=(select xmin from target_pref) and parse_json(bbox):ymax <=(select ymax from target_pref) and parse_json(bbox):ymin >=(select ymin from target_pref) limit 10 ; with target_pref as( select st_xmax(polygon_jp_pref_3_light)as xmax, st_xmin(polygon_jp_pref_3_light)as xmin, st_ymax(polygon_jp_pref_3_light)as ymax, st_ymin(polygon_jp_pref_3_light)as ymin from PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 where pref_name ='東京都' ) select id, st_aswkt(geometry)as wkt from overture_maps__buildings.carto.building where parse_json(bbox):xmax <=(select xmax from target_pref) and parse_json(bbox):xmin >=(select xmin from target_pref) and parse_json(bbox):ymax <=(select ymax from target_pref) and parse_json(bbox):ymin >=(select ymin from target_pref) limit 10 ;