在Google表格中,我有一个包含纬度和经度坐标的列。该列表从A2:A1000中列出。我还有B1、C1和D1分别用于城市、州和国家的列。是否有一种公式或脚本可以读取坐标并在各自的列中提供城市、州和国家信息?我不知道如何使用JavaScript、XML、JSON、序列化PHP等内容,因此如果您的建议包括其中之一,请提供一些说明。提前感谢。
好的,我有一个近似解决方案。进入谷歌电子表格 > 工具 > 脚本编辑器,并将以下代码粘贴到空白项目中:
function reverse_geocode(lat,lng) {
Utilities.sleep(1500);
var response = Maps.newGeocoder().reverseGeocode(lat,lng);
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];
Logger.log('%s: %s, %s', result.formatted_address, result.geometry.location.lat,
result.geometry.location.lng);
return result.formatted_address;
}
}
然后在电子表格中使用这个公式:
=reverse_geocode(latitude_goes_here,longitude_goes_here)
例如,如果我的纬度在A2单元格,经度在B2单元格:=reverse_geocode(A2,B2)
这将提供完整的地址。我现在正在尝试弄清如何从地址中解析出国家。
/**
* Return the closest, human-readable address type based on the the latitude and longitude values specified.
*
* @param {"locality"} addressType Address type. Examples of address types include
* a street address, a country, or a political entity.
* For more info check: https://developers.google.com/maps/documentation/geocoding/intro#Types
* @param {"52.379219"} lat Latitude
* @param {"4.900174"} lng Longitude
* @customfunction
*/
function reverseGeocode(addressType, lat, lng) {
Utilities.sleep(1500);
if (typeof addressType != 'string') {
throw new Error("addressType should be a string.");
}
if (typeof lat != 'number') {
throw new Error("lat should be a number");
}
if (typeof lng != 'number') {
throw new Error("lng should be a number");
}
var response = Maps.newGeocoder().reverseGeocode(lat, lng),
key = '';
response.results.some(function (result) {
result.address_components.some(function (address_component) {
return address_component.types.some(function (type) {
if (type == addressType) {
key = address_component.long_name;
return true;
}
});
});
});
return key;
}
然后在电子表格中使用以下公式:
=reverseGeocode(address_type; latitude_goes_here; longitude_goes_here)
=reverseGeocode("locality"; A2; B2)
如果您想使用国家,可以使用以下方法:
=reverseGeocode("country"; A2; B2)
提取地址的额外功能:
/**
* Return the closest, human-readable address type based on the the address passed.
*
* @param {"locality"} addressType Address type. Examples of address types include
* a street address, a country, or a political entity.
* For more info check: https://developers.google.com/maps/documentation/geocoding/intro#Types
* @param {"Amsterdam"} address The street address that you want to geocode,
* in the format used by the national postal service
* of the country concerned. Additional address elements
* such as business names and unit, suite or floor
* numbers should be avoided.
* @customfunction
*/
function geocode(addressType, address) {
if (typeof addressType != 'string') {
throw new Error("addressType should be a string.");
}
if (typeof address != 'string') {
throw new Error("address should be a string.");
}
var response = Maps.newGeocoder().geocode(address),
key = "";
response.results.some(function (result) {
return result.address_components.some(function (address_component) {
return address_component.types.some(function (type) {
if (type === addressType) {
key = address_component.long_name;
}
});
});
});
return key;
}