BigQueryで2拠点間の距離を算出する

BigQueryで2拠点間の距離を算出します

ライブラリのインストール
テーブルの作成
BigQueryで各拠点間の組み合わせを作成
BigQueryで2拠点間距離を算出
参考サイト

# ライブラリのインストール

!pip install --upgrade pandas-gbq 'google-cloud-bigquery[bqstorage,pandas]'

# テーブルの作成

# データフレームの作成

仮のデータを作成します。

import pandas as pd

# データフレームの作成
df = pd.DataFrame(data=[['仙台駅', 38.26063926000281, 140.8822763620918],
                        ['青葉城', 38.252377384994105, 140.85612549176253],
                        ['榴岡公園', 38.26066273495602, 140.89707530354525],
                        ['ラーメン二郎仙台店', 38.26181864469619, 140.8662996759938]],
                  columns=['name', 'lat', 'lon'])

display(df)

# BigQueryと接続

from google.cloud import bigquery

# keyとなるjsonファイルを読み込み
KEY_PATH = '****.json'

bq_client = bigquery.Client.from_service_account_json(KEY_PATH)

# データセットの生成¶

# データセットの生成
dataset_id = 'sample_data'
bq_client.create_dataset(dataset_id)

for ds in bq_client.list_datasets():
    print(ds.dataset_id)

# テーブルの作成

# データ型の確認
df_columns = df.dtypes
df_columns = pd.DataFrame(df_columns).reset_index()
df_columns = df_columns.rename(columns={'index': 'columns', 0: 'type'})

# スキーマ
schema = []
for i in range(len(df_columns)):
    column = df_columns['columns'][i]
    dtype = str(df_columns['type'][i]).replace('datetime64[ns]', 'DATETIME')\
                                      .replace('int64', 'INTEGER')\
                                      .replace('object', 'STRING')
    schema.append(bigquery.SchemaField(column, dtype, mode="NULLABLE"))

# table_idを生成（形式 : project_name.dataset_id.table_name）
dataset = bq_client.get_dataset(dataset_id)
table_id = '%s.%s.spot_table' % (dataset.project, dataset.dataset_id)

# テーブル生成
table = bigquery.Table(table_id, schema=schema)
bq_client.create_table(table)

# pandasからデータを投入

pandasのDataFrameからそのままデータを投入する

# Tableを取得
table = bigquery.Table(table_id, schema=schema)

# DataFrameを投入
bq_client.insert_rows_from_dataframe(table, df)

# テーブルの確認

import pandas as pd

query = f'''SELECT * 
            FROM {table_id}'''

# strベースのクエリと、project_idが必要
df_gbq = pd.read_gbq(query, table_id.split(".")[0])
display(df_gbq)

name	lat	lon
仙台駅	38.260639	140.882276
青葉城	38.252377	140.856125
榴岡公園	38.260663	140.897075
ラーメン二郎仙台店	38.261819	140.866300

# BigQueryで各拠点間の組み合わせを作成

「CROSS JOIN」より、BigQueryで各拠点間の組み合わせを作成します。

import pandas as pd

query = f'''
        WITH t1 AS (
            SELECT
            t0.name AS o_name, t0.lat AS o_lat, t0.lon AS o_lon
            FROM 
                {table_id} AS t0 
        ),
        t2 AS (
            SELECT
            t0.name AS d_name, t0.lat AS d_lat, t0.lon AS d_lon
            FROM 
                {table_id} AS t0 
        )
        SELECT
            *
        FROM 
            t1 
        CROSS JOIN t2'''

# strベースのクエリと、project_idが必要
df_gbq = pd.read_gbq(query, table_id.split(".")[0])
display(df_gbq)

o_name	o_lat	o_lon	d_name	d_lat	d_lon
榴岡公園	38.260663	140.897075	榴岡公園	38.260663	140.897075
榴岡公園	38.260663	140.897075	仙台駅	38.260639	140.882276
榴岡公園	38.260663	140.897075	ラーメン二郎仙台店	38.261819	140.866300
榴岡公園	38.260663	140.897075	青葉城	38.252377	140.856125

# BigQueryで2拠点間距離を算出

「st_distance」より、BigQueryで2拠点間距離(m)を算出します。

import pandas as pd

query = f'''
        WITH t1 AS (
            SELECT
            t0.name AS o_name, t0.lat AS o_lat, t0.lon AS o_lon
            FROM 
                {table_id} AS t0 
        ),
        t2 AS (
            SELECT
            t0.name AS d_name, t0.lat AS d_lat, t0.lon AS d_lon
            FROM 
                {table_id} AS t0 
        )
        SELECT
            *, 
            st_distance(
                st_geogpoint(o_lon, o_lat),
                st_geogpoint(d_lon, d_lat)
            ) as distance
        FROM (
            SELECT
                *
            FROM 
                t1 
            CROSS JOIN t2)'''

# strベースのクエリと、project_idが必要
df_gbq = pd.read_gbq(query, table_id.split(".")[0])
display(df_gbq)

o_name	o_lat	o_lon	d_name	d_lat	d_lon	distance
ラーメン二郎仙台店	38.261819	140.866300	ラーメン二郎仙台店	38.261819	140.866300	0.000000
ラーメン二郎仙台店	38.261819	140.866300	青葉城	38.252377	140.856125	1375.247245
ラーメン二郎仙台店	38.261819	140.866300	仙台駅	38.260639	140.882276	1401.073901
ラーメン二郎仙台店	38.261819	140.866300	榴岡公園	38.260663	140.897075	2690.088942