Files
airflow/dags/poi/dag_check_gov_township_diff.py

328 lines
14 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- 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
)