def main():
map = options['map']
layer = options['layer']
column = options['column']
mapset = grass.gisenv()['MAPSET']
if not grass.find_file(map, element = 'vector', mapset = mapset):
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
f = grass.vector_layer_db(map, layer)
table = f['table']
keycol = f['key']
database = f['database']
driver = f['driver']
if not table:
grass.fatal(_("There is no table connected to the input vector map. Cannot rename any column"))
cols = column.split(',')
oldcol = cols[0]
newcol = cols[1]
if driver == "dbf":
if len(newcol) > 10:
grass.fatal(_("Column name <%s> too long. The DBF driver supports column names not longer than 10 characters") % newcol)
if oldcol == keycol:
grass.fatal(_("Cannot rename column <%s> as it is needed to keep table <%s> connected to the input vector map") % (oldcol, table))
# describe old col
oldcoltype = None
for f in grass.db_describe(table)['cols']:
if f[0] != oldcol:
continue
oldcoltype = f[1]
oldcollength = f[2]
# old col there?
if not oldcoltype:
grass.fatal(_("Column <%s> not found in table <%s>") % (oldcol, table))
# some tricks
if driver in ['sqlite', 'dbf']:
if oldcoltype.upper() == "CHARACTER":
colspec = "%s varchar(%s)" % (newcol, oldcollength)
else:
colspec = "%s %s" % (newcol, oldcoltype)
grass.run_command('v.db.addcolumn', map = map, layer = layer, column = colspec)
sql = "UPDATE %s SET %s=%s" % (table, newcol, oldcol)
grass.write_command('db.execute', input = '-', database = database, driver = driver, stdin = sql)
grass.run_command('v.db.dropcolumn', map = map, layer = layer, column = oldcol)
else:
sql = "ALTER TABLE %s RENAME %s TO %s" % (table, oldcol, newcol)
grass.write_command('db.execute', input = '-', database = database, driver = driver, stdin = sql)
# write cmd history:
grass.vector_history(map)
def main():
map = options['map']
layer = options['layer']
column = options['column']
otable = options['otable']
ocolumn = options['ocolumn']
f = grass.vector_layer_db(map, layer)
maptable = f['table']
database = f['database']
driver = f['driver']
if driver == 'dbf':
grass.fatal(_("JOIN is not supported for tables stored in DBF format"))
if not maptable:
grass.fatal(_("There is no table connected to this map. Unable to join any column."))
if not grass.vector_columns(map, layer).has_key(column):
grass.fatal(_("Column <%s> not found in table <%s> at layer <%s>") % (column, map, layer))
all_cols_ot = grass.db_describe(otable, driver = driver, database = database)['cols']
all_cols_tt = grass.vector_columns(map, int(layer)).keys()
select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column"
template = string.Template("UPDATE $table SET $colname=(%s);" % select)
for col in all_cols_ot:
# Skip the vector column which is used for join
colname = col[0]
if colname == column:
continue
# Sqlite 3 does not support the precision number any more
if len(col) > 2 and driver != "sqlite":
coltype = "%s(%s)" % (col[1], col[2])
else:
coltype = "%s" % col[1]
colspec = "%s %s" % (colname, coltype)
# Add only the new column to the table
if colname not in all_cols_tt:
if grass.run_command('v.db.addcolumn', map = map, columns = colspec, layer = layer) != 0:
grass.fatal(_("Error creating column <%s>") % colname)
stmt = template.substitute(table = maptable, column = column,
otable = otable, ocolumn = ocolumn,
colname = colname)
grass.verbose(_("Updating column <%s> of vector map <%s>...") % (colname, map))
if grass.write_command('db.execute', stdin = stmt, input = '-', database = database, driver = driver) != 0:
grass.fatal(_("Error filling column <%s>") % colname)
# write cmd history:
grass.vector_history(map)
def get_columns(self, layers, layer):
"""
load columns
:param layers: dictionary of all layers in map
:param layer: layer chosen in widget Layer
"""
for item in script.db_describe(
table=layers[layer]["table"],
driver=layers[layer]["driver"],
database=layers[layer]["database"])['cols']:
self.addItem(item[0])
def _DescribeTables(self):
"""Describe linked tables"""
for layer in self.layers.keys():
# determine column names and types
table = self.layers[layer]["table"]
columns = {} # {name: {type, length, [values], [ids]}}
i = 0
for item in grass.db_describe(
table=self.layers[layer]["table"],
driver=self.layers[layer]["driver"],
database=self.layers[layer]["database"],
)["cols"]:
name, type, length = item
# FIXME: support more datatypes
if type.lower() == "integer":
ctype = int
elif type.lower() == "double precision":
ctype = float
else:
ctype = str
columns[name.strip()] = {
"index": i,
"type": type.lower(),
"ctype": ctype,
"length": int(length),
"values": [],
"ids": [],
}
i += 1
# check for key column
# v.db.connect -g/p returns always key column name lowercase
if self.layers[layer]["key"] not in columns.keys():
for col in columns.keys():
if col.lower() == self.layers[layer]["key"]:
self.layers[layer]["key"] = col.upper()
break
self.tables[table] = columns
return True
def _DescribeTables(self):
"""Describe linked tables"""
for layer in self.layers.keys():
# determine column names and types
table = self.layers[layer]["table"]
columns = {} # {name: {type, length, [values], [ids]}}
i = 0
for item in grass.db_describe(table = self.layers[layer]["table"],
driver = self.layers[layer]["driver"],
database = self.layers[layer]["database"])['cols']:
name, type, length = item
# FIXME: support more datatypes
if type.lower() == "integer":
ctype = int
elif type.lower() == "double precision":
ctype = float
else:
ctype = str
columns[name.strip()] = { 'index' : i,
'type' : type.lower(),
'ctype' : ctype,
'length' : int(length),
'values' : [],
'ids' : []}
i += 1
# check for key column
# v.db.connect -g/p returns always key column name lowercase
if self.layers[layer]["key"] not in columns.keys():
for col in columns.keys():
if col.lower() == self.layers[layer]["key"]:
self.layers[layer]["key"] = col.upper()
break
self.tables[table] = columns
return True
def main():
input = options['input']
db_table = options['db_table']
output = options['output']
key = options['key']
mapset = grass.gisenv()['MAPSET']
if db_table:
input = db_table
if not output:
tmpname = input.replace('.', '_')
output = grass.basename(tmpname)
# check if table exists
try:
nuldev = open(os.devnull, 'w+')
s = grass.read_command('db.tables', flags='p', quiet=True, stderr=nuldev)
nuldev.close()
except CalledModuleError:
# check connection parameters, set if uninitialized
grass.read_command('db.connect', flags='c')
s = grass.read_command('db.tables', flags='p', quiet=True)
for l in decode(s).splitlines():
if l == output:
if grass.overwrite():
grass.warning(_("Table <%s> already exists and will be "
"overwritten") % output)
grass.write_command('db.execute', input='-',
stdin="DROP TABLE %s" % output)
break
else:
grass.fatal(_("Table <%s> already exists") % output)
# treat DB as real vector map...
layer = db_table if db_table else None
vopts = {}
if options['encoding']:
vopts['encoding'] = options['encoding']
try:
grass.run_command('v.in.ogr', flags='o', input=input, output=output,
layer=layer, quiet=True, **vopts)
except CalledModuleError:
if db_table:
grass.fatal(
_("Input table <%s> not found or not readable") %
input)
else:
grass.fatal(_("Input DSN <%s> not found or not readable") % input)
# rename ID col if requested from cat to new name
if key:
grass.write_command('db.execute', quiet=True, input='-',
stdin="ALTER TABLE %s ADD COLUMN %s integer" %
(output, key))
grass.write_command('db.execute', quiet=True, input='-',
stdin="UPDATE %s SET %s=cat" % (output, key))
# ... and immediately drop the empty geometry
vectfile = grass.find_file(output, element='vector', mapset=mapset)['file']
if not vectfile:
grass.fatal(_("Something went wrong. Should not happen"))
else:
# remove the vector part
grass.run_command('v.db.connect', quiet=True, map=output, layer='1',
flags='d')
grass.run_command('g.remove', flags='f', quiet=True, type='vector',
name=output)
# get rid of superfluous auto-added cat column (and cat_ if present)
nuldev = open(os.devnull, 'w+')
grass.run_command('db.dropcolumn', quiet=True, flags='f', table=output,
column='cat', stdout=nuldev, stderr=nuldev)
nuldev.close()
records = grass.db_describe(output)['nrows']
grass.message(_("Imported table <%s> with %d rows") % (output, records))
def main():
map = options["map"]
layer = options["layer"]
column = options["column"]
otable = options["other_table"]
ocolumn = options["other_column"]
if options["subset_columns"]:
scolumns = options["subset_columns"].split(",")
else:
scolumns = None
try:
f = grass.vector_layer_db(map, layer)
except CalledModuleError:
sys.exit(1)
maptable = f["table"]
database = f["database"]
driver = f["driver"]
if driver == "dbf":
grass.fatal(_("JOIN is not supported for tables stored in DBF format"))
if not maptable:
grass.fatal(
_("There is no table connected to this map. Unable to join any column."
))
# check if column is in map table
if column not in grass.vector_columns(map, layer):
grass.fatal(
_("Column <%s> not found in table <%s>") % (column, maptable))
# describe other table
all_cols_ot = grass.db_describe(otable, driver=driver,
database=database)["cols"]
# check if ocolumn is on other table
if ocolumn not in [ocol[0] for ocol in all_cols_ot]:
grass.fatal(
_("Column <%s> not found in table <%s>") % (ocolumn, otable))
# determine columns subset from other table
if not scolumns:
# select all columns from other table
cols_to_add = all_cols_ot
else:
cols_to_add = []
# check if scolumns exists in the other table
for scol in scolumns:
found = False
for col_ot in all_cols_ot:
if scol == col_ot[0]:
found = True
cols_to_add.append(col_ot)
break
if not found:
grass.warning(
_("Column <%s> not found in table <%s>") % (scol, otable))
all_cols_tt = grass.vector_columns(map, int(layer)).keys()
select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column"
template = string.Template("UPDATE $table SET $colname=(%s);" % select)
for col in cols_to_add:
# skip the vector column which is used for join
colname = col[0]
if colname == column:
continue
use_len = False
if len(col) > 2:
use_len = True
# Sqlite 3 does not support the precision number any more
if driver == "sqlite":
use_len = False
# MySQL - expect format DOUBLE PRECISION(M,D), see #2792
elif driver == "mysql" and col[1] == "DOUBLE PRECISION":
use_len = False
if use_len:
coltype = "%s(%s)" % (col[1], col[2])
else:
coltype = "%s" % col[1]
colspec = "%s %s" % (colname, coltype)
# add only the new column to the table
if colname not in all_cols_tt:
try:
grass.run_command("v.db.addcolumn",
map=map,
columns=colspec,
layer=layer)
except CalledModuleError:
grass.fatal(_("Error creating column <%s>") % colname)
stmt = template.substitute(
table=maptable,
column=column,
otable=otable,
ocolumn=ocolumn,
colname=colname,
)
grass.debug(stmt, 1)
grass.verbose(
_("Updating column <%s> of vector map <%s>...") % (colname, map))
try:
grass.write_command("db.execute",
stdin=stmt,
input="-",
database=database,
driver=driver)
except CalledModuleError:
grass.fatal(_("Error filling column <%s>") % colname)
# write cmd history
grass.vector_history(map)
return 0
region = ['amazon','qf','andes']
for reg in region:
for param in ['aspect_compass','slope']:
vect = reg + '_' + param + '_random'
print 'maintenance for vector ' + vect
for col in colList:
# get column from db as a dict
col_dict = gvect.vector_db_select(map=vect, columns=col)['values']
# get cats of NULL entries
null_list = [int(i[1]) for i in col_dict.values() if i[0]=='']
print 'removing NULL entries...'
for n in null_list:
grass.write_command("db.execute", \
stdin="DELETE FROM %s WHERE cat = %d" % (vect,n))
grass.db_describe(vect)
#----------------------------------------------------
# 9.3 - correlation for slope (linear)
# files for results
os.chdir('/Volumes/HDD/Users/guano/Dropbox/artigos/derivadas_dem/stats')
fileOut = open('correlacao_PA_AP_original_slope.txt', 'w')
fileOut.write('coeficientes de correlacao - SLOPE \n')
colListPA = ['r10pa', 'r15pa', 'r20pa', 'r25pa', 'r30pa', \
'r35pa', 'r40pa', 'r45pa', 'r50pa', 'r55pa', 'r01pa']
colListAP = ['r10ap', 'r15ap', 'r20ap', 'r25ap', 'r30ap', \
'r35ap', 'r40ap', 'r45ap', 'r50ap', 'r55ap', 'r01ap']
def main():
table = options['table']
column = options['column']
force = flags['f']
# check if DB parameters are set, and if not set them.
grass.run_command('db.connect', flags='c')
kv = grass.db_connection()
database = kv['database']
driver = kv['driver']
# schema needed for PG?
if force:
grass.message(_("Forcing ..."))
if column == "cat":
grass.warning(
_("Deleting <%s> column which may be needed to keep table connected to a vector map"
) % column)
cols = [f[0] for f in grass.db_describe(table)['cols']]
if column not in cols:
grass.fatal(_("Column <%s> not found in table") % column)
if not force:
grass.message(_("Column <%s> would be deleted.") % column)
grass.message("")
grass.message(
_("You must use the force flag (-f) to actually remove it. Exiting."
))
return 0
if driver == "sqlite":
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
colnames = []
coltypes = []
for f in grass.db_describe(table)['cols']:
if f[0] == column:
continue
colnames.append(f[0])
coltypes.append("%s %s" % (f[0], f[1]))
colnames = ", ".join(colnames)
coltypes = ", ".join(coltypes)
cmds = [
"BEGIN TRANSACTION",
"CREATE TEMPORARY TABLE ${table}_backup(${coldef})",
"INSERT INTO ${table}_backup SELECT ${colnames} FROM ${table}",
"DROP TABLE ${table}", "CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
"DROP TABLE ${table}_backup", "COMMIT"
]
tmpl = string.Template(';\n'.join(cmds))
sql = tmpl.substitute(table=table, coldef=coltypes, colnames=colnames)
else:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
if grass.write_command(
'db.execute', input='-', database=database, driver=driver,
stdin=sql) != 0:
grass.fatal(_("Cannot continue (problem deleting column)"))
return 0
def main():
table = options['table']
column = options['column']
force = flags['f']
# check if DB parameters are set, and if not set them.
grass.run_command('db.connect', flags = 'c')
kv = grass.db_connection()
database = kv['database']
driver = kv['driver']
# schema needed for PG?
if force:
grass.message(_("Forcing ..."))
if column == "cat":
grass.warning(_("Deleting <%s> column which may be needed to keep table connected to a vector map") % column)
cols = [f[0] for f in grass.db_describe(table)['cols']]
if column not in cols:
grass.fatal(_("Column <%s> not found in table") % column)
if not force:
grass.message(_("Column <%s> would be deleted.") % column)
grass.message("")
grass.message(_("You must use the force flag to actually remove it. Exiting."))
sys.exit(0)
if driver == "sqlite":
#echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q13
colnames = []
coltypes = []
for f in grass.db_describe(table)['cols']:
if f[0] == column:
continue
colnames.append(f[0])
coltypes.append("%s %s" % (f[0], f[1]))
colnames = ", ".join(colnames)
coltypes = ", ".join(coltypes)
cmds = [
"BEGIN TRANSACTION",
"CREATE TEMPORARY TABLE ${table}_backup(${coldef})",
"INSERT INTO ${table}_backup SELECT ${colnames} FROM ${table}",
"DROP TABLE ${table}",
"CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
"DROP TABLE ${table}_backup",
"COMMIT"
]
tmpl = string.Template(';\n'.join(cmds))
sql = tmpl.substitute(table = table, coldef = coltypes, colnames = colnames)
else:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
if grass.write_command('db.execute', input = '-', database = database, driver = driver,
stdin = sql) != 0:
grass.fatal(_("Cannot continue (problem deleting column)."))
def main():
global tmp
tmp = gscript.tempfile()
extend = flags['e']
shellstyle = flags['g']
table = options['table']
column = options['column']
database = options['database']
driver = options['driver']
where = options['where']
perc = options['percentile']
perc = [float(p) for p in perc.split(',')]
desc_table = gscript.db_describe(table, database=database, driver=driver)
if not desc_table:
gscript.fatal(_("Unable to describe table <%s>") % table)
found = False
for cname, ctype, cwidth in desc_table['cols']:
if cname == column:
found = True
if ctype not in ('INTEGER', 'DOUBLE PRECISION'):
gscript.fatal(_("Column <%s> is not numeric") % cname)
if not found:
gscript.fatal(_("Column <%s> not found in table <%s>") % (column, table))
if not shellstyle:
gscript.verbose(_("Calculation for column <%s> of table <%s>..."
) % (column, table))
gscript.message(_("Reading column values..."))
sql = "SELECT %s FROM %s" % (column, table)
if where:
sql += " WHERE " + where
if not database:
database = None
if not driver:
driver = None
tmpf = file(tmp, 'w')
gscript.run_command('db.select', flags='c', table=table,
database=database, driver=driver, sql=sql,
stdout=tmpf)
tmpf.close()
# check if result is empty
tmpf = file(tmp)
if tmpf.read(1) == '':
gscript.fatal(_("Table <%s> contains no data.") % table)
tmpf.close()
# calculate statistics
if not shellstyle:
gscript.verbose(_("Calculating statistics..."))
N = 0
sum = 0.0
sum2 = 0.0
sum3 = 0.0
minv = 1e300
maxv = -1e300
tmpf = file(tmp)
for line in tmpf:
if len(line.rstrip('\r\n')) == 0:
continue
x = float(line.rstrip('\r\n'))
N += 1
sum += x
sum2 += x * x
sum3 += abs(x)
maxv = max(maxv, x)
minv = min(minv, x)
tmpf.close()
if N <= 0:
gscript.fatal(_("No non-null values found"))
if not shellstyle:
sys.stdout.write("Number of values: %d\n" % N)
sys.stdout.write("Minimum: %.15g\n" % minv)
sys.stdout.write("Maximum: %.15g\n" % maxv)
sys.stdout.write("Range: %.15g\n" % (maxv - minv))
sys.stdout.write("Mean: %.15g\n" % (sum / N))
sys.stdout.write(
"Arithmetic mean of absolute values: %.15g\n" %
(sum3 / N))
sys.stdout.write("Variance: %.15g\n" % ((sum2 - sum * sum / N) / N))
sys.stdout.write(
"Standard deviation: %.15g\n" %
(math.sqrt((sum2 - sum * sum / N) / N)))
sys.stdout.write(
"Coefficient of variation: %.15g\n" %
((math.sqrt((sum2 - sum * sum / N) / N)) /
(math.sqrt(sum * sum) / N)))
sys.stdout.write("Sum: %.15g\n" % sum)
else:
sys.stdout.write("n=%d\n" % N)
sys.stdout.write("min=%.15g\n" % minv)
sys.stdout.write("max=%.15g\n" % maxv)
sys.stdout.write("range=%.15g\n" % (maxv - minv))
sys.stdout.write("mean=%.15g\n" % (sum / N))
sys.stdout.write("mean_abs=%.15g\n" % (sum3 / N))
sys.stdout.write("variance=%.15g\n" % ((sum2 - sum * sum / N) / N))
sys.stdout.write(
"stddev=%.15g\n" %
(math.sqrt(
(sum2 - sum * sum / N) / N)))
sys.stdout.write(
"coeff_var=%.15g\n" %
((math.sqrt((sum2 - sum * sum / N) / N)) /
(math.sqrt(sum * sum) / N)))
sys.stdout.write("sum=%.15g\n" % sum)
if not extend:
return
# preparations:
sortfile(tmp, tmp + ".sort")
odd = N % 2
eostr = ['even', 'odd'][odd]
q25pos = round(N * 0.25)
if q25pos == 0:
q25pos = 1
q50apos = round(N * 0.50)
if q50apos == 0:
q50apos = 1
q50bpos = q50apos + (1 - odd)
q75pos = round(N * 0.75)
if q75pos == 0:
q75pos = 1
ppos = {}
pval = {}
for i in range(len(perc)):
ppos[i] = round(N * perc[i] / 100)
if ppos[i] == 0:
ppos[i] = 1
pval[i] = 0
inf = file(tmp + ".sort")
l = 1
for line in inf:
if l == q25pos:
q25 = float(line.rstrip('\r\n'))
if l == q50apos:
q50a = float(line.rstrip('\r\n'))
if l == q50bpos:
q50b = float(line.rstrip('\r\n'))
if l == q75pos:
q75 = float(line.rstrip('\r\n'))
for i in range(len(ppos)):
if l == ppos[i]:
pval[i] = float(line.rstrip('\r\n'))
l += 1
q50 = (q50a + q50b) / 2
if not shellstyle:
sys.stdout.write("1st Quartile: %.15g\n" % q25)
sys.stdout.write("Median (%s N): %.15g\n" % (eostr, q50))
sys.stdout.write("3rd Quartile: %.15g\n" % q75)
for i in range(len(perc)):
if perc[i] == int(perc[i]): # integer
if int(perc[i]) % 10 == 1 and int(perc[i]) != 11:
sys.stdout.write(
"%dst Percentile: %.15g\n" %
(int(
perc[i]),
pval[i]))
elif int(perc[i]) % 10 == 2 and int(perc[i]) != 12:
sys.stdout.write(
"%dnd Percentile: %.15g\n" %
(int(
perc[i]),
pval[i]))
elif int(perc[i]) % 10 == 3 and int(perc[i]) != 13:
sys.stdout.write(
"%drd Percentile: %.15g\n" %
(int(
perc[i]),
pval[i]))
else:
sys.stdout.write(
"%dth Percentile: %.15g\n" %
(int(
perc[i]),
pval[i]))
else:
sys.stdout.write(
"%.15g Percentile: %.15g\n" %
(perc[i], pval[i]))
else:
sys.stdout.write("first_quartile=%.15g\n" % q25)
sys.stdout.write("median=%.15g\n" % q50)
sys.stdout.write("third_quartile=%.15g\n" % q75)
for i in range(len(perc)):
percstr = "%.15g" % perc[i]
percstr = percstr.replace('.', '_')
sys.stdout.write("percentile_%s=%.15g\n" % (percstr, pval[i]))
def main():
map = options['map']
layer = options['layer']
columns = options['columns'].split(',')
mapset = grass.gisenv()['MAPSET']
# does map exist in CURRENT mapset?
if not grass.find_file(map, element='vector', mapset=mapset)['file']:
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
f = grass.vector_layer_db(map, layer)
table = f['table']
keycol = f['key']
database = f['database']
driver = f['driver']
if not table:
grass.fatal(_("There is no table connected to the input vector map. "
"Unable to delete any column. Exiting."))
if keycol in columns:
grass.fatal(_("Unable to delete <%s> column as it is needed to keep table <%s> "
"connected to the input vector map <%s>") %
(keycol, table, map))
for column in columns:
if column not in grass.vector_columns(map, layer):
grass.warning(_("Column <%s> not found in table <%s>. Skipped") % (column, table))
continue
if driver == "sqlite":
# echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q11
colnames = []
coltypes = []
for f in grass.db_describe(table, database=database, driver=driver)['cols']:
if f[0] == column:
continue
colnames.append(f[0])
coltypes.append("%s %s" % (f[0], f[1]))
colnames = ", ".join(colnames)
coltypes = ", ".join(coltypes)
cmds = [
"BEGIN TRANSACTION",
"CREATE TEMPORARY TABLE ${table}_backup(${coldef})",
"INSERT INTO ${table}_backup SELECT ${colnames} FROM ${table}",
"DROP TABLE ${table}",
"CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
"CREATE UNIQUE INDEX ${table}_cat ON ${table} (${keycol} )",
"DROP TABLE ${table}_backup",
"COMMIT"
]
tmpl = string.Template(';\n'.join(cmds))
sql = tmpl.substitute(table=table, coldef=coltypes, colnames=colnames, keycol=keycol)
else:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
try:
grass.write_command('db.execute', input='-', database=database, driver=driver,
stdin=sql)
except CalledModuleError:
grass.fatal(_("Deleting column failed"))
# write cmd history:
grass.vector_history(map)
def main():
input = options['input']
db_table = options['db_table']
output = options['output']
key = options['key']
mapset = grass.gisenv()['MAPSET']
if db_table:
input = db_table
if not output:
tmpname = input.replace('.', '_')
output = grass.basename(tmpname)
# check if table exists
try:
nuldev = file(os.devnull, 'w+')
s = grass.read_command('db.tables', flags='p', quiet=True, stderr=nuldev)
nuldev.close()
except CalledModuleError:
# check connection parameters, set if uninitialized
grass.read_command('db.connect', flags='c')
s = grass.read_command('db.tables', flags='p', quiet=True)
for l in s.splitlines():
if l == output:
if grass.overwrite():
grass.warning(_("Table <%s> already exists and will be "
"overwritten") % output)
grass.write_command('db.execute', input='-',
stdin="DROP TABLE %s" % output)
break
else:
grass.fatal(_("Table <%s> already exists") % output)
# treat DB as real vector map...
layer = db_table if db_table else None
vopts = {}
if options['encoding']:
vopts['encoding'] = options['encoding']
try:
grass.run_command('v.in.ogr', flags='o', input=input, output=output,
layer=layer, quiet=True, **vopts)
except CalledModuleError:
if db_table:
grass.fatal(
_("Input table <%s> not found or not readable") %
input)
else:
grass.fatal(_("Input DSN <%s> not found or not readable") % input)
# rename ID col if requested from cat to new name
if key:
grass.write_command('db.execute', quiet=True, input='-',
stdin="ALTER TABLE %s ADD COLUMN %s integer" %
(output, key))
grass.write_command('db.execute', quiet=True, input='-',
stdin="UPDATE %s SET %s=cat" % (output, key))
# ... and immediately drop the empty geometry
vectfile = grass.find_file(output, element='vector', mapset=mapset)['file']
if not vectfile:
grass.fatal(_("Something went wrong. Should not happen"))
else:
# remove the vector part
grass.run_command('v.db.connect', quiet=True, map=output, layer='1',
flags='d')
grass.run_command('g.remove', flags='f', quiet=True, type='vector',
name=output)
# get rid of superfluous auto-added cat column (and cat_ if present)
nuldev = file(os.devnull, 'w+')
grass.run_command('db.dropcolumn', quiet=True, flags='f', table=output,
column='cat', stdout=nuldev, stderr=nuldev)
nuldev.close()
records = grass.db_describe(output)['nrows']
grass.message(_("Imported table <%s> with %d rows") % (output, records))
def main():
options, unused = grass.parser()
mapName = options['input']
trainingMapName = options['training']
columnWithClass = options['class_column']
useAllColumns = True
if options['columns']:
# columns as string
columns = options['columns'].strip()
useAllColumns = False
# TODO: allow same input and output map only if --overwrite was specified
# TODO: is adding column overwriting or overwriting is only updating of existing?
# variable names connected to training dataset have training prefix
# variable names connected to classified dataset have no prefix
# checking database connection (if map has a table)
# TODO: layer
checkDbConnection(trainingMapName)
checkDbConnection(mapName)
# loading descriptions first to check them
trainingTableDescription = grass.db_describe(table=trainingMapName)
if useAllColumns:
trainingMinNcols = 3
checkNcols(trainingMapName, trainingTableDescription, trainingMinNcols)
else:
pass
checkNrows(trainingMapName, trainingTableDescription)
if not hasColumn(trainingTableDescription, columnWithClass):
fatal_noClassColumn(trainingMapName, columnWithClass)
tableDescription = grass.db_describe(table=mapName)
if useAllColumns:
minNcols = 2
checkNcols(mapName, tableDescription, minNcols)
else:
pass
checkNrows(mapName, tableDescription)
# TODO: check same (+-1) number of columns
# loadnig data
# TODO: make fun from this
if useAllColumns:
dbTable = grass.db_select(table=trainingMapName)
else:
# assuming that columns concatenated by comma
sql = 'SELECT %s,%s FROM %s' % (columnWithClass, columns, trainingMapName)
dbTable = grass.db_select(sql=sql)
trainingParameters = fromDbTableToSimpleTable(dbTable,
columnsDescription=trainingTableDescription['cols'],
columnWithClass=columnWithClass)
if useAllColumns:
trainingClasses = extractColumnWithClass(dbTable,
columnsDescription=trainingTableDescription['cols'],
columnWithClass=columnWithClass)
else:
# FIXME: magic num?
trainingClasses = extractNthColumn(dbTable, 0)
# TODO: hard coded 'cat'?
if useAllColumns:
dbTable = grass.db_select(table=mapName)
else:
# assuming that columns concatenated by comma
sql = 'SELECT %s,%s FROM %s' % ('cat', columns, mapName)
dbTable = grass.db_select(sql=sql)
parameters = fromDbTableToSimpleTable(dbTable,
columnsDescription=tableDescription['cols'],
columnWithClass=columnWithClass)
if useAllColumns:
cats = extractColumnWithCats(dbTable, columnsDescription=tableDescription['cols'])
else:
cats = extractNthColumn(dbTable, 0)
# since dbTable can be big it is better to avoid to have it in memory twice
del dbTable
del trainingTableDescription
classifier = Classifier()
classifier.learn(trainingParameters, trainingClasses)
classes = classifier.pred(parameters)
# add column only if not exists and the classification was successful
if not hasColumn(tableDescription, columnWithClass):
addColumn(mapName, columnWithClass, 'int')
updateColumn(mapName, columnWithClass, cats, classes)
def main():
table = options["table"]
column = options["column"]
otable = options["other_table"]
ocolumn = options["other_column"]
if options["subset_columns"]:
scolumns = options["subset_columns"].split(",")
else:
scolumns = None
database = options["database"]
driver = options["driver"]
# this error handling is completely different among th db.* scripts - FIX
if not database:
database = None
if not driver:
driver = None
if driver == "dbf":
grass.fatal(_("JOIN is not supported for tables stored in DBF format"))
# describe input table
all_cols_tt = grass.db_describe(table, driver=driver,
database=database)["cols"]
if not all_cols_tt:
grass.fatal(_("Unable to describe table <%s>") % table)
found = False
# check if column is in input table
if column not in [col[0] for col in all_cols_tt]:
grass.fatal(_("Column <%s> not found in table <%s>") % (column, table))
# describe other table
all_cols_ot = grass.db_describe(otable, driver=driver,
database=database)["cols"]
# check if ocolumn is in other table
if ocolumn not in [ocol[0] for ocol in all_cols_ot]:
grass.fatal(
_("Column <%s> not found in table <%s>") % (ocolumn, otable))
# determine columns subset from other table
if not scolumns:
# select all columns from other table
cols_to_add = all_cols_ot
else:
cols_to_add = []
# check if scolumns exists in the other table
for scol in scolumns:
found = False
for col_ot in all_cols_ot:
if scol == col_ot[0]:
found = True
cols_to_add.append(col_ot)
break
if not found:
grass.warning(
_("Column <%s> not found in table <%s>") % (scol, otable))
select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column"
template = string.Template("UPDATE $table SET $colname=(%s);" % select)
for col in cols_to_add:
# skip the vector column which is used for join
colname = col[0]
if colname == column:
continue
use_len = False
if len(col) > 2:
use_len = True
# Sqlite 3 does not support the precision number any more
if driver == "sqlite":
use_len = False
# MySQL - expect format DOUBLE PRECISION(M,D), see #2792
elif driver == "mysql" and col[1] == "DOUBLE PRECISION":
use_len = False
if use_len:
coltype = "%s(%s)" % (col[1], col[2])
else:
coltype = "%s" % col[1]
colspec = "%s %s" % (colname, coltype)
# add only the new column to the table
if colname not in all_cols_tt:
p = grass.feed_command("db.execute",
input="-",
database=database,
driver=driver)
p.stdin.write("ALTER TABLE %s ADD COLUMN %s" % (table, colspec))
grass.debug("ALTER TABLE %s ADD COLUMN %s" % (table, colspec))
p.stdin.close()
if p.wait() != 0:
grass.fatal(_("Unable to add column <%s>.") % colname)
stmt = template.substitute(table=table,
column=column,
otable=otable,
ocolumn=ocolumn,
colname=colname)
grass.debug(stmt, 1)
grass.verbose(
_("Updating column <%s> of table <%s>...") % (colname, table))
try:
grass.write_command("db.execute",
stdin=stmt,
input="-",
database=database,
driver=driver)
except CalledModuleError:
grass.fatal(_("Error filling column <%s>") % colname)
return 0
def main():
map = options['map']
layer = options['layer']
column = options['column']
otable = options['otable']
ocolumn = options['ocolumn']
if options['scolumns']:
scolumns = options['scolumns'].split(',')
else:
scolumns = None
f = grass.vector_layer_db(map, layer)
maptable = f['table']
database = f['database']
driver = f['driver']
if driver == 'dbf':
grass.fatal(_("JOIN is not supported for tables stored in DBF format"))
if not maptable:
grass.fatal(_("There is no table connected to this map. Unable to join any column."))
# check if column is in map table
if not grass.vector_columns(map, layer).has_key(column):
grass.fatal(_("Column <%s> not found in table <%s>") % (column, maptable))
# describe other table
all_cols_ot = grass.db_describe(otable, driver = driver, database = database)['cols']
# check if ocolumn is on other table
if ocolumn not in [ocol[0] for ocol in all_cols_ot]:
grass.fatal(_("Column <%s> not found in table <%s>") % (ocolumn, otable))
# determine columns subset from other table
if not scolumns:
# select all columns from other table
cols_to_add = all_cols_ot
else:
cols_to_add = []
# check if scolumns exists in the other table
for scol in scolumns:
found = False
for col_ot in all_cols_ot:
if scol == col_ot[0]:
found = True
cols_to_add.append(col_ot)
break
if not found:
grass.warning(_("Column <%s> not found in table <%s>.") % (scol, otable))
all_cols_tt = grass.vector_columns(map, int(layer)).keys()
select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column"
template = string.Template("UPDATE $table SET $colname=(%s);" % select)
for col in cols_to_add:
# skip the vector column which is used for join
colname = col[0]
if colname == column:
continue
# Sqlite 3 does not support the precision number any more
if len(col) > 2 and driver != "sqlite":
coltype = "%s(%s)" % (col[1], col[2])
else:
coltype = "%s" % col[1]
colspec = "%s %s" % (colname, coltype)
# add only the new column to the table
if colname not in all_cols_tt:
if grass.run_command('v.db.addcolumn', map = map, columns = colspec, layer = layer) != 0:
grass.fatal(_("Error creating column <%s>") % colname)
stmt = template.substitute(table = maptable, column = column,
otable = otable, ocolumn = ocolumn,
colname = colname)
grass.debug(stmt, 1)
grass.verbose(_("Updating column <%s> of vector map <%s>...") % (colname, map))
if grass.write_command('db.execute', stdin = stmt, input = '-', database = database, driver = driver) != 0:
grass.fatal(_("Error filling column <%s>") % colname)
# write cmd history
grass.vector_history(map)
return 0
def main():
map = options['map']
layer = options['layer']
columns = options['columns'].split(',')
mapset = grass.gisenv()['MAPSET']
# does map exist in CURRENT mapset?
if not grass.find_file(map, element='vector', mapset=mapset)['file']:
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
f = grass.vector_layer_db(map, layer)
table = f['table']
keycol = f['key']
database = f['database']
driver = f['driver']
if not table:
grass.fatal(_("There is no table connected to the input vector map. "
"Unable to delete any column. Exiting."))
if keycol in columns:
grass.fatal(_("Unable to delete <%s> column as it is needed to keep table <%s> "
"connected to the input vector map <%s>") %
(keycol, table, map))
for column in columns:
if column not in grass.vector_columns(map, layer):
grass.warning(_("Column <%s> not found in table <%s>. Skipped") % (column, table))
continue
if driver == "sqlite":
# echo "Using special trick for SQLite"
# http://www.sqlite.org/faq.html#q11
colnames = []
coltypes = []
for f in grass.db_describe(table, database=database, driver=driver)['cols']:
if f[0] == column:
continue
colnames.append(f[0])
coltypes.append("%s %s" % (f[0], f[1]))
colnames = ", ".join(colnames)
coltypes = ", ".join(coltypes)
cmds = [
"BEGIN TRANSACTION",
"CREATE TEMPORARY TABLE ${table}_backup(${coldef})",
"INSERT INTO ${table}_backup SELECT ${colnames} FROM ${table}",
"DROP TABLE ${table}",
"CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
"CREATE UNIQUE INDEX ${table}_cat ON ${table} (${keycol} )",
"DROP TABLE ${table}_backup",
"COMMIT"
]
tmpl = string.Template(';\n'.join(cmds))
sql = tmpl.substitute(table=table, coldef=coltypes, colnames=colnames, keycol=keycol)
else:
sql = "ALTER TABLE %s DROP COLUMN %s" % (table, column)
try:
grass.write_command('db.execute', input='-', database=database, driver=driver,
stdin=sql)
except CalledModuleError:
grass.fatal(_("Deleting column failed"))
# write cmd history:
grass.vector_history(map)
def main():
dsn = options['dsn']
db_table = options['db_table']
output = options['output']
key = options['key']
mapset = grass.gisenv()['MAPSET']
if db_table:
input = db_table
else:
input = dsn
if not output:
tmpname = input.replace('.', '_')
output = grass.basename(tmpname)
if not grass.overwrite():
s = grass.read_command('db.tables', flags='p')
for l in s.splitlines():
if l == output:
grass.fatal(_("Table <%s> already exists") % output)
else:
grass.write_command('db.execute',
input='-',
stdin="DROP TABLE %s" % output)
# treat DB as real vector map...
if db_table:
layer = db_table
else:
layer = None
if grass.run_command(
'v.in.ogr', flags='o', dsn=dsn, output=output, layer=layer,
quiet=True) != 0:
if db_table:
grass.fatal(
_("Input table <%s> not found or not readable") % input)
else:
grass.fatal(_("Input DSN <%s> not found or not readable") % input)
# rename ID col if requested from cat to new name
if key:
grass.write_command('db.execute',
quiet=True,
input='-',
stdin="ALTER TABLE %s ADD COLUMN %s integer" %
(output, key))
grass.write_command('db.execute',
quiet=True,
input='-',
stdin="UPDATE %s SET %s=cat" % (output, key))
# ... and immediately drop the empty geometry
vectfile = grass.find_file(output, element='vector', mapset=mapset)['file']
if not vectfile:
grass.fatal(_("Something went wrong. Should not happen"))
else:
# remove the vector part
grass.run_command('v.db.connect',
quiet=True,
map=output,
layer='1',
flags='d')
grass.run_command('g.remove',
flags='f',
quiet=True,
type='vect',
pattern=output)
# get rid of superfluous auto-added cat column (and cat_ if present)
nuldev = file(os.devnull, 'w+')
grass.run_command('db.dropcolumn',
quiet=True,
flags='f',
table=output,
column='cat',
stdout=nuldev,
stderr=nuldev)
nuldev.close()
records = grass.db_describe(output)['nrows']
grass.message(_("Imported table <%s> with %d rows") % (output, records))
def main():
input = options["input"]
gdal_config = options["gdal_config"]
gdal_doo = options["gdal_doo"]
db_table = options["db_table"]
output = options["output"]
key = options["key"]
mapset = grass.gisenv()["MAPSET"]
if db_table:
input = db_table
if not output:
tmpname = input.replace(".", "_")
output = grass.basename(tmpname)
# check if table exists
try:
nuldev = open(os.devnull, "w+")
s = grass.read_command("db.tables", flags="p", quiet=True, stderr=nuldev)
nuldev.close()
except CalledModuleError:
# check connection parameters, set if uninitialized
grass.read_command("db.connect", flags="c")
s = grass.read_command("db.tables", flags="p", quiet=True)
for l in decode(s).splitlines():
if l == output:
if grass.overwrite():
grass.warning(
_("Table <%s> already exists and will be " "overwritten") % output
)
grass.write_command(
"db.execute", input="-", stdin="DROP TABLE %s" % output
)
break
else:
grass.fatal(_("Table <%s> already exists") % output)
# treat DB as real vector map...
layer = db_table if db_table else None
vopts = {}
if options["encoding"]:
vopts["encoding"] = options["encoding"]
try:
grass.run_command(
"v.in.ogr",
flags="o",
input=input,
gdal_config=gdal_config,
gdal_doo=gdal_doo,
output=output,
layer=layer,
quiet=True,
**vopts,
)
except CalledModuleError:
if db_table:
grass.fatal(_("Input table <%s> not found or not readable") % input)
else:
grass.fatal(_("Input DSN <%s> not found or not readable") % input)
# rename ID col if requested from cat to new name
if key:
grass.write_command(
"db.execute",
quiet=True,
input="-",
stdin="ALTER TABLE %s ADD COLUMN %s integer" % (output, key),
)
grass.write_command(
"db.execute",
quiet=True,
input="-",
stdin="UPDATE %s SET %s=cat" % (output, key),
)
# ... and immediately drop the empty geometry
vectfile = grass.find_file(output, element="vector", mapset=mapset)["file"]
if not vectfile:
grass.fatal(_("Something went wrong. Should not happen"))
else:
# remove the vector part
grass.run_command("v.db.connect", quiet=True, map=output, layer="1", flags="d")
grass.run_command("g.remove", flags="f", quiet=True, type="vector", name=output)
# get rid of superfluous auto-added cat column (and cat_ if present)
nuldev = open(os.devnull, "w+")
grass.run_command(
"db.dropcolumn",
quiet=True,
flags="f",
table=output,
column="cat",
stdout=nuldev,
stderr=nuldev,
)
nuldev.close()
records = grass.db_describe(output)["nrows"]
grass.message(_("Imported table <%s> with %d rows") % (output, records))
def main():
map = options['map']
layer = options['layer']
column = options['column']
mapset = grass.gisenv()['MAPSET']
if not grass.find_file(map, element='vector', mapset=mapset):
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
f = grass.vector_layer_db(map, layer)
table = f['table']
keycol = f['key']
database = f['database']
driver = f['driver']
if not table:
grass.fatal(
_("There is no table connected to the input vector map. Cannot rename any column"
))
cols = column.split(',')
oldcol = cols[0]
newcol = cols[1]
if driver == "dbf":
if len(newcol) > 10:
grass.fatal(
_("Column name <%s> too long. The DBF driver supports column names not longer than 10 characters"
) % newcol)
if oldcol == keycol:
grass.fatal(
_("Cannot rename column <%s> as it is needed to keep table <%s> connected to the input vector map"
) % (oldcol, table))
# describe old col
oldcoltype = None
for f in grass.db_describe(table)['cols']:
if f[0] != oldcol:
continue
oldcoltype = f[1]
oldcollength = f[2]
# old col there?
if not oldcoltype:
grass.fatal(_("Column <%s> not found in table <%s>") % (oldcol, table))
# some tricks
if driver in ['sqlite', 'dbf']:
if oldcoltype.upper() == "CHARACTER":
colspec = "%s varchar(%s)" % (newcol, oldcollength)
else:
colspec = "%s %s" % (newcol, oldcoltype)
grass.run_command('v.db.addcolumn',
map=map,
layer=layer,
column=colspec)
sql = "UPDATE %s SET %s=%s" % (table, newcol, oldcol)
grass.write_command('db.execute',
input='-',
database=database,
driver=driver,
stdin=sql)
grass.run_command('v.db.dropcolumn',
map=map,
layer=layer,
column=oldcol)
elif driver == 'mysql':
if oldcoltype.upper() == "CHARACTER":
newcoltype = "varchar(%s)" % (oldcollength)
else:
newcoltype = oldcoltype
sql = "ALTER TABLE %s CHANGE %s %s %s" % (table, oldcol, newcol,
newcoltype)
grass.write_command('db.execute',
input='-',
database=database,
driver=driver,
stdin=sql)
else:
sql = "ALTER TABLE %s RENAME %s TO %s" % (table, oldcol, newcol)
grass.write_command('db.execute',
input='-',
database=database,
driver=driver,
stdin=sql)
# write cmd history:
grass.vector_history(map)
def main():
color = options['color']
column = options['column']
layer = options['layer']
map = options['map']
range = options['range']
raster = options['raster']
rgb_column = options['rgb_column']
rules = options['rules']
flip = flags['n']
global tmp, tmp_colr, tmp_vcol
pid = os.getpid()
tmp = tmp_colr = tmp_vcol = None
mapset = grass.gisenv()['MAPSET']
gisbase = os.getenv('GISBASE')
# does map exist in CURRENT mapset?
kv = grass.find_file(map, element = 'vector', mapset = mapset)
if not kv['file']:
grass.fatal(_("Vector map <%s> not found in current mapset") % map)
vector = map.split('@', 1)
# sanity check mutually exclusive color options
if not options['color'] and not options['raster'] and not options['rules']:
grass.fatal(_("Pick one of color, rules, or raster options"))
if color:
#### check the color rule is valid
color_opts = os.listdir(os.path.join(gisbase, 'etc', 'colors'))
color_opts += ['random', 'grey.eq', 'grey.log', 'rules']
if color not in color_opts:
grass.fatal(_("Invalid color rule <%s>\n") % color +
_("Valid options are: %s") % ' '.join(color_opts))
elif raster:
if not grass.find_file(raster)['name']:
grass.fatal(_("Raster raster map <%s> not found") % raster)
elif rules:
if not os.access(rules, os.R_OK):
grass.fatal(_("Unable to read color rules file <%s>") % rules)
# column checks
# check input data column
cols = grass.vector_columns(map, layer = layer)
if column not in cols:
grass.fatal(_("Column <%s> not found") % column)
ncolumn_type = cols[column]['type']
if ncolumn_type not in ["INTEGER", "DOUBLE PRECISION"]:
grass.fatal(_("Column <%s> is not numeric but %s") % (column, ncolumn_type))
# check if GRASSRGB column exists, make it if it doesn't
table = grass.vector_db(map)[int(layer)]['table']
if rgb_column not in cols:
# RGB Column not found, create it
grass.message(_("Creating column <%s>...") % rgb_column)
try:
grass.run_command('v.db.addcolumn', map = map, layer = layer, column = "%s varchar(11)" % rgb_column)
except CalledModuleError:
grass.fatal(_("Creating color column"))
else:
column_type = cols[rgb_column]['type']
if column_type not in ["CHARACTER", "TEXT"]:
grass.fatal(_("Column <%s> is not of compatible type (found %s)") % (rgb_column, column_type))
else:
num_chars = dict([(v[0], int(v[2])) for v in grass.db_describe(table)['cols']])[rgb_column]
if num_chars < 11:
grass.fatal(_("Color column <%s> is not wide enough (needs 11 characters)"), rgb_column)
cvals = grass.vector_db_select(map, layer = int(layer), columns = column)['values'].values()
# find data range
if range:
# order doesn't matter
vals = range.split(',')
else:
grass.message(_("Scanning values..."))
vals = [float(x[0]) for x in cvals]
minval = min(vals)
maxval = max(vals)
grass.verbose(_("Range: [%s, %s]") % (minval, maxval))
if minval is None or maxval is None:
grass.fatal(_("Scanning data range"))
# setup internal region
grass.use_temp_region()
grass.run_command('g.region', rows = 2, cols = 2)
tmp_colr = "tmp_colr_%d" % pid
# create dummy raster map
if ncolumn_type == "INTEGER":
grass.mapcalc("$tmp_colr = int(if(row() == 1, $minval, $maxval))",
tmp_colr = tmp_colr, minval = minval, maxval = maxval)
else:
grass.mapcalc("$tmp_colr = double(if(row() == 1, $minval, $maxval))",
tmp_colr = tmp_colr, minval = minval, maxval = maxval)
if color:
color_cmd = {'color': color}
elif raster:
color_cmd = {'raster': raster}
elif rules:
color_cmd = {'rules': rules}
if flip:
flip_flag = 'n'
else:
flip_flag = ''
grass.run_command('r.colors', map = tmp_colr, flags = flip_flag, quiet = True, **color_cmd)
tmp = grass.tempfile()
# calculate colors and write SQL command file
grass.message(_("Looking up colors..."))
f = open(tmp, 'w')
p = grass.feed_command('r.what.color', flags = 'i', input = tmp_colr, stdout = f)
lastval = None
for v in sorted(vals):
if v == lastval:
continue
p.stdin.write('%f\n' % v)
p.stdin.close()
p.wait()
f.close()
tmp_vcol = "%s_vcol.sql" % tmp
fi = open(tmp, 'r')
fo = open(tmp_vcol, 'w')
t = string.Template("UPDATE $table SET $rgb_column = '$colr' WHERE $column = $value;\n")
found = 0
for line in fi:
[value, colr] = line.split(': ')
colr = colr.strip()
if len(colr.split(':')) != 3:
continue
fo.write(t.substitute(table = table, rgb_column = rgb_column, colr = colr, column = column, value = value))
found += 1
fi.close()
fo.close()
if not found:
grass.fatal(_("No values found in color range"))
# apply SQL commands to update the table with values
grass.message(_("Writing %s colors...") % found)
try:
grass.run_command('db.execute', input = tmp_vcol)
except CalledModuleError:
grass.fatal(_("Processing SQL transaction"))
if flags['s']:
vcolors = "vcolors_%d" % pid
grass.run_command('g.rename', raster = (tmp_colr, vcolors), quiet = True)
grass.message(_("Raster map containing color rules saved to <%s>") % vcolors)
# TODO save full v.colors command line history
grass.run_command('r.support', map = vcolors,
history = "",
source1 = "vector map = %s" % map,
source2 = "column = %s" % column,
title = _("Dummy raster to use as thematic vector legend"),
description = "generated by v.colors using r.mapcalc")
grass.run_command('r.support', map = vcolors,
history = _("RGB saved into <%s> using <%s%s%s>") % (rgb_column, color, raster, rules))
def main():
global tmp
tmp = gscript.tempfile()
extend = flags["e"]
shellstyle = flags["g"]
table = options["table"]
column = options["column"]
database = options["database"]
driver = options["driver"]
where = options["where"]
perc = options["percentile"]
perc = [float(p) for p in perc.split(",")]
desc_table = gscript.db_describe(table, database=database, driver=driver)
if not desc_table:
gscript.fatal(_("Unable to describe table <%s>") % table)
found = False
for cname, ctype, cwidth in desc_table["cols"]:
if cname == column:
found = True
if ctype not in ("INTEGER", "DOUBLE PRECISION"):
gscript.fatal(_("Column <%s> is not numeric") % cname)
if not found:
gscript.fatal(_("Column <%s> not found in table <%s>") % (column, table))
if not shellstyle:
gscript.verbose(
_("Calculation for column <%s> of table <%s>...") % (column, table)
)
gscript.message(_("Reading column values..."))
sql = "SELECT %s FROM %s" % (column, table)
if where:
sql += " WHERE " + where
if not database:
database = None
if not driver:
driver = None
tmpf = open(tmp, "w")
gscript.run_command(
"db.select",
flags="c",
table=table,
database=database,
driver=driver,
sql=sql,
stdout=tmpf,
)
tmpf.close()
# check if result is empty
tmpf = open(tmp)
if tmpf.read(1) == "":
gscript.fatal(_("Table <%s> contains no data.") % table)
tmpf.close()
# calculate statistics
if not shellstyle:
gscript.verbose(_("Calculating statistics..."))
N = 0
sum = 0.0
sum2 = 0.0
sum3 = 0.0
minv = 1e300
maxv = -1e300
tmpf = open(tmp)
for line in tmpf:
line = line.rstrip("\r\n")
if len(line) == 0:
continue
x = float(line)
N += 1
sum += x
sum2 += x * x
sum3 += abs(x)
maxv = max(maxv, x)
minv = min(minv, x)
tmpf.close()
if N <= 0:
gscript.fatal(_("No non-null values found"))
if not shellstyle:
sys.stdout.write("Number of values: %d\n" % N)
sys.stdout.write("Minimum: %.15g\n" % minv)
sys.stdout.write("Maximum: %.15g\n" % maxv)
sys.stdout.write("Range: %.15g\n" % (maxv - minv))
sys.stdout.write("Mean: %.15g\n" % (sum / N))
sys.stdout.write("Arithmetic mean of absolute values: %.15g\n" % (sum3 / N))
if not ((sum2 - sum * sum / N) / N) < 0:
sys.stdout.write("Variance: %.15g\n" % ((sum2 - sum * sum / N) / N))
sys.stdout.write(
"Standard deviation: %.15g\n" % (math.sqrt((sum2 - sum * sum / N) / N))
)
sys.stdout.write(
"Coefficient of variation: %.15g\n"
% ((math.sqrt((sum2 - sum * sum / N) / N)) / (math.sqrt(sum * sum) / N))
)
else:
sys.stdout.write("Variance: 0\n")
sys.stdout.write("Standard deviation: 0\n")
sys.stdout.write("Coefficient of variation: 0\n")
sys.stdout.write("Sum: %.15g\n" % sum)
else:
sys.stdout.write("n=%d\n" % N)
sys.stdout.write("min=%.15g\n" % minv)
sys.stdout.write("max=%.15g\n" % maxv)
sys.stdout.write("range=%.15g\n" % (maxv - minv))
sys.stdout.write("mean=%.15g\n" % (sum / N))
sys.stdout.write("mean_abs=%.15g\n" % (sum3 / N))
if not ((sum2 - sum * sum / N) / N) < 0:
sys.stdout.write("variance=%.15g\n" % ((sum2 - sum * sum / N) / N))
sys.stdout.write("stddev=%.15g\n" % (math.sqrt((sum2 - sum * sum / N) / N)))
sys.stdout.write(
"coeff_var=%.15g\n"
% ((math.sqrt((sum2 - sum * sum / N) / N)) / (math.sqrt(sum * sum) / N))
)
else:
sys.stdout.write("variance=0\n")
sys.stdout.write("stddev=0\n")
sys.stdout.write("coeff_var=0\n")
sys.stdout.write("sum=%.15g\n" % sum)
if not extend:
return
# preparations:
sortfile(tmp, tmp + ".sort")
odd = N % 2
eostr = ["even", "odd"][odd]
q25pos = round(N * 0.25)
if q25pos == 0:
q25pos = 1
q50apos = round(N * 0.50)
if q50apos == 0:
q50apos = 1
q50bpos = q50apos + (1 - odd)
q75pos = round(N * 0.75)
if q75pos == 0:
q75pos = 1
ppos = {}
pval = {}
for i in range(len(perc)):
ppos[i] = round(N * perc[i] / 100)
if ppos[i] == 0:
ppos[i] = 1
pval[i] = 0
inf = open(tmp + ".sort")
l = 1
for line in inf:
line = line.rstrip("\r\n")
if len(line) == 0:
continue
if l == q25pos:
q25 = float(line)
if l == q50apos:
q50a = float(line)
if l == q50bpos:
q50b = float(line)
if l == q75pos:
q75 = float(line)
for i in range(len(ppos)):
if l == ppos[i]:
pval[i] = float(line)
l += 1
q50 = (q50a + q50b) / 2
if not shellstyle:
sys.stdout.write("1st Quartile: %.15g\n" % q25)
sys.stdout.write("Median (%s N): %.15g\n" % (eostr, q50))
sys.stdout.write("3rd Quartile: %.15g\n" % q75)
for i in range(len(perc)):
if perc[i] == int(perc[i]): # integer
if int(perc[i]) % 10 == 1 and int(perc[i]) != 11:
sys.stdout.write(
"%dst Percentile: %.15g\n" % (int(perc[i]), pval[i])
)
elif int(perc[i]) % 10 == 2 and int(perc[i]) != 12:
sys.stdout.write(
"%dnd Percentile: %.15g\n" % (int(perc[i]), pval[i])
)
elif int(perc[i]) % 10 == 3 and int(perc[i]) != 13:
sys.stdout.write(
"%drd Percentile: %.15g\n" % (int(perc[i]), pval[i])
)
else:
sys.stdout.write(
"%dth Percentile: %.15g\n" % (int(perc[i]), pval[i])
)
else:
sys.stdout.write("%.15g Percentile: %.15g\n" % (perc[i], pval[i]))
else:
sys.stdout.write("first_quartile=%.15g\n" % q25)
sys.stdout.write("median=%.15g\n" % q50)
sys.stdout.write("third_quartile=%.15g\n" % q75)
for i in range(len(perc)):
percstr = "%.15g" % perc[i]
percstr = percstr.replace(".", "_")
sys.stdout.write("percentile_%s=%.15g\n" % (percstr, pval[i]))
def main():
options, unused = grass.parser()
mapName = options['input']
trainingMapName = options['training']
columnWithClass = options['class_column']
useAllColumns = True
if options['columns']:
# columns as string
columns = options['columns'].strip()
useAllColumns = False
# TODO: allow same input and output map only if --overwrite was specified
# TODO: is adding column overwriting or overwriting is only updating of existing?
# variable names conected to training dataset have training prefix
# variable names conected to classified dataset have no prefix
# checking database connection (if map has a table)
# TODO: layer
checkDbConnection(trainingMapName)
checkDbConnection(mapName)
# loading descriptions first to check them
trainingTableDescription = grass.db_describe(table=trainingMapName)
if useAllColumns:
trainingMinNcols = 3
checkNcols(trainingMapName, trainingTableDescription, trainingMinNcols)
else:
pass
checkNrows(trainingMapName, trainingTableDescription)
if not hasColumn(trainingTableDescription, columnWithClass):
fatal_noClassColumn(trainingMapName, columnWithClass)
tableDescription = grass.db_describe(table=mapName)
if useAllColumns:
minNcols = 2
checkNcols(mapName, tableDescription, minNcols)
else:
pass
checkNrows(mapName, tableDescription)
# TODO: check same (+-1) number of columns
# loadnig data
# TODO: make fun from this
if useAllColumns:
dbTable = grass.db_select(table=trainingMapName)
else:
# assuming that columns concatenated by comma
sql = 'SELECT %s,%s FROM %s' % (columnWithClass, columns,
trainingMapName)
dbTable = grass.db_select(sql=sql)
trainingParameters = fromDbTableToSimpleTable(
dbTable,
columnsDescription=trainingTableDescription['cols'],
columnWithClass=columnWithClass)
if useAllColumns:
trainingClasses = extractColumnWithClass(
dbTable,
columnsDescription=trainingTableDescription['cols'],
columnWithClass=columnWithClass)
else:
# FIXME: magic num?
trainingClasses = extractNthColumn(dbTable, 0)
# TODO: hard coded 'cat'?
if useAllColumns:
dbTable = grass.db_select(table=mapName)
else:
# assuming that columns concatenated by comma
sql = 'SELECT %s,%s FROM %s' % ('cat', columns, mapName)
dbTable = grass.db_select(sql=sql)
parameters = fromDbTableToSimpleTable(
dbTable,
columnsDescription=tableDescription['cols'],
columnWithClass=columnWithClass)
if useAllColumns:
cats = extractColumnWithCats(
dbTable, columnsDescription=tableDescription['cols'])
else:
cats = extractNthColumn(dbTable, 0)
# since dbTable can be big it is better to avoid to have it in memory twice
del dbTable
del trainingTableDescription
classifier = Classifier()
classifier.learn(trainingParameters, trainingClasses)
classes = classifier.pred(parameters)
# add column only if not exists and the classification was successful
if not hasColumn(tableDescription, columnWithClass):
addColumn(mapName, columnWithClass, 'int')
updateColumn(mapName, columnWithClass, cats, classes)
def main():
dsn = options['dsn']
db_table = options['db_table']
output = options['output']
key = options['key']
mapset = grass.gisenv()['MAPSET']
if db_table:
input = db_table
else:
input = dsn
if not output:
tmpname = input.replace('.', '_')
output = grass.basename(tmpname)
if not grass.overwrite():
s = grass.read_command('db.tables', flags = 'p')
for l in s.splitlines():
if l == output:
grass.fatal(_("Table <%s> already exists") % output)
else:
grass.write_command('db.execute', input = '-', stdin = "DROP TABLE %s" % output)
# treat DB as real vector map...
if db_table:
layer = db_table
else:
layer = None
if grass.run_command('v.in.ogr', flags = 'o', dsn = dsn, output = output,
layer = layer, quiet = True) != 0:
if db_table:
grass.fatal(_("Input table <%s> not found or not readable") % input)
else:
grass.fatal(_("Input DSN <%s> not found or not readable") % input)
nuldev = file(os.devnull, 'w')
# rename ID col if requested from cat to new name
if key:
grass.run_command('db.execute', quiet = True, flags = 'f',
input = '-',
stdin = "ALTER TABLE %s ADD COLUMN %s integer" % (output, key) )
grass.run_command('db.execute', quiet = True, flags = 'f',
input = '-',
stdin = "UPDATE %s SET %s=cat" % (output, key) )
# ... and immediately drop the empty geometry
vectfile = grass.find_file(output, element = 'vector', mapset = mapset)['file']
if not vectfile:
grass.fatal(_("Something went wrong. Should not happen"))
else:
# remove the vector part
grass.run_command('v.db.connect', quiet = True, map = output, layer = '1', flags = 'd')
grass.run_command('g.remove', quiet = True, vect = output)
# get rid of superfluous auto-added cat column (and cat_ if present)
grass.run_command('db.dropcolumn', quiet = True, flags = 'f', table = output,
column = 'cat', stdout = nuldev, stderr = nuldev)
records = grass.db_describe(output)['nrows']
grass.message(_("Imported table <%s> with %d rows") % (output, records))
