def addlocation():
form = locationForm()
if form.validate_on_submit():
location = Locations(place_name=form.place_name.data,
county=form.county.data,
country=form.country.data)
db.session.add(location)
db.session.commit()
return redirect(url_for('index'))
return render_template('locationform.html', form=form)
def add_location():
form = LocationForm()
if request.method == "POST":
location = Locations(location_name=form.location.data)
db.session.add(location)
db.session.commit()
flash("Your Location has been created", "success")
return redirect(url_for('locations.locations'))
return render_template('add_location.html',
form=form,
title='Add Location')
def add_loc_proj(i, warning=None):
""" Compiles location and project data given an index for the tbl_project
DataFrame
"""
# -- Adding Location Data ----------------------------------------------- #
state_code = tbl_project['txt_StateCode'][i]
state_name = lkp_stateandfhwadistrict.loc[
lkp_stateandfhwadistrict['txt_StateCode'] == state_code,
'txt_StateName'].values[0] if not pd.isna(state_code) else None
country_code = tbl_project['txt_CountryCode'][i]
country_name = tbl_country.loc[tbl_country['txt_CountryCode'] ==
country_code,
'txt_CountryDescription'].values[0]
loc = Locations(
address=tbl_project['txt_Address'][i]
if not pd.isna(tbl_project['txt_Address'][i]) else None,
city=tbl_project['txt_City'][i]
if not pd.isna(tbl_project['txt_City'][i]) else None,
county=tbl_project['txt_County'][i]
if not pd.isna(tbl_project['txt_County'][i]) else None,
state=state_name,
country=country_name,
latitude=tbl_project['dbl_Latitude'][i]
if abs(tbl_project['dbl_Latitude'][i]) < 100 else None,
longitude=tbl_project['dbl_Longitude'][i]
if abs(tbl_project['dbl_Longitude'][i]) < 100 else None,
)
db.session.add(loc)
# -- Adding Project Data ------------------------------------------------ #
prj = Projects(
location=loc,
user_id=data_owner().id,
source_db='FHWA DFLTD v.2',
source_id=int(tbl_project['lng_KeyProject'][i]),
description=tbl_project['mem_Remarks'][i]
if not pd.isna(tbl_project['mem_Remarks'][i]) else None,
site_name=tbl_project['txt_ProjectName'][i]
if not pd.isna(tbl_project['txt_ProjectName'][i]) else None,
source_ref=tbl_project['txt_Publication'][i]
if not pd.isna(tbl_project['txt_Publication'][i]) else None,
contractor=tbl_project['txt_GeneralContractor'][i]
if not pd.isna(tbl_project['txt_GeneralContractor'][i]) else None,
number=tbl_project['txt_ProjectID'][i]
if not pd.isna(tbl_project['txt_ProjectID'][i]) else None,
title=tbl_project['txt_Title'][i]
if not pd.isna(tbl_project['txt_Title'][i]) else None,
date_added=pd.to_datetime(tbl_project['dte_AddDate'][i] if not pd.isna(
tbl_project['dte_AddDate'][i]) else None),
warning=warning)
return prj
def setUp(self):
db.create_all()
location = Locations(id=1,
place_name="Bennachie",
county="Aberdeenshire",
country="Scotland")
bird = Birds(id=1,
scientific_name="Motacilla alba",
common_name="Pied Wagtail")
sighting = Sightings(location_id=1,
bird_id=1,
recorded="24/06/15",
gender="Male",
life_stage="Adult",
number=2)
db.session.add(location)
db.session.add(bird)
db.session.add(sighting)
db.session.commit()
def location_post(args):
status = query_vendor_status(args["vendor_id"])
response = dict(vendor_id=args["vendor_id"], success=False)
if not status:
response["msg"] = "vendor does not exist".format(args["vendor_id"])
elif status and not status.active:
response["msg"] = "vendor is not currently active".\
format(args["vendor_id"])
elif status and status.active:
try:
location = Locations(**args)
db.session.add(location)
db.session.commit()
response["success"] = True
response["msg"] = "location updated".\
format(args["vendor_id"])
except Exception as e:
response["exception"] = str(e)
return jsonify(response)
def add_location(session_obj, zipcode, average_rating, average_price,
highest_price, lowest_price, popular_food_type,
highest_rated_restaurant, most_popular_restaurant,
number_restaurants):
"""
Adds a location with the provided variables to the
provided database.
"""
new_location = Locations(zipcode=zipcode,
average_rating=average_rating,
average_price=average_price,
highest_price=highest_price,
lowest_price=lowest_price,
popular_food_type=popular_food_type,
highest_rated_restaurant=highest_rated_restaurant,
most_popular_restaurant=most_popular_restaurant,
number_restaurants=number_restaurants)
session_obj.add(new_location)
session_obj.commit()
def add_loc_proj(prj_id, pile_id):
""" Compiles location and project data given an index for the tbl_project
DataFrame
"""
# -- Adding Location Data ----------------------------------------------- #
parish_id = int(dt_projects['Parish_1'][dt_projects.ID == prj_id])
parish = lst_parishes['Parish_Name'][lst_parishes.ID ==
parish_id].values[0]
lat = dt_testpiles['Latitude'][dt_testpiles.ID == pile_id].values[0]
lon = dt_testpiles['Longitude'][dt_testpiles.ID == pile_id].values[0]
loc = Locations(county=parish,
state='Louisiana',
country='USA',
latitude=lat,
longitude=lon)
db.session.add(loc)
# -- Adding Project Data ------------------------------------------------ #
contr_id = int(dt_projects['Contractor'][dt_projects.ID == prj_id])
contractor = lst_contractors['Contractor'][lst_contractors.ID ==
contr_id].values[0]
source_ref = 'From: {}'.format(
source_dic[dt_projects['Source'][dt_projects.ID == prj_id].values[0]])
number = '{}; {}'.format(
dt_projects['Project_Num_H'][dt_projects.ID == prj_id].values[0],
dt_projects['Project_Num_old'][dt_projects.ID == prj_id].values[0])
prj = Projects(
location=loc,
user_id=data_owner().id,
source_db='LTRC LAPLTD',
source_id=prj_id,
source_ref=source_ref,
number=number,
title=dt_projects['Project_Name'][dt_projects.ID == prj_id].values[0],
site_name=dt_projects['Route'][dt_projects.ID == prj_id].values[0] if
not pd.isna(dt_projects['Route'][dt_projects.ID == prj_id].values[0])
else None,
contractor=contractor)
return prj
def load_iowa_records():
""" Iterates through source files and adds the Iowa PILOT records to the
database
"""
plt_records = pd.read_csv(plt_records_path)
counties = pd.read_csv(counties_path)
pile_types = pd.read_csv(pile_types_path)
avg_soil = pd.read_csv(avg_soil_path)
slt_results = pd.read_csv(slt_path)
print('--- Importing Iowa PILOT records ---')
for i in tqdm(range(len(plt_records))):
# -- Adding Location Data ------------------------------------------- #
loc = Locations(
county=counties.loc[
counties['ID'] == plt_records['County'][i], 'County'].iloc[0],
township=plt_records['Township'][i],
description=plt_records['Pile Location'][i])
db.session.add(loc)
# -- Adding Project Data -------------------------------------------- #
wt_loc = plt_records['Water Table Location'][i]
if pd.isna(wt_loc):
desc = None
else:
desc = 'Water Table ' + str(wt_loc)
prj = Projects(
user_id=data_owner().id,
source_db='Iowa PILOT',
source_id=int(plt_records['ID'][i]),
contractor=plt_records['Contractor'][i],
number=plt_records['Project Number'][i],
description=desc,
location=loc)
db.session.add(prj)
# -- Adding Pile Data ----------------------------------------------- #
emb_pile_length = plt_records['Embedded Pile Length'][i]
pile_length = plt_records['Pile Length'][i]
if pd.isna(pile_length):
pile_length = pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Length'
].iloc[0]
if pd.isna(pile_length):
pile_length = emb_pile_length
pile = Piles(
project=prj,
type=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Type'
].iloc[0],
shape=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Shape'
].iloc[0] if not pd.isna(pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Shape'
].iloc[0]) else None,
emb_length=emb_pile_length,
length=pile_length,
diameter=pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Diameter'
].iloc[0] if not pd.isna(pile_types.loc[
pile_types['ID'] == plt_records['Pile Type'][i], 'Diameter'
].iloc[0]) else None,
cross_area=plt_records['Pile Cross-Sectional Area'][i] if not
pd.isna(plt_records['Pile Cross-Sectional Area'][i]) else None,
weight=plt_records['Weight of Pile'][i] if not pd.isna(
plt_records['Weight of Pile'][i]) else None,
predrill_depth=plt_records['Initial Bored Hole Depth'][i] if
plt_records['Initial Bored Hole Depth'][i] != 0 else None,
predrilled=True if plt_records['Initial Bored Hole Depth'][i] != 0
else None,
date_driven=pd.to_datetime(
plt_records['Date Driven'][i] if not
pd.isna(plt_records['Date Driven'][i]) else None),
design_load=plt_records['Design Load'][i] if not
pd.isna(plt_records['Design Load'][i]) else None,
toe_elevation=plt_records['Pile Toe Elevation'][i] if not
pd.isna(plt_records['Pile Toe Elevation'][i]) else None,
)
db.session.add(pile)
# -- Adding Soil Data ----------------------------------------------- #
soil_class = {1: 'CLAY', 2: 'SAND', 3: 'SACL'}
p_soil = plt_records['Test Site Soil Classification'][i]
boring = Borings(
project=prj,
name='Average Soil Profile',
predom_soil=soil_class[p_soil] if not pd.isna(p_soil) else None,
remarks=plt_records['Borehole Numbers at Test Pile Location'][i],
)
db.session.add(boring)
layers = avg_soil[avg_soil['Record ID'] == i+1]
for l in layers.index:
layer = Layers(
boring=boring,
index=layers['Soil Layer'][l] if not
pd.isna(layers['Soil Layer'][l]) else None,
soil_type=iowa_soil_dict[layers['Material Description'][l]] if
not pd.isna(layers['Material Description'][l]) else None,
description=layers['Material Description'][l],
height=layers['Thickness'][l],
nval=layers['NAVG'][l]
if not pd.isna(layers['NAVG'][l]) else None,
iowa_unit_friction=layers['Unit Friction'][l],
iowa_total_friction=layers['Total Friction'][l],
)
db.session.add(layer)
# -- Adding load test data ------------------------------------------ #
load_test = LoadTests(
pile=pile,
direction='Compression (Static)',
tested_by=plt_records['Tested By'][i],
date_tested=pd.to_datetime(
plt_records['Date Tested'][i] if not
pd.isna(plt_records['Date Tested'][i]) else None),
rebound_time=plt_records['Rebound Time Duration'][i] if not
pd.isna(plt_records['Rebound Time Duration'][i]) else None,
rebound_displacement=plt_records['Rebound Gauge Reading'][i],
remarks=plt_records['Static Load Test Remarks'][i],
reliable=True
if plt_records['Load Test Reliability Classification'][i] == 1
else False,
)
db.session.add(load_test)
static_test = slt_results[slt_results['Record ID'] == i+1]
i_slt = 1
for p in static_test.index:
slt_point = StaticTests(
index=i_slt,
load=static_test['Load (Tons)'][p] * 1000,
displacement=static_test['Gauge Reading (in)'][p],
load_test=load_test
)
db.session.add(slt_point)
i_slt += 1
# -- Adding Interpreted Capacity Data ------------------------------- #
interp_dict = {
'Maximum Applied Load': 'Maximum Load',
'Highest Gauge Reading': 'Maximum Displacement',
'Davisson Pile Capacity': 'Standard Davisson'
}
for key in interp_dict:
ival = plt_records[key][i]
if not pd.isna(ival) and float(ival) > 0:
ic = InterpCapacities(
load=float(ival) * 2 if key != 'Highest Gauge Reading'
else None,
displacement=float(ival) if key == 'Highest Gauge Reading'
else None,
type=interp_dict[key],
origin='source DB',
load_test=load_test)
db.session.add(ic)
# -- Adding Calculated Capacity Data -------------------------------- #
calc_dict = {
'Formula Bearing': 'Iowa DOT Modified ENR (bearing)',
'Theoretical End Bearing': 'Iowa Theoretical End Bearing',
'Theoretical Pile Capacity': 'Iowa Theoretical Capacity',
'Blue Book Capacity': 'Iowa Blue Book Method',
'SPT Capacity': 'Meyerhof',
'Alpha Capacity': 'API 1984',
'Beta Capacity': 'Beta Burland 1973',
'Nordland Capacity': 'Nordlund',
'ENR Capacity': 'ENR Formula',
'Modified ENR Capacity': 'Iowa DOT Modified ENR',
'Gates Capacity': 'Gates Formula',
'FHWA Modified Gates Capacity': 'FHWA Modified Gates Formula',
'Janbu Capacity': 'Janbu Formula',
'PCUBC Capacity': 'Pacific Coast Uniform BC Formula',
'WSDOT Capacity': 'Washington DOT Formula'
}
for key in calc_dict:
cval = plt_records[key][i]
if not pd.isna(cval) and float(cval) > 0:
cc = CalcCapacities(
load=float(cval) * 2,
type=calc_dict[key],
origin='source DB',
pile=pile)
db.session.add(cc)
# -- Adding Attachments --------------------------------------------- #
attach_attr = ['Attachments (1)', 'Attachments (2)', 'Attachments (3)',
'Attachments (4)', 'Attachments (5)', 'Attachments (6)']
for attr in attach_attr:
val = plt_records[attr][i]
if not pd.isna(val):
fname = val.split('#')[0]
furl = val.split('#')[1]
attachment = Attachments(
project=prj,
file_name=fname,
file_url=furl
)
db.session.add(attachment)
# -- Adding Miscellaneous Data -------------------------------------- #
misc = Misc(
project=prj,
iowa_test_folder=plt_records['Test Folder'][i],
iowa_lab_number=plt_records['Lab Number'][i],
iowa_design_number=plt_records['Design Number'][i],
iowa_section=plt_records['Section'][i],
iowa_date_reported=pd.to_datetime(
plt_records['Date Reported'][i] if not
pd.isna(plt_records['Date Reported'][i]) else None),
iowa_record_complete=plt_records['Record Complete'][i],
iowa_borehole_count=plt_records['Total Number of Boreholes'][i]
if not pd.isna(
plt_records['Total Number of Boreholes'][i]) else None,
iowa_spt_count=plt_records['Boreholes With SPT Data'][i] if not
pd.isna(plt_records['Boreholes With SPT Data'][i]) else None,
iowa_borehole_near_pile=plt_records[
'Borehole at Test Pile Location'][i]
)
db.session.add(misc)
db.session.commit()
def add_olson_data(record, qs=None):
""" Accepts an Olson record as extracted from ``extract_olson_values()``
and commits this record to the database.
"""
loc = Locations(description=record['location'])
db.session.add(loc)
prj = Projects(user_id=data_owner().id,
source_db='Olson APC',
source_id=record['source_id'],
description=record['description'],
warning=record['warning'],
source_ref=record['source_ref'],
dqf=record['dqf'],
location=loc)
db.session.add(prj)
misc = Misc(reo_check=record['reo_check'],
ts_check=record['ts_check'],
ut_boring=record['ut_boring'],
ut_sounding=record['ut_sounding'],
i_code=record['i_code'],
project=prj)
db.session.add(misc)
boring = Borings(predom_soil=record['predom_soil'],
layer_count=record['layer_count'],
ewt=record['ewt'],
sqf=record['sqf'],
project=prj)
db.session.add(boring)
cnt = len(record['layers']['index'])
for i in range(cnt):
layer = Layers(index=record['layers']['index'][i],
soil_type=record['layers']['soil_type'][i],
height=record['layers']['height'][i],
evso=record['layers']['evso'][i],
tuw=record['layers']['tuw'][i],
water_content=record['layers']['water_content'][i],
nval=record['layers']['nval'][i],
cnval=record['layers']['cnval'][i],
ssuu=record['layers']['ssuu'][i],
ssfv=record['layers']['ssfv'][i],
ssms=record['layers']['ssms'][i],
ssqt=record['layers']['ssqt'][i],
qc=record['layers']['qc'][i],
fsl=record['layers']['fsl'][i],
liquid_limit=record['layers']['liquid_limit'][i],
plasticity=record['layers']['plasticity'][i],
boring=boring)
db.session.add(layer)
pile = Piles(type=record['type'],
shape=record['shape'],
length=record['length'],
emb_length=record['emb_length'],
diameter=record['diameter'],
circumference=record['circumference'],
square_circ=record['square_circ'],
aeol=record['aeol'],
weight=record['weight'] * 1000,
dratio=record['dratio'],
tapered=record['tapered'],
vibro=record['vibro'],
cased=record['cased'],
predrilled=record['predrilled'],
relief_drilled=record['relief_drilled'],
jetted=record['jetted'],
project=prj)
db.session.add(pile)
load_test = LoadTests(
direction='Compression (Static)'
if record['direction'] == 'COMP' else 'Tension (Static)',
setup_time=record['setup_time'],
pile=pile)
db.session.add(load_test)
if qs:
i_slt = 1
for i in qs:
static_test = StaticTests(index=i_slt,
load=i[0],
displacement=i[1],
load_test=load_test)
db.session.add(static_test)
i_slt += 1
if record['half_inch_capacity']:
ic = InterpCapacities(load=record['half_inch_capacity'],
displacement=0.5,
type='Load @ 0.5 inches',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['davisson_capacity']:
ic = InterpCapacities(load=record['davisson_capacity'],
displacement=record['davisson_displacement'],
type='Standard Davisson',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['brown_capacity']:
ic = InterpCapacities(load=record['brown_capacity'],
type='Brown',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['max_load']:
ic = InterpCapacities(load=record['max_load'],
type='Maximum Load',
origin='source DB',
load_test=load_test)
db.session.add(ic)
if record['max_displacement']:
ic = InterpCapacities(displacement=record['max_displacement'],
type='Maximum Displacement',
origin='source DB',
load_test=load_test)
db.session.add(ic)
db.session.commit()