使用 BigQuery GIS 資料

BigQuery GIS 可以讓您在 BigQuery 中分析地理資料,地理資料即是所謂的地理空間資料。

BigQuery GIS 新增了對 GEOGRAPHY 標準 SQL 資料型態的支援,此 GEOGRAPHY 資料型態代表地球表面上的地理資訊點集合。地理資訊點集合是參考 WGS84 上的測地線球面所集合而成的點、線與多邊形。

您可以透過呼叫其中一個標準 SQL 地理位置函式來使用 GEOGRAPHY 資料類型。 地理位置函式的輸出結果會以 WKT (well-known text) 呈現,WKT 以經度第一筆資料格式、緯度為第二筆資料格式。

地理空間資料格式

地球上的單一位置點都可以用 (longitude, latitude) 的敘述予以配對, 如需描述更為複雜的地理位置 (像是線與多邊形),只要資料滿足下列格式,BigQuery 都能夠讓您將地理空間資料載入 GEOGRAPHY 資料欄:

WKT 與 GeoJSON 之差異

幾何物件相對於空間特徵與特徵集合

使用空間資料時的常見物件類型包括以下項目:

  • 個別幾何或 GEOGRAPHY 值代表地球上的表面積,通常使用點、線、多邊形,或是點、線與多邊形的集合描述。幾何集合代表集合中所有形狀的空間聯集。
  • 空間特徵代表邏輯空間物件,結合了幾何與任意其他應用程式專用屬性。
  • 空間特徵集合是一組特徵物件。

WKT 是一種文字格式,用來描述個別的幾何形狀,通常使用點、線、含選用孔的多邊形,或點、線或多邊形的集合描述。舉例來說,採用 WKT 格式的資料點如下所示:

POINT(-121 41)

如要描述空間特徵,WKT 通常會嵌入某些容器檔案格式 (通常為 CSV 檔案) 或資料庫資料表中。檔案資料列或資料表資料列通常會對應於空間特徵。整個檔案或資料表會對應至特徵集合。

WKB 是 WKT 格式的二進位版本。

GeoJSON 是一種更複雜且以 JSON 為基礎的幾何和空間特徵格式。例如,採用 GeoJSON 格式的資料點會如下所示:

{ "type": "Point", "coordinates": [-121,41] }

GeoJSON 可用於描述下列其中之一:

  • 個別幾何物件。單一物件可以有複雜的空間形狀,使用點、線、含選用孔的多邊形的聯集形式描述。這與 WKT 格式的使用方法類似。
  • 特徵物件是由幾何加上其他任意命名屬性所集結而成的特徵物件。這與含 WKT 資料欄的 CSV 檔案中的一個資料列類似。
  • 特徵集合是一組特徵物件集合,類似於資料庫中的資料表,或是具有許多資料列和資料欄的 CSV 檔案。

BigQuery GIS 僅支援採用 GeoJSON 格式的個別幾何物件。BigQuery GIS 目前不支援 GeoJSON 特徵物件、特徵集合或 GeoJSON 檔案格式。

座標系與邊緣

WKT 格式並不提供座標系,因此 BigQuery GIS 自行定義了一個座標系。在 BigQuery GIS 中,WKT 資料點是指一個 WGS84 球體的表面位置,以經度和大地緯度的方式表示。邊緣是指兩個端點之間的球面測地線。在 GeoJSON 中,座標系會明確地由 WGS84 與平面邊緣表示。

如要在這兩種邊緣系統之間進行轉換,BigQuery GIS 會在必要時為線條加入額外的資料點,以便讓轉換後的邊緣序列維持在原始線條的 10 公尺範圍之內。此處理稱之為曲面細分或是非均勻致密化。截至目前為止,您尚無法直接控制曲面細分處理。

如要匯入具有球形邊緣的地理位置,請按照下列範例來使用 WKT:

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  mytable

如要匯入具有平面邊緣的地理位置 (通常稱為「幾何圖形」),請按照下列範例來使用 GeoJSON:

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  mytable

