def onegeojson():
result = {
"code": 1,
"data": None,
"msg": "显示任务图层"
}
task_id = request.args.get("task_id")
if not task_id:
result["code"] = 0
result["msg"] = "task_id缺失"
return jsonify(result)
sql = '''SELECT updated_at from predict_buildings where task_id = {task_id} order by updated_at desc limit 1'''
queryData = queryBySQL(sql.format(task_id=task_id))
row = queryData.fetchone()
updated_at = row[0]
sql = '''select st_asgeojson(geom),gid from "BUIA" where gid in (select a.gid from predict_buildings as a where task_id ={task_id}) '''
sql = '''SELECT jsonb_build_object ( 'type', 'FeatureCollection', 'features', jsonb_agg ( features.feature ) )
FROM (SELECT jsonb_build_object ( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON ( geom ) :: jsonb, 'properties', to_jsonb ( inputs ) - 'geom' ) AS feature
FROM ( SELECT gid, (ST_DUMP(geom)).geom::geometry(Polygon)as geom FROM "predict_buildings" WHERE task_id = '{task_id}' and updated_at = '{updated_at}' and status = 1) inputs) features; '''
queryData = queryBySQL(sql.format(task_id=task_id, updated_at = updated_at))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
row = queryData.fetchone()
result['data'] = row[0]
return jsonify(result)
def geojson():
extent = request.args.get("extent")
if not extent:
return jsonify("")
extentArr = extent.split(',')
if len(extentArr) != 4:
return jsonify("")
if float(extentArr[2]) - float(extentArr[0]) > 0.05 or float(
extentArr[3]) - float(extentArr[1]) > 0.04:
return jsonify("")
sql = '''SELECT
jsonb_build_object ( 'type', 'FeatureCollection', 'features', jsonb_agg ( features.feature ) )
FROM
(
SELECT
jsonb_build_object ( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON ( geom ) :: jsonb, 'properties', to_jsonb ( inputs ) - 'geom' ) AS feature
FROM
(
SELECT gid,geom AS geom
FROM "{buildings_table}" WHERE
geom @
ST_MakeEnvelope ( {extent}, {srid} )) inputs
) features; '''
queryData = queryBySQL(
sql.format(buildings_table=setting.BUILDINGS_TABLE,
extent=extent,
srid=4326))
row = queryData.fetchone()
return jsonify(row["jsonb_build_object"])
def onegeojson():
result = {"code": 1, "data": None, "msg": "ok"}
extent = request.args.get("extent")
if not extent:
result["code"] = 0
result["msg"] = "参数有误"
return jsonify(result)
# coords = extent.split(',')
sql = '''SELECT
jsonb_build_object ( 'type', 'FeatureCollection', 'features', jsonb_agg ( features.feature ) )
FROM
(
SELECT
jsonb_build_object ( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON ( geom ) :: jsonb, 'properties', to_jsonb ( inputs ) - 'geom' ) AS feature
FROM
(
SELECT gid,geom AS geom
FROM "predict_buildings" WHERE
geom @
ST_MakeEnvelope ( {extent}, {srid} )) inputs
) features; '''
queryData = queryBySQL(sql.format(extent=extent, srid=4326))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
row = queryData.fetchone()
result["data"] = row
return jsonify(result)
def get_one_job():
sql = '''SELECT task_id, extent, user_id, state, area_code, created_at, updated_at from task WHERE STATE =1 ORDER BY created_at ASC LIMIT 1'''
queryData = queryBySQL(sql)
row = queryData.fetchone()
if row:
return row
else:
return None
def doing_job():
IPADDR = SETTING.IPADDR
sql = '''SELECT * FROM "task" where state='2' and handler=''' + "'" + IPADDR + "'"
queryData = queryBySQL(sql)
row = queryData.fetchone()
if row:
return True
else:
return False
def get_one_job():
# sql = '''SELECT task_id, extent, user_id, state, area_code, updated_at from {task} WHERE STATE =1 ORDER BY task_id ASC LIMIT 1'''
sql = '''SELECT * from {task} WHERE STATE =1 and status =1 ORDER BY task_id ASC LIMIT 1'''
queryData = queryBySQL(sql.format(task=taskTable))
row = queryData.fetchone()
if row:
return row
else:
return None
def doing_job():
IPADDR = SETTING.IPADDR
sql = '''SELECT * FROM "{task}" where state='2' and handler=''' + \
"'" + IPADDR + "'"
queryData = queryBySQL(sql.format(task=taskTable))
row = queryData.fetchone()
if row:
return True
else:
return False
def get_job_num():
result = {"code": 1, "data": None, "msg": "任务数量查询成功"}
sql = '''SELECT count(*) from task WHERE state = 1 or state =2'''
queryData = queryBySQL(sql)
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
rows = queryData.fetchall()
result["data"] = rows
return jsonify(result)
def get_processing_job():
result = {"code": 1, "data": None, "msg": "任务查询成功"}
sql = '''SELECT task_id,user_id from task WHERE state =2''' # ,task.created_at 返回任务创建时间
queryData = queryBySQL(sql)
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
rows = queryData.fetchall()
result["data"] = rows
if result["data"] == []:
result["data"] = "没有正在执行的任务"
return jsonify(result)
def job_listen():
sql = '''SELECT task_id from {task} WHERE STATE =2 and updated_at+ '5 minute' <now()'''
queryData = queryBySQL(sql.format(task=taskTable))
rows = queryData.fetchall()
if rows:
for row in rows:
task_id = row[0]
with DB.auto_commit():
task = TASK.query.filter_by(task_id=task_id).first_or_404()
if not task:
continue
task.state = 4
DB.session.add(task)
def get(gid):
result = {"code": 1, "data": None, "msg": "ok"}
sql = '''select st_asgeojson(geom) as geojson from predict_buildings where gid ={gid}'''
queryData = queryBySQL(sql.format(gid=gid))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
if queryData.rowcount == 0:
result["code"] = 0
result["msg"] = "未查询到内容"
return jsonify(result)
row = queryData.fetchone()
result["data"] = json.loads(row["geojson"])
return jsonify(result)
def job_listen():
result = {"code": 1, "data": None, "msg": "队列运行正常"}
sql = '''SELECT task_id from task WHERE STATE =2 and updated_at+ '6 minute' <now()'''
queryData = queryBySQL(sql)
rows = queryData.fetchall()
if rows:
for row in rows:
task_id = row[0]
with DB.auto_commit():
task = TASK.query.filter_by(task_id=task_id).first_or_404()
task.state = 1
DB.session.add(task)
result['msg'] = '任务已重新排队'
else:
return
return result
def get_task_by_id(task_id):
result = {"code": 1, "data": None, "msg": "ok"}
# check params
if not task_id.isdigit():
result["code"] = 0
result["msg"] = "task_id not numbers"
return jsonify(result)
sql = '''SELECT task_id, extent, user_id, state, created_at, updated_at from task WHERE task_id = {task_id}'''
queryData = queryBySQL(sql.format(task_id=task_id))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
row = queryData.fetchone()
result["data"] = row
return jsonify(result)
def onegeojson():
result = {"code": 1, "data": None, "msg": "显示任务图层"}
task_id = request.args.get("task_id")
if not task_id:
result["code"] = 0
result["msg"] = "task_id缺失"
return jsonify(result)
sql = '''select st_asgeojson(geom),gid from "BUIA" where gid in (select a.gid from predict_buildings as a where task_id ={task_id}) '''
sql = '''SELECT jsonb_build_object ( 'type', 'FeatureCollection', 'features', jsonb_agg ( features.feature ) )
FROM (SELECT jsonb_build_object ( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON ( geom ) :: jsonb, 'properties', to_jsonb ( inputs ) - 'geom' ) AS feature
FROM ( SELECT gid,geom AS geom FROM "predict_buildings" WHERE task_id = {task_id} and status = 1) inputs) features; '''
queryData = queryBySQL(sql.format(task_id=task_id))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
row = queryData.fetchone()
if row['geojson']:
result["data"] = json.loads(row["geojson"])
else:
result['data'] = None
return jsonify(result)
def update_buildings():
result = {"code": 1, "data": None, "msg": "建筑物更新成功"}
# check params
if not request.json:
result['code'] = 0
result['msg'] = 'miss params.'
return jsonify(result)
paramsDic = request.json
params = json.loads(json.dumps(paramsDic))
if 'status' not in params:
result['code'] = 0
result['msg'] = 'miss status.'
return jsonify(result)
if 'gids' not in params and 'task_id' not in params:
result['code'] = 0
result['msg'] = 'miss gids or task_id.'
return jsonify(result)
if 'gids' in params and 'task_id' in params:
result['code'] = 0
result['msg'] = 'both have gids and task_id.'
return jsonify(result)
status = params['status']
def updateBuildBygids(gids):
for gid in gids:
build = PredictBuildings.query.filter_by(gid=gid).first_or_404()
if not build:
continue
build.status = status
with DB.auto_commit():
DB.session.add(build)
if "gids" in params:
gids = params['gids']
if not isinstance(gids, list):
result['code'] = 0
result['msg'] = 'gids not list type.'
return jsonify(result)
updateBuildBygids(gids)
if "task_id" in params:
task_id = params['task_id']
# builds = PredictBuildings.query.filter_by(task_id=task_id)
# if not builds:
# result['code'] = 0
# result['msg'] = 'task id not found'
# return jsonify(result)
# for build in builds:
# build.status = status
# DB.session.bulk_save_objects(builds)
sql = '''SELECT gid, task_id, extent, user_id, state, status from predict_buildings WHERE task_id = {task_id}'''
queryData = queryBySQL(sql.format(task_id=task_id))
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
rows = queryData.fetchall()
gids = []
for row in rows:
gid = row.gid
gids.append(gid)
updateBuildBygids(gids)
return jsonify(result)
def get_task_list():
result = {"code": 1, "data": None, "msg": "获取列表成功"}
# code = request.args.get('code')
page = request.args.get('page') or '1'
count = request.args.get('count') or '10'
# state = request.args.get('state')
area_code = request.args.get('area_code')
user_id = request.args.get('user_id')
# check params
if not page.isdigit():
result["code"] = 0
result["msg"] = "page not numbers"
return jsonify(result)
if not count.isdigit():
result["code"] = 0
result["msg"] = "count not numbers"
return jsonify(result)
# if not state.isdigit():
# result["code"] = 0
# result["msg"] = "state not numbers"
# return jsonify(result)
if not area_code.isdigit():
result["code"] = 0
result["msg"] = "area_code not numbers"
return jsonify(result)
if user_id and not user_id.isdigit():
result["code"] = 0
result["msg"] = "user_id not numbers"
return jsonify(result)
# 查询该用户所有任务
start = (int(page) - 1) * int(count)
sql = '''SELECT task_id, extent, originalextent, user_id, area_code, state, created_at, end_at from {task} WHERE status !=0 '''
# if state:
# sql = sql + ''' AND state='''+"'"+state+"'"
if user_id:
sql = sql + ''' AND user_id=''' + "'" + user_id + "'"
if area_code:
sql = sql + ''' AND area_code=''' + "'" + area_code + "'"
sql = sql + ''' ORDER BY created_at desc LIMIT {count} OFFSET {start}'''
queryData = queryBySQL(sql.format(task=taskTable, start=start,
count=count)) # 参数format
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
rows = queryData.fetchall()
tasks = []
for row in rows:
d = dict(row.items())
if row.state == 1:
# 查询目前排队情况
sql_order = '''select task_id from {task} where state = 1 and status = 1 ORDER BY task_id LIMIT 1'''
queryData_order = queryBySQL(
sql_order.format(task=taskTable)) # 参数format
first_task = queryData_order.fetchone()
if first_task:
first_id = first_task.task_id
d['rank'] = row.task_id - first_id + 1
else:
d['rank'] = None
# 调整originalExtent格式:'x1,y1,x2,y2'->[[x1,y1],[x2,y2]]
# if d['originalextent'] != None:
# orgnl_extent_list = d['originalextent'].split(',')
# [coorsx1,coorsy1] = [float(orgnl_extent_list[0]),float(orgnl_extent_list[1])]
# [coorsx2,coorsy2] = [float(orgnl_extent_list[2]),float(orgnl_extent_list[3])]
# d['originalextent'] = [[coorsx1,coorsy1],[coorsx2,coorsy2]]
# d['originalextent'] = str(d['originalextent'])
tasks.append(d)
result['data'] = tasks
return jsonify(result)
def get_task_list():
result = {"code": 1, "data": None, "msg": "获取列表成功"}
# code = request.args.get('code')
page = request.args.get('page') or '1'
count = request.args.get('count') or '10'
# state = request.args.get('state')
area_code = request.args.get('area_code')
user_id = request.args.get('user_id')
# check params
if not page.isdigit():
result["code"] = 0
result["msg"] = "page not numbers"
return jsonify(result)
if not count.isdigit():
result["code"] = 0
result["msg"] = "count not numbers"
return jsonify(result)
# if not state.isdigit():
# result["code"] = 0
# result["msg"] = "state not numbers"
# return jsonify(result)
if not area_code.isdigit():
result["code"] = 0
result["msg"] = "area_code not numbers"
return jsonify(result)
if user_id and not user_id.isdigit():
result["code"] = 0
result["msg"] = "user_id not numbers"
return jsonify(result)
# 查询该用户所有任务
start = (int(page) - 1) * int(count)
sql = '''SELECT task_id, extent, user_id, area_code, state, created_at, end_at from task WHERE status !=0 '''
# if state:
# sql = sql + ''' AND state='''+"'"+state+"'"
if user_id:
sql = sql + ''' AND user_id=''' + "'" + user_id + "'"
if area_code:
sql = sql + ''' AND area_code=''' + "'" + area_code + "'"
sql = sql + ''' ORDER BY updated_at desc LIMIT {count} OFFSET {start}'''
queryData = queryBySQL(sql.format(start=start, count=count)) # 参数format
if not queryData:
result["code"] = 0
result["msg"] = "查询语句有问题"
return jsonify(result)
rows = queryData.fetchall()
tasks = []
for row in rows:
d = dict(row.items())
if row.state == 1:
# 查询目前排队情况
sql_order = '''select task_id from task where state = 1 ORDER BY task_id LIMIT 1'''
queryData_order = queryBySQL(sql_order) # 参数format
first_task = queryData_order.fetchone()
if first_task:
first_id = first_task.task_id
d['rank'] = row.task_id - first_id + 1
else:
d['rank'] = None
tasks.append(d)
result['data'] = tasks
return jsonify(result)
def create_task_by_areacode():
result = {"code": 1, "data": None, "msg": "create bat task success！"}
areacode = request.args.get('areacode')
zoom = request.args.get('zoom') or '14' # 将区域范围分割成zoom级别瓦片大小的任务
zoom = eval(zoom)
if not areacode:
result['code'] = 0
result['msg'] = "no areacode params"
return jsonify(result)
quhuaTable = ''
if len(areacode) == 9: #FIXME:bug when null areacode
quhuaTable = SETTING.QUHUA_XIANG
elif len(areacode) == 6:
quhuaTable = SETTING.QUHUA_XIAN
elif len(areacode) == 4:
quhuaTable = SETTING.QUHUA_SHI
elif len(areacode) == 2:
quhuaTable = SETTING.QUHUA_SHENG
else:
result['code'] = 0
result['msg'] = "areacode not support"
return jsonify(result)
areacode = areacode.ljust(12, '0')
sql = """
SELECT
'{{"type": "Feature", "geometry": '
|| ST_AsGeoJSON(st_simplify(geom,0.001))
|| '}}' AS features
FROM {quhuaTable} WHERE code = '{areacode}'
"""
queryData = queryBySQL(sql.format(areacode=areacode,
quhuaTable=quhuaTable))
if not queryData:
result["code"] = 0
result["msg"] = "not found this area,areacode:" + areacode
return jsonify(result)
area_json = queryData.fetchone()
feature_map = collections.defaultdict(list)
# FIXME: fetchall will not always fit in memory...
for feature in area_json:
feature_map = geojson_parse_feature(zoom, 4326, feature_map,
json.loads(feature))
cover = feature_map.keys()
extents = []
for tile in cover:
w, s, n, e = transform_bounds("epsg:3857", "epsg:4326",
*xy_bounds(tile))
extent = [w, s, n, e]
extents.append(','.join([str(elem) for elem in extent]))
for extent in extents:
# originalExtent = extent
user_id = "ADMIN"
area_code = areacode
with DB.auto_commit():
task = TASK_ADMIN()
task.extent = extent
# task.originalextent = originalExtent
task.user_id = user_id
task.area_code = area_code
DB.session.add(task)
result['data'] = {"count": len(extents), "extent": extents}
return jsonify(result)
