PostgreSQL中Quadkey转瓦片中心点经纬度的SQL实现方案求助

方案1：创建PL/pgSQL函数 #

你可以创建一个可复用的函数，基于Web墨卡托瓦片系统的Quadkey编码规则完成转换：

CREATE OR REPLACE FUNCTION quadkey_to_center_lonlat(quadkey text)
RETURNS point AS $$
DECLARE
    z integer := length(quadkey);
    x bigint := 0;
    y bigint := 0;
    i integer;
    char char(1);
    tile_size_x double precision;
    tile_size_y double precision;
    lon double precision;
    lat double precision;
BEGIN
    FOR i IN 1..z LOOP
        char := substring(quadkey FROM i FOR 1);
        x := x << 1;
        y := y << 1;
        CASE char
            WHEN '0' THEN
                y := y + 1;
            WHEN '1' THEN
                x := x + 1;
                y := y + 1;
            WHEN '2' THEN
                NULL;
            WHEN '3' THEN
                x := x + 1;
            ELSE
                RAISE EXCEPTION '无效的Quadkey字符: %', char;
        END CASE;
    END LOOP;
    x := x >> 1;
    y := y >> 1;

    tile_size_x := 360.0 / (2 ^ z);
    tile_size_y := 170.1022575596 / (2 ^ z);

    lon := -180.0 + (x + 0.5) * tile_size_x;
    lat := 85.0511287798 - (y + 0.5) * tile_size_y;

    RETURN point(lon, lat);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用方式：

-- 转换示例Quadkey
SELECT quadkey_to_center_lonlat('12302313230320012');

返回结果为point类型，格式为(经度, 纬度)。

方案2：纯SQL查询（无需创建函数） #

如果不想创建函数，可直接用CTE完成转换：

WITH quadkey_input AS (
    SELECT '12302313230320012' AS quadkey -- 替换为你的Quadkey列
),
tile_calc AS (
    SELECT
        quadkey,
        length(quadkey) AS z,
        SUM(CASE WHEN substring(quadkey FROM i FOR 1) IN ('1','3') THEN 1 << (length(quadkey) - i) ELSE 0 END) AS x,
        SUM(CASE WHEN substring(quadkey FROM i FOR 1) IN ('0','1') THEN 1 << (length(quadkey) - i) ELSE 0 END) AS y
    FROM quadkey_input
    CROSS JOIN generate_series(1, length(quadkey)) AS i
    GROUP BY quadkey
)
SELECT
    quadkey,
    z AS tile_level,
    -180.0 + (x + 0.5) * (360.0 / (2 ^ z)) AS center_longitude,
    85.0511287798 - (y + 0.5) * (170.1022575596 / (2 ^ z)) AS center_latitude
FROM tile_calc;

关键说明 #

• Quadkey的长度对应瓦片层级（z），层级越高，瓦片越小，中心点精度越高
• 纬度范围限制在-85.0511到85.0511之间，这是Web墨卡托投影的有效纬度范围
• 仅支持合法Quadkey字符（0、1、2、3），无效字符会触发函数异常或导致SQL计算错误

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