如何在多行数据上应用表值UDF生成球面正多边形？

原表值UDF定义 #

用于生成球面正多边形顶点的表值UDF如下：

CREATE OR REPLACE TEMPORARY FUNCTION sphere_regular_polygon_vertices(
  lon1 float,
  lat1 float,
  s float,
  n integer
)
RETURNS table (i integer, a float, lon2 float, lat2 float)
IMMUTABLE
COMMENT = $$
在球面上构造正多边形。

参数说明：
  lon1: 起始经度（弧度）
  lat1: 起始纬度（弧度）
  s: 多边形半径（中心角弧度：目标表面距离 ÷ 球体半径）
  n: 顶点数量（有效多边形至少需要3个顶点）

返回列说明：
  i: 行号
  a: 顶点方位角
  lon2: 顶点经度（弧度）
  lat2: 顶点纬度（弧度）
$$
AS $$
select
  i, a, lon2, lat2
from (
  select
    
    row_number() over (order by null) as i,
    
    (n - i) / n * (2 * pi()) as a,
    
    asin(
      sin(lat1) * cos(s) +
      cos(lat1) * sin(s) * cos(a)
    ) as lat2,
    
    lon1 + atan2(
      ( sin(a) * sin(s) * cos(lat1) ),
      ( cos(s) - sin(lat1) * sin(lat2) )
    ) as lon2

    from table(generator(rowcount => n))
)
$$;

单条数据生成多边形的方法 #

针对单个点，可通过以下查询生成GEOGRAPHY类型的多边形：

select
    array_agg([degrees(lon2), degrees(lat2)]) as coordinates,
    st_makepolygon(
      to_geography(
        {'type': 'LineString', 'coordinates': array_append(coordinates, coordinates[0])}
      )
    ) as polygon
from table(
  sphere_regular_polygon_vertices(
    radians(-105), radians(25),  -- 中心点：西经105°，北纬25°
    2000::float / 6371000,       -- 圆半径2公里，地球半径取6371公里
    8                            -- 8个顶点
  )
)

批量数据需求 #

现在需要对多行数据批量执行上述操作，现有测试数据：

CREATE OR REPLACE TEMPORARY TABLE mypoints AS
  SELECT $1::float as lon1, $2::float as lat1, $3::float as radius
  FROM (VALUES
    (-105, 25, 2000),
    (-105, 26, 3000),
    (-107, 27, 4000))

错误尝试及问题分析 #

尝试使用LATERAL关联查询时触发错误Unsupported subquery type cannot be evaluated，原代码如下：

SELECT
  array_agg([degrees(lon2), degrees(lat2)]) as coordinates,
  st_makepolygon(
    to_geography(
      {'type': 'LineString', 'coordinates': array_append(coordinates, coordinates[0])}
    )
  ) as polygon
FROM
  mypoints AS mp,
  LATERAL sphere_regular_polygon_vertices(
    radians(mp.lon1), radians(mp.lat1),
    mp.radius / 6371000,
    8
  )

问题根源：

1. 缺少GROUP BY子句，所有点的顶点会被无差别聚合，无法按单个中心点分组处理
2. 在st_makepolygon中直接引用同一层级生成的聚合列coordinates，逻辑上不合法

解决方案1：修正LATERAL关联查询 #

通过添加分组、调整聚合逻辑层级，实现批量生成：

WITH point_vertices AS (
  SELECT
    mp.lon1,
    mp.lat1,
    mp.radius,
    array_agg([degrees(lon2), degrees(lat2)]) as coordinates
  FROM mypoints AS mp
  LATERAL JOIN sphere_regular_polygon_vertices(
    radians(mp.lon1), radians(mp.lat1),
    mp.radius / 6371000,
    8
  )
  GROUP BY mp.lon1, mp.lat1, mp.radius
)
SELECT
  lon1,
  lat1,
  radius,
  coordinates,
  st_makepolygon(
    to_geography(
      {'type': 'LineString', 'coordinates': array_append(coordinates, coordinates[0])}
    )
  ) as polygon
FROM point_vertices;

解决方案2：封装为标量UDF #

编写一个标量UDF，直接输入参数即可返回GEOGRAPHY多边形，简化调用：

CREATE OR REPLACE TEMPORARY FUNCTION make_sphere_regular_polygon(
  lon1 float,
  lat1 float,
  radius_m float,
  n integer
)
RETURNS GEOGRAPHY
IMMUTABLE
COMMENT = $$
根据中心点经纬度、半径（米）和顶点数，生成球面上的正多边形地理对象
$$
AS $$
SELECT
  st_makepolygon(
    to_geography(
      {'type': 'LineString', 'coordinates': array_append(coord_array, coord_array[0])}
    )
  )
FROM (
  SELECT array_agg([degrees(lon2), degrees(lat2)]) as coord_array
  FROM table(sphere_regular_polygon_vertices(
    radians(lon1), radians(lat1),
    radius_m / 6371000,
    n
  ))
)
$$;

调用方式简洁直观：

SELECT
  lon1,
  lat1,
  radius,
  make_sphere_regular_polygon(lon1, lat1, radius, 8) as polygon
FROM mypoints;

内容的提问来源于stack exchange，提问作者shadowtalker