R开发者求助：MS Access中为位置匹配对应网格方格的方法

问题描述 #

作为R程序员，需在MS Access中完成以下任务：

• 现有一张网格参考表，存储每个网格方格的纬度（最小值、最大值）和经度（最小值、最大值）
• 另一张位置表包含多个地理位置的经纬度，需为每个位置分配对应的网格方格

R中的实现方式 #

在R中通过自定义函数结合case_when实现匹配，示例代码如下：

网格参考表与位置表定义 #

# 网格方格表
data2 <- data.frame(
  GS= c('OOOZ','WYAG','WYAH','WZAG','WZAH','WZAJ','WZAK','WZAL','WZAM','WZAN','XAAG','XAAH','XAAJ','XAAK','XAAL','XAAM','XAAN','XAAP','XAAQ'),
  Latitude= c(0,-47.633,-47.633,-47.883,-47.883,-47.883,-47.883,-47.883,-47.883,-47.883,-48.133,-48.133,-48.133,-48.133,-48.133,-48.133,-48.133,-48.133,-48.133),
  Longitude= c(0,-60.75,-60.25,-60.75,-60.25,-59.75,-59.25,-58.75,-58.25,-57.75,-60.75,-60.25,-59.75,-59.25,-58.75,-58.25,-57.75,-57.25,-56.75)
)

# 位置表
Data <- data.frame(Latitude = rnorm(100, -50, 3), Longitude = rnorm(100, -60, 3))

自定义匹配函数 #

# 纬度匹配函数
GS_Lat <-function(Latitude){
  case_when(
    Latitude <= -47.00 & Latitude > -47.25 ~ "WW",
    Latitude <= -47.25 & Latitude > -47.50 ~ "WX",
    Latitude <= -47.50 & Latitude > -47.75 ~ "WY",
    Latitude <= -47.75 & Latitude > -48.00 ~ "WZ",
    Latitude <= -48.00 & Latitude > -48.25 ~ "XA",
    TRUE ~ "OZ"
  )
}

# 经度匹配函数
GS_Lon <-function(Longitude){
  case_when(
    Longitude >= -64.00 & Longitude < -63.50 ~ "AA",
    Longitude >= -63.50 & Longitude < -63.00 ~ "AB",
    Longitude >= -63.00 & Longitude < -62.50 ~ "AC",
    Longitude >= -62.50 & Longitude < -62.00 ~ "AD",
    Longitude >= -62.00 & Longitude < -61.50 ~ "AE",
    Longitude >= -61.50 & Longitude < -61.00 ~ "AF",
    Longitude >= -61.00 & Longitude < -60.50 ~ "AG",
    Longitude >= -60.50 & Longitude < -60.00 ~ "AH",
    Longitude >= -60.00 & Longitude < -59.50 ~ "AJ",
    Longitude >= -59.50 & Longitude < -59.00 ~ "AK",
    Longitude >= -59.00 & Longitude < -58.50 ~ "AL",
    Longitude >= -58.50 & Longitude < -58.00 ~ "AM",
    Longitude >= -58.00 & Longitude < -57.50 ~ "AN",
    Longitude >= -57.50 & Longitude < -57.00 ~ "AP",
    Longitude >= -57.00 & Longitude < -56.50 ~ "AQ",
    TRUE ~ "OZ"
  )
}

拼接结果 #

paste(GS_Lat(Data$Latitude), GS_Lon(Data$Longitude))

Access中的现有尝试 #

1. Switch表达式：尝试用Switch构建查询列，但因表达式过于复杂，Access无法计算：

Longitude:=Switch([Longitude]<-63.5,"AA",[Longitude]<-63,"AB",[Longitude]<-62.5,"AC",[Longitude]<-62,"AD",[Longitude]<-61.5,"A",[Longitude]<-61,"A",[Longitude]<-60.5,"AG",[Longitude]<-60,"AH",[Longitude]<-59.5,"AJ",[Longitude]<-59,"AK",[Longitude]<-58.5,"AL",[Longitude]<-58,"AM",[Longitude]<-57.5,"AN",[Longitude]<-57,"AP",[Longitude]<-56.5,"AQ")

2. JOIN查询：修改为SQL JOIN方式，通过关联纬度范围表Northing和经度范围表Easting实现匹配：

