Spark での空間照会の実行

単純な例として、指定の位置 (ST_Point コンストラクターを使用して構成される) の特定の距離内にある病院を見つけます。

// register the data frame  as a table
hospitalsDf.createOrReplaceTempView("hospitals")

以下の照会では、NYC の Grand Central Station から 10 km 以内にある病院のリストが戻されます。

SELECT name, city, state
FROM hospitals
WHERE ST_Distance(location, ST_Point(-77.574722, 43.146732)) < 10000.0

点を含む多角形を特定する空間処理関数の例は、以下のとおりです。

1. ST_Contains(geom1, geom2) - geom2 の値が、geom1 によって識別される多角形に完全に含まれている場合は、TRUE を戻します。
2. ST_Within(geom1, geom2) - geom1 の値が、geom2 によって識別される多角形内にある場合は、TRUE を戻します。
3. ST_Intersects(geom1, geom2) - geom1 と geom2 が、接触、クロス、相互包含など、空間でなんらかの方法で交差している場合は、TRUE を戻します。

以下の各照会では、これらの関数のいずれかを使用して、指定した点を含む郡の名前が戻されます。

SELECT NAME 
FROM counties 
WHERE 
ST_Contains(shape, ST_Point(-74.237, 42.037))

SELECT NAME
FROM counties
WHERE
ST_Within(ST_Point(-74.237, 42.037), shape)

SELECT NAME
FROM counties
WHERE
ST_Intersects(shape, ST_Point(-74.237, 42.037))

