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
fromOVERTURE_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
fromOVERTURE_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
fromOVERTURE_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
fromOVERTURE_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
fromPREPPER_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
fromPREPPER_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;