SELECT Positions.Latitude, Northing.Grid, Easting.Grid, Positions.Longitude
FROM (Positions INNER JOIN Northing ON (Positions.Latitude < Northing.To) AND (Positions.Latitude >= Northing.From)) INNER JOIN Easting ON (Positions.Longitude < Easting.To) AND (Positions.Longitude >= Easting.From);

优化方案 #

方案1：使用VBA自定义函数（类似R的自定义函数） #

在Access中创建VBA模块，编写两个自定义函数分别处理纬度和经度的网格匹配，逻辑与R中的GS_Lat、GS_Lon一致：

1. 打开Access，按Alt + F11进入VBA编辑器
2. 插入新模块，输入以下代码：

Function GS_Lat(Latitude As Double) As String
    Select Case Latitude
        Case Is <= -47.00 And Is > -47.25
            GS_Lat = "WW"
        Case Is <= -47.25 And Is > -47.50
            GS_Lat = "WX"
        Case Is <= -47.50 And Is > -47.75
            GS_Lat = "WY"
        Case Is <= -47.75 And Is > -48.00
            GS_Lat = "WZ"
        Case Is <= -48.00 And Is > -48.25
            GS_Lat = "XA"
        Case Else
            GS_Lat = "OZ"
    End Select
End Function

Function GS_Lon(Longitude As Double) As String
    Select Case Longitude
        Case Is >= -64.00 And Is < -63.50
            GS_Lon = "AA"
        Case Is >= -63.50 And Is < -63.00
            GS_Lon = "AB"
        Case Is >= -63.00 And Is < -62.50
            GS_Lon = "AC"
        Case Is >= -62.50 And Is < -62.00
            GS_Lon = "AD"
        Case Is >= -62.00 And Is < -61.50
            GS_Lon = "AE"
        Case Is >= -61.50 And Is < -61.00
            GS_Lon = "AF"
        Case Is >= -61.00 And Is < -60.50
            GS_Lon = "AG"
        Case Is >= -60.50 And Is < -60.00
            GS_Lon = "AH"
        Case Is >= -60.00 And Is < -59.50
            GS_Lon = "AJ"
        Case Is >= -59.50 And Is < -59.00
            GS_Lon = "AK"
        Case Is >= -59.00 And Is < -58.50
            GS_Lon = "AL"
        Case Is >= -58.50 And Is < -58.00
            GS_Lon = "AM"
        Case Is >= -58.00 And Is < -57.50
            GS_Lon = "AN"
        Case Is >= -57.50 And Is < -57.00
            GS_Lon = "AP"
        Case Is >= -57.00 And Is < -56.50
            GS_Lon = "AQ"
        Case Else
            GS_Lon = "OZ"
    End Select
End Function

3. 保存模块，返回Access查询设计，在查询中直接调用函数并拼接结果：

SELECT Positions.Latitude, Positions.Longitude, 
       GS_Lat([Latitude]) & GS_Lon([Longitude]) AS GridCode
FROM Positions;

这种方式逻辑清晰，维护方便，避免了复杂的Switch表达式。

方案2：使用完整网格参考表做JOIN #

将网格的完整经纬度范围整理成一张独立的GridRef表，结构示例如下：

然后直接通过JOIN匹配位置与网格：

SELECT Positions.Latitude, Positions.Longitude, GridRef.GS
FROM Positions INNER JOIN GridRef 
ON (Positions.Longitude >= GridRef.LonMin) 
AND (Positions.Longitude < GridRef.LonMax) 
AND (Positions.Latitude >= GridRef.LatMin) 
AND (Positions.Latitude < GridRef.LatMax);

该方案无需编写函数，直接通过表关联实现，若网格规则有变化，只需更新GridRef表即可，扩展性更强。

方案3：优化现有JOIN查询 #

在现有JOIN基础上，直接拼接网格代码，同时确保Northing和Easting表的范围无重叠、无遗漏：

SELECT Positions.Latitude, Positions.Longitude, 
       Northing.Grid & Easting.Grid AS GridCode
FROM (Positions INNER JOIN Northing 
      ON (Positions.Latitude < Northing.To) AND (Positions.Latitude >= Northing.From)) 
INNER JOIN Easting 
ON (Positions.Longitude < Easting.To) AND (Positions.Longitude >= Easting.From);

此方案适合已维护好纬度、经度范围表的场景，语法简洁，执行效率较高。

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