如何在Snowflake中定义GEOGRAPHY点变量并在SQL中使用？

一、直接定义地理变量的可行性 #

你写的那种直接声明并赋值变量的方式，在大多数SQL环境里不能直接执行，因为SQL（除特定 procedural 扩展外）是声明式语言，变量定义需遵循对应数据库的语法规则。

以下是主流数据库的正确写法示例：

• PostgreSQL（使用psql客户端或PL/pgSQL）：

  -- 会话级变量
  \set point 'ST_MAKEPOINT(-2.6661587, 53.368992)::GEOGRAPHY'
  -- 或在PL/pgSQL块内使用局部变量
  DO $$
  DECLARE
    point GEOGRAPHY := ST_MAKEPOINT(-2.6661587, 53.368992);
  BEGIN
    -- 在此处执行使用变量的逻辑
    RAISE NOTICE 'Point value: %', point;
  END $$;
  

• Snowflake：

  -- 会话级变量
  SET point = ST_MAKEPOINT(-2.6661587, 53.368992);
  -- 后续查询中引用变量
  SELECT ST_DISTANCE($point, POLYGON) AS some_distance FROM ...;
  

二、绑定变量未设置的错误解决 #

你写的匿名块和外部SELECT是分离的，匿名块内的point是局部变量，外部查询无法访问，因此会报:point未设置的错误。可通过以下方法解决：

方法1：用WITH子句预计算点，嵌入查询 #

无需单独定义变量，将点的计算整合到查询逻辑中：

WITH point_cte AS (
  SELECT ST_MAKEPOINT(-2.6661587, 53) AS point
)
SELECT
  ST_DISTANCE(p.point, POLYGON) AS some_distance
FROM "bla"."di"."bla", point_cte p
ORDER BY some_distance ASC;

方法2：使用会话级变量 #

先设置会话级变量，再在查询中引用（不同数据库语法略有差异）：
以Snowflake为例：

SET point = ST_MAKEPOINT(-2.6661587, 53);

SELECT
  ST_DISTANCE($point, POLYGON) AS some_distance
FROM "bla"."di"."bla"
ORDER BY some_distance ASC;

以PostgreSQL（psql）为例：

\set point 'ST_MAKEPOINT(-2.6661587, 53)::GEOGRAPHY'

SELECT
  ST_DISTANCE(:'point', POLYGON) AS some_distance
FROM "bla"."di"."bla"
ORDER BY some_distance ASC;

方法3：在匿名块内执行完整查询 #

将查询逻辑放入匿名块中，直接使用局部变量：

BEGIN
  LET point GEOGRAPHY := ST_MAKEPOINT(-2.6661587, 53);
  -- 在块内直接执行查询
  SELECT
    ST_DISTANCE(point, POLYGON) AS some_distance
  FROM "bla"."di"."bla"
  ORDER BY some_distance ASC;
END;

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