处理 BigQuery GIS 数据

借助 BigQuery GIS,您可以分析 BigQuery 中的地理位置数据。地理位置数据也称为地理空间数据。

BigQuery GIS 向标准 SQL 中添加了对 GEOGRAPHY 数据类型的支持。GEOGRAPHY 数据类型表示地球表面的一个点集。点集是 WGS84 参考球体上的一组点、线和多边形,具有测地线边。

如需使用 GEOGRAPHY 数据类型,调用任一标准 SQL 地理位置函数即可。地理函数的输出呈现为 WKT(已知文本)。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 文件中的行类似。
  • FeatureCollection。特征集合是一组特征对象,类似于数据库中的表或含有许多行和列的 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 会假设输入中的多边形按如下方式定向:如果您按输入顶点顺序穿过多边形的边界,则多边形的内部在左侧。将地理位置对象导出到 WKT 和 WKB 字符串时,BigQuery GIS 使用同一规则。

使用 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 时,您可能会遇到来自其他工具的无效 WKT 或 GeoJSON 数据,这些数据无法转换为 GEOGRAPHY 列。例如,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)

例如,如果您的表格包含 COUNTRYSTATEZIP 列,请在该表中添加一列来存储这些列的合并版本。以下查询片段对此进行了演示:

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 运算符实现经过优化的空间联接:

未针对以下情况优化空间联接:

  • LEFT、RIGHT 或 FULL OUTER 联接
  • 在涉及 ANTI 联接的情况中
  • 空间谓词被否定时

仅当距离参数为常量表达式时,使用 ST_DWithin 谓词的联接才会优化。

导出空间数据

从 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 类型。

后续步骤

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面