def get_top_coordinates_for_song(artistId):
query = (
"""
SELECT songId, longitude, latitude, COUNT(songId) as count FROM `conuhacks2019-229901.plays.*`
WHERE artistId = @artistId
GROUP BY longitude, latitude, songId
ORDER BY count DESC LIMIT 10 """
)
query_params = [
bigquery.ScalarQueryParameter(
'artistId', 'INT64', artistId)
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location="US",
job_config=job_config
) # API request - starts the query
res = query_job.result()
styles = plays.Res(many=True).load(res)[0]
return jsonify({'data': styles})
def get_top_coordinates_for_song():
query = ("""
SELECT DISTINCT(style), longitude, latitude, COUNT(style) as count FROM `conuhacks2019-229901.plays.*`
GROUP BY longitude, latitude, style ORDER BY count DESC LIMIT 10 """)
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location="US",
) # API request - starts the query
res = query_job.result()
styles = plays.Res(many=True).load(res)[0]
return jsonify({'data': styles})
def get_top_songs():
request_data = request.get_json(force=True)
arr = request_data['coordinates']
geo_from_json = {"type": "Polygon", "coordinates": arr}
query = ("""
SELECT
*
FROM (
SELECT
songId,
artistId,
style,
COUNT(songId) as count
FROM
`conuhacks2019-229901.plays.*`
WHERE
ST_DISTANCE(
ST_GEOGPOINT(longitude, latitude),
ST_CENTROID(ST_GEOGFROMGEOJSON(@s))
) < ST_MAXDISTANCE(
ST_CENTROID(ST_GEOGFROMGEOJSON(@s)),
ST_GEOGFROMGEOJSON(@s)
) AND ST_DWITHIN(
ST_GEOGPOINT(longitude, latitude),
ST_GEOGFROMGEOJSON(@s),
0
)
GROUP BY
songId,
artistId,
style
LIMIT 10
)
ORDER BY
count DESC
""")
query_params = [
bigquery.ScalarQueryParameter(
's',
'STRING',
str(geo_from_json),
),
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
query,
# Location must match that of the dataset(s) referenced in the query.
location="US",
job_config=job_config) # API request - starts the query
res = query_job.result()
songs = plays.Res(many=True).load(res)[0]
thread_pool = []
for song in songs:
# check if cached
cached_data = cache.get(song['artistId'])
if cached_data is None:
thread = Thread(target=fetch_artist_name,
args=[song['artistId'], song])
thread_pool.append(thread)
thread.start()
for thread in thread_pool:
thread.join()
# clear the pool for next async operations
song_thread_pool = []
for song in songs:
cached_data = song_cache.get(song['songId'])
if cached_data is None:
thread = Thread(target=fetch_song_name,
args=[song['songId'], song])
song_thread_pool.append(thread)
thread.start()
for thread in song_thread_pool:
thread.join()
return jsonify({'data': songs})