def near_rests(lat=34.985458, lng=135.757755, zoom=1, limit=100, lonely=True):
"""引数のlat(緯度)とlng(経度)を中心として、縦横margin*2の正方形ないにある
レストランをTabelogテーブルから取得する
デフォルト値は京都駅の緯度経度
"""
session = Session()
margin = _get_margin(zoom)
left, right = lng - margin, lng + margin
bottom, top = lat - margin, lat + margin
# lonely = u"一人で" if lonely else ""
lonely = u'and t.situation like "%一人で%"' if lonely else ""
# box = 'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))' \
# % (left, bottom, right, bottom, right, top, left, top, left, bottom)
# # % (x+m, y-m, x-m, y-m, x-m, y+m, x+m, y+m, x+m, y-m)
# point = 'POINT({lat} {lng})'
# # s = session.query(Tabelog.RestaurantName, gf.wkt(Tabelog.LatLng))\
# s = session.query(
# Tabelog.Rcd.label('rst_id'), Tabelog.RestaurantName, Tabelog.Category,
# # Tabelog.TabelogMobileUrl, Tabelog.TotalScore, Tabelog.Situation,
# # Tabelog.DinnerPrice, Tabelog.LunchPrice, Tabelog.Category,
# # Tabelog.Station, Tabelog.Address, Tabelog.Tel,
# # Tabelog.BusinessHours, Tabelog.Holiday,
# # gf.wkt(Tabelog.LatLng).label('Point'),
# gf.x(Tabelog.LatLng).label('lat'),
# gf.y(Tabelog.LatLng).label('lng'),
# gf.length(LineString(point.format(lat=lat, lng=lng),\
# gf.wkt(Tabelog.LatLng))).label('length'),\
# func.round(func.avg(UserPost.difficulty)).label('difficulty'),\
# func.avg(UserPost.difficulty).label('raw_difficulty'))\
# .filter(UserPost.rst_id == Tabelog.Rcd)\
# .filter(Tabelog.LatLng.within(box))\
# .order_by('length')\
# .group_by(UserPost.id)\
# .limit(limit).all()
# # .order_by(gf.distance(point.format(lat=lat, lng=lng),\
# # point.format(lat=gf.x(Tabelog.LatLng), lng=gf.y(Tabelog.LatLng))))\
# # .desc()\
try:
s = session.execute(
unicode(
"select t.Rcd as rst_id, t.RestaurantName, t.Category, "
"X(t.LatLng) as lat, Y(t.LatLng) as lng, "
"floor(avg(up.difficulty)+0.5) as difficulty,"
"avg(up.difficulty) as raw_difficulty, "
"GLength(GeomFromText(Concat('LineString("
"{lat} {lng}, ', Y(t.LatLng), ' ', X(t.LatLng), ')'))) as distance "
"from tabelog as t, user_post as up "
"where t.Rcd = up.rst_id and MBRContains(GeomFromText('"
"LineString({tr_lng} {tr_lat}, {bl_lng} {bl_lat})'), t.LatLng) "
"{lonely} " # 一人で？
"group by up.id order by distance asc limit {limit}"
).format(lat=lat, lng=lng, tr_lng=right, tr_lat=top, bl_lng=left, bl_lat=bottom, limit=limit, lonely=lonely)
)
results = [dict(result) for result in s.fetchall()]
except Exception, e:
print "errorin near_rsts", e
session.rollback()
results = False