您也可以將原始 GeoJSON 欄位排除在結果之外:

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  mytable

請務必使用正確的格式。多數系統會宣稱它們支援 WKT 的解析地理功能 (而非幾何圖形),或是採用平面邊緣。在這種情形下,GeoJSON 應該用做格式交換。

您的座標應該先列出經度,再列出緯度。如果地理位置具有任何長線段或邊緣,則它們必須經過曲面細分處理,否則 BigQuery GIS 會將其詮釋為球形測地線,因此可能會無法對應至原始資料來源的座標系。

多邊形方向

在球體上,每個多邊形都有一個互補的多邊形。舉例來說,描述地球各大洲的多邊形會有一個描述地球海洋的多邊形。由於兩個多邊形都使用相同的界線環形描述,因此需要規則來解決在兩個多邊形中的模糊性 (兩個多邊形中,哪一個由指定的 WKT 字串描述)。

當您透過檔案或使用串流擷取功能載入 WKT 和 WKB 字串時,BigQuery GIS 會假設輸入項目中的多邊形方向如下:如果您按照輸入端點的順序周遊多邊形界線,多邊形的內部會在左側。BigQuery GIS 會使用相同的規則將地理位置物件匯出至 WKT 和 WKB 字串。

使用 ST_GeogFromText 函式從 WKT 字串建構地理位置物件時,有兩個選項可以判斷 WKT 字串描述的多邊形:

  1. (預設) 將輸入內容解譯為具有較小面積的多邊形。不假設定向多邊形 (oriented = FALSE)。

  2. 假設定向多邊形允許載入面積大於一個半球的多邊形 (oriented = TRUE)。

載入 GeoJSON 字串時,這些規則並不適用。由於 GeoJSON 字串在平面地圖上進行定義,即使輸入內容未遵照 GeoJSON RFC 7946 第 3.1.6 節的方向規則定義 (多邊形:外環為逆時針方向,內環為順時針方向),也可以明確地判定方向。

載入 BigQuery GIS 資料

將 BigQuery GIS 資料載入 BigQuery 時,您可以在資料表的結構定義中指定 GEOGRAPHY 資料欄。將該欄位的資料類型指定為 GEOGRAPHY 之後,BigQuery GIS 即可偵測該資料採用 WKT 格式或 GeoJSON 格式。

將 GeoJSON 幾何物件載入 GEOGRAPHY 欄位後,系統應該會將其格式化為文字字串,而非 JSON 物件,即使該物件是從以換行符號分隔的 JSON 檔案載入也是如此。

如果您使用結構定義自動偵測功能來載入資料,系統會將地理位置值載入為 STRING。截至目前為止,結構定義自動偵測尚無法測得地理相關欄位。

如要進一步瞭解如何將資料載入 BigQuery,請參閱從 Cloud Storage 載入資料簡介一文。

轉換 BigQuery GIS 資料

如果您的資料表包含獨立的經度與緯度資料欄,則可以透過標準 SQL 地理位置函式 (例如 ST_GeogPoint),將這些值轉換為地理位置。舉例來說,如果您有兩個 DOUBLE 欄位分別存放經度與緯度,則可以使用下列查詢建立一個地理位置欄位:

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  mytable

BigQuery 目前支援將 WKT 與 GeoJSON 字串轉換成地理位置類型。Shapefile 以及許多其他格式應由外部工具予以事先轉換。

處理未正確格式化的空間資料

當您載入資料至 BigQuery 時,您可能會遇到來自其他工具,無法轉換為 GEOGRAPHY 資料欄的無效 WKT 或 GeoJSON 資料,像是 Edge K has duplicate vertex with edge N 錯誤,即表示多邊形具有重複的頂點 (除第一個與最後一個以外)。

為避免格式化問題,您可以建立一個函式讓輸出結果符合標準。例如,當您從 PostGIS 匯出資料時,您可以使用 ST_MakeValid 函式來將輸出標準化。

如要查找或忽略格式不正確的資料,請使用 SAFE 前綴函式輸出有問題的資料。例如,下列查詢使用 SAFE 前置字元來擷取格式不正確的空間資料。

