328 lines
14 KiB
Python
328 lines
14 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""
|
||
县政府所在乡镇街道核验排查定时任务
|
||
每月1日9点运行,比对 ES administrative_mapping_45 索引中县政府所在街道与高德逆地理街道并发送审核邮件
|
||
"""
|
||
import os
|
||
import time
|
||
import requests
|
||
import pandas as pd
|
||
from datetime import datetime
|
||
from goojia_utils import ElasticSearchClient
|
||
from airflow import DAG
|
||
from airflow.operators.python import PythonOperator
|
||
from airflow.utils.email import send_email
|
||
import pendulum
|
||
|
||
# 高德 Web 服务 API Key
|
||
AMAP_KEY = "a34894d151fffff94f9a33bae3d5565c"
|
||
INDEX_NAME = "administrative_mapping_45"
|
||
ES_HOST = "192.168.0.206"
|
||
|
||
|
||
def get_amap_township(lon, lat, key=AMAP_KEY, max_retries=5):
|
||
"""
|
||
调用高德逆地理编码接口获取 township (乡镇/街道)
|
||
https://restapi.amap.com/v3/geocode/regeo?location=lon,lat&extensions=base&roadlevel=0
|
||
遇 CUQPS_HAS_EXCEEDED_THE_LIMIT (code 10021) 时 sleep 0.2 秒后重试
|
||
"""
|
||
url = "https://restapi.amap.com/v3/geocode/regeo"
|
||
params = {
|
||
"key": key,
|
||
"location": f"{lon},{lat}",
|
||
"extensions": "base",
|
||
"roadlevel": 0
|
||
}
|
||
for attempt in range(max_retries):
|
||
try:
|
||
resp = requests.get(url, params=params, timeout=5)
|
||
if resp.status_code == 200:
|
||
data = resp.json()
|
||
if data.get("status") == "1":
|
||
regeocode = data.get("regeocode", {})
|
||
addr_comp = regeocode.get("addressComponent", {})
|
||
township = addr_comp.get("township")
|
||
if isinstance(township, str):
|
||
return township.strip()
|
||
elif isinstance(township, list) and township:
|
||
return str(township[0]).strip()
|
||
return ""
|
||
else:
|
||
info = str(data.get("info", ""))
|
||
infocode = str(data.get("infocode", ""))
|
||
print(f"高德接口返回异常: {info}, code: {infocode}")
|
||
# 超出并发限制 (10021 / CUQPS_HAS_EXCEEDED_THE_LIMIT) 时等待 0.2 秒后重试
|
||
if infocode == "10021" or "CUQPS_HAS_EXCEEDED_THE_LIMIT" in info:
|
||
time.sleep(0.2)
|
||
continue
|
||
except Exception as e:
|
||
print(f"请求高德逆地理编码失败 (第{attempt+1}次): {e}")
|
||
time.sleep(0.5)
|
||
return ""
|
||
|
||
|
||
def get_amap_district_geocode(province, city, district, key=AMAP_KEY, max_retries=5):
|
||
"""
|
||
调用高德地理编码接口获取区域经纬度
|
||
https://restapi.amap.com/v3/geocode/geo?address=...&key=...
|
||
地址规则:省份+城市+区域 拼接(若城市与区域相同或城市为空/包含区域,避免重复拼接)
|
||
要求返回的 geocodes 中 level == '区县'
|
||
返回: "lon,lat" 或 ""
|
||
"""
|
||
url = "https://restapi.amap.com/v3/geocode/geo"
|
||
|
||
prov_str = str(province or "").strip()
|
||
city_str = str(city or "").strip()
|
||
dist_str = str(district or "").strip()
|
||
|
||
# 拼接地址:注意城市和区域有可能相同,避免重复拼接(如 海南省-文昌市-文昌市 -> 海南省文昌市)
|
||
addr_parts = [prov_str]
|
||
if city_str and city_str != prov_str:
|
||
addr_parts.append(city_str)
|
||
if dist_str and dist_str != city_str:
|
||
addr_parts.append(dist_str)
|
||
address = "".join(addr_parts)
|
||
|
||
params = {
|
||
"key": key,
|
||
"address": address
|
||
}
|
||
|
||
for attempt in range(max_retries):
|
||
try:
|
||
resp = requests.get(url, params=params, timeout=5)
|
||
if resp.status_code == 200:
|
||
data = resp.json()
|
||
if data.get("status") == "1":
|
||
geocodes = data.get("geocodes", [])
|
||
for item in geocodes:
|
||
if item.get("level") == "区县":
|
||
location = item.get("location", "")
|
||
if location and "," in location:
|
||
return location.strip()
|
||
# 如果没有精确匹配到 level=='区县'
|
||
return ""
|
||
else:
|
||
info = str(data.get("info", ""))
|
||
infocode = str(data.get("infocode", ""))
|
||
print(f"高德地理编码返回异常: {info}, code: {infocode}")
|
||
if infocode == "10021" or "CUQPS_HAS_EXCEEDED_THE_LIMIT" in info:
|
||
time.sleep(0.2)
|
||
continue
|
||
except Exception as e:
|
||
print(f"请求高德地理编码失败 (第{attempt+1}次): {e}")
|
||
time.sleep(0.5)
|
||
return ""
|
||
|
||
|
||
def check_gov_township_diff(**context):
|
||
"""
|
||
县政府所在乡镇街道比对主任务,支持从 dag_run.conf 或 params 中传入自定义 amap_key
|
||
"""
|
||
dag_run = context.get('dag_run')
|
||
conf = (dag_run.conf if dag_run else {}) or {}
|
||
params = context.get('params') or {}
|
||
amap_key = conf.get('amap_key') or conf.get('key') or params.get('amap_key') or AMAP_KEY
|
||
amap_key = str(amap_key).strip()
|
||
|
||
print(f"[{datetime.now()}] 使用的高德 API Key: {amap_key[:6]}******{amap_key[-4:] if len(amap_key) > 10 else ''}")
|
||
print(f"[{datetime.now()}] 开始连接 206 ES 集群...")
|
||
es_client = ElasticSearchClient(host=ES_HOST)
|
||
|
||
# 1. 查询 administrative_mapping_45 中的区域数据,排除超大围栏 shape
|
||
query_district = {
|
||
"_source": {
|
||
"excludes": ["shape", "geo_shape", "boundary", "geometry", "polygon"]
|
||
},
|
||
"query": {
|
||
"bool": {
|
||
"must": [
|
||
{"term": {"type": "区域"}}
|
||
]
|
||
}
|
||
}
|
||
}
|
||
|
||
print(f"[{datetime.now()}] 正在扫描区域数据 (type='区域')...")
|
||
district_docs = es_client.scan(index=INDEX_NAME, query=query_district)
|
||
|
||
diff_list = []
|
||
missing_loc_list = []
|
||
total_districts = 0
|
||
processed_count = 0
|
||
|
||
for hit in district_docs:
|
||
total_districts += 1
|
||
source = hit.get("_source", {})
|
||
doc_id = hit.get("_id")
|
||
|
||
province = str(source.get("province", "")).strip()
|
||
city = source.get("city", "")
|
||
district = str(source.get("district", "")).strip()
|
||
|
||
# 台湾省的行政区划跳过
|
||
if "台湾" in province:
|
||
continue
|
||
|
||
# 校验的是县政府驻扎地,如果区域是以“区”结尾(市辖区),直接跳过
|
||
if district.endswith("区"):
|
||
continue
|
||
|
||
mzb_district_code = source.get("mzb_district_code", "")
|
||
tjj_district_code = source.get("tjj_district_code", "")
|
||
location = source.get("location", {})
|
||
|
||
lon = location.get("lon") if isinstance(location, dict) else None
|
||
lat = location.get("lat") if isinstance(location, dict) else None
|
||
|
||
if not location or lon is None or lat is None or not str(lon).strip() or not str(lat).strip():
|
||
print(f"区域 {province}-{city}-{district}({mzb_district_code}) 缺少有效经纬度,调用高德地理编码获取...")
|
||
geo_location = get_amap_district_geocode(province, city, district, key=amap_key)
|
||
missing_loc_list.append({
|
||
"ES文档ID": doc_id,
|
||
"省份": province,
|
||
"城市": city,
|
||
"区域": district,
|
||
"民政部区划代码": mzb_district_code,
|
||
"统计局区划代码": tjj_district_code,
|
||
"经纬,维度": geo_location if geo_location else "",
|
||
"人工审核": "未审核",
|
||
"原因": "缺少有效经纬度(location)",
|
||
"登记时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
||
})
|
||
continue
|
||
|
||
processed_count += 1
|
||
if processed_count % 50 == 0:
|
||
print(f"已处理 {processed_count} 个县/市/旗, 当前发现差异 {len(diff_list)} 条, 缺少经纬度 {len(missing_loc_list)} 条...")
|
||
|
||
# 2. 调用高德 API 获取 township(传入可配置的 amap_key)
|
||
amap_township = get_amap_township(lon, lat, key=amap_key)
|
||
|
||
# 3. 根据民政部区划代码 mzb_district_code 查询该区域下 has_gov='是' 的街道
|
||
query_street = {
|
||
"_source": {
|
||
"excludes": ["shape", "geo_shape", "boundary", "geometry", "polygon"]
|
||
},
|
||
"query": {
|
||
"bool": {
|
||
"must": [
|
||
{"term": {"type": "乡镇街道"}},
|
||
{"term": {"mzb_district_code": str(mzb_district_code)}},
|
||
{"term": {"has_gov": "是"}}
|
||
]
|
||
}
|
||
},
|
||
"from": 0,
|
||
"size": 10
|
||
}
|
||
|
||
try:
|
||
res_street = es_client.elastic_client.search(index=INDEX_NAME, body=query_street)
|
||
street_hits = res_street.get("hits", {}).get("hits", [])
|
||
except Exception as e:
|
||
print(f"查询街道数据异常 [{mzb_district_code}]: {e}")
|
||
street_hits = []
|
||
|
||
es_gov_streets = []
|
||
for s_hit in street_hits:
|
||
s_source = s_hit.get("_source", {})
|
||
# street 字段名称可能为 street、name 或 township
|
||
s_name = s_source.get("street") or s_source.get("name") or s_source.get("township") or ""
|
||
if s_name:
|
||
es_gov_streets.append(s_name)
|
||
|
||
es_gov_street_str = ",".join(es_gov_streets)
|
||
|
||
# 4. 对比 ES 中的街道名称和高德 township
|
||
# 不一致的情况:没有标记街道、或者标记的街道与高德不匹配
|
||
is_diff = False
|
||
if not es_gov_streets:
|
||
# ES中没有标记 has_gov='是' 的乡镇街道
|
||
is_diff = True
|
||
else:
|
||
# 判断高德 township 是否在标记列表中(考虑后缀差异如镇/街道,或完全不相等)
|
||
if amap_township not in es_gov_streets:
|
||
# 简单清洗匹配(如去掉“街道/镇/乡”后是否相同)
|
||
cleaned_amap = amap_township.replace("街道", "").replace("镇", "").replace("乡", "") if amap_township else ""
|
||
cleaned_es = [s.replace("街道", "").replace("镇", "").replace("乡", "") for s in es_gov_streets]
|
||
if not cleaned_amap or cleaned_amap not in cleaned_es:
|
||
is_diff = True
|
||
|
||
if is_diff:
|
||
diff_list.append({
|
||
"ES文档ID": doc_id,
|
||
"省份": province,
|
||
"城市": city,
|
||
"区域": district,
|
||
"民政部区划代码": mzb_district_code,
|
||
"统计局区划代码": tjj_district_code,
|
||
"经度(lon)": lon,
|
||
"纬度(lat)": lat,
|
||
"ES库中县政府所在街道(has_gov=是)": es_gov_street_str if es_gov_street_str else "【未标记】",
|
||
"高德逆地理街道(township)": amap_township if amap_township else "【高德未返回】",
|
||
"排查时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
||
})
|
||
|
||
print(f"[{datetime.now()}] 扫描完成!共遍历有效区域: {processed_count} 个,缺少经纬度区域: {len(missing_loc_list)} 个,发现不一致/缺失街道: {len(diff_list)} 条")
|
||
|
||
# 5. 生成 Excel 并邮件发送(包含两个 Sheet)
|
||
tmp_dir = os.path.join(os.path.expanduser('~'), 'tmp', 'gov_diff')
|
||
os.makedirs(tmp_dir, exist_ok=True)
|
||
today_str = datetime.now().strftime('%Y%m%d_%H%M%S')
|
||
excel_path = os.path.join(tmp_dir, f"县政府所在乡镇街道差异排查_{today_str}.xlsx")
|
||
|
||
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
|
||
df_diff = pd.DataFrame(diff_list)
|
||
df_diff.to_excel(writer, sheet_name='街道差异名单', index=False)
|
||
|
||
df_missing = pd.DataFrame(missing_loc_list)
|
||
df_missing.to_excel(writer, sheet_name='缺少经纬度区域', index=False)
|
||
|
||
print(f"排查明细已写入 Excel: {excel_path} (含 '街道差异名单' 与 '缺少经纬度区域' 两个 Sheet)")
|
||
|
||
# 发送邮件
|
||
receiver = "wangwenyan@fangjia.com"
|
||
cc_receiver = "daijinshui@fangjia.com"
|
||
subject = f"【人工审核】县政府所在乡镇街道差异排查报告 - {datetime.now().strftime('%Y-%m-%d')}"
|
||
html_content = f"""
|
||
<h3>王老师:</h3>
|
||
<p>您好!</p>
|
||
<p>系统已完成 206 ES <code>{INDEX_NAME}</code> 索引中县政府所在乡镇街道(<code>has_gov='是'</code>)与高德逆地理街道(<code>township</code>)的比对排查,详情如下:</p>
|
||
<ul>
|
||
<li><b>比对县/市/旗区域数:</b> {processed_count} 个</li>
|
||
<li><b>发现街道差异/待核对记录数:</b> {len(diff_list)} 条(见 Sheet: 街道差异名单)</li>
|
||
<li><b>缺少经纬度待补全区域数:</b> {len(missing_loc_list)} 个(见 Sheet: 缺少经纬度区域)</li>
|
||
</ul>
|
||
<p>详细名单已整理在附件 Excel 中,请人工审核确认。</p>
|
||
"""
|
||
|
||
print(f"正在发送审核邮件至: {receiver}, 抄送: {cc_receiver} ...")
|
||
send_email(
|
||
to=receiver,
|
||
subject=subject,
|
||
html_content=html_content,
|
||
files=[excel_path],
|
||
cc=cc_receiver
|
||
)
|
||
print("邮件发送完成!")
|
||
|
||
|
||
# Airflow DAG 定义:每月1号上午9:00执行(北京时间)
|
||
with DAG(
|
||
dag_id="check_gov_township_diff_dag",
|
||
schedule="0 9 1 * *",
|
||
start_date=pendulum.datetime(2024, 1, 1, tz="Asia/Shanghai"),
|
||
catchup=False,
|
||
description="县政府所在乡镇街道核验排查(每月1日9点)",
|
||
params={
|
||
"amap_key": AMAP_KEY
|
||
},
|
||
tags=["poi", "gov", "audit"]
|
||
) as dag:
|
||
|
||
check_task = PythonOperator(
|
||
task_id="check_gov_township_diff_task",
|
||
python_callable=check_gov_township_diff
|
||
)
|