如何在Snowflake中计算两邮编距离及判断地址/邮编是否在60英里内？

1. 准备邮编地理数据 #

Snowflake内置了美国邮编的示例数据集SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES，其中包含每个邮编对应的中心点地理信息（LOCATION字段，GEOGRAPHY类型）。如果使用自定义数据集，需将邮编对应的经纬度转换为GEOGRAPHY类型，可通过ST_POINT(longitude, latitude)函数实现。

2. 计算两个邮编之间的距离 #

使用Snowflake原生地理空间函数ST_DISTANCE，该函数返回两个地理点的球面距离（默认单位为米），可转换为英里（1英里≈1609.34米）。

示例SQL：

SELECT
    z1.ZIP AS zip_code_1,
    z2.ZIP AS zip_code_2,
    ROUND(ST_DISTANCE(z1.LOCATION, z2.LOCATION) / 1609.34, 2) AS distance_miles
FROM SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES z1
JOIN SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES z2
ON z1.ZIP = '90210' -- 第一个目标邮编
AND z2.ZIP = '10001'; -- 第二个目标邮编

3. 判断地址/邮编是否在60英里范围内 #

3.1 基于邮编的范围判断 #

直接通过ST_DISTANCE添加条件过滤即可：

SELECT
    z.ZIP AS target_zip,
    z.CITY,
    z.STATE
FROM SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES z
JOIN SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES z_ref
ON z_ref.ZIP = '90210' -- 参考邮编
WHERE ST_DISTANCE(z.LOCATION, z_ref.LOCATION) / 1609.34 <= 60;

3.2 基于地址的范围判断 #

若需直接使用地址，先通过GEOCODE函数（需启用Snowflake Search Service）解析地址为地理坐标，再进行范围判断：

WITH ref_address AS (
    SELECT GEOCODE('123 Main St, Los Angeles, CA') AS ref_location
)
SELECT
    z.ZIP,
    z.CITY
FROM SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES z, ref_address
WHERE ST_DISTANCE(z.LOCATION, ref_address.ref_location) / 1609.34 <= 60;

性能优化建议 #

• 为LOCATION字段创建地理空间索引：CREATE INDEX idx_zip_location ON SNOWFLAKE_SAMPLE_DATA.GEOGRAPHY.ZIPCODES(LOCATION);，可大幅提升大范围查询效率。
• 处理批量数据时，优先使用批量JOIN而非逐条查询，减少计算开销。
• 自定义数据集需确保GEOGRAPHY字段采用SRID 4326（WGS84坐标系，Snowflake默认）。

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