def get_documents_by_geoshape(cls, geometry):
# geometry should have the following form for example:
# geometry {
# "type":"MultiPolygon",
# "coordinates":[[[[]]]]
# }
_body = {
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": {
"geo_shape": {
"location": {
"shape": geometry,
"relation": "within"
}
}
}
}
}
}
res = es.search(index=settings.HOME_INDEX,
doc_type=settings.HOME_TYPE,
body=_body,
size=10)
return res['hits']
def index():
results = {}
for k_form, v_form in request.form.items(): # It's a trick, there should be only 1 iteration
found = es.search(index='chgk-index', doc_type='chgk', q=v_form)
results[v_form] = [hit["_source"] for hit in found['hits']['hits']]
return render_template('index.html', results=results)
def index():
LOG.info('Access: %s, %s, %s' % (request.remote_addr, request.args, request.form))
results = {}
for k_form, v_form in request.form.items():
found = es.search(index='chgk-index', doc_type='chgk', q=v_form)
results[v_form] = [hit["_source"] for hit in found['hits']['hits']]
return render_template('index.html', results=results)
def finance():
"""
only for apocalypse application
"""
incoming = request.get_json()
rule = g.current_rule
home_ids = incoming.get('home_id')
result = {}
includes_fields = json.loads(rule.includes) if rule.includes else []
excludes_fields = json.loads(rule.excludes) if rule.excludes else []
query_should = [{"match_phrase":{"_id":home_ids}}]
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
filter_path=['hits.hits._*'],
body={
"query": {
"bool": {
"must": [
{
"match_all": {}
},
{
"bool": {
"should": query_should,
"minimum_should_match": 1
}
}
],
"filter": [],
"should": [],
"must_not": []
}
}
},
_source_exclude=EsqueryHelper.config_include_or_exclude_field_from_rule(rule,exclude=True),
_source_include=EsqueryHelper.config_include_or_exclude_field_from_rule(rule,include=True)
)
if not res:
return jsonify(success=True,result=result)
res_value = res['hits']['hits'][0]['_source']
item = EsqueryHelper.to_dict_with_filter(EsqueryHelper.process_room_item(res_value),columns=includes_fields)
item['score_radar'] = process_score(item) # radar score
if item.get('wehome_rent') and item['wehome_rent'] is not None:
item['rent'] = item['wehome_rent']
# get propety price trend
if not item.get('url'):
item['url'] = "https://www.zillow.com/homedetails/"+"{}_zpid/".format(home_ids.split('_')[0])
prices = return_price_point_trend(url=item['url'],zpid=item['source_id'])
item['price_trend'] = prices['Home']['data'] if prices and prices.get('Home') else []
return jsonify(success=True,result=item)
def nearby():
# controid example: "33.727929,-112.0005255"
rule = g.current_rule
centroid = request.args.get("centroid")
NEARBY_RANGE_KM = "5mi"
ROOMS_LENGTH = 15 # return 15 rooms nearby the centroid
query = {
"query": {
"bool": {
"must": [],
"filter": {
"geo_distance" : {
"distance" : NEARBY_RANGE_KM,
"location_point" : centroid,
}
},
"should": [],
"must_not": [{"match_phrase":{"room_type":{"query": ""}}}]
}
},
"sort":[
{
"_geo_distance" : {
"location_point" : centroid,
"order": "asc",
"unit": "mi"
}
}
]
}
includes_fields = json.loads(rule.includes) if rule.includes else []
excludes_fields = json.loads(rule.excludes) if rule.excludes else []
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=query,
size=ROOMS_LENGTH,
_source_include=EsqueryHelper.config_include_or_exclude_field_from_rule(rule,exclude=True),
_source_exclude=EsqueryHelper.config_include_or_exclude_field_from_rule(rule,exclude=True)
)
if not res:
return jsonify(success=True,result=[])
item_processed = []
for r in res['hits']['hits']:
new_dict = r['_source']
new_dict['distance'] = "{} mi".format(r['sort'][0])
item = EsqueryHelper.to_dict_with_filter(EsqueryHelper.process_room_item(new_dict),columns=includes_fields)
item_processed.append(item)
return jsonify(success=True,result=item_processed)
def group():
""" get area's statistices data
"""
_geojson=request.args.get('geojson')
_level=request.args.get('level')
_geoids=request.args.get('geoid')
_beds = request.args.get("beds")
_baths = request.args.get("baths")
_areamin = request.args.get("areamin")
_areamax = request.args.get("areamax")
_val_expectmin = request.args.get("val_expectmin")
_val_expectmax = request.args.get("val_expectmax")
_rent_expectmin = request.args.get("rent_expectmin")
_rent_expectmax = request.args.get("rent_expectmax")
_house_pricemin = request.args.get("house_pricemin")
_house_pricemax = request.args.get("house_pricemax")
_status=request.args.get("status")
_sort_by_val_expect = request.args.get("sort_val")
_sort_by_price = request.args.get("sort_price")
_sort_by_online_date = request.args.get("sort_od")
_sort_by_year=request.args.get("sort_year")
_search_text = request.args.get("search_text")
_sort_by_cz = request.args.get("sort_cz")
_isVilla = request.args.get('isVilla')
_isApartment = request.args.get('isApartment')
_online_day = request.args.get('online_day')
area_ids = _geoids.split(',') if _geoids else []
area_ids = app.config['POPULAR_AREAS'] if not area_ids else area_ids
# prepare the query body (without search)
_query=[]
query_body = {
'bool': {
'must': filter_city(_query, area_ids=area_ids,val_expect=[_val_expectmin, _val_expectmax], beds=_beds, baths=_baths,area=[_areamin, _areamax], house_price=[_house_pricemin, _house_pricemax],rent_expect=[_rent_expectmin, _rent_expectmax],status=_status,isVilla=_isVilla,isApartment=_isApartment,online_day=_online_day)}
}
# prepare the query body (with search)
if _search_text:
query_body['bool']['must'].append({"multi_match": {"fields": ["city", "addr"],
"query": _search_text}})
# prepare the sort body
_sort_body = sort_result(sort_val=_sort_by_val_expect,
sort_price=_sort_by_price,
sort_od=_sort_by_online_date,
sort_cz=_sort_by_cz,
sort_year=_sort_by_year)
includes_fields = ["id", "location"]
# the whole body here (fetch ten items from every area which satisfied the certain criteria)
_body = {
"query": query_body,
"sort": _sort_body,
"size": 0,
"aggs": {
"top-areas": {
"terms": {
"field": "area.id.keyword",
"size":30,
},
"aggs": {
"top_area_hits": {
"top_hits": {
"size": 10, # only ten from every msa
"_source": {
"includes": includes_fields
}
}
},
}
}
}
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=20000,
_source_include=includes_fields,
)
area = Area.query.filter(Area.geoid.in_(area_ids)).all()
if _level=='0':
areadict={}
for each in area:
areadict[str(each.geoid)] = [[float(each.lng),float(each.lat)],each.eng_name]
features=[]
for each in res['aggregations']['top-areas']['buckets']:
before = {
"count":each['doc_count'],
"eng_name":areadict[each['key']][1],
"geoid":each['key'],
"center":areadict[each['key']][0]
}
after = EsqueryHelper.get_stats_by_area_id(each['key'])
z = before.copy()
z.update(after)
features.append({"geometry":
{ "type":"Point",
"coordinates":areadict[each['key']][0]
},
"properties":z,
"type":"Feature"})
r_res = {
"group": {
"features":features,
"type": "FeatureCollection"
}
,
"level":0,
"length":0,
"rooms":[]
}
return jsonify(result=r_res, success=True)
if _level=='1':
features = []
for each in res['hits']['hits']:
features.append({"geometry":
{"type":"Point",
"coordinates":each['_source']['location']['coordinates']},
"properties":{"id":each['_source']['id']},
"type":"Feature"})
for each in res['hits']['hits']:
del each['_id']
del each['_index']
del each['_score']
r_res = {
"group": {
"features": features,
"type": "FeatureCollection"
},
"level": 1,
"boundary":dict({"type": "FeatureCollection"}.items()+area[0].properties.items())
}
return jsonify(result=r_res, success=True)
def homeinvestment():
"""
For investment application
"""
incoming = request.get_json()
home_id = incoming['home_id']
purchase_price = incoming.get('purchase_price')
rent = incoming.get('rent')
down_payment = incoming.get('down_payment')
loan_interest_rate = incoming.get('loan_interest_rate')
appreciation = incoming.get('appreciation')
expenses = incoming.get('expenses')
closing_costs_misc = incoming.get('closing_costs_misc')
includes_fields = ['house_price_dollar','rent','area','neighborhood','rent_increase_ratio','total_ratio']
investment_related_fields=['property_tax','hoa','vaccancy_rate','property_management_fee','leasing_commission','insurance_cost','repair','cap_ex','acquisition_cost','disposition_cost','rent_growth','increase_ratio','down_payment','expenses','loan_interest_rate','closing_costs_misc']
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
filter_path=['hits.hits._*'],
body={
"query": {
"bool": {
"must": [
{
"match_all": {}
},
{
"match_phrase": {
"_id": {
"query": home_id
}
}
}
],
"filter": [],
"should": [],
"must_not": []
}
}
},
_source_include=includes_fields+investment_related_fields
)
item = res['hits']['hits'][0]['_source'] if res else {}
if not item:
return jsonify(success=True,message='no data')
region_paras = EsqueryHelper.get_region_default_paras(area_id=item['area']['id'], neighbor_id=item['neighborhood']['id'])
# prepare investment fields
for f in investment_related_fields:
if item.get(f):
continue
if not item.get(f) and region_paras.get('neighborhood') and item.get('neighborhood') and region_paras['neighborhood'].get(f):
if f=='hoa':
continue
item[f]=region_paras['neighborhood'][f]
continue
if not item.get(f) and region_paras.get('area') and item.get('area'):
if f=='hoa':
continue
item[f]=region_paras['area'][f]
# user enter information
item['house_price_dollar'] = float(purchase_price) if purchase_price else item['house_price_dollar']
item['rent'] = float(rent) if rent else item['rent']
item['down_payment'] = float(down_payment) if down_payment else item['down_payment']
item['loan_interest_rate'] = float(loan_interest_rate) if loan_interest_rate else item['loan_interest_rate']
if item['loan_interest_rate']:
item['property_management_fee'],item['leasing_commission'],item['insurance_cost'],item['cap_ex'] = 0.32*item['loan_interest_rate'],0.12*item['loan_interest_rate'],0.22*item['loan_interest_rate'],0.34*item['loan_interest_rate']
item['appreciation'] = incoming.get('appreciation') if appreciation else item['increase_ratio']
expenses = incoming.get('expenses')
closing_costs_misc = incoming.get('closing_costs_misc') if closing_costs_misc else item['closing_costs_misc']
if not item.get('hoa'):item['hoa']=0
# calculate irr
invest = EsqueryHelper.cal_cashflow(item,years=5)
initial_data = {"initial_investment":item['down_payment']*item['house_price_dollar'],
"current_rent":item.get('rent'),
"appreciation":item.get('increase_ratio'),
"cap_rate":invest.pop('cap_rate'),
"cash_flow":2000, # TODO
"total_cash_return":2000*12*5, #TODO
"irr":invest.pop('irr')
}
return jsonify(success=True,assumptions=invest.pop('assumptions'),total_expenses=invest.pop('total_expenses'),invest=invest,initial_data=initial_data,estimated_total_gain=invest.pop('estimated_total_gain'),equity_build_up=invest.pop('equity_build_up'),net_cash_flow=invest.pop('net_cash_flow'))
def update_price():
"""
30 Dorset Dr, Kissimmee, FL, 4 Beds, 2 Baths, 28.17111, -81.489976, 46315662_zpid
449 Hardy Water Dr, Lawrenceville, GA, 4 Beds, 3 Baths, 33.973542, -83.937303, 97994816_zpid
300 N 130th St, Unit 1103, Seattle, WA, 1 bed, 1 bath, 47.723786, -122.353735, 82362491_zpid
"""
NEARBY_RANGE_KM = "2mi"
ROOMS_LENGTH = 10000
incoming = request.get_json()
home_id = incoming['home_id']
#home_id = "46315662_zillow"
properties = {}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
filter_path=['hits.hits._*'],
body={
"query": {
"bool": {
"must": [
{
"match_all": {}
},
{
"match_phrase": {
"_id": {
"query": home_id
}
}
}
],
"filter": [],
"should": [],
"must_not": []
}
}
}
)
properties["id"] = home_id
properties["address"] = res['hits']['hits'][0]['_source']['addr']
properties["RoomType"] = res['hits']['hits'][0]['_source']['room_type']
properties["Beds"] = res['hits']['hits'][0]['_source']['beds']
properties["Baths"] = res['hits']['hits'][0]['_source']['baths']
properties["current_price"] = 0
properties["location"] = res['hits']['hits'][0]['_source']['location_point']
centroid = properties["location"]
query = {
"query": {
"bool": {
"must": [],
"filter": {
"geo_distance" : {
"distance" : NEARBY_RANGE_KM,
"location_point" : centroid,
}
},
"must": [{"match_phrase":{"status": 2}}],
"must_not": [{"match_phrase":{"room_type":{"query": ""}}}]
}
},
"sort":[
{
"_geo_distance" : {
"location_point" : centroid,
"order": "asc",
"unit": "mi"
}
}
]
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=query,
size=ROOMS_LENGTH,
)
database = GetData(res['hits']['hits'])
area_target_properties = GetTargets(database.index.values, database, properties)
properties['current_price'] = int(np.percentile(area_target_properties['Price'], '60'))
properties['adjust_price_high'] = int(np.percentile(area_target_properties['Price'], '65'))
properties['adjust_price_low'] = int(np.percentile(area_target_properties['Price'], '55'))
properties['average_price'] = (properties['adjust_price_high'] + properties['adjust_price_low']) * 0.5
properties['z_index'] = (properties['adjust_price_high'] - properties['adjust_price_low']) / properties['adjust_price_low']
return jsonify(**properties)
def process_item(item, rule, incoming):
# get nearby rooms
NEARBY_RANGE_KM = "2mi"
NEARBY_RANGE_SCORE = "3mi"
ROOMS_LENGTH = 1000 # return 20 rooms nearby the centroid
#rent_status = 1
query = {
"query": {
"bool": {
"must": [],
"filter": {
"geo_distance": {
"distance": NEARBY_RANGE_KM,
"location_point": item['centroid'],
}
},
#"must": [{"match_phrase":{"status":rent_status}}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
},
"sort": [{
"_geo_distance": {
"location_point": item['centroid'],
"order": "asc",
"unit": "mi"
}
}]
}
query_score = {
"query": {
"bool": {
"must": [],
"filter": {
"geo_distance": {
"distance": NEARBY_RANGE_SCORE,
"location_point": item['centroid'],
}
},
#"must": [{"match_phrase":{"status": 2}}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
},
"sort": [{
"_geo_distance": {
"location_point": item['centroid'],
"order": "asc",
"unit": "mi"
}
}]
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=query,
size=ROOMS_LENGTH,
_source_include=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule,
exclude=True),
_source_exclude=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule,
exclude=True))
res_score = es.search(
index=HOME_INDEX,
doc_type=HOME_TYPE,
body=query_score,
size=ROOMS_LENGTH,
_source_include=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule, exclude=True),
_source_exclude=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule, exclude=True))
rent = calcrent(res, item)
if rent == 'N/A':
return None
else:
item['Suggested_Rent'] = calcrent(
res, item) if not incoming.get('rent') else float(incoming['rent'])
item['Return'] = '%.2f' % float(12 * item['Suggested_Rent'])
item['Revenue'] = '%.2f' % float(12 * item['Suggested_Rent'] -
0.035 * item['listing_price'])
item['Ratio'] = '%.2f' % float(
(12 * item['Suggested_Rent'] / item['listing_price']) * 100)
item['Irr'] = '%.2f' % cashflow(item['listing_price'],
item['Suggested_Rent'])
try:
item['Appr'] = '%.2f' % calcappr(
res, item) if not incoming.get('appr') else float(
incoming['appr'])
except TypeError as e:
#app.logger.warning(e)
return None
item['Cap'] = '%.2f' % (float(item['Appr']) + float(item['Ratio']) -
3.5)
if item['Appr'] == 'N/A':
return None
nearest_items_in_db = []
if res_score['hits']['hits'] and res_score['hits']['hits'].__len__() > 5:
for i in range(5):
nearest_item_in_db = res_score['hits']['hits'][i]['_source']
if nearest_item_in_db.get(
'area') and nearest_item_in_db['area'].get('id'):
item['area_id'] = nearest_item_in_db['area']['id']
if nearest_item_in_db.get('neighborhood') and nearest_item_in_db[
'neighborhood'].get('id'):
item['neighborhood_id'] = nearest_item_in_db['neighborhood'][
'id']
break
data = calcscore(res_score, item)
if data == 'N/A':
return None
item['Score'] = data['score']
item['Median_Price'] = '%.2f' % (data['price'])
item['Median_Revenue'] = '%.2f' % (data['revenue'])
item['Median_Appr'] = '%.2f' % (data['appr'])
item['Median_Cap'] = '%.2f' % (data['cap'])
item['Median_Ratio'] = '%.2f' % (data['ratio'])
item['Median_Score'] = '%.2f' % (data['median_score'])
item['Addr1'] = data['first']
item['Addr2'] = data['second']
item['Addr3'] = data['third']
item['first_address'] = data['first_address']
item['second_address'] = data['second_address']
item['third_address'] = data['third_address']
item['rent_score'] = data['rent-score']
item['appr_score'] = data['appr-score']
item['risk_score'] = data['risk-score']
item['cap_score'] = data['cap-score']
item['cost_score'] = data['cost-score']
item['nm_rent'] = data['nm-rent']
item['nm_cost'] = data['nm-cost']
item['nm_appr'] = data['nm-appr']
item['nm_risk'] = data['nm-risk']
item['nm_cap'] = data['nm-cap']
# added neighborhood houses comparation data
item['first_price'] = '%.2f' % data['first_price']
item['first_appr'] = '%.2f' % data['first_appr']
item['first_cash'] = '%.2f' % data['first_cash']
item['second_price'] = '%.2f' % data['second_price']
item['second_appr'] = '%.2f' % data['second_appr']
item['second_cash'] = '%.2f' % data['second_cash']
item['third_price'] = '%.2f' % data['third_price']
item['third_appr'] = '%.2f' % data['third_appr']
item['third_cash'] = '%.2f' % data['third_cash']
# print("IT", item)
if item.get('fixme') and not incoming.get('changed'):
upsert_to_es(item)
# calculate started
return item
def job(neighbor):
neighbor_statistics = {}
if not neighbor.properties:
return
p = json.loads(neighbor.properties)['features'][0]
_body = {
"query": {
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']
}
}
},
"aggs": {
"median_rent_income_ratio": {
"percentiles": {
"field": "rental_income_ratio",
"percents": [50.0]
}
},
"median_increase_ratio": {
"percentiles": {
"field": "increase_ratio",
"percents": [50.0]
}
},
"median_sale_rent_ratio": {
"percentiles": {
"field": "sale_rent_ratio",
"percents": [50.0]
}
},
"median_house_price": {
"percentiles": {
"field": "house_price_dollar",
"percents": [50.0]
}
},
"median_rent_price": {
"percentiles": {
"field": "rent",
"percents": [50.0]
}
},
"score": {
"percentiles": {
"field": "score",
"percents": [50.0]
}
},
"median_airbnb_price": {
"percentiles": {
"field": "airbnb_rent",
"percents": [50.0]
}
},
"median_airbnb_rental_ratio": {
"percentiles": {
"field": "airbnb_rental_ratio",
"percents": [50.0]
}
}
}
}
try:
with app.app_context():
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=0)
p['properties']['rent_increase_ratio'] = res['aggregations'][
'median_rent_income_ratio']['values']['50.0']
p['properties']['increase_ratio'] = res['aggregations'][
'median_increase_ratio']['values']['50.0']
p['properties']['sale_rent_ratio'] = res['aggregations'][
'median_sale_rent_ratio']['values']['50.0']
p['properties']['house_price'] = res['aggregations'][
'median_house_price']['values']['50.0']
p['properties']['rent_median'] = res['aggregations'][
'median_rent_price']['values']['50.0']
p['properties']['airbnb_median'] = res['aggregations'][
'median_airbnb_price']['values']['50.0']
p['properties']['airbnb_rental_ratio'] = res['aggregations'][
'median_airbnb_rental_ratio']['values']['50.0']
p['properties']['score'] = res['aggregations']['score']['values'][
'50.0']
p['properties']['region_id'] = neighbor.region_id
airbnb_rental_ratio = None if p['properties'][
'airbnb_rental_ratio'] == u'NaN' else p['properties'][
'airbnb_rental_ratio']
rent_increase_ratio = None if p['properties'][
'rent_increase_ratio'] == u'NaN' else p['properties'][
'rent_increase_ratio']
increase_ratio = None if p['properties'][
'increase_ratio'] == u'NaN' else p['properties']['increase_ratio']
sale_rent_ratio = None if p['properties'][
'sale_rent_ratio'] == u'NaN' else p['properties']['sale_rent_ratio']
total_ratio = increase_ratio + rent_increase_ratio if increase_ratio and rent_increase_ratio else None
house_price = None if p['properties']['house_price'] == u'NaN' else p[
'properties']['house_price']
rent_median = None if p['properties']['rent_median'] == u'NaN' else p[
'properties']['rent_median']
airbnb_rent_median = None if p['properties'][
'airbnb_median'] == u'NaN' else p['properties']['airbnb_median']
score = None if p['properties']['score'] == u'NaN' else p[
'properties']['score']
neighbor_statistics[neighbor.region_id] = {
"increase_ratio": increase_ratio,
"rent_increase_ratio": rent_increase_ratio,
"sale_rent_ratio": sale_rent_ratio,
"house_price": house_price,
"rent_median": rent_median,
"airbnb_rent_median": airbnb_rent_median,
"score": score,
"total_ratio": total_ratio,
"airbnb_rental_ratio": airbnb_rental_ratio,
"total_ratio": total_ratio
}
more_fields_for_irr = {
"acquisition_cost": neighbor.acquisition_cost,
"cap_ex": neighbor.cap_ex,
"disposition_cost": neighbor.disposition_cost,
"insurance_cost": neighbor.insurance_cost,
"leasing_commission": neighbor.leasing_commission,
"property_management_fee": neighbor.property_management_fee,
"property_tax": neighbor.property_tax,
"repair": neighbor.repair,
"vaccancy_rate": neighbor.vaccancy_rate,
"rent_growth": neighbor.rent_growth
}
neighbor_statistics[neighbor.region_id].update(more_fields_for_irr)
redis_store.hmset(settings.NEIGHBORHOOD_STATISTICS,
neighbor_statistics)
except TransportError:
with app.app_context():
logger.warning("ES transport error")
def homedetail():
"""
get information of certain property
"""
incoming = request.get_json()
rule = g.current_rule
home_ids = str(
incoming['home_id']).split(',') if incoming.get('home_id') else None
place_name = incoming.get('place_name')
if not (home_ids or place_name):
return jsonify(error=True, message="Unmatched query parameters"), 409
result = []
includes_fields = json.loads(rule.includes) if rule.includes else []
excludes_fields = json.loads(rule.excludes) if rule.excludes else []
if home_ids:
query_should = [{"match_phrase": {"_id": r}} for r in home_ids if r]
else:
query_should = [{"match_phrase": {"addr": place_name}}]
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
filter_path=['hits.hits._*'],
body={
"query": {
"bool": {
"must": [{
"match_all": {}
}, {
"bool": {
"should": query_should,
"minimum_should_match": 1
}
}],
"filter": [],
"should": [],
"must_not": []
}
}
},
_source_exclude=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule,
exclude=True))
if not res:
return jsonify(success=True, result=[])
items_list = [r['_source'] for r in res['hits']['hits']]
if not items_list:
return jsonify(success=True, result=items_list)
# processed
d = {}
item_processed = [
EsqueryHelper.to_dict_with_re_filter(
EsqueryHelper.process_room_item(item), columns=includes_fields)
for item in items_list
]
# add neighborhood history
for i in item_processed:
if i.get('neighborhood') and i['neighborhood'].get('id'):
i['neighborhood'][
'history'] = get_neighborhood_history_by_regionid(
i['neighborhood'].get('id'))
return jsonify(success=True, result=item_processed)
def calcscore(res_score, item):
db = GetData(res_score['hits']['hits'])
target = GetTargets(db.index.values, db, item)
if len(target) == 0:
return 'N/A'
stats = CalcStats(db)
def score(res=res_score,
stats=stats,
fact1='listing_price',
fact2='Appr',
fact3='Revenue',
fact4='Cap',
item=item):
cost_score = 32 - GetScore(stats, item[fact1], fact1)
appreciation_score = GetScore(stats, item[fact2], fact2)
cap_score = GetScore(stats, item[fact4], fact4)
if appreciation_score == EXCELLENT_SCORE:
risk_score = BAD_SCORE
elif appreciation_score == GOOD_SCORE:
risk_score = LOW_SCORE
elif appreciation_score == MEDIUM_SCORE:
risk_score = MEDIUM_SCORE
elif appreciation_score == LOW_SCORE:
risk_score = GOOD_SCORE
else:
risk_score = EXCELLENT_SCORE
rent_score = GetScore(stats, item[fact3], fact3)
frame_risk_score = []
frame_cost_score = np.array(
list(32 - GetScore(stats, target.loc[i][fact1], fact1)
for i in target.index.values))
frame_appr_score = np.array(
list(
GetScore(stats, target.loc[i][fact2], fact2)
for i in target.index.values))
frame_rent_score = np.array(
list(
GetScore(stats, target.loc[i][fact3], fact3)
for i in target.index.values))
frame_cap_score = np.array(
list(
GetScore(stats, target.loc[i][fact4], fact4)
for i in target.index.values))
for score in frame_appr_score:
if score == EXCELLENT_SCORE:
risk_score = BAD_SCORE
elif score == GOOD_SCORE:
risk_score = LOW_SCORE
elif score == MEDIUM_SCORE:
risk_score = MEDIUM_SCORE
elif score == LOW_SCORE:
risk_score = GOOD_SCORE
else:
risk_score = EXCELLENT_SCORE
frame_risk_score.append(score)
frame_risk_score = np.array(frame_risk_score)
frame_score = frame_risk_score + frame_appr_score + frame_rent_score + frame_cost_score + frame_cap_score
risk_score = 32 - appreciation_score
total_score = cost_score + appreciation_score + risk_score + rent_score + cap_score
#print(total_score, cost_score, appreciation_score, risk_score, rent_score, cap_score)
#print(np.median(frame_cost_score), np.median(frame_appr_score), np.median(frame_risk_score), np.median(frame_rent_score), np.median(frame_cap_score))
return {
'item-total-score': total_score,
'cost-score': cost_score,
'appreciation-score': appreciation_score,
'risk-score': risk_score,
'rent-score': rent_score,
'cap-score': cap_score,
'frame-score': frame_score,
'nm-cost': np.median(frame_cost_score),
'nm-appr': np.median(frame_appr_score),
'nm-risk': np.median(frame_risk_score),
'nm-rent': np.median(frame_rent_score),
'nm-cap': np.median(frame_cap_score)
}
data = score()
target['Score'] = data['frame-score']
median_score = np.median(data['frame-score'])
res_db = target.sort_values('Score', ascending=False)
#print(res_db)
first_index, second_index, third_index = res_db.index.values[
0], res_db.index.values[1], res_db.index.values[2]
first, second, third = res_db.loc[first_index]['Address'], res_db.loc[
second_index]['Address'], res_db.loc[third_index]['Address']
first_addr, second_addr, third_addr = res_db.loc[first_index][
'url'], res_db.loc[second_index]['url'], res_db.loc[third_index]['url']
first_price, second_price, third_price = res_db.loc[first_index][
'listing_price'], res_db.loc[second_index][
'listing_price'], res_db.loc[third_index]['listing_price']
# start fix appreciation
top_indexes = [first_index, second_index, third_index]
top_apprs = []
for index in top_indexes:
# make iterative new es_calls
centroid = res_db.loc[index]['coordinate']
nearby_range = "3mi"
room_length = 6000
query = {
"query": {
"bool": {
"must": [],
"filter": {
"geo_distance": {
"distance": nearby_range,
"location_point": centroid,
}
},
#"must": [{"match_phrase":{"status":rent_status}}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
},
"sort": [{
"_geo_distance": {
"location_point": centroid,
"order": "asc",
"unit": "mi"
}
}]
}
res = es.search(
body=query,
size=room_length,
)
item = {
'RoomType': res_db.loc[index]['RoomType'],
'Beds': res_db.loc[index]['Beds'],
'Baths': res_db.loc[index]['Baths'],
'size': res_db.loc[index]['Size'],
'Revenue': res_db.loc[index]['Revenue'],
'Cap': res_db.loc[index]['Cap'],
'Appr': res_db.loc[index]['Appr'],
'listing_price': res_db.loc[index]['listing_price']
}
top_apprs.append(calcappr(res, item))
first_appr, second_appr, third_appr = top_apprs[0], top_apprs[
1], top_apprs[2]
#print("TOP", top_apprs)
# end fix appreciation
first_cash, second_cash, third_cash = res_db.loc[first_index][
'Ratio'], res_db.loc[second_index]['Ratio'], res_db.loc[third_index][
'Ratio']
result = {
'price': stats['listing_price']['50'],
'appr': stats['Appr']['50'],
'revenue': stats['Revenue']['50'],
'score': data['item-total-score'],
'cost-score': data['cost-score'],
'appr-score': data['appreciation-score'],
'risk-score': data['risk-score'],
'rent-score': data['rent-score'],
'cap-score': data['cap-score'],
'ratio': stats['Ratio']['50'],
'cap': stats['Cap']['50'],
'median_score': median_score,
'first': first,
'second': second,
'third': third,
'first_address': first_addr,
'second_address': second_addr,
'third_address': third_addr,
'nm-cost': data['nm-cost'],
'nm-appr': data['nm-appr'],
'nm-risk': data['nm-risk'],
'nm-rent': data['nm-rent'],
'nm-cap': data['nm-cap'],
'first_price': first_price,
'first_appr': first_appr,
'first_cash': first_cash,
'second_price': second_price,
'second_appr': second_appr,
'second_cash': second_cash,
'third_price': third_price,
'third_appr': third_appr,
'third_cash': third_cash
}
return result
def search_text():
rule = g.current_rule
includes = rule.includes
_search_text = request.args.get("search_text")
result = {}
# address search
if "addr" in includes or len(includes) == 0:
_body = {
'query': {
'bool': {
'must': {
"multi_match": {
"fields": ["addr", "city"],
"query": _search_text
}
}
}
}
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=10,
_source_include=["addr"])
result['addr'] = [r['_source'] for r in res['hits']['hits']]
## zipcode search
if "zipcode" in includes or len(includes) == 0:
_body = {
'query': {
'bool': {
'must': {
"multi_match": {
"fields": ["zipcode"],
"query": _search_text
}
}
}
}
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
_source_include=["zipcode"])
result['zipcode'] = [r['_source'] for r in res['hits']['hits']]
#city search
if "city" in includes or len(includes) == 0:
_body = {
'query': {
'bool': {
'must': {
"multi_match": {
"fields": ["city"],
"query": _search_text
}
}
}
}
}
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
_source_include=["city", "addr"])
result['city'] = [r['_source'] for r in res['hits']['hits']]
return jsonify(result=result)
def query():
"""
property search and sort within the certain areas or an area
"""
rule = g.current_rule
includes_fields = json.loads(rule.includes) if rule.includes else []
excludes_fields = json.loads(rule.excludes) if rule.excludes else []
_area_id = request.args.get("_na")
_search_text = request.args.get("search_text")
_beds = request.args.get("beds")
_bedroom = request.args.get("bedroom") # support mutiple bed query
_upper_bed = request.args.get("bedroom_upper")
_baths = request.args.get("baths")
_areamin = request.args.get("areamin")
_areamax = request.args.get("areamax")
_val_expectmin = request.args.get("val_expectmin")
_val_expectmax = request.args.get("val_expectmax")
_rent_expectmin = request.args.get("rent_expectmin")
_rent_expectmax = request.args.get("rent_expectmax")
_house_pricemin = request.args.get("house_pricemin")
_house_pricemax = request.args.get("house_pricemax")
_house_price_range = request.args.get("house_price_ranges") # price range
_upper_house_price = request.args.get("house_price_upper")
_status = request.args.get("status")
_zipcode = request.args.get("zipcode")
_sort_by_val_expect = request.args.get("sort_val") or "1"
_sort_by_price = request.args.get("sort_price")
_sort_by_online_date = request.args.get("sort_od")
_sort_by_cz = request.args.get("sort_cz")
_sort_by_year = request.args.get("sort_year")
_limit = request.args.get('limit')
_page = request.args.get('page')
_online_day = request.args.get('online_day')
_isVilla = request.args.get('isVilla')
_isApartment = request.args.get('isApartment')
if _page:
page = int(_page) - 1
else:
page = 0
if _limit:
limit = int(_limit)
else:
limit = 20
lm = page * limit
_area_id = _area_id.split(',') if _area_id else []
_area_id = app.config['POPULAR_AREAS'] if not _area_id else _area_id
_query = []
query_body = {
'bool': {
'must':
filter_city(_query,
area_ids=_area_id,
val_expect=[_val_expectmin, _val_expectmax],
beds=_beds,
baths=_baths,
area=[_areamin, _areamax],
house_price=[_house_pricemin, _house_pricemax],
rent_expect=[_rent_expectmin, _rent_expectmax],
status=_status,
isVilla=_isVilla,
isApartment=_isApartment,
online_day=_online_day,
zipcode=_zipcode,
bedroom=_bedroom,
price_range=_house_price_range,
upper_bed=_upper_bed,
house_price_range=_house_price_range,
upper_house_price=_upper_house_price),
'must_not': [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
}
# prepare the query body (with search)
if _search_text:
query_body['bool']['must'].append({
"multi_match": {
"fields": ["city", "addr", "zipcode"],
"query": _search_text
}
})
_sort_body = sort_result(sort_val=_sort_by_val_expect,
sort_price=_sort_by_price,
sort_od=_sort_by_online_date,
sort_cz=_sort_by_cz,
sort_year=_sort_by_year)
_body = {"query": query_body, "sort": _sort_body}
res = es.search(
index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=limit, # TODO
_source_include=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule, include=True),
_source_exclude=EsqueryHelper.
config_include_or_exclude_field_from_rule(rule, exclude=True),
from_=lm)
result = {}
result['rooms'] = {}
result['rooms'] = [
EsqueryHelper.to_dict_with_filter(
EsqueryHelper.process_room_item(source["_source"]),
columns=includes_fields) for source in res['hits']['hits']
] if res else []
result['length'] = res['hits']['total']
return jsonify(result=result)
def job(area):
area_statistics = {}
if not area.properties:
return
p = area.properties['features'][0]
_body = {
"query": {
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']
}
}
},
"aggs":{
"median_rent_income_ratio":
{"percentiles":{"field":"rental_income_ratio","percents":[50.0]}},
"median_increase_ratio":
{"percentiles":{"field":"increase_ratio","percents":[50.0]}},
"median_sale_rent_ratio":
{"percentiles":{"field":"sale_rent_ratio","percents":[50.0]}},
"median_house_price":
{"percentiles":{"field":"house_price_dollar","percents":[50.0]}},
"median_rent_price":
{"percentiles":{"field":"rent","percents":[50.0]}},
"score":
{"percentiles":{"field":"score","percents":[50.0]}},
"median_airbnb_price":
{"percentiles":{"field":"airbnb_rent","percents":[50.0]}},
"median_airbnb_rental_ratio":
{"percentiles":{"field":"airbnb_rental_ratio","percents":[50.0]}}
}
}
with app.app_context():
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=0
)
p['properties']['rent_increase_ratio'] = res['aggregations']['median_rent_income_ratio']['values']['50.0']
p['properties']['increase_ratio'] = res['aggregations']['median_increase_ratio']['values']['50.0']
p['properties']['sale_rent_ratio'] = res['aggregations']['median_sale_rent_ratio']['values']['50.0']
p['properties']['house_price'] = res['aggregations']['median_house_price']['values']['50.0']
p['properties']['rent_median'] = res['aggregations']['median_rent_price']['values']['50.0']
p['properties']['airbnb_median'] = res['aggregations']['median_airbnb_price']['values']['50.0']
p['properties']['airbnb_rental_ratio'] = res['aggregations']['median_airbnb_rental_ratio']['values']['50.0']
p['properties']['score'] = res['aggregations']['score']['values']['50.0']
p['properties']['region_id'] = area.geoid
# separate sale and sold status
with app.app_context():
status_sale = 2
_body_separate = {
"query": {
"bool":{
"must":[{"match":{"status":2}}],
"filter":{
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']}
}
}
}
},
"aggs":{
"median_house_price":
{"percentiles":{"field":"price","percents":[50.0]}}
}
}
res_sale = es.search(index=HOME_INDEX,doc_type=HOME_TYPE,body=_body_separate,size=0)
with app.app_context():
status_sold = 4
_body_separate = {
"query": {
"bool":{
"must":[{"match":{"status":4}}],
"filter":{
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']}
}
}
}
},
"aggs":{
"median_house_price":
{"percentiles":{"field":"price","percents":[50.0]}}
}
}
res_sold = es.search(index=HOME_INDEX,doc_type=HOME_TYPE,body=_body_separate,size=0)
p['properties']['listing_price'] = res_sale['aggregations']['median_house_price']['values']['50.0']
p['properties']['sold_price'] = res_sold['aggregations']['median_house_price']['values']['50.0']
airbnb_rental_ratio = None if p['properties']['airbnb_rental_ratio']==u'NaN' else p['properties']['airbnb_rental_ratio']
rent_increase_ratio = None if p['properties']['rent_increase_ratio']==u'NaN' else p['properties']['rent_increase_ratio']
increase_ratio = None if p['properties']['increase_ratio']==u'NaN' else p['properties']['increase_ratio']
sale_rent_ratio = None if p['properties']['sale_rent_ratio']==u'NaN' else p['properties']['sale_rent_ratio']
total_ratio = increase_ratio + rent_increase_ratio if increase_ratio and rent_increase_ratio else None
house_price = None if p['properties']['house_price']==u'NaN' else p['properties']['house_price']
rent_median = None if p['properties']['rent_median']==u'NaN' else p['properties']['rent_median']
airbnb_rent_median = None if p['properties']['airbnb_median']==u'NaN' else p['properties']['airbnb_median']
score = None if p['properties']['score']==u'NaN' else p['properties']['score']
sale_price = None if p['properties']['listing_price'] ==u'NaN' else p['properties']['listing_price']
sold_price = None if p['properties']['sold_price'] ==u'NaN' else p['properties']['sold_price']
area_statistics[area.geoid] = {"increase_ratio":increase_ratio,
"rent_increase_ratio":rent_increase_ratio,
"sale_rent_ratio":sale_rent_ratio,
"house_price":house_price,
"rent_median":rent_median,
"airbnb_rent_median":airbnb_rent_median,
"score":score,
"total_ratio":total_ratio,
"airbnb_rental_ratio":airbnb_rental_ratio,
"score":score,
"sale_price":sale_price,
"sold_price":sold_price}
more_fields_for_irr = {"acquisition_cost":area.acquisition_cost,
"cap_ex":area.cap_ex,
"disposition_cost":area.disposition_cost,
"insurance_cost":area.insurance_cost,
"leasing_commission":area.leasing_commission,
"property_management_fee":area.property_management_fee,
"property_tax":area.property_tax,
"repair":area.repair,
"vaccancy_rate":area.vaccancy_rate,
"rent_growth":area.rent_growth,
"down_payment":area.down_payment,
"expenses":area.expenses,
"loan_interest_rate":area.loan_interest_rate,
"closing_costs_misc":area.closing_costs_misc}
area_statistics[area.geoid].update(more_fields_for_irr)
redis_store.hmset(settings.AREA_STATISTICS,area_statistics)
def get_city_sources(city):
RENT, SALE, SOLD = 1, 2, 4
ROOMS_LENGTH = 20000
query_rent = {
"query": {
"bool": {
"must": [{
"match_phrase": {
"city": city
}
}, {
"match_phrase": {
"status": RENT
}
}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
}
}
rent = es.search(
body=query_rent,
size=ROOMS_LENGTH,
)['hits']['hits']
query_sale = {
"query": {
"bool": {
"must": [{
"match_phrase": {
"city": city
}
}, {
"match_phrase": {
"status": SALE
}
}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
}
}
sale = es.search(
body=query_sale,
size=ROOMS_LENGTH,
)['hits']['hits']
query_sold = {
"query": {
"bool": {
"must": [{
"match_phrase": {
"city": city
}
}, {
"match_phrase": {
"status": SOLD
}
}],
"must_not": [{
"match_phrase": {
"room_type": {
"query": ""
}
}
}]
}
}
}
sold = es.search(
body=query_sold,
size=ROOMS_LENGTH,
)['hits']['hits']
listing_price = np.median(
np.array(
list(item['_source']['house_price_dollar'] for item in sale
if isinstance(item['_source']['house_price_dollar'], float))))
sold_price = np.median(
np.array(
list(item['_source']['house_price_dollar'] for item in sold
if isinstance(item['_source']['house_price_dollar'], float))))
rent = np.median(
np.array(
list(item['_source']['rent'] for item in rent
if isinstance(item['_source']['rent'], float))))
return {
"listing_price": listing_price,
"sold_price": sold_price,
"rent": rent
}