def get_nearme_orders(store_id): """ Retrieve all the existing orders as a user or as a store, steps to proceed are: 1. Check user's role is "store". 2. Retrieve and check the store with the given id. 3. Search all near orders using the store's location. 4. Returns every order found. """ # Step 1 user = _request_ctx_stack.top.current_user if user['app_metadata']['user_role'] != 'store': abort(401) # Step 2 store = session.query(Store).filter(Store.id == store_id).first() if not store: abort(404) # Step 3 radius = store.rad or DEFAULT_ORDER_RADIUS distance = KM_UNIT *\ func.acos(func.cos(func.radians(store.lat)) *\ func.cos(func.radians(Order.lat)) *\ func.cos(func.radians(Order.lon) -\ func.radians(store.lon)) +\ func.sin(func.radians(store.lat)) *\ func.sin(func.radians(Order.lat))) orders = session.query(Order).filter(and_(distance <= radius, Order.status == ORDER_MADE)).order_by(desc(Order.created_at)).all() # Step 4 return jsonify(json_list=[order.serialize for order in orders]), 200
def great_circle_distance(cls, p, l):
radius = 6371 # Radius of earth
p1 = cls.latitude
l1 = cls.longitude
print(p1, l1)
p2 = p
l2 = l
dl = func.radians(func.abs(l1 - l2))
p1 = func.radians(p1)
p2 = func.radians(p2)
l1 = func.radians(l1)
l2 = func.radians(l2)
ds = func.acos((func.sin(p1) * func.sin(p2)) +
(func.cos(p1) * func.cos(p2) * func.cos(dl)))
dist = radius * ds
return dist
def distance(cls, latitude, longitude):
earth_radius = 6371
pi_converter = func.pi() / 180
dlat = (latitude - cls.latitude) * pi_converter
dlon = (longitude - cls.longitude) * pi_converter
lat1 = (cls.latitude) * pi_converter
lat2 = (latitude) * pi_converter
haversine = func.sin(dlat/2) * func.sin(dlat/2) + func.sin(dlon/2) * func.sin(dlon/2) * func.cos(lat1) * func.cos(lat2)
return earth_radius * 2 * func.atan2(func.sqrt(haversine), func.sqrt(1-haversine))
def api_search_store():
category_list = ["카페", "편의점/마트", "음식점", "디저트", "병원/약국", "의류"]
list = [
["비알코올 음료점업"],
[
"음ㆍ식료품 위주 종합 소매업", "식료품 소매업", "서적 및 문구용품 소매업",
"신선 식품 및 단순 가공 식품 도매업"
],
["한식 음식점업", "외국식 음식점업", "기타 간이 음식점업", "주점업"],
["떡, 빵 및 과자류 제조업"],
["수의업", "병원", "의약품, 의료%", "의원"],
["의복 소매업"],
]
q = request.form.get('q', '')
c = request.form.getlist('c[]')
lng = request.form.get('lng', 0)
lat = request.form.get('lat', 0)
qArr = q.split(' ')
cArr = []
if len(c) > 0 and c[0] == "전체":
cArr.append("%%")
elif len(c) == 0:
cArr.append("")
else:
for i in range(0, len(c)):
try:
type = category_list.index(c[i])
except ValueError:
continue
for j in range(0, len(list[type])):
cArr.append(list[type][j])
for i in range(0, len(qArr)):
qArr[i] = "%" + qArr[i] + "%"
store = Store.query \
.filter(and_(Store.lng != 0, Store.lat != 0)) \
.filter((func.acos(
func.sin(func.radians(lat)) * func.sin(func.radians(Store.lat)) + func.cos(func.radians(lat)) * func.cos(
func.radians(Store.lat)) * func.cos(func.radians(Store.lng) - (func.radians(lng)))) * 6371) < 0.5) \
.order_by(asc((func.acos(
func.sin(func.radians(lat)) * func.sin(func.radians(Store.lat)) + func.cos(func.radians(lat)) * func.cos(
func.radians(Store.lat)) * func.cos(func.radians(Store.lng) - (func.radians(lng)))) * 6371))) \
.filter(or_(or_(*[Store.name.like(name) for name in qArr]), or_(*[Store.category.like(name) for name in qArr]))) \
.filter(or_(*[Store.category.like(name) for name in cArr])) \
.limit(100)
result = {
"code": 200,
"message": "success",
"stores": StoreSchema(many=True).dump(store)
}
return dump(result)
def distance(self, center_latitude, center_longitude):
return (
GeoLocation.EARTH_RADIUS *
func.acos(
func.cos(func.radians(center_latitude)) *
func.cos(func.radians(self.latitude)) *
func.cos(func.radians(self.longitude) - func.radians(center_longitude)) +
func.sin(func.radians(center_latitude)) *
func.sin(func.radians(self.latitude)))
)
def haversine(cls, other):
r = 6371
lat1, lon1 = cls.latitude_pos, cls.longitude_pos
lat2, lon2 = other.latitude_pos, other.longitude_pos
import math
dlat = func.radians(lat2 - lat1)
dlon = func.radians(lon2 - lon1)
a = func.sin(dlat/2) * func.sin(dlat/2) + func.cos(func.radians(lat1)) \
* func.cos(math.radians(lat2)) * func.sin(dlon/2) * func.sin(dlon/2)
c = 2 * func.atan2(func.sqrt(a), func.sqrt(1 - a))
d = r * c
return d
def distance(pt1, pt2):
lat1, lon1 = pt1
lat2, lon2 = pt2
R = 3961
dlat = func.radians(lat2 - lat1)
dlon = func.radians(lon2 - lon1)
a = func.power(func.sin(dlat / 2), 2) + func.cos(func.radians(lat1)) * func.cos(
func.radians(lat2)
) * func.power(func.sin(dlon / 2), 2)
c = 2 * func.atan2(func.sqrt(a), func.sqrt(1 - a))
d = R * c
return d
def location_subquery(self,
latitude,
longitude,
radius,
locable_only=False, fields=['id']):
from math import degrees, radians, cos
R = 6371 # earth's mean radius, km
latitude = float(latitude)
longitude = float(longitude)
radius = float(radius)
# first-cut bounding box (in degrees)
maxLat = latitude + degrees(radius / R)
minLat = latitude - degrees(radius / R)
# compensate for degrees longitude
# getting smaller with increasing latitude
maxLon = longitude + degrees(radius / R / cos(radians(latitude)))
minLon = longitude - degrees(radius / R / cos(radians(latitude)))
filters = [self.latitude.between(minLat, maxLat),
self.longitude.between(minLon, maxLon)]
if locable_only:
filters.append(self.locable == True)
rlat = radians(latitude)
rlng = radians(longitude)
distance = (
func.acos(
func.sin(rlat)
* func.sin(func.radians(column('latitude')))
+ func.cos(rlat)
* func.cos(func.radians(column('latitude')))
* func.cos(func.radians(column('longitude')) - rlng)
) * R
).label('distance')
subquery = db.session.query(
self.id,
self.latitude,
self.longitude,
self.method,
self.city,
self.country,
self.country_code,
self.modified,
distance) \
.filter(*filters) \
.subquery('FirstCut')
sub = select(map(column, fields)) \
.select_from(subquery) \
.where(distance <= radius)
return sub
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 get_distance(self, user_lat, user_lng):
pi180 = pi / 180
lat1 = float(user_lat) * pi180
lng1 = float(user_lng) * pi180
lat2 = Location.lat * pi180
lng2 = Location.lng * pi180
r = 6371
dlat = lat2 - lat1
dlng = lng2 - lng1
a = func.sin(dlat / 2) * func.sin(dlat / 2) + func.cos(
lat1) * func.cos(lat2) * func.sin(dlng / 2) * func.sin(dlng / 2)
c = 2 * func.atan2(func.sqrt(a), func.sqrt(1 - a))
km = r * c
return km
def location_subquery(self,
latitude,
longitude,
radius,
locable_only=False,
fields=['id']):
from math import degrees, radians, cos
R = 6371 # earth's mean radius, km
latitude = float(latitude)
longitude = float(longitude)
radius = float(radius)
# first-cut bounding box (in degrees)
maxLat = latitude + degrees(radius / R)
minLat = latitude - degrees(radius / R)
# compensate for degrees longitude
# getting smaller with increasing latitude
maxLon = longitude + degrees(radius / R / cos(radians(latitude)))
minLon = longitude - degrees(radius / R / cos(radians(latitude)))
filters = [
self.latitude.between(minLat, maxLat),
self.longitude.between(minLon, maxLon)
]
if locable_only:
filters.append(self.locable == True)
rlat = radians(latitude)
rlng = radians(longitude)
distance = (func.acos(
func.sin(rlat) * func.sin(func.radians(column('latitude'))) +
func.cos(rlat) * func.cos(func.radians(column('latitude'))) *
func.cos(func.radians(column('longitude')) - rlng)) *
R).label('distance')
subquery = db.session.query(
self.id,
self.latitude,
self.longitude,
self.method,
self.city,
self.country,
self.country_code,
self.modified,
distance) \
.filter(*filters) \
.subquery('FirstCut')
sub = select(map(column, fields)) \
.select_from(subquery) \
.where(distance <= radius)
return sub
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 api_store():
lng = request.form.get('lng', 0)
lat = request.form.get('lat', 0)
store = Store.query \
.filter(and_(Store.lng != 0, Store.lat != 0)) \
.filter((func.acos(
func.sin(func.radians(lat)) * func.sin(func.radians(Store.lat)) + func.cos(func.radians(lat)) * func.cos(
func.radians(Store.lat)) * func.cos(func.radians(Store.lng) - (func.radians(lng)))) * 6371) < 0.5) \
.order_by(asc((func.acos(
func.sin(func.radians(lat)) * func.sin(func.radians(Store.lat)) + func.cos(func.radians(lat)) * func.cos(
func.radians(Store.lat)) * func.cos(func.radians(Store.lng) - (func.radians(lng)))) * 6371))) \
.limit(100)
result = {
"code": 200,
"message": "success",
"stores": StoreSchema(many=True).dump(store)
}
return dump(result)
def get(self):
if not request.args:
return self.create_param_string(), 200
if not current_app.debug:
current_app.config['RESTFUL_JSON'] = {}
args = self.reqparse.parse_args()
# Check that valid channels were queried
channels = args['channel'].split(',')
unknown = (set([x.upper()
for x in channels]).difference(tilt._tablenames))
if len(unknown) > 0:
return {'Error': 'unknown channel: %s' % ','.join(unknown)}
# Timezone
tz = (args['timezone'] == 'hst')
# Start by getting all the data
start, end = create_date_from_input(args['starttime'], args['endtime'],
tz)
raw_output = {}
count = 0
# If we're downsampling, we need to create the query by hand
for channel in channels:
cname = getattr(tilt, channel.upper())
queryclauses = []
orderby = []
# Set up query filters
queryclauses.append(
cname.timestamp.between(date_to_j2k(start, tz),
date_to_j2k(end, tz)))
# Set up orderby clauses
orderby.append(cname.timestamp.asc())
if args['rank'] == 0:
orderby.append(cname.rid.desc())
else:
queryclauses.append(cname.rid == args['rank'])
if args['downsample'] == 'none':
# Query the data and map it to the raw_tilt_fields structure
tt = tilt.TiltTranslation
q_items = []
q_items.extend([
cname.timestamp.label('timestamp'),
cname.rid.label('rid')
])
q_items.append(
(func.cos(func.radians(tt.azimuth)) *
(cname.xTilt * tt.cxTilt + tt.dxTilt).self_group() +
func.sin(func.radians(tt.azimuth)) *
(cname.yTilt * tt.cyTilt + tt.dyTilt).self_group()
).label('east'))
q_items.append(
(-func.sin(func.radians(tt.azimuth)) *
(cname.xTilt * tt.cxTilt + tt.dxTilt).self_group() +
func.cos(func.radians(tt.azimuth)) *
(cname.yTilt * tt.cyTilt + tt.dyTilt).self_group()
).label('north'))
q_items.extend([tilt.TiltRank.name, tt])
# Add optional parameters
if any(x in args['series'] for x in ['holeTemp', 'all']):
q_items.append((cname.holeTemp * tt.choleTemp +
tt.dholeTemp).label('holeTemp'))
if any(x in args['series'] for x in ['boxTemp', 'all']):
q_items.append((cname.boxTemp * tt.cboxTemp +
tt.dboxTemp).label('boxTemp'))
if any(x in args['series'] for x in ['instVolt', 'all']):
q_items.append((cname.instVolt * tt.cinstVolt +
tt.dinstVolt).label('instVolt'))
if any(x in args['series'] for x in ['rainfall', 'all']):
q_items.append((cname.rainfall * tt.crainfall +
tt.drainfall).label('rainfall'))
q = db.session.query(*q_items).join(tt, tilt.TiltRank)
q = q.filter(*queryclauses).order_by(*orderby)
try:
q = q.limit(MAX_LINES['TILT'])
except KeyError:
pass
data = q.all()
data = self.filter_nulls(data)
raw_output[channel] = [*map(self.create_initial_output, data)]
# Adjust dates from j2ksec to actual datetime
for d in raw_output[channel]:
d['date'] = j2k_to_date(d['date'], tz).strftime(FFMT)
elif args['downsample'] == 'decimate':
interval = int(args['dsint'])
dbname = 'v3_hvo_deformation_tilt$tilt'
s = ("SELECT * FROM(SELECT fullquery.*, @row := @row+1 AS "
"rownum FROM (SELECT j2ksec as timestamp, c.rid, c.name, "
"COS(RADIANS(b.azimuth)) * (xTilt * cxTilt + dxTilt) "
"+ SIN(RADIANS(b.azimuth)) * (yTilt * cyTilt + dyTilt) "
"as east, (-SIN(RADIANS(b.azimuth))) * (xTilt * cxTilt "
"+ dxTilt) + COS(RADIANS(b.azimuth)) * (yTilt * cyTilt "
"+ dyTilt) as north, holeTemp * cHoleTemp + dHoleTemp as "
"holeTemp, boxTemp * cboxTemp + dboxTemp as boxTemp, "
"instVolt * cinstVolt + dinstVolt as instVolt, rainfall "
"* crainfall + drainfall as rainfall FROM "
f"{dbname}.{cname.__tablename__} a INNER JOIN "
f"{dbname}.translations b on a.tid = b.tid INNER JOIN "
f"{dbname}.ranks c ON a.rid = c.rid WHERE "
"j2ksec BETWEEN :st AND :et ")
if args['rank'] != 0:
s += "AND c.rid = :rid ORDER BY j2ksec ASC"
else:
s += "ORDER BY j2ksec ASC AND a.rid DESC"
s += (") fullquery, (SELECT @row:=0) r) ranked WHERE "
"rownum % :dsint = 1")
try:
s += ' LIMIT ' + str(MAX_LINES['TILT'])
except KeyError:
pass
data = (db.session.execute(text(s),
params=dict(
dsint=interval,
st=date_to_j2k(start, tz),
et=date_to_j2k(end, tz),
rid=args['rank'])).fetchall())
data = self.filter_nulls(data)
raw_output[channel] = [*map(self.create_initial_output, data)]
for d in raw_output[channel]:
d['date'] = j2k_to_date(d['date'], tz).strftime(FFMT)
elif args['downsample'] == 'mean':
pass
# Calculate rainfall
if 'rainfall' in args['series'] or 'all' in args['series']:
lastval = -1
total = 0
for d in raw_output[channel]:
if lastval == -1:
lastval = d['rainfall']
d['rainfall'] = 0
elif d['rainfall'] == lastval:
d['rainfall'] = total
else:
total += d['rainfall'] - lastval
lastval = d['rainfall']
d['rainfall'] = total
count += len(data)
# Now go through and compute things like radial, tangential,
# azimuth, magnitude if requested by the user
if set(args['series'].split(',')).intersection([
'all', 'radial', 'tangential', 'magnitude', 'azimuth', 'east',
'north'
]):
tc = tilt.TiltChannel
for channel in channels:
data = raw_output[channel]
if args['azimuth'] == 'nominal':
azimuth = (tc.query.filter(
tc.code == channel.upper()).first().azimuth % 360.0)
elif args['azimuth'] == 'optimal':
azimuth = self.get_optimal_azimuth(data) % 360.0
else:
azimuth = args['azimuth'] % 360.0
if len([*data]) == 0:
continue
# Subtract means to get zero-based values
em = sum([x['east'] for x in data]) / len(data)
nm = sum([x['north'] for x in data]) / len(data)
for i in data:
i['east'] -= em
i['north'] -= nm
tr = radians(azimuth)
rotation_matrix = matrix([[cos(tr), sin(tr)],
[-sin(tr), cos(tr)]])
# Add radial, tangential, magnitude, and azimuth vals to output
ox = data[0]['east']
oy = data[0]['north']
for i in data:
e, n = i['east'], i['north']
m = matrix([[e, n]]) * rotation_matrix
if any(x in args['series'] for x in ['radial', 'all']):
i['radial'] = m.A[0][1]
if any(x in args['series'] for x in ['tangential', 'all']):
i['tangential'] = m.A[0][0]
if any(x in args['series'] for x in ['magnitude', 'all']):
i['magnitude'] = sqrt((e - ox) * (e - ox) + (n - oy) *
(n - oy))
if any(x in args['series'] for x in ['azimuth', 'all']):
i['azimuth'] = atan2(n - oy, e - ox)
# If east and/or north aren't in the series list,
# remove them from output
if not any(x in args['series'] for x in ['east', 'all']):
for d in data:
del d['east']
if not any(x in args['series'] for x in ['north', 'all']):
for d in data:
del d['north']
return {
'nr': count,
'used_azimuth': azimuth,
'tangential_azimuth': (azimuth + 90) % 360,
'records': raw_output
}, 200
def calcSQLDist(ra1, dec1, ra2, dec2): from sqlalchemy import func from math import pi return func.acos(func.sin(dec1*pi/180.0)*func.sin(dec2*pi/180.0) + func.cos(dec1*pi/180.0)*func.cos(dec2*pi/180.0)*func.cos((ra1-ra2)*pi/180.0))