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)