SELECT
  geojson AS bad_geojson
FROM
  mytable
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

將 BigQuery GIS 資料分區及分群

您可以將包含 GEOGRAPHY 資料欄的資料表分區分群GEOGRAPHY 資料欄可做為分群資料欄使用,但 GEOGRAPHY 資料欄無法當成分區資料欄。

GEOGRAPHY 資料儲存至已分區或分群的資料表時,如果您的查詢使用空間述詞篩選資料,請確認您的地理位置資料在空間上是緊湊的。空間述詞會呼叫布林地理位置函式,並且會將 GEOGRAPHY 欄做為引數使用。下列範例展示如何透過 ST_DWithin 函式使用空間述詞:

WHERE ST_DWithin(geo, ST_GeogPoint(longitude, latitude), 100)

舉例來說,若您有一個包含 COUNTRYSTATE 以及 ZIP 資料欄的資料表,請新增一個欄來存放這些串連欄。下列查詢字串展示這項操作:

CONCAT(country, '+', IFNULL(state, ''), '+', IFNULL(zip, '')) as loc

在此範例中,IFNULL 用於刪減遺漏值。當您建立串連欄之後,便可將此欄用於叢集資料表。

將 JOIN 應用於空間資料

空間 JOIN 是指在 WHERE 子句中,將兩份具有述詞地理位置函式的資料表予以合併。例如:

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zcta_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.us_zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zcta_geom,
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC

當您的地理位置資料保持不變時,空間合併的效果會更好。上述範例在查詢中建立了地理位置值,但若將地理位置值存入 BigQuery 資料表的話將會有更加的效能表現。

舉例來說,下列查詢會擷取經度與緯度組合,並且將它們轉換成地理資料點。執行這項查詢時,您可以指定新的目的地資料表來儲存查詢結果。

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  mytable

BigQuery 可透過下列標準 SQL 述詞函式對 INNER JOIN 和 CROSS JOIN 運算子實作最佳化空間 JOIN:

下列項目的空間合併並未進行最佳化:

  • LEFT、RIGHT 或 FULL OUTER 合併
  • 如果涉及 ANTI 合併
  • 當空間述詞為否定時

使用 ST_DWithin 述詞的 JOIN 只會在距離參數為常數運算式時最佳化。

匯出空間資料

當您從 BigQuery 匯出空間資料時,GEOGRAPHY 欄值將一律格式化成 WKT 字串。如要以 GeoJSON 格式匯出資料,請使用 ST_AsGeoJSON 函式。

如果用於分析匯出資料的工具無法理解 GEOGRAPHY 資料類型,您可以使用地理位置函式 (例如 ST_AsTextST_AsGeoJSON),將資料欄值轉換為字串。BigQuery GIS 會在必要時為線條加上額外的資料點,以便讓轉換後的邊緣序列維持在原始測地線的 10 公尺範圍之內。

例如,下列查詢使用 ST_AsGeoJSON 將 GeoJSON 值轉換為字串。

SELECT
  ST_AsGeoJSON(ST_MakeLine(ST_GeogPoint(1,1), ST_GeogPoint(3,2)))

結果資料會如下所示:

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

GeoJSON 線條擁有兩個額外的資料點。BigQuery GIS 會新增這些資料點,這樣 GeoJSON 線條會和原始線條一樣貼近地面上的相同路徑。

使用 BigQuery 用戶端程式庫中的地理位置

目前只有 Python 適用的 BigQuery 用戶端程式庫支援 GEOGRAPHY 資料類型。如要使用其他用戶端程式庫,請透過 ST_ASTEXTST_ASGEOJSON 函式將 GEOGRAPHY 值轉換為字串。例如,使用 ST_AsText 函式:ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry

使用 ST_AsText 轉換為文字僅會儲存單一值,而轉換成 WKT 格式意謂著將資料標註為 STRING 類型 (而非 GEOGRAPHY 類型)。

後續步驟

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
需要協助嗎?請前往我們的支援網頁