高质量代码-智慧城市GIS平台后端代码

# -*- coding:utf-8 -*-
from peewee import *

from datetime import date,datetime
import itertools

db=SqliteDatabase('gis.db')
class BaseModel(Model):
    class Meta:
        database=db
class Station(BaseModel):
    name=CharField(null = False)
    address=CharField(null = True)
    latitude=DoubleField(null = True)
    longitude=DoubleField(null = True)
    x=DoubleField(null = True)
    y=DoubleField(null = True)
    
class Meter(BaseModel):
    name=CharField(null=True)
    unit=CharField(null=True)
class Species(BaseModel):
    name=CharField(null=True)


class Monitor(BaseModel):
    station=ForeignKeyField(Station,backref='monitors')
    species=ForeignKeyField(Species,backref='monitors')   
class MonitorMeter(BaseModel):
    monitor=ForeignKeyField(Monitor,backref='meters')
    meter=ForeignKeyField(Meter,backref='monitors')    
 
class Data(BaseModel):
    monitor=ForeignKeyField(Monitor,backref='data')
    meter=ForeignKeyField(Meter,backref='data')
    value=DoubleField(null=True)
    timestamp=DateTimeField(null=True)

class WaterSupplyPipe(BaseModel):
    ShpID=BigIntegerField(null=True)
    Diameter=DoubleField(null=True)
    Depth=DoubleField(null=True)
    Material=CharField(null=True)

#选择Station表所有记录
result=Station.Select()
#打印结果记录数量
print result.count()
#打印结果
for i in result:
    print i.name,i.x,i.y
#将结果直接以json格式打印出来
print list(r.dicts())

#关联Monitor和Station和Species,注意select,switch,join,alias的用法
result=Monitor.select(Station.name.alias('stationName'),Species.name.alias('speciesName'))\
.join(Station).switch(Monitor).join(Species)
#打印peewee生成的sql语句
print result.sql()

#根据名字模糊搜索Station表
Station.select().where(Station.name.contains("厂"))

#查询所有流量监测站监测到的最新的电压数据，并关联地理站点表方便地图展示。
result=Monitor.select(Station.name.alias('stationName'),Station.x.alias('stationX'),
Station.y.alias('stationY'),fn.MAX(Data.timestamp).alias('dataTimestamp'),Data.value.alias('dataValue'))
.join(Station).switch(Monitor).join(Data).join(Meter).where(Meter.name=='Pressure')
.switch(Monitor).join(Species).where(Species.name=='flowStations').group_by(Monitor.id)
#将日期数据格式化
result=map(lambda x:convertDate(x),result.dicts())

def convertDate(x):
    x['dataTimestamp']=datetime.strftime(x['dataTimestamp'],'%Y-%m-%d %H:%M:%S')
    return x

转为def getMonitorDataBySpecies(species,search,limit,offset):
    #print search,limit,offset
    
    result=Monitor.select(Species.name.alias('speciesName'),Monitor.id.alias('monitorId'),
    Station.name.alias('stationName'),Meter.name.alias('meterName'),Station.x.alias('stationX'),
    Station.y.alias('stationY'),fn.MAX(Data.timestamp).alias('dataTimestamp'),
    Data.value.alias('dataValue')).join(Station)
    print result.count()
    if search!='':
        result=result.where(Station.name.contains(search))
        print result.count()
    result=result.switch(Monitor).join(Data).join(Meter).switch(Monitor).join(Species)
    print result.count()
    if species!='ALL':
        result=result.where(Species.name==species)
        print result.count()
    result=result.group_by(Monitor.id,Meter.id)
    print result.count()
   
    result=map(lambda x:convertDate(x),result.dicts())
    print len(result)
    #这里使用自定义的分页，而不是sql语句的分页
    result=convertFeatures(result)[offset:limit+offset]
    print len(result)
    result=renderGeoJson('GeoJsonTemplate.txt',result)
    
    return result

def convertFeatures(data):
    data=sorted(data,key=lambda x:x['monitorId'])
    gpData=itertools.groupby(data,lambda x:x['monitorId'])
    groups=[]
    for k,g in gpData:
        
        groups.append(convertFeature(list(g)))
    return groups

def convertFeature(data):
    if(len(data)<0):
        return {}
    item=data[0].copy()
    fields=['meterName','dataValue']
    item['x']=item['stationX']
    item['y']=item['stationY']
    
    for field in fields:
        if item.has_key(field):
            item.pop(field)
    
    properties=dict(map(lambda x:[x[fields[0]],x[fields[1]]],data))
    
    item.update(properties)
    
    return item

class getRecentMonitorGeoJsonHandler(cross_originAllowed_Handler):
    def get(self,species):
        self.write('success')

    def post(self,species):
        search=self.get_argument('search','')
        limit=self.get_argument('limit',10)
        offset=self.get_argument('offset',0) 
        #print search
        result=dbTools.getMonitorDataBySpecies(species,search.strip(),int(limit),int(offset))
        self.write(result)

{"type":"FeatureCollection",
"features":[
{% for i in range(len(data)) %}
	{"type":"Feature",
	"geometry":{
		"type":"Point",
		"coordinates":[{{data[i]['x']}},{{data[i]['y']}}]
		},
	"properties":	{% raw json.dumps(data[i]) %}
	} {% if i<len(data)-1 %} ,{% else %} {% end %}
{% end %}
]}