如何从Snowflake嵌套JSON坐标字段中提取经纬度最大最小值

实现思路 #

你之前的写法无效是因为MIN()函数需要作用于单值的多行数据集，不能直接对JSON数组做聚合计算，需要先把嵌套坐标数组拆分为单个坐标点的行级数据，再分别提取经纬度计算极值。

完整SQL示例 #

假设你的表名为YOUR_TABLE，且表内有id字段作为每行的唯一标识：

WITH point_extract AS (
  SELECT
    t.id,
    -- 提取单个坐标点的经度（坐标数组第0位）、纬度（坐标数组第1位）
    f.value[0]::FLOAT AS longitude,
    f.value[1]::FLOAT AS latitude
  FROM YOUR_TABLE t,
  -- 拆分多边形外环的坐标数组为单行坐标点
  LATERAL FLATTEN(input => t.ZONE:coordinates[0]) f
)
SELECT
  id,
  -- 拼接为要求的输出格式
  'MIN: ' || MIN(longitude) || ',' || MIN(latitude) AS min_coords,
  'MAX: ' || MAX(longitude) || ',' || MAX(latitude) AS max_coords
FROM point_extract
GROUP BY id;

特殊场景适配 #

如果你存储的多边形存在内环，需要将所有环的坐标纳入极值计算，调整FLATTEN逻辑遍历所有环即可：

WITH ring_extract AS (
  SELECT
    t.id,
    r.value AS ring_coords
  FROM YOUR_TABLE t,
  LATERAL FLATTEN(input => t.ZONE:coordinates) r
),
point_extract AS (
  SELECT
    id,
    f.value[0]::FLOAT AS longitude,
    f.value[1]::FLOAT AS latitude
  FROM ring_extract,
  LATERAL FLATTEN(input => ring_coords) f
)
SELECT
  id,
  'MIN: ' || MIN(longitude) || ',' || MIN(latitude) AS min_coords,
  'MAX: ' || MAX(longitude) || ',' || MAX(latitude) AS max_coords
FROM point_extract
GROUP BY id;

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