def addVulnerabilityColumns():
# creating a Cursor
table = "popoliforpostgres"
filename = "C:/Data/Python/metodologia.txt"
sql = ""
sqllist = []
if (filename != ""):
infile = open(filename, "r")
lines = infile.readlines()
infile.close()
for i in range(0, len(lines)):
tokens = lines[i]
if (len(sql)) > 1:
#otrosql = otrosql + ", " + tokens
sql = "alter table popoliforpostgres add column " + tokens
print "Logica 525 \n " + sql
sqllist.append(sql)
else:
#otrosql = tokens
sql = ""
conectarpostgres.executeSQL(sqllist)
return sql
def updatevulnerabilityindex(namedatabase):
'''update popoliforpostgres
set vulindex = (select (sum(paramvalue*(vkimportance-vkprotection))/sum(paramvalue))/6 + 0.5 from valutazione where idstructure=17)
where gid=17
'''
sqllist = []
numberofstructures = calculatenumberofstructures(namedatabase)
# vki = Z* W * F
# vkp = wz*m
sql = "update valutazione set vkimportance = w1*z*f, vkprotection=w2*z*n"
sqllist.append(sql)
for idstructura in range(1, numberofstructures):
# otrosql = "update popolispatial set vulnerab = (select sum(paramvalue*(vkimportance-vkprotection))*(1/6)*(1/(select sum(paramvalue) from valutazione where idstructure= " + str(idstructura) + "))+ 0.5 from valutazione where idstructure= " + str(idstructura) +" ) where ogc_fid= " + str(idstructura) + ""
sql = "update popoliforpostgres set vulindex = "
sql = sql + "(select (sum(paramvalue*(vkimportance-vkprotection))/sum(paramvalue))/6 + 0.5 "
sql = sql + "from valutazione where idstructure='" + str(
idstructura) + "'"
sql = sql + ") "
sql = sql + "where gid='" + str(idstructura) + "'"
print sql
sqllist.append(sql)
conectarpostgres.executeSQL(sqllist)
def updatevulnerabilityfactor(namedatabase):
sqllist = []
sql = "update popoliforpostgres set vulfactor = 0.53 + 1.15*vulindex -4*vulindex*vulindex + 4.21*vulindex*vulindex*vulindex"
# print otrosql
sqllist.append(sql)
conectarpostgres.executeSQL(sqllist)
def updatevulnerabilityparamaters(namedatabase):
'''update popoliforpostgres
set vulindex = (select (sum(paramvalue*(vkimportance-vkprotection))/sum(paramvalue))/6 + 0.5 from valutazione where idstructure=17)
where gid=17
'''
sqllist = []
sql = "update valutazione set paramvalue='1' where (idparam=4 or idparam=5 or idparam=6 or idparam=7 )"
print sql
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.5' where idparam=9"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='1' where idparam=10"
print sql
# cur.executd(otrosql)
sql = "update valutazione set paramvalue='0.8' where (idparam=11 or idparam=8)"
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.5' where idparam=12"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='1.5' where idparam=13"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.3' where idparam=14"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
logging.debug(" PredictiveModel 137 ")
for sql in sqllist:
logging.debug(sql)
conectarpostgres.executeSQL(sqllist)
def importValutazioneFromExcelv2(filename, database):
sqllist = readFromExcel(filename)
#import connectdb
import conectarpostgres
conectarpostgres.executeSQL(sqllist)
updatevulnerabilityparamaters(database)
updatevulnerabilityindex(database)
updatevulnerabilityfactor(database)
def populateValutazione(databasename):
numberofstructures = calculatenumberofstructures(databasename)
logging.debug("numberofstructures")
logging.debug(numberofstructures)
# get the active layer
# layer = iface.activeLayer()
'''
init postgres
http://gis.stackexchange.com/questions/86983/how-to-properly-establish-a-postgresql-connection-using-qgscredentials
'''
sqllist = []
for idstructura in range(0, numberofstructures):
for param in range(1, 15):
x = random.sample([0, 1], 1)
# z= random.sample([0,1], 1)
z = x
# f = random.sample([0,1.5], 1) ## dont know the ranges of values
h1 = random.uniform(0, 1.5)
h = round(h1, 2)
f = [h]
w1 = random.sample([1, 2], 1)
# w2= random.sample([1,2], 1)
h1 = random.uniform(0, 1.5)
h = round(h1, 2)
n = [h]
idstructura = str(idstructura)
param = str(param)
x = x[0]
z = z[0]
w1 = w1[0]
f = f[0]
# w2= w2[0]
n = n[0]
w2 = w1
z = x
# n = f
# vki = Z* W * F
vki_factor = w1 * z * f
vki_float = round(vki_factor)
# vkp = wz*m
vkp_factor = w2 * z * n
vkp_float = round(vkp_factor)
x = str(x)
z = str(z)
f = str(f)
w1 = str(w1)
w2 = str(w2)
n = str(n)
# vki = Z* W * F
vki = str(vki_float)
# vkp = wz*m
vkp = str(vkp_float)
'''
idstructure integer NOT NULL,
idparam integer NOT NULL,
x integer,
z integer,
f integer,
w1 integer,
w2 integer,
n integer,
vkimportance integer,
vkprotection integer,
paramvalue real,
'''
sql = " insert into valutazione values (" + idstructura + "," + param + "," + x + ", " + z + ", " + f + "," + w1 + "," + w2 + ", " + n + "," + vki + "," + vkp + ", 0)"
# sql = sql + ";"
# print sql
# cur.execute(otrosql)
sqllist.append(sql)
# cur.execute(otrosql)
sql = "update valutazione set paramvalue='1' where (idparam=4 or idparam=5 or idparam=6 or idparam=7 )"
print sql
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.5' where idparam=9"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='1' where idparam=10"
print sql
# cur.executd(otrosql)
sql = "update valutazione set paramvalue='0.8' where (idparam=11 or idparam=8)"
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.5' where idparam=12"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='1.5' where idparam=13"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
sql = "update valutazione set paramvalue='0.3' where idparam=14"
print sql
# cur.execute(otrosql)
sqllist.append(sql)
logging.debug(" PredictiveModel 137 ")
for sql in sqllist:
logging.debug(sql)
conectarpostgres.executeSQL(sqllist)
print " valutation populated"