Geometry in SQL

説明

すべての地理空間操作に専用のステップが必要なわけではありません。単一行の列式で表現できる処理は、Table Extractステップの式セルとして、行がウェアハウスの外に出ることなくデータベース内で実行されます。そのため、ワークフローエンジンではなくテーブルの規模に合わせてスケールします。Alteryxの変換では、AlteryxのCreate PointsツールとDistanceツールにこの方法が選ばれます。

ポータブルなジオメトリ関数

Lakehouse v1とv2では、ジオメトリ関数の名称が異なります。たとえば、ST_MAKEGEOMPOINTに対してST_POINT、ST_ASWKTに対してST_ASTEXTです。geom_*ファミリーは、実行しているワークスペースに合った関数に解決されるため、1回書いた式が両方で動作します。

式	Lakehouse v1	Lakehouse v2
`func.geom_from_wkt(x)`	`ST_GEOMETRYFROMWKT`	`ST_GEOMETRYFROMTEXT`
`func.geom_point(lon, lat)`	`ST_MAKEGEOMPOINT`	`ST_POINT`
`func.geom_as_wkt(g)`	`ST_ASWKT`	`ST_ASTEXT`
`func.geom_contains(a, b)`	`ST_CONTAINS`	`ST_CONTAINS`
`func.geom_within(a, b)`	`ST_WITHIN`	`ST_CONTAINS`（引数を入れ替え）
`func.geom_x(g)`	`ST_X`	`ST_X`
`func.geom_y(g)`	`ST_Y`	`ST_Y`

ポイントの作成

経度の列と緯度の列からWKTポイントを作成します。

func.geom_as_wkt(
    func.geom_point(
        get_column(table, 'Longitude'),
        get_column(table, 'Latitude'),
    )
)

geom_as_wktは結果をWKTとして書き込みます。これは、PlaidCloudのすべての空間ステップが読み書きする形式です。

2点間の距離

距離は、ここで扱う操作の中で唯一、ポータブルな記述がないものです。2つのLakehouseでは、関数と戻り値の単位の両方が異なるため、式はバージョンによって異なります。

Lakehouse v2

ST_DISTANCE_SPHEREは4つの座標を受け取り、メートルで返します。

case(
    (
        and_(
            get_column(table, 'Origin').like('POINT%'),
            get_column(table, 'Destination').like('POINT%'),
        ),
        func.st_distance_sphere(
            func.geom_x(func.geom_from_wkt(get_column(table, 'Origin'))),
            func.geom_y(func.geom_from_wkt(get_column(table, 'Origin'))),
            func.geom_x(func.geom_from_wkt(get_column(table, 'Destination'))),
            func.geom_y(func.geom_from_wkt(get_column(table, 'Destination'))),
        ) * 0.0006213711922373339,
    ),
    else_=null,
)

単位	メートルからの係数
マイル	`0.0006213711922373339`
キロメートル	`0.001`
メートル	`1`
フィート	`3.280839895013123`

Lakehouse v1

geom_distanceは2つのジオメトリ間の平面距離を度で返すため、係数はメートルではなく度からの換算になります。

func.geom_distance(
    func.geom_from_wkt(get_column(table, 'Origin')),
    func.geom_from_wkt(get_column(table, 'Destination')),
) * 69.1709

単位	度からの係数
マイル	`69.1709`
キロメートル	`111.32`
メートル	`111320.0`
フィート	`364566.929`

平面距離であるため、これは赤道から離れるほど精度が下がる近似値です。商圏分析で一般的な局所的な距離であれば問題ありませんが、大陸をまたぐ距離には向きません。

v2の式にあるLIKE 'POINT%'のガードは重要です。ST_DISTANCE_SPHEREは4つの座標を受け取るため、どちらかの側にポイント以外のジオメトリがあると、誤ったものを黙って測定してしまいます。CASEは、そのような行にはnullを返します。

代わりにステップを使用する場合

目的	使用するもの
各行から別のテーブルの最も近い行までの距離	Spatial Find Nearestステップ
空間結合のフィルターとしての距離	空間照合
面積、長さ、重心、外接矩形	Spatial Infoステップ
ジオメトリの形状を変更する処理すべて	地理空間ステップ