SELECT name
FROM hospitals
WHERE
ST_Contains(ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'), location)

SELECT name 
FROM hospitals 
WHERE ST_Within(location, ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

SELECT name 
FROM hospitals 
WHERE ST_Intersects(location, ST_WKTToSQL('POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

各照会により、21 の病院のリストが戻されます。最初の 5 つの病院は以下のとおりです。

1. Marshall Hospital
2. Southwestern Medical Center
3. Hillcrest Hospital
4. Saint Lukes Hospital
5. Adventist Home

空間結合関数を使用して、特定の郡にある病院を検索することができます。例えば、以下の照会では、Dutchess 郡のすべての病院のリストが戻されます。

SELECT c.NAME, h.name 
FROM counties AS c, hospitals AS h 
WHERE c.NAME = 'Dutchess' 
AND ST_Intersects(c.shape, h.location)

この照会は、次の結果セットを生成します。

別の方法として、WHERE 節の空間述部と同等の SQL JOIN ... ON 表記を使用することもできます。 例えば、以下の照会は、前の照会と同じ結果セットを生成します。

SELECT h.name, c.NAME
FROM counties AS c
JOIN hospitals AS h
ON c.NAME = 'Dutchess'
AND ST_Intersects(h.location, c.shape)

以下の照会により、特定の病院がある郡の名前が戻されます。

SELECT c.NAME, h.name
FROM hospitals AS h, counties AS c
WHERE ST_Intersects(h.location, c.shape)
AND h.name = 'Vassar Brothers Hospital'

この照会は、次の結果セットを生成します。

"Vassar Brothers Hospital", "Dutchess"

この例では、郡の表内の州名で修飾することによって、New York 州の各郡内の病院に対する照会が実行されます。

SELECT c.NAME, h.name
FROM counties AS c, hospitals AS h
WHERE ST_Intersects(h.location, c.shape)
AND c.STATE_NAME='New York'
ORDER BY c.NAME, h.name

結果には、New York の 230 の病院のうち、最初の 5 つが表示されます。 病院の表のフィールドを使用して上記の照会を書き直すことによって、同じ結果を得ることができます。

SELECT c.NAME, h.name
FROM hospitals AS h, counties AS c
WHERE ST_Intersects(h.location, c.shape)
AND h.state='NY'
ORDER BY c.NAME, h.name

以下の例は、New York の郡ごとの病院数を要約したものです。

SELECT c.NAME, COUNT(h.name) AS hospital_count
FROM counties AS c, hospitals AS h
WHERE ST_Intersects(h.location, c.shape)
AND c.STATE_NAME='New York'
GROUP BY c.NAME

これにより、以下の表に示す最初の 5 つの結果 (順序は以下のとおりとは限りません) を伴う合計 45 個の結果が生成されます。 人口に対する病院数が不十分な郡を特定するには、各郡の病院当たりの人口が重要なメトリックとなる場合があります。2000 年の各郡の人口を使用して、この数値を計算することができます。

SELECT c.NAME, 
COUNT(h.name) AS hospital_count, 
c.POP2000 AS Population, 
c.POP2000/COUNT(h.name) AS people_per_hospital
FROM counties AS c, hospitals AS h
WHERE c.STATE_NAME='New York'
AND ST_Intersects(h.location, c.shape)
GROUP BY c.NAME, c.POP2000
ORDER BY people_per_hospital DESC

最初の 5 つについてのこの結果 (合計 45 レコード) が表示されます。 病床数、病院当たりの医師の数などの追加情報がある場合、医療の可能性のより良い測度を特定することができます。

マッピング・アプリケーション、特に Web マッピングの一般的なユース・ケースは、長方形領域内にあるオブジェクトを選択することです。これを行うには、長方形および ST_Intersects 空間述部を表す多角形を作成します。

SELECT name
FROM hospitals
WHERE ST_Intersects(location, ST_WKTToSQL(
 'POLYGON ((-74.0 42.0, -73.0 42.0, -73.0 43.0, -74.0 43.0, -74.0 42.0))'))

同じ機能を持つ別の空間述部に EnvelopesIntersect があります。これを使用して、長方形領域と交差するエンベロープを持つオブジェクトを選択することができます。 EnvelopesIntersect は、空間列の名前、長方形の左下角および右上角を表す 4 つの Double 値をパラメーターとしてとります。この空間述部は、長方形の範囲については ST_Intersects よりも使いやすく効率的です。

SELECT name
FROM hospitals
WHERE EnvelopesIntersect(location, -74.0, 42.0, -73.0, 43.0)

Google マップ、Yahoo! 地図、Bing Maps などのプロバイダーの広く使用されている Web マッピング API を使用して Web マッピング・アプリケーションを構築する場合、マップ上にカスタム・マーカーを配置する際に使用する経度と緯度の値を指定する必要があります。この情報は、以下のような照会で取得します。

SELECT name, ST_X(location) AS longitude, ST_Y(location) AS latitude
FROM hospitals
WHERE EnvelopesIntersect(location, -74.0, 42.0, -73.0, 43.0)

空間照会の別の一般的なタイプとして、特定の位置の指定した距離内の対象を検索する照会があります。Web マッピング・アプリケーションを使用してこのような情報を取得したことがあると思います。アプリケーションから、以下のような照会のために SQL 照会を発行することができます。

• 店舗から 16 km (10 マイル) 以内の顧客の検索
• 現在の位置から 500 メートル以内の ATM の検索
• 提案された店舗の位置から 10 km 以内の競合店の検索

ここでの主要な空間処理関数は ST_Distance です。この関数は、空間値の間の距離を計算し、結果をメートルで戻します。以下の照会によって、8 つの結果が生成されます。

SELECT name
FROM hospitals
WHERE ST_Distance(location, ST_Point(-74.237, 42.037)) < 46800
ORDER BY name

結果 上記と同じ照会を実行する別の方法として、ST_Buffer を使用することもできます。この場合、指定の形状の周りに円形のバッファーが作成され、そのバッファー内の目的の形状が特定されます。ST_Buffer 関数は、空間形状およびこの空間値の周りのバッファーへの距離 (メートル) をパラメーターとしてとります。結果は同じです。

SELECT name
FROM hospitals
WHERE
ST_Intersects(location,
  ST_Buffer(ST_Point(-74.237, 42.037), 46800.0))
ORDER BY name

引き続き上記の例を使用して、指定した点から半径約 46800 m (30 マイル) の内の各オブジェクトへの距離をこの照会で見つけることができます。以下に、その結果を示します。

SELECT name, ST_Distance(location, ST_Point(-74.237, 42.037)) AS distance
FROM hospitals
WHERE ST_Distance(location, ST_Point(-74.237, 42.037)) < 46800.0
ORDER BY distance

または、ST_Buffer を使用して空間的な関係を計算してから、距離を特定することができます。

SELECT name, ST_Distance(location, ST_Point(-74.237, 42.037)) AS distance
FROM hospitals
WHERE
  ST_Intersects(location,
  ST_Buffer(ST_Point(-74.237, 42.037), 46800.0))
ORDER BY distance

注意が必要となる重要な違いは、このパッケージの ST_Buffer が任意の形状のバッファリングをサポートし、その方法で計算するために使用できる点です。以下の点に注意してください。

• 大きい形状に対する ST_Buffer 照会は、高コストになる可能性があります。
• 形状数が多い場合、ユーザーはバッファーを別々に計算し、バッファーを列に保管して、保管されたバッファーに対して操作を実行することをお勧めします。

SELECT name, ST_Distance(location, ST_WKTToSQL(
 'LINESTRING (-74.0 42.0, -73.0 42.0)'))
FROM hospitals
WHERE ST_Intersects(location, ST_Buffer(ST_WKTToSQL(
 'LINESTRING (-74.0 42.0, -73.0 42.0)'), 46800.0))

これにより 31 の結果が戻され、最初の 5 つの結果は以下のようになります。