ST_EXTENT还是ST_ENVELOPE in BigQuery？

Question

我想要与ST_EXTENT或ST_ENVELOPE相同的BigQuery，但是我找不到一种方法来运行这个查询：

SELECT REGEXP_EXTRACT(name, ', (..)') state
  , ST_EXTENT(ARRAY_AGG(urban_area_geom)) corners
  , COUNT(*) cities
FROM `bigquery-public-data.geo_us_boundaries.urban_areas`
GROUP BY state

这个查询的预期结果是一个按州分组的包围框列表，用于覆盖美国所有的城市地区。

Answer 1

我创建了一个特性请求，以获得ST_EXTENT()的本机实现。请添加您的投票和证据，说明您为什么需要此功能，以便团队能够优先考虑并随时向您通报任何进展：

• https://issuetracker.google.com/issues/148915449

同时，我能提供的最好的解决方案是：

• fhoffa.x.st_bounding_box()：一个天真的包围框UDF.

像这样使用它：

SELECT REGEXP_EXTRACT(name, ', (..)') state
  , fhoffa.x.st_bounding_box(ARRAY_AGG(urban_area_geom)).polygon 
  , COUNT(*) urban_areas
FROM `bigquery-public-data.geo_us_boundaries.urban_areas`
GROUP BY state

​

​

​

其背后的代码：

CREATE OR REPLACE FUNCTION fhoffa.x.st_bounding_box(arr ANY TYPE) AS ((
  SELECT AS STRUCT *
    , ST_MakePolygon(ST_GeogFromText(FORMAT('LINESTRING(%f %f,%f %f,%f %f,%f %f)',minlon,minlat,maxlon,minlat,maxlon,maxlat,minlon, maxlat))) polygon
  FROM (
    SELECT MIN(m.min_x) minlon, MAX(m.max_x) maxlon , MIN(m.min_y) minlat, MAX(m.max_y) maxlat
    FROM (
      SELECT 
        (SELECT AS STRUCT MIN(x) min_x, MAX(x) max_x, MIN(y) min_y, MAX(y) max_y FROM UNNEST(coords)) m
      FROM (
        SELECT ARRAY(
          SELECT STRUCT(
            CAST(SPLIT(c, ', ')[OFFSET(0)] AS FLOAT64) AS x, 
            CAST(SPLIT(c, ', ')[OFFSET(1)] AS FLOAT64) AS y
          )
          FROM UNNEST(REGEXP_EXTRACT_ALL(ST_ASGEOJSON(geog), r'\[([^[\]]*)\]')) c
        ) coords
        FROM UNNEST(arr) geog
      )
    )
  )
))

备注：

• 需要额外的努力才能使它与跨越-180线的几何图形一起工作。
• ，由于测地线的边缘，函数的结果不是一个真正的包围盒，即ST_Covers(box，geom)可能返回FALSE，图中的
• 在上面的图中并不期望每个州都被完全覆盖，只是它的城市区域。因此，如果这些未覆盖的角落中没有城市区域，则包围框是正确的。
• 下面的多边形构造将给出精确的“矩形”，但它们会变得更加复杂。--

ST_GEOGFROMGEOJSON(
  FORMAT('{"type": "Polygon", "coordinates": [[[%f,%f],[%f,%f],[%f,%f],[%f,%f],[%f, %f]]]}'
  , minlon,minlat,maxlon,minlat,maxlon,maxlat,minlon,maxlat,minlon,minlat)
)

​

​

我期待着你的意见和建议。