如何在BigQuery中将东距和北距转换为BST时区的经纬度？

首先明确：东距（easting）和北距（northing）通常对应英国国家格网参考系统（OSGB36，EPSG:27700），而你需要的经度/纬度属于WGS84坐标系（EPSG:4326）。英国标准时区（BST）是时间时区，与经纬度转换本身无关，但如果后续需要基于经纬度处理BST时间，可参考最后补充内容。

在BigQuery中，你可以通过指定坐标系构造点、转换坐标系、提取经纬度的步骤完成需求：

示例SQL #

WITH sample_data AS (
  SELECT 538890 AS easting, 180966 AS northing -- 伦敦特拉法加广场的OSGB36参考坐标
)
SELECT
  easting,
  northing,
  -- 提取转换后的经度
  ST_X(ST_Transform(ST_GeogFromText(CONCAT('POINT(', easting, ' ', northing, ')'), 27700), 4326)) AS longitude,
  -- 提取转换后的纬度
  ST_Y(ST_Transform(ST_GeogFromText(CONCAT('POINT(', easting, ' ', northing, ')'), 27700), 4326)) AS latitude
FROM sample_data;

核心步骤说明 #

• ST_GeogFromText(..., 27700)：使用OSGB36坐标系（EPSG:27700）将东距、北距组合构造成地理点
• ST_Transform(..., 4326)：将OSGB36格式的坐标转换为通用的WGS84经纬度坐标系
• ST_X/ST_Y：分别从转换后的地理点中提取经度和纬度值

关于BST的补充处理 #

如果需要基于转换后的经纬度获取对应BST时间，可直接通过时区参数转换：

SELECT
  CURRENT_TIMESTAMP() AS utc_time,
  CURRENT_TIMESTAMP() AT TIME ZONE 'Europe/London' AS bst_time

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