《高质量代码-智慧城市GIS平台数据表设计》一文介绍了项目中的数据库表设计。此文介绍优良合理的表设计给后端接口开发带来的便利性。
整个后端使用Python语言开发，tornado作为web框架，peewee作为ORM和数据库打交道。下面展示利用peewee操作数据库(读取)是多么的简单。
使用peewee定义表，注意ForeignKeyField和backref的用法
代码语言：python代码运行次数：0复制# -*- 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("厂"))
下面再演示稍微复杂的查询语句，注意group_by,order_by,以及fn.MAX,fn.MIN等聚合函数使用。
代码语言：python代码运行次数：0复制#查询所有流量监测站监测到的最新的电压数据，并关联地理站点表方便地图展示。
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
下面定义一个函数，函数参数是由web请求的参数传递过来的，然后以标准的geoJson格式将最终结果返回，注意为了防止一个语句太长不方便阅读和条件判断，我们将sql语句的每个过程分开写，但是还是生成一句sql语句在获取数据结果时执行。耐心看每个过程的使用，为了将数据转化为geojson中的feature，而且要将表中最终Data表数据转化为feature["properties"]，即[{'meterName':'Pressure','dataValue':500},{'meterName':'Voltage','value':500}]转为
{'properties':{'Pressure':500,'Voltage':500} }，所以我们使用了辅助函数convertFeatures和convertFeature
代码语言：python代码运行次数：0复制转为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
tornado web框架中对应的调用
代码语言：python代码运行次数：0复制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)这里使用模板来生成geoJson,模板txt如下：
代码语言：python代码运行次数：0复制{"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 %}
]}
生成的geoJson数据如下结构：
感谢Python，才使得这些如此容易。
