def db_get_easting_northing(lon, lat):
q = "SELECT ST_AsText(%s)" % db_coord_transform(
lon, lat, cs.WGS84_LATLON_CODE, cs.WEB_MERCATOR_CODE)
session = session_factory()
result = session.execute(q).fetchone()[0]
easting, northing = result.replace('POINT(', '').replace(')', '').split()
return round(float(easting)), round(float(northing))
def get_reportweek_for_contributor_and_tile(contributor, tile):
week_class = get_reportweek_class(current_week_number())
session = session_factory()
with session.begin(subtransactions=True):
result = session.query(week_class).filter(and_(week_class.contributor_id == contributor.id,
week_class.tile_id == tile.id))
return result.first() if result else None
def add_stumbles_for_contributor(email, display_name, query_json):
# Hack
json_object = query_json
session = session_factory()
with session.begin(subtransactions=True):
contributor = session.query(Contributor).filter_by(email=email).first()
if contributor is None:
contributor = Contributor(nickname=display_name,
email=email)
session.add(contributor)
elif contributor.nickname != display_name:
# Update the displayname
contributor.nickname = display_name
# Need to add to the session to mark as dirty
session.add(contributor)
for row in json_object['items']:
tile_coord = row[key_tile_easting_northing]
east, north = tile_coord.split(",")
tile = Tile.get_tile_mercator(int(float(east)), int(float(north)))
week_per_tile = insert_or_update_reportweek(contributor, tile)
obs = row[key_observations]
if not week_per_tile.observation_count:
week_per_tile.observation_count = 0
week_per_tile.observation_count += obs
return True
def set_country_for_tile(tile, use_intersect=False, use_nearby=False):
from tile import Tile
assert isinstance(tile, Tile)
geo = tile.geo_as_wgs84()
session = session_factory()
with session.begin(subtransactions=True):
result = session.query(CountryBounds).filter(
CountryBounds.wkb_geometry.ST_Contains(func.ST_Centroid(geo)))
c = result.first()
if not c or use_intersect:
result = session.query(CountryBounds).filter(
CountryBounds.wkb_geometry.ST_Intersects(geo))
c = result.first()
if not c or use_nearby:
# https://github.com/geoalchemy/geoalchemy2/issues/94 broken <->
# also doesn't work SELECT ogc_fid FROM country_bounds ORDER BY wkb_geometry <-> ST_Centroid('%s') LIMIT 1
nearby = '''
WITH index_query AS (
SELECT ST_Distance(wkb_geometry, '%s') as d, name, ogc_fid
FROM country_bounds
ORDER BY wkb_geometry <-> '%s' LIMIT 10)
SELECT ogc_fid
FROM index_query
ORDER BY d limit 1
''' % (geo, geo)
r = session.execute(nearby)
c_id = r.fetchone()[0]
c = session.query(CountryBounds).filter_by(ogc_fid=c_id).first()
tile.country = c
def add_stumbles_for_contributor(email, display_name, query_json):
# Hack
json_object = query_json
session = session_factory()
with session.begin(subtransactions=True):
contributor = session.query(Contributor).filter_by(email=email).first()
if contributor is None:
contributor = Contributor(nickname=display_name, email=email)
session.add(contributor)
elif contributor.nickname != display_name:
# Update the displayname
contributor.nickname = display_name
# Need to add to the session to mark as dirty
session.add(contributor)
for row in json_object['items']:
tile_coord = row[key_tile_easting_northing]
east, north = tile_coord.split(",")
tile = Tile.get_tile_mercator(int(float(east)), int(float(north)))
week_per_tile = insert_or_update_reportweek(contributor, tile)
obs = row[key_observations]
if not week_per_tile.observation_count:
week_per_tile.observation_count = 0
week_per_tile.observation_count += obs
return True
def geo_as_wgs84(self):
session = session_factory()
ewkt = "'" + session.execute(func.ST_AsEWKT(
self.geometry)).first().values()[0] + "'" + '::geometry'
# select ST_Transform('SRID=3785;POLYGON((-8839000 5419000,-8839000 5419500,-8838500 5419500,-8838500 5419000,-8839000 5419000))', 4326);
r = session.execute("select ST_AsEWKT(ST_Transform(%s, %d))" %
(ewkt, cs.WGS84_LATLON_CODE))
return r.fetchone()[0]
def get_reportweek_for_contributor_and_tile(contributor, tile):
week_class = get_reportweek_class(current_week_number())
session = session_factory()
with session.begin(subtransactions=True):
result = session.query(week_class).filter(
and_(week_class.contributor_id == contributor.id,
week_class.tile_id == tile.id))
return result.first() if result else None
def get_globals():
session = session_factory()
with session.begin(subtransactions=True):
try:
return session.query(LeaderboardGlobals).one()
except NoResultFound:
lg = LeaderboardGlobals()
session.add(lg)
return lg
def get_current_week():
g = LeaderboardGlobals.get_globals()
if not g.current_week or not 0 < g.current_week < 54:
session = session_factory()
with session.begin(subtransactions=True):
g.current_week = datetime.date.today().year
session.add(g)
return g.current_week
def create_one_contributor():
global contributor_counter
contributor_counter += 1
contributor = Contributor()
contributor.email = '[email protected]%d' % contributor_counter
contributor.nickname = 'nick%d' % contributor_counter
session = session_factory()
with session.begin(subtransactions=True):
session.add(contributor)
return contributor
def create_one_contributor():
global contributor_counter
contributor_counter += 1
contributor = Contributor()
contributor.email = '[email protected]%d' % contributor_counter
contributor.nickname = 'nick%d' % contributor_counter
session = session_factory()
with session.begin(subtransactions=True):
session.add(contributor)
return contributor
def get_tile_mercator(easting, northing):
session = session_factory()
with session.begin(subtransactions=True):
r = session.query(Tile).filter(func.ST_Contains(
Tile.geometry, func.ST_SetSRID(func.ST_Point(easting, northing), cs.WEB_MERCATOR_CODE))).first()
if r:
return r
ewkt = Tile.create_tile_ewkt_mercator(easting, northing)
tile = Tile()
tile.geometry = ewkt
CountryBounds.set_country_for_tile(tile)
return tile
def test_weekly_rollover(self):
# add data
self.test_add_contributor_tile_and_report()
week_class = reportweeks.get_current_reportweek_class()
session = session_factory()
rows = None
try:
rows = session.query(week_class).one()
except NoResultFound:
pass
assert rows
g = LeaderboardGlobals.get_globals()
g.current_week = reportweeks.current_week_number() - 1
# verify data gets truncated on next access
week_class = reportweeks.get_current_reportweek_class()
session = session_factory()
rows = None
try:
rows = session.query(week_class).one()
except NoResultFound:
pass
assert not rows
def get_tile_mercator(easting, northing):
session = session_factory()
with session.begin(subtransactions=True):
r = session.query(Tile).filter(
func.ST_Contains(
Tile.geometry,
func.ST_SetSRID(func.ST_Point(easting, northing),
cs.WEB_MERCATOR_CODE))).first()
if r:
return r
ewkt = Tile.create_tile_ewkt_mercator(easting, northing)
tile = Tile()
tile.geometry = ewkt
CountryBounds.set_country_for_tile(tile)
return tile
def insert_or_update_reportweek(contributor, tile):
week_class = get_current_reportweek_class()
db = get_db()
session = session_factory()
with session.begin(subtransactions=True):
if not db.table_exists(week_class.__tablename__):
db.get_metadata().create_all(db.engine)
existing = get_reportweek_for_contributor_and_tile(contributor, tile)
if existing:
return existing
w = week_class()
w.contributor_id = contributor.id
w.tile_id = tile.id
session.add(w)
return w
def insert_or_update_reportweek(contributor, tile):
week_class = get_current_reportweek_class()
db = get_db()
session = session_factory()
with session.begin(subtransactions=True):
if not db.table_exists(week_class.__tablename__):
db.get_metadata().create_all(db.engine)
existing = get_reportweek_for_contributor_and_tile(contributor, tile)
if existing:
return existing
w = week_class()
w.contributor_id = contributor.id
w.tile_id = tile.id
session.add(w)
return w
def drop_all(self):
from leaderboard.models.country_bounds import CountryBounds # NOQA
from leaderboard.models.contributor import Contributor # NOQA
from leaderboard.models.tile import Tile # NOQA
from leaderboard.models.reportweeks import get_current_reportweek_class
wk = get_current_reportweek_class()
if self.table_exists(wk.__tablename__):
t = wk.__table__
t.drop(get_db().engine)
for tbl in reversed(get_db().get_metadata().sorted_tables):
if self.table_exists(tbl.name):
get_db().engine.execute(tbl.delete())
tbl.drop(get_db().engine)
session = session_factory()
with session.begin(subtransactions=True):
session.expunge_all()
session.expire_all()
def drop_all(self):
from leaderboard.models.country_bounds import CountryBounds # NOQA
from leaderboard.models.contributor import Contributor # NOQA
from leaderboard.models.tile import Tile # NOQA
from leaderboard.models.reportweeks import get_current_reportweek_class
wk = get_current_reportweek_class()
if self.table_exists(wk.__tablename__):
t = wk.__table__
t.drop(get_db().engine)
for tbl in reversed(get_db().get_metadata().sorted_tables):
if self.table_exists(tbl.name):
get_db().engine.execute(tbl.delete())
tbl.drop(get_db().engine)
session = session_factory()
with session.begin(subtransactions=True):
session.expunge_all()
session.expire_all()
def load_countries():
import ogr
import os
pwd = os.path.dirname(os.path.abspath(__file__))
infile = "%s/../fixtures/world.geo.json" % pwd
drv = ogr.GetDriverByName('GeoJSON')
assert drv
ds = drv.Open(infile)
layer = ds.GetLayer(0)
srs = ogr.osr.SpatialReference()
srs.ImportFromEPSG(WGS84_LATLON_CODE)
session = session_factory()
with session.begin(subtransactions=True):
for feature in layer:
fid = feature.GetFID()
name = feature.GetField(0)
geo = feature.GetGeometryRef()
wkb = 'SRID=%d;%s' % (CountryBounds.coord_sys(), geo.ExportToWkt())
country = CountryBounds(ogc_fid=fid, wkb_geometry=wkb, name=name)
session.add(country)
def is_week_rolling_over():
""" Weeks 1-53 are stored in the db, and are reused as the year rolls over.
As week 1 turns into week2 (for example), week2 must be truncated before using it.
The current week is stored in the LeaderboardGlobals.
This function both checks for rollover, and updates the db to the new week.
:return: True if the actual week is greater than the current week in the db
"""
from leaderboard.models import reportweeks
week_in_db = LeaderboardGlobals.get_current_week()
actual_week = reportweeks.current_week_number()
is_rollover = actual_week != week_in_db
if is_rollover:
session = session_factory()
with session.begin(subtransactions=True):
lbGlobals = LeaderboardGlobals.get_globals()
lbGlobals.current_week = actual_week
session.add(lbGlobals)
return is_rollover
def get_reportweek_class(week_num):
assert (0 < week_num < 54)
tablename = 'reportweek%d' % week_num
if LeaderboardGlobals.is_week_rolling_over():
session = session_factory()
session.execute('truncate table %s;' % tablename)
if tablename in _week_classes_cache:
return _week_classes_cache[tablename]
class Week(Base):
__tablename__ = tablename
id = Column(Integer, primary_key=True)
contributor_id = Column(Integer, ForeignKey(Contributor.id))
contributor = relationship(Contributor, backref=backref(tablename))
tile_id = Column(Integer, ForeignKey(Tile.id))
tile = relationship(Tile)
observation_count = Column(BigInteger)
_week_classes_cache[tablename] = Week
return Week
def get_leaders_for_country(country_id):
week_class = reportweeks.get_current_reportweek_class()
session = session_factory()
q = session.query(contributor.Contributor,
func.sum(week_class.observation_count).label('obs_sum')). \
filter(tile.Tile.country_id == country_id). \
filter(week_class.contributor_id == contributor.Contributor.id). \
filter(week_class.tile_id == tile.Tile.id). \
group_by(contributor.Contributor.id). \
order_by(desc('obs_sum'))
result = q.all()
if result:
assert isinstance(result[0][0], contributor.Contributor)
assert isinstance(result[0][1], Decimal)
rows = []
for row in result:
rows.append({'name': row[0].nickname,
'observations': str(row[1])})
country_name = session.query(country_bounds.CountryBounds.name). \
filter_by(ogc_fid=country_id).first()
return {'country_name': country_name, 'leaders': rows}
def test_submit_server_new_nick(self):
import time
session = session_factory()
contributor = create_one_contributor()
with session.begin():
session.add(contributor)
nick = contributor.nickname
nickname = time.time()
# this will update the nick
add_stumbles_for_contributor(contributor.email, nickname,
json.loads(canada_observations_json))
# check contributor has 1 tile, 1 weekX row, and total obs is 101
weekly_per_tile = contributor.get_report_for_current_week().values()
eq_(len(weekly_per_tile), 1)
single_week = weekly_per_tile[0][0]
assert isinstance(single_week, get_current_reportweek_class())
eq_(single_week.observation_count, 101)
eq_(single_week.tile.country.name.lower(), 'canada')
# Verify that the contributor nickname was updated
eq_(contributor.nickname, nickname)
def test_submit_server_new_nick(self):
import time
session = session_factory()
contributor = create_one_contributor()
with session.begin():
session.add(contributor)
nick = contributor.nickname
nickname = time.time()
# this will update the nick
add_stumbles_for_contributor(contributor.email, nickname,
json.loads(canada_observations_json))
# check contributor has 1 tile, 1 weekX row, and total obs is 101
weekly_per_tile = contributor.get_report_for_current_week().values()
eq_(len(weekly_per_tile), 1)
single_week = weekly_per_tile[0][0]
assert isinstance(single_week, get_current_reportweek_class())
eq_(single_week.observation_count, 101)
eq_(single_week.tile.country.name.lower(), 'canada')
# Verify that the contributor nickname was updated
eq_(contributor.nickname, nickname)
def db_get_easting_northing(lon, lat):
q = "SELECT ST_AsText(%s)" % db_coord_transform(lon, lat, cs.WGS84_LATLON_CODE, cs.WEB_MERCATOR_CODE)
session = session_factory()
result = session.execute(q).fetchone()[0]
easting, northing = result.replace('POINT(', '').replace(')', '').split()
return round(float(easting)), round(float(northing))
def setup(self):
if not hasattr(self, 'db'):
self.db = init_sessions()
self.db.drop_all()
self.db.create_all()
self.session = session_factory()
def geo_as_wgs84(self):
session = session_factory()
ewkt = "'" + session.execute(func.ST_AsEWKT(self.geometry)).first().values()[0] + "'" + '::geometry'
# select ST_Transform('SRID=3785;POLYGON((-8839000 5419000,-8839000 5419500,-8838500 5419500,-8838500 5419000,-8839000 5419000))', 4326);
r = session.execute("select ST_AsEWKT(ST_Transform(%s, %d))" % (ewkt, cs.WGS84_LATLON_CODE))
return r.fetchone()[0]