xml_result_2_taos_db（工作太忙，仅仅作为记录）
import taos from pyproj import Proj, CRS from PIL import Image, ImageOps import xml.etree.ElementTree as ET import os import time import requests import platform from osgeo import gdal def get_taosdb_info(): host = "192.168.1.235" user = "root" password = "taosdata" database = "taishi" if platform.system().lower() == ‘linux‘: cfg = "/etc/taos" return host, user, password, database, cfg elif platform.system().lower() == ‘windows‘: cfg = "C:/TDengine/cfg" return host, user, password, database, cfg def get_Device_Name(): deviceName2Type = {‘person‘:1, ‘car‘:2, ‘road‘:3, ‘bridge‘:4, ‘gasStation‘:5, ‘other‘:6} return deviceName2Type def getConnecter(host, user, password, cfg): try: conn = taos.connect(host=host, user=user, password=password, config=cfg) print(‘database connect succeed‘) except Exception as err: print(‘database connect failed‘) raise err return conn def GetFilesFromXML(xmlpath): tree = ET.ElementTree(file=os.path.join(xmlpath)) # 打开文件，解析成一棵树型结构 root = tree.getroot() # 获取树型结构的根 input_fileName = str(root.find(‘filename_in‘).text) output_fileName = str(root.find(‘filename_out‘).text) return input_fileName, output_fileName ##get object annotation bndbox loc start def GetAnnotBoxLoc(AnotPath, lon_img=120.000000, lat_img=39.000000, pix_m_img=1.0): # AnotPath VOC标注文件路径 lon_img = lon_img lat_img = lat_img pix_m_img = pix_m_img tree = ET.ElementTree(file=os.path.join(AnotPath)) # 打开文件，解析成一棵树型结构 root = tree.getroot() # 获取树型结构的根 input_fileName = str(root.find(‘filename_in‘).text) output_fileName = str(root.find(‘filename_out‘).text) pic_anno = [] ObjectSet = root.findall(‘object‘) # 找到文件中所有含有object关键字的地方，这些地方含有标注目标 for Object in ObjectSet: ObjDic = {} ObjName = Object.find(‘name‘).text BndBox = Object.find(‘box‘) x1 = int(BndBox.find(‘xmin‘).text) # -1 #-1是因为程序是按0作为起始位置的 y1 = int(BndBox.find(‘ymin‘).text) # -1 x2 = int(BndBox.find(‘xmax‘).text) # -1 y2 = int(BndBox.find(‘ymax‘).text) # -1 geo_loc = xy2lonlat(x1, y1, x2, y2, lon_img, lat_img, pix_m_img) ObjDic[ObjName] = geo_loc pic_anno.append(ObjDic) return input_fileName, output_fileName, pic_anno # 返回矩形框目标中心的经纬度 # 输入：像素box边界，名称，影像左上角点的经纬度，以及pix_m分辨率（默认是1.0米） def xy2lonlat(xmin, ymin, xmax, ymax, lon, lat, pix_m=1.0): crs_3857 = CRS.from_epsg(3857) PRO = Proj(crs_3857) X, Y = PRO(lon, lat, inverse=False) delt_x = 0.5*(xmin + xmax) delt_y = 0.5*(ymin + ymax) X += delt_x * pix_m Y -= delt_y * pix_m lon_out, lat_out = PRO(X, Y, inverse=True) return [str(lon_out), str(lat_out)] def get_time_stamp(): ct = time.time() local_time = time.localtime(ct) data_head = time.strftime("%Y-%m-%d %H:%M:%S", local_time) data_secs = (ct - int(ct)) * 1000 time_stamp = "%s.%03d" % (data_head, data_secs) return time_stamp def get_time_string(): ct = time.time() local_time = time.localtime(ct) time_string = time.strftime("%Y%m%d%H%M%S", local_time) return time_string #发送位置信息到 sensorszh table POI 数据库 def save_geo_loc(pic_anno, deviceName2Type): objName_DB = [key for key, _ in deviceName2Type.items()] i = 0 for obj in pic_anno: for objName, lon_lat in obj.items(): if objName not in objName_DB: objName = ‘other‘ lon = lon_lat[0] lat = lon_lat[1] timestamp = get_time_string() timestamp = eval(timestamp)+i i = i+1 # print(timestamp) url = ‘http://192.168.1.220:8102/insert?deviceId=1&deviceName=‘+ str(objName) + ‘&devicetype=‘ + str(deviceName2Type[objName]) + ‘&lon=‘+ str(lon) +‘&lat=‘ + str(lat) + ‘&time=‘ + str(timestamp) r = requests.get(url) print(‘POI point insert into sensorszh table, status_code:‘, r.status_code) # data insert into anno_loc def save_xml_db(conn, database, xml_file, pic_anno, deviceName2Type): objName_DB = [key for key, _ in deviceName2Type.items()] original_pic_path, processed_pic_path = GetFilesFromXML(xml_file) cur = conn.cursor() try: cur.execute(‘use ‘ + database) except Exception as err: print(‘use ‘ + database + ‘failed,mabey the database is not exist!‘) raise err try: # sql = "DROP TABLE anno_loc" sql = "CREATE TABLE IF NOT EXISTS anno_loc(create_time TIMESTAMP, pic_name binary(50), obj_name binary(20), devicetype binary(10), lon binary(100),lat binary(100),original_pic_path binary(500), processed_pic_path binary(500), xml_anno binary(500))" cur.execute(sql) except Exception as err: raise err # anno_loc: "create_time, pic_name, obj_name, devicetype, lon, lat, original_pic_path, processed_pic_path, xml_anno" xml_anno = xml_file _, pic_name = os.path.split(original_pic_path) for obj in pic_anno: for objName, lon_lat in obj.items(): if objName not in objName_DB: objName = ‘other‘ lon = lon_lat[0] lat = lon_lat[1] devicetype = deviceName2Type[objName] create_time = get_time_stamp() try: sql = "insert into anno_loc values (‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)" % (create_time, pic_name, str(objName), str(devicetype), lon, lat, original_pic_path, processed_pic_path, xml_anno) print(sql) cur.execute(sql) conn.commit() print("insert into anno_loc succeed!!!") except Exception as err: raise err if cur: cur.close conn.commit() conn.close() def save_xml_matedata_db(conn, database, xml_file, task_type = "td"): # task_type = "td" or "rs" cur = conn.cursor() try: cur.execute(‘use ‘ + database) except Exception as err: print(‘use ‘ + database + ‘failed, mabey the database is not exist!‘) raise err try: # sql = "DROP TABLE files" sql = "CREATE TABLE IF NOT EXISTS files(create_time TIMESTAMP, ID binary(50), original_pic_path binary(500), processed_pic_path binary(500), original_pic_simp_path binary(500), processed_pic_simp_path binary(500), task_type binary(10))" cur.execute(sql) except Exception as err: raise err create_time = get_time_stamp() ID = str(eval(get_time_string())) original_pic_path, processed_pic_path = GetFilesFromXML(xml_file) original_pic_simp_path = original_pic_path.split(‘.‘)[-2]+"-S."+original_pic_path.split(‘.‘)[-1] processed_pic_simp_path = processed_pic_path.split(‘.‘)[-2]+"-S."+processed_pic_path.split(‘.‘)[-1] task_type = task_type# "td" or "rs" try: sql = "insert into files values (‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘,‘%s‘)" % (create_time, ID, original_pic_path, processed_pic_path, original_pic_simp_path, processed_pic_simp_path, task_type) print(sql) cur.execute(sql) conn.commit() print("insert into files succeed!!!") except Exception as err: raise err if cur: cur.close conn.commit() conn.close() def get_full_and_simplified_name_path(xml_file): input_pic_path, out_pic_path = GetFilesFromXML(xml_file) input_pic_simp_path = input_pic_path.split(‘.‘)[-2]+"-S."+input_pic_path.split(‘.‘)[-1] out_pic_simp_path = out_pic_path.split(‘.‘)[-2]+"-S."+out_pic_path.split(‘.‘)[-1] return input_pic_path, input_pic_simp_path, out_pic_path, out_pic_simp_path def resize_geotiff(input_path, dst_path, scale): ds = gdal.Open(input_path) # prj = ds.GetProjection() # print(prj) im_width = ds.RasterXSize # 栅格矩阵的列数 im_height = ds.RasterYSize # 栅格矩阵的行数 opt = gdal.WarpOptions(height=int(scale * im_height), width=int(scale * im_width)) path, filename = os.path.split(input_path) if ‘.tif‘ not in dst_path: dst_path = os.path.join(dst_path, filename) gdal.Warp(dst_path, input_path, options=opt) # input_path = ‘D:/inputTIF.tif‘ # dst_path = ‘D:/out.tif‘ # # dst_path = ‘D:/output‘ # resize_geotiff(input_path, dst_path, 0.5) def resize_common_picture(input_path, dst_path, scale): origin_img = Image.open(input_path) h, w = origin_img.size h_out, w_out = int(h*scale), int(w*scale) target_size = (h_out, w_out) scale_img = origin_img.copy() scale_img.thumbnail(target_size, Image.ANTIALIAS) scale_img.save(dst_path) # detection_into_tao_db def detection_into_tao_db(xml_file, lon_img, lat_img, pix_m_img, task_type="td", scale=0.5): host, user, password, database, cfg = get_taosdb_info() input_fileName, output_fileName, pic_anno = GetAnnotBoxLoc(xml_file, lon_img=lon_img, lat_img=lat_img, pix_m_img=pix_m_img) # poi to sensorszh table save_geo_loc(pic_anno, get_Device_Name()) print("poi to sensorszh table succeed!!!") # target insert into anno_loc conn = getConnecter(host, user, password, cfg) save_xml_db(conn, database, xml_file, pic_anno, get_Device_Name()) print("target insert into anno_loc succeed!!!") # xml_matedata insert into files conn = getConnecter(host, user, password, cfg) save_xml_matedata_db(conn, database, xml_file, task_type=task_type) # task_type = "td" or "rs" print("save_xml_matedata_db succeed!!!") # get_full_and_simplified_name_path input_pic_path, input_pic_simp_path, out_pic_path, out_pic_simp_path = get_full_and_simplified_name_path(xml_file) # 对 img 缩放 if ‘tif‘ in input_pic_path.split(‘.‘)[-1]: resize_geotiff(input_pic_path, input_pic_simp_path, scale=scale) resize_geotiff(out_pic_path, out_pic_simp_path, scale=scale) print("resize_geotiff succeed!!!") else: resize_common_picture(input_pic_path, input_pic_simp_path, scale=scale) resize_common_picture(out_pic_path, out_pic_simp_path, scale=scale) print("resize_common_picture succeed!!!") # segmentation_into_tao_db def segmentation_into_tao_db(xml_file, task_type="rs", scale=0.5): host, user, password, database, cfg = get_taosdb_info() # xml_matedata insert into files conn = getConnecter(host, user, password, cfg) save_xml_matedata_db(conn, database, xml_file, task_type=task_type) # task_type = "td" or "rs" print("save_xml_matedata_db succeed!!!") # get_full_and_simplified_name_path input_pic_path, input_pic_simp_path, out_pic_path, out_pic_simp_path = get_full_and_simplified_name_path(xml_file) # 对 img 缩放 if ‘tif‘ in input_pic_path.split(‘.‘)[-1]: resize_geotiff(input_pic_path, input_pic_simp_path, scale=scale) resize_geotiff(out_pic_path, out_pic_simp_path, scale=scale) print("resize_geotiff succeed!!!") else: resize_common_picture(input_pic_path, input_pic_simp_path, scale=scale) resize_common_picture(out_pic_path, out_pic_simp_path, scale=scale) print("resize_common_picture succeed!!!") if __name__ == ‘__main__‘: det_xml_file = ‘D:/研究/课题资料/申请后提交/验收-准备/coding4-yanshou/out-det/P0034.xml‘ lon_img = 120.000000 lat_img = 39.000000 pix_m_img = 1.0 detection_into_tao_db(det_xml_file, lon_img, lat_img, pix_m_img, task_type="td", scale=0.5) seg_xml_file = ‘D:/研究/课题资料/申请后提交/验收-准备/coding4-yanshou/out-seg/inputTIF.xml‘ segmentation_into_tao_db(seg_xml_file, task_type="rs", scale=0.5)
原文：https://www.cnblogs.com/jeshy/p/15308130.html