Take Your Time

仕事や研究、コンピューターとの付き合い方

spatial join

大変だったのでメモ。 行政区域データからmultipolygonで境界データをとってきて、別のattributeテーブルにある2つの属性をポイントでジョインした上でサマライズ。 st_union_aggを見つけるまで苦労した。まあ、bigqueryのマニュアルにあったのだが。 geometry型はavgなどの集計が使えないので適宜st_関数を探す必要がある。

with t1 as (select N03_001 as prefecture, N03_004 as city, ST_GeogFromText(WKT) as polygon from kokuseisuuchi.kyokai ),
t2 as (select att1, att2, st_geogpoint(cast(longitude as numeric), cast(latitude as numeric)) as loc from data.attribute where longitude is not null and latitude is not null and longitude != "NA" and latitude != "NA") 
select prefecture, city, avg(att1) as att1, avg(att2) as att2, st_union_agg(t1.polygon) as polygon from t1 join t2 on st_contains(t1.polygon, t2.loc) group by 1,2;