def get_galaxy_data(connection, galaxy_id, tables, radius):
time_start = time.time()
map_galaxy_data = {}
radius_2 = math.pow(radius, 2)
for table in tables.values():
time_start_table = time.time()
table_extended = DATA_TABLES_EXTENDED[table.name + '__extended']
select_statement = select(
[table, table_extended]
).where(
and_(
table.c.galaxy_id == galaxy_id,
table.c.galaxy_id == IMAGE_DETAIL.c.galaxy_id,
table.c.id == table_extended.c.original_id,
func.pow(table.c.x - IMAGE_DETAIL.c.x_centroid, 2) + func.pow(table.c.y - IMAGE_DETAIL.c.y_centroid, 2) <= radius_2
)
)
for table_data in connection.execute(select_statement):
key = '{0}_{1}'.format(
table_data[table.c.x],
table_data[table.c.y]
)
map_x_y = map_galaxy_data.get(key)
if map_x_y is None:
map_x_y = {
'galaxy_id': galaxy_id,
'x': table_data[table.c.x],
'y': table_data[table.c.y],
'data': {}
}
map_galaxy_data[key] = map_x_y
value = table_data[table.c.value]
value_zscore = table_data[table_extended.c.value_zscore]
value_min_max = table_data[table_extended.c.value_min_max]
value_ln = table_data[table_extended.c.value_ln]
value_softmax = table_data[table_extended.c.value_softmax]
data = map_x_y['data']
data[table.name] = {
'value': value,
'zscore': value_zscore,
'min_max': value_min_max,
'ln': value_ln,
'softmax': value_softmax,
}
time_end_table = time.time()
LOG.info('get_galaxy_data: [table: {0}, time: {1:.3f}s]'.format(table.name, time_end_table - time_start_table))
time_end = time.time()
LOG.info(
'get_galaxy_data: [galaxy_id: {0}, time: {1:.3f}s, len: {2}]'.format(
galaxy_id,
time_end - time_start,
len(map_galaxy_data.values())
)
)
return map_galaxy_data.values()
def in_range(cls, latitude, longitude, radius):
"""Check if user in range (radius in meters)."""
R = 6373.0 # approximate radius of earth in km
from sqlalchemy import func
lat1 = func.radians(cls.latitude)
lon1 = func.radians(cls.longitude)
lat2 = func.radians(latitude)
lon2 = func.radians(longitude)
dlon = lon2 - lon1
dlat = lat2 - lat1
a = func.pow(func.sin(dlat / 2), 2) + func.cos(lat1) * func.cos(lat2) \
* func.pow(func.sin(dlon / 2), 2)
c = R * 2 * func.atan2(func.sqrt(a), func.sqrt(1 - a))
print(c)
return c
def rank(cls):
# Get rank
rank = math.prod(
[1]
+ [
func.pow(getattr(cls, field), weight)
for field, weight in weighted_columns_positive.items()
]
) / math.prod(
[1]
+ [
func.pow(getattr(cls, field), abs(weight))
for field, weight in weighted_columns_negative.items()
]
)
return rank
def get_explore_trending100(cls, request, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
now = int(round(time.time()/60/60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin\
(Qvote, and_(Question.questionasker==Qvote.questionasker,\
Qvote.userid == logged_in_alias)).filter(Question.askedby !="Sebastian").filter(Question.askedby !="sebastian").filter(Question.answered==True).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))).limit(100), page,
url=page_url, items_per_page=100)
def get_hot(cls, request, channellower, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
now = int(round(time.time() / 60 / 60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin\
(Qvote, and_(Question.questionasker==Qvote.questionasker,\
Qvote.userid == logged_in_alias)).filter(Question.askedtoalias == channellower).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))), page,
url=page_url, items_per_page=20)
def get_explore_trending100(cls, request, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
now = int(round(time.time() / 60 / 60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin\
(Qvote, and_(Question.questionasker==Qvote.questionasker,\
Qvote.userid == logged_in_alias)).filter(Question.askedby !="Sebastian").filter(Question.askedby !="sebastian").filter(Question.answered==True).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))).limit(100), page,
url=page_url, items_per_page=100)
def get_hot(cls, request, channellower, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
now = int(round(time.time()/60/60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin\
(Qvote, and_(Question.questionasker==Qvote.questionasker,\
Qvote.userid == logged_in_alias)).filter(Question.askedtoalias == channellower).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))), page,
url=page_url, items_per_page=20)
def distance(cls, pos):
# approximate radius of earth in km
R = 6373.0
(pos_lat, pos_lng) = pos
pos_lat = math.radians(pos_lat)
pos_lng = math.radians(pos_lng)
rad_latitude = func.radians(cls.latitude)
rad_longitude = func.radians(cls.longitude)
delta_lat = pos_lat - rad_latitude
delta_lng = pos_lng - rad_longitude
a = func.pow(func.sin(delta_lat / 2), 2) + func.cos(pos_lat) * \
func.cos(rad_latitude) * func.pow(func.sin(delta_lng / 2), 2)
c = 2 * func.asin(func.sqrt(a))
return R * c
def stream_hot(cls, request, followinglist, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
try:
followinglist = followinglist.lower()
followinglist = followinglist.split()
except:
followinglist = ""
now = int(round(time.time() / 60 / 60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin(Qvote, and_(Question.questionasker==Qvote.questionasker,Qvote.userid == logged_in_alias)).filter(Question.askedtoalias.in_\
(followinglist)).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))), page,
url=page_url, items_per_page=20)
def stream_hot(cls, request, followinglist, logged_in_alias, page=1):
page_url = PageURL_WebOb(request)
try:
followinglist = followinglist.lower()
followinglist = followinglist.split()
except:
followinglist = ""
now = int(round(time.time()/60/60)) + 1
gravity = 1.8
return Page(DBSession.query(Question, Qvote, Images).outerjoin(Images, Images.usralias == Question.askedbyalias).outerjoin(Qvote, and_(Question.questionasker==Qvote.questionasker,Qvote.userid == logged_in_alias)).filter(Question.askedtoalias.in_\
(followinglist)).order_by(sa.desc((Question.qkarma -1) /\
func.pow((now - Question.unixage), gravity))), page,
url=page_url, items_per_page=20)
def hybridMag(cls):
if index is not None:
# It needs to be index + 1 because Postgresql arrays are 1-indexed.
flux = getattr(cls, flux_parameter)[index + 1]
else:
flux = getattr(cls, flux_parameter)
flux *= 1e-9
bb_band = bb[band]
xx = flux / (2. * bb_band)
asinh_mag = (-2.5 / func.log(10) *
(func.log(xx + func.sqrt(func.pow(xx, 2) + 1)) +
func.log(bb_band)))
return cast(asinh_mag, Float)
def decayed_score(score,
created_at,
peak=5,
nominal_timestamp=14 * 24 * 60 * 60):
"""
Creates a decaying (over time) version of the provided `score`. The returned
value is score * a multiplier determined by `peak` and `nominal_timestamp`.
Args:
score: (number) The base score to modify
created_at: (timestamp) The timestamp the score is attributed to
peak?: (number) The peak multipler possible
Returns:
A SQLAlchemy expression representing decayed score (score * multipler)
where multipler is represented by:
max(0.2, 5 ^ 1 - min(time_ago / nominal_timestamp, 1))
"""
return score * func.greatest(
func.pow(
5, 1 - func.least(seconds_ago(created_at) / nominal_timestamp, 1)),
0.2)
def run_variable(pkgbases: List[PackageBase] = []) -> None:
"""
Update popularity on a list of PackageBases.
If no PackageBase is included, we update the popularity
of every PackageBase in the database.
:param pkgbases: List of PackageBase instances
"""
now = time.utcnow()
# NumVotes subquery.
votes_subq = db.get_session().query(
func.count("*")
).select_from(PackageVote).filter(
PackageVote.PackageBaseID == PackageBase.ID
)
# Popularity subquery.
pop_subq = db.get_session().query(
coalesce(_sum(func.pow(0.98, (now - PackageVote.VoteTS) / 86400)), 0.0),
).select_from(PackageVote).filter(
and_(PackageVote.PackageBaseID == PackageBase.ID,
PackageVote.VoteTS.isnot(None))
)
with db.begin():
query = db.query(PackageBase)
ids = set()
if pkgbases:
ids = {pkgbase.ID for pkgbase in pkgbases}
query = query.filter(PackageBase.ID.in_(ids))
query.update({
"NumVotes": votes_subq.scalar_subquery(),
"Popularity": pop_subq.scalar_subquery()
})
def end_distance(self, lat, lng):
lat_scale_factor = 0.009032
long_scale_factor = 0.0146867
return (func.pow(
((self.end_lat - lat) / lat_scale_factor), 2) + func.pow(
((self.end_long - lng) / long_scale_factor), 2))
def calc_distance(p1, p2):
return func.sqrt(
func.pow(69.1 * (p1[0] - p2[0], 2) + func.pow(53.0 *
(p1[1] - p2[1]), 2)))
def generate_quiz_question(quiz):
node = (db.session.query(
Node.id, Node.node_rgt).where(Node.ott == quiz["ott"]).first())
# Find a parent node which has at least two direct valid quiz nodes
parent_node = (db.session.query(Node.id, Node.ott, Node.name).join(
QuizNode, QuizNode.node_id == Node.id).where(
between(
Node.id, node.id,
node.node_rgt)).where(QuizNode.can_branch == True).order_by(
desc(func.pow(Node.popularity, 0.1) *
func.rand())).first())
if not parent_node:
raise InternalServerError(
f"No suitable parent nodes for the ott: {quiz['ott']}")
# Randomly pick two valid child nodes which will become node_left and node_right
result = db.session.execute(
text("""
SELECT n.id, n.leaf_lft, n.leaf_rgt
FROM ordered_nodes n
JOIN quiz_nodes q on q.node_id = n.id
WHERE n.real_parent = :parent_node_id AND q.num_quiz_leaves > 1
ORDER BY rand()
LIMIT 2
"""),
{
"parent_node_id": parent_node.id
},
).fetchall()
if len(result) < 2:
print("Selected parent node has fewer than two valid node children.")
print(parent_node._asdict())
raise InternalServerError()
node_left_result, node_right_result = result
node_left = dict(zip(["id", "leaf_left", "leaf_right"], node_left_result))
node_right = dict(zip(["id", "leaf_left", "leaf_right"],
node_right_result))
leaf_left_1 = generate_quiz_leaf(node_left, False)
leaf_left_2 = generate_quiz_leaf(node_left, True, leaf_left_1["id"])
leaf_right = generate_quiz_leaf(node_right, True)
swap = random() > 0.5
question = {
"compare": leaf_left_1,
"option1": leaf_left_2 if swap else leaf_right,
"option2": leaf_right if swap else leaf_left_2,
}
save_question_to_db(
quiz_id=quiz["id"],
leaf_compare=question["compare"],
leaf_1=question["option1"],
leaf_2=question["option2"],
)
return question
def calc_distance(lat1, lon1, lat2, lon2):
Earth_radius_km = 6371.009
km_per_deg_lat = 2 * 3.14159265 * Earth_radius_km / 360.0
km_per_deg_lon = km_per_deg_lat * func.cos(func.radians(lat1))
distance = func.sqrt(func.pow((km_per_deg_lat * (lat1 - lat2)), 2) + func.pow((km_per_deg_lon * (lon1 - lon2)), 2))
return distance
def distance(cls, other):
return func.abs(
func.sqrt(
func.pow(other.x - cls.x, 2) + func.pow(other.y - cls.y, 2) +
func.pow(other.z - cls.z, 2)))
