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)