# -*- 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"""

王老师:

您好!

系统已完成 206 ES {INDEX_NAME} 索引中县政府所在乡镇街道(has_gov='是')与高德逆地理街道(township)的比对排查,详情如下:

详细名单已整理在附件 Excel 中,请人工审核确认。

""" 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 )