class UserTableDao:
#constructor
def __init__(self):
relative_path = os.path.abspath(os.path.dirname(__file__) + '/config')
self.db = PsqlDB(relative_path, 'db.cfg', 'publish')
def getUUID(self, email):
"""
Get UUID from table for one user.
@return UUID, a string that represents the UUID related with one user
"""
sql = "SELECT uuid "
sql += "FROM public.downloads_by_uuid "
sql += "WHERE user_id=(SELECT id FROM public.user WHERE email='{0}')".format(
email)
return self.__fetch(sql)
def generateUUID(self, user_id):
"""
Generates one UUID for one user.
@return uuid, the UUID to user or None if no data was returned
"""
sql = "INSERT INTO public.downloads_by_uuid(uuid,user_id) VALUES "
sql += "(gen_random_uuid(), {0}) RETURNING uuid".format(user_id)
ret_data = {'uuid': None}
data = self.__execSQL(sql, True)
if (data != None):
ret_data = {'uuid': data}
return ret_data
def storeClient(self, name, email, institution):
"""
Store the client data.
@return user_id, the user id or None if user email exists
"""
sql = "INSERT INTO public.user(name,email,institution) VALUES "
sql += "('{0}','{1}','{2}') ON CONFLICT (email) DO NOTHING RETURNING id".format(
name, email, institution)
ret_data = {'user_id': None}
data = self.__execSQL(sql, True)
if (data != None):
ret_data = {'user_id': data}
return ret_data
def __fetch(self, sql):
data = None
ret_data = {'uuid': None}
try:
self.db.connect()
data = self.db.fetchData(sql)
except BaseException:
# by default return None
return data
finally:
self.db.close()
if (len(data) == 1 and len(data[0]) == 1 and data[0][0] != None):
ret_data = {'uuid': data[0][0]}
return ret_data
def __execSQL(self, sql, withReturn=False):
try:
self.db.connect()
if withReturn:
data = self.db.execQuery(sql, True)
self.db.commit()
return data
else:
self.db.execQuery(sql)
self.db.commit()
except BaseException:
self.db.rollback()
raise DatabaseError('Query execute issue', sql)
finally:
self.db.close()
class CopyDao:
"""
The Copy Data Access Object reads the most recent data from DETER Cerrado production table
and write them in one output table to DETER Cerrado publish table.
The input and output databases may are in different hosts.
See the db.cfg file for access the databases configuration definitions.
See the model.cfg file for another configurations about the database tables names.
"""
#constructor
def __init__(self):
relative_path = 'cerrado-deter/src/config/'
self.inputdb = PsqlDB(relative_path, 'db.cfg', 'production')
self.outputdb = PsqlDB(relative_path, 'db.cfg', 'publish')
self.__loadConfigurations(relative_path)
# get env var setted in Dockerfile
self.is_docker_env = os.getenv("DOCKER_ENV", False)
# If the environment is docker then use the absolute path to write log file
if self.is_docker_env:
self.data_dir = '/usr/local/data/'
else:
self.data_dir = os.path.realpath(
os.path.dirname(__file__) + '/../') + '/'
def __loadConfigurations(self, relative_path):
# read model parameters
try:
productioncfg = ConfigLoader(relative_path, 'model.cfg',
'production')
self.production_cfg = productioncfg.get()
publishcfg = ConfigLoader(relative_path, 'model.cfg', 'publish')
self.publish_cfg = publishcfg.get()
except Exception as configError:
raise configError
def copy(self, renew=False):
"""
Start copy process
The renew parameter is used to configure the behaviour of the copy process.
If renew is equal True them the output table are dropped and all data will be copied.
Return start and end date values.
Will raise a DatabaseError if exception occured.
Warning: This method opens connection, run the process and close connection.
"""
start_date = max_created_date = max_view_date = None
try:
# verify if table exists
if self.__outputTableExists() and renew:
# DROP the output table for renew all data
self.__dropOutputTable()
else:
start_date, max_view_date = self.__getLastDate()
self.__generateInsertScript(start_date)
self.__writeToOutputTable()
max_created_date, max_view_date = self.__getLastDate()
except BaseException as error:
raise error
return start_date, max_created_date, max_view_date
def __dropOutputTable(self):
"""
Drop output table from the database.
We using this method when want copy all data from input table and process that data and provide for API.
No return value but in error raise a DatabaseError exception.
Warning: This method opens connection, run the process and close connection.
"""
drop_table = "DROP TABLE IF EXISTS"
try:
self.outputdb.connect()
sql = '{0} {1}.{2}'.format(drop_table, self.publish_cfg["schema"],
self.publish_cfg["table"])
self.outputdb.execQuery(sql)
self.outputdb.commit()
except Exception as error:
self.outputdb.rollback()
raise DatabaseError('Database error:', error)
finally:
self.outputdb.close()
def __getLastDate(self):
"""
Read the last date from output table to created date and to view date.
@return string, two values, the max created date and the max view date.
"""
created = view = None
if self.__outputTableExists():
# select max date from output table
sql = "SELECT MAX(created_date::date)::varchar, MAX(view_date::date)::varchar "
sql += "FROM {0}.{1} ".format(self.publish_cfg["schema"],
self.publish_cfg["table"])
try:
self.outputdb.connect()
data = self.outputdb.fetchData(sql)
except BaseException:
# by default return None
return created, view
finally:
self.outputdb.close()
if (len(data) == 1 and len(data[0]) == 2):
created = data[0][0]
view = data[0][1]
return created, view
def __generateInsertScript(self,
from_date=None,
filter_area=0.03,
file_name=None):
"""
Read data from output table and generate a set of INSERT statements as SQL Script.
@return string, the path and name for output file with SQL insert statements or false if error.
"""
read_from_table = sql_filter = write_to_table = ""
write_to_table = "{0}.{1}".format(self.publish_cfg["schema"],
self.publish_cfg["table"])
read_from_table = "{0}.{1}".format(self.production_cfg["schema"],
self.production_cfg["table"])
if filter_area:
sql_filter = "ST_Area(ST_Transform(spatial_data,4326)::geography)/1000000 > {0}".format(
filter_area)
if from_date:
sql_filter = " {0} AND created_date::date > '{1}'".format(
sql_filter, from_date)
sql = "SELECT ('INSERT INTO {0} (object_id, cell_oid, local_name, class_name, scene_id, task_id,".format(
write_to_table)
sql += "satellite, sensor, spatial_data, area_total_km, path_row, quadrant,view_date, created_date, updated_date, auditar, control) VALUES(' || "
sql += "object_id || ',' || quote_nullable(cell_oid) || ',' || quote_nullable(local_name) || ',''' || class_name || ''',' || quote_nullable(scene_id) || ',' || quote_nullable(task_id) || ',''' || "
sql += "satellite || ''',''' || sensor || ''',''' || ST_Multi(spatial_data)::text || ''',' || ST_Area(ST_Transform(spatial_data,4326)::geography)/1000000 || ',' || "
sql += "quote_nullable(path || '_' || row) || ',' || quote_nullable(quadrant) || ',''' || view_date || ''',' || quote_nullable(created_date) || ',' || quote_nullable(updated_date) || ',' || "
sql += "quote_nullable(auditar) || ',' || quote_nullable(control) || ');') as inserts "
sql += "FROM {0} ".format(read_from_table)
if sql_filter:
sql += "WHERE {0}".format(sql_filter)
data_file = False
try:
self.inputdb.connect()
data = self.inputdb.fetchData(sql)
data_file = self.__writeScriptToFile(data, file_name)
except BaseException as error:
raise error
finally:
self.inputdb.close()
return data_file
def __writeToOutputTable(self):
# Before insert data, verify if table exists.
try:
if not self.__outputTableExists():
# Case not it'll be created.
self.__createOutputTable()
except BaseException as error:
raise error
data_file = self.data_dir + self.publish_cfg['output_data_file']
if not os.path.exists(data_file):
raise MissingParameterError(
'Import data file',
'File, {0}, was not found.'.format(data_file))
inserts = None
try:
inserts = [line.rstrip('\r\n') for line in open(data_file)]
except Exception as error:
raise MissingParameterError('Import data file',
'Error: {0}'.format(error))
try:
self.outputdb.connect()
for insert in inserts:
self.outputdb.execQuery(insert)
self.outputdb.commit()
except BaseException as error:
self.outputdb.rollback()
raise DatabaseError('Database error:', error)
finally:
self.outputdb.close()
def __writeScriptToFile(self, content, file_name=None):
output_file = self.publish_cfg['output_data_file']
if file_name:
output_file = file_name
data_file = self.data_dir + output_file
f = open(data_file, "w+")
for i in content:
if i[0]:
f.write("{0}\r\n".format(i[0]))
f.close()
if os.path.exists(data_file):
return os.path.realpath(data_file)
else:
return False
def __outputTableExists(self):
sql = "SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='{0}')".format(
self.publish_cfg["table"])
try:
self.outputdb.connect()
data = self.outputdb.fetchData(sql)
except BaseException as error:
raise error
finally:
self.outputdb.close()
return data[0][0]
def __createOutputTable(self):
sql = "CREATE TABLE {0}.{1} ".format(self.publish_cfg["schema"],
self.publish_cfg["table"])
sql += "( "
sql += "object_id integer NOT NULL, "
sql += "cell_oid character varying(255), "
sql += "local_name text, "
sql += "class_name text, "
sql += "scene_id integer, "
sql += "task_id integer, "
sql += "satellite text, "
sql += "sensor text, "
sql += "spatial_data geometry(MultiPolygon,4674), "
sql += "area_total_km double precision, "
sql += "path_row character varying(10), "
sql += "quadrant character varying(1), "
sql += "view_date date, "
sql += "created_date timestamp without time zone, "
sql += "updated_date timestamp without time zone, "
sql += "auditar character varying(5), "
sql += "control integer, "
sql += "CONSTRAINT {0}_pk PRIMARY KEY (object_id) ".format(
self.publish_cfg["table"])
sql += ") "
sql += "WITH ( "
sql += "OIDS = FALSE "
sql += ")"
try:
self.outputdb.connect()
self.outputdb.execQuery(sql)
self.outputdb.commit()
except BaseException as error:
self.outputdb.rollback()
raise DatabaseError('Database error:', error)
finally:
self.outputdb.close()
class IntersectionDao:
"""
The Intersection Data Access Object runs intersections over all
features in DETER Cerrado publish table for prepare data in comply
the SFS standart to deploy on GeoServer.
See configuration file, db.cfg for database connection definitions.
"""
#constructor
def __init__(self):
relative_path = 'cerrado-deter/src/config/'
self.db = PsqlDB(relative_path, 'db.cfg', 'publish')
self.__loadConfigurations(relative_path)
def __loadConfigurations(self, relative_path):
# read model parameters
try:
cfg = ConfigLoader(relative_path, 'model.cfg', 'publish_sfs')
self.cfg_data = cfg.get()
except Exception as configError:
raise configError
def intersections(self, renew=False):
"""
Start intersections process.
The renew parameter is used to configure the behaviour of the intersection process.
If renew is equal True them the output table are dropped and all data will be process.
Return the most recent date for the data.
Will raise a DatabaseError if exception occured.
Warning: This method opens connection, run the process and close connection.
"""
# used to return the date for the most recent data.
end_date = None
try:
if renew:
# Truncate the output table for renew all data
self.truncateOutputTable()
"""
# Drop the table is bad practice because can be cause an connection error on GeoServer.
if renew:
# DROP the output table for renew all data
self.dropOutputTable()
"""
self.dropIntermediaryTables()
self.db.connect()
last_date = self.__getLastDate()
self.__createSequence()
self.__createDataTable(last_date)
self.__intersectAlertsAndUC()
self.__intersectAlertsAndOutsideUC()
self.__joinUcAlerts()
self.__intersectUcAlertsAndCounty()
self.__dropSequence()
end_date = self.__getLastDate()
self.db.commit()
except BaseException as error:
self.db.rollback()
raise error
finally:
self.db.close()
#self.dropIntermediaryTables()
return end_date
def dropIntermediaryTables(self):
"""
Drop intermediary tables from the database.
No return value but in error raise a DatabaseError exception.
Warning: This method opens connection, run the process and close connection.
"""
drop_table = "DROP TABLE IF EXISTS"
jobber_tables = self.cfg_data["jobber_tables"]
try:
self.db.connect()
for key in jobber_tables:
sql = '{0} {1}.{2}'.format(drop_table,
self.cfg_data["jobber_schema"],
jobber_tables[key])
self.db.execQuery(sql)
self.db.commit()
except Exception as error:
self.db.rollback()
raise DatabaseError('Database error:', error)
finally:
self.db.close()
def dropOutputTable(self):
"""
Drop output table from the database.
We using this method when want copy all data from input table and process that data and provide for API.
The issue related is the cenarium where one or more geometries was removed and recriated on reclassification
by TerraAmazon leaving the new portion of geometry out of filter by date.
No return value but in error raise a DatabaseError exception.
Warning: This method opens connection, run the process and close connection.
"""
drop_table = "DROP TABLE IF EXISTS"
try:
self.db.connect()
sql = '{0} {1}.{2}'.format(drop_table,
self.cfg_data["output_schema"],
self.cfg_data["output_table"])
self.db.execQuery(sql)
self.db.commit()
except Exception as error:
self.db.rollback()
raise DatabaseError('Database error:', error)
finally:
self.db.close()
def truncateOutputTable(self):
"""
Truncate output table from the database.
We using this method when want copy all data from input table and process that data and provide for API.
The issue related is the cenarium where one or more geometries was removed and recriated on reclassification
by TerraAmazon leaving the new portion of geometry out of filter by date.
No return value but in error raise a DatabaseError exception.
Warning: This method opens connection, run the process and close connection.
"""
truncate_table = "TRUNCATE {0}.{1} RESTART IDENTITY".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
try:
self.db.connect()
sql = truncate_table
self.db.execQuery(sql)
self.db.commit()
except Exception as error:
self.db.rollback()
raise DatabaseError('Database error:', error)
finally:
self.db.close()
def __outputTableExists(self):
sql = "SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='{0}')".format(
self.cfg_data["output_table"])
data = False
try:
data = self.__fetchExecute(sql)
except BaseException as error:
raise error
if (len(data) == 1 and len(data[0]) == 1):
data = data[0][0]
return data
def __getLastDate(self):
"""
Read the last date from output table to created date.
@return string, one value, the last created date.
"""
date = None
outputTableExists = self.__outputTableExists()
if outputTableExists:
# select max date from output table
sql = "SELECT MAX(created_date::date)::varchar "
sql += "FROM {0}.{1} ".format(self.cfg_data["output_schema"],
self.cfg_data["output_table"])
try:
data = self.__fetchExecute(sql)
except BaseException:
# by default return None
return date
if (len(data) == 1 and len(data[0]) == 1):
date = data[0][0]
return date
def __getLastIdentifier(self):
"""
Read the last identifier from output table to reset sequence.
@return gid, the last identifier for output table.
"""
gid = 1
if self.__outputTableExists():
# select max gid from output table
sql = "SELECT MAX(gid) "
sql += "FROM {0}.{1} ".format(self.cfg_data["output_schema"],
self.cfg_data["output_table"])
try:
data = self.__fetchExecute(sql)
except BaseException:
# by default return 1
return gid
if (len(data) == 1 and len(data[0]) == 1):
gid = (data[0][0] if data[0][0] else 1)
return (gid + 1)
def __createDataTable(self, last_date):
sql = "CREATE TABLE {0}.{1} AS ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
sql += "SELECT nextval('{0}.{1}') as gid, ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "alerts.object_id as origin_gid, "
sql += "alerts.cell_oid, "
sql += "alerts.class_name, "
sql += "alerts.quadrant, "
sql += "alerts.path_row, "
sql += "alerts.view_date, "
sql += "alerts.created_date::date as created_date, "
sql += "alerts.sensor, "
sql += "alerts.satellite, "
sql += "alerts.spatial_data as geometries "
sql += "FROM {0}.{1} as alerts ".format(self.cfg_data["input_schema"],
self.cfg_data["input_table"])
sql += "WHERE alerts.view_date IS NOT NULL "
#sql += "AND alerts.created_date IS NOT NULL "
if last_date:
sql += "AND alerts.created_date::date > '{0}'".format(last_date)
self.__resetSequence()
self.__basicExecute(sql)
# calculate total area to complete table contents
self.__computeTotalArea()
# create gist index to improve intersections
self.__createSpatialIndex(self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
def __computeTotalArea(self):
sql = "ALTER TABLE {0}.{1} ADD COLUMN area_total_km double precision".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
self.__basicExecute(sql)
sql = "UPDATE {0}.{1} SET area_total_km = ST_Area((geometries)::geography)/1000000".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
self.__basicExecute(sql)
def __createSpatialIndex(self, schema, table, column="geometries"):
sql = "CREATE INDEX sidx_{0}_{1}_{2} ON {0}.{1} USING gist ({2}) TABLESPACE pg_default".format(
schema, table, column)
self.__basicExecute(sql)
def __intersectAlertsAndUC(self):
sql = "CREATE TABLE {0}.{1} AS ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb2"])
sql += "SELECT alerts.gid as origin_gid, "
sql += "alerts.class_name, "
sql += "alerts.quadrant, "
sql += "alerts.path_row, "
sql += "alerts.view_date, "
sql += "alerts.created_date, "
sql += "alerts.sensor, "
sql += "alerts.satellite, "
sql += "alerts.area_total_km, "
sql += "ST_Area(ST_Intersection(alerts.geometries, uc.geom)::geography)/1000000 as area_uc_km, "
sql += "uc.nome as uc, "
sql += "ST_Intersection(alerts.geometries, uc.geom) as geometries, "
sql += "nextval('{0}.{1}') as gid ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "FROM {0}.{1} as alerts ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
sql += "INNER JOIN {0}.{1} as uc ON ( (alerts.geometries && uc.geom) AND ST_Intersects(alerts.geometries, uc.geom) )".format(
self.cfg_data["jobber_schema"], self.cfg_data["uc_table"])
self.__resetSequence()
self.__basicExecute(sql)
def __intersectAlertsAndOutsideUC(self):
sql = "CREATE TABLE {0}.{1} AS ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb3"])
sql += "SELECT alerts.gid as origin_gid, "
sql += "alerts.class_name, "
sql += "alerts.quadrant, "
sql += "alerts.path_row, "
sql += "alerts.view_date, "
sql += "alerts.created_date, "
sql += "alerts.sensor, "
sql += "alerts.satellite, "
sql += "alerts.area_total_km, "
sql += "(0)::double precision as area_uc_km, "
sql += "(NULL)::character varying as uc, "
sql += "ST_Intersection(alerts.geometries, outside.geom) as geometries, "
sql += "nextval('{0}.{1}') as gid ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "FROM {0}.{1} as alerts ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb1"])
sql += "INNER JOIN {0}.{1} as outside ON ( (alerts.geometries && outside.geom) AND ST_Intersects(alerts.geometries, outside.geom) )".format(
self.cfg_data["jobber_schema"], self.cfg_data["limit_cerrado"])
self.__resetSequence()
self.__basicExecute(sql)
def __joinUcAlerts(self):
sql = "CREATE TABLE {0}.{1} AS ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb4"])
sql += "SELECT nextval('{0}.{1}') as gid, * FROM ( ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "SELECT uc.origin_gid, "
sql += "uc.class_name, "
sql += "uc.quadrant, "
sql += "uc.path_row, "
sql += "uc.view_date, "
sql += "uc.created_date, "
sql += "uc.sensor, "
sql += "uc.satellite, "
sql += "uc.area_total_km, "
sql += "uc.area_uc_km, "
sql += "uc.uc, "
sql += "uc.geometries "
sql += "FROM {0}.{1} as uc ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb2"])
sql += "UNION "
sql += "SELECT outucs.origin_gid, "
sql += "outucs.class_name, "
sql += "outucs.quadrant, "
sql += "outucs.path_row, "
sql += "outucs.view_date, "
sql += "outucs.created_date, "
sql += "outucs.sensor, "
sql += "outucs.satellite, "
sql += "outucs.area_total_km, "
sql += "outucs.area_uc_km, "
sql += "outucs.uc, "
sql += "outucs.geometries "
sql += "FROM {0}.{1} as outucs ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb3"])
sql += ") as ta "
sql += "WHERE origin_gid is not null"
self.__resetSequence()
self.__basicExecute(sql)
# create gist index to improve intersections
self.__createSpatialIndex(self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb4"])
def __intersectUcAlertsAndCounty(self):
tableExists = self.__outputTableExists()
last_gid = 1
sql = "SELECT alerts.origin_gid, "
sql += "alerts.class_name as classname, "
sql += "alerts.quadrant, "
sql += "alerts.path_row, "
sql += "alerts.view_date, "
sql += "alerts.created_date, "
sql += "alerts.sensor, "
sql += "alerts.satellite, "
sql += "alerts.area_total_km as areatotalkm, "
sql += "alerts.area_uc_km as areauckm, "
sql += "alerts.uc, "
sql += "ST_Area(ST_Intersection(alerts.geometries, mun.geom)::geography)/1000000 as areamunkm, "
sql += "mun.nm_municip as county, "
sql += "mun.nm_sigla as uf, "
sql += "coalesce(ST_Intersection(alerts.geometries, mun.geom), alerts.geometries) as geom, "
sql += "nextval('{0}.{1}') as gid ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "FROM {0}.{1} as alerts ".format(
self.cfg_data["jobber_schema"],
self.cfg_data["jobber_tables"]["tb4"])
sql += "LEFT JOIN {0}.{1} as mun ON ( (alerts.geometries && mun.geom) AND ST_Intersects(alerts.geometries, mun.geom) )".format(
self.cfg_data["jobber_schema"], self.cfg_data["county_table"])
if tableExists:
last_gid = self.__getLastIdentifier()
sql_insert = "INSERT INTO {0}.{1} ".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
sql_insert += "(origin_gid, classname, quadrant, path_row, view_date, "
sql_insert += "created_date, sensor, satellite, areatotalkm, areauckm, "
sql_insert += "uc, areamunkm, county, uf, geom, gid) "
sql_insert += "SELECT tb1.origin_gid, tb1.classname, tb1.quadrant, tb1.path_row, tb1.view_date, "
sql_insert += "tb1.created_date, tb1.sensor, tb1.satellite, tb1.areatotalkm, tb1.areauckm, "
sql_insert += "tb1.uc, tb1.areamunkm, tb1.county, tb1.uf, tb1.geom, tb1.gid "
sql_insert += "FROM ({0}) as tb1 ".format(sql)
sql = sql_insert
else:
sql = "CREATE TABLE {0}.{1} AS {2}".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"],
sql)
self.__resetSequence(last_gid)
self.__basicExecute(sql)
"""
Update area for Features where occurs the fractionation after intersect with counties
"""
sql = "UPDATE {0}.{1} SET areauckm=ST_Area(geom::geography)/1000000 WHERE uc is not null".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
if not tableExists:
"""
Alter table to add temporal column to publish on geoserver with monthly granularity
"""
sql = "ALTER TABLE {0}.{1} ADD COLUMN publish_month date".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
sql = "UPDATE {0}.{1} SET publish_month=overlay(view_date::varchar placing '01' from 9 for 2)::date".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
sql = "CREATE INDEX publish_month_idx ON {0}.{1} USING btree (publish_month ASC NULLS LAST)".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
sql = "ALTER TABLE {0}.{1} CLUSTER ON publish_month_idx".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
"""
Create geographic index
"""
sql = "CREATE INDEX deter_cerrado_geom_index ON {0}.{1} USING gist (geom)".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
else:
sql = "UPDATE {0}.{1} SET publish_month=overlay(view_date::varchar placing '01' from 9 for 2)::date".format(
self.cfg_data["output_schema"], self.cfg_data["output_table"])
self.__basicExecute(sql)
def __resetSequence(self, next_gid=1):
sql = "ALTER SEQUENCE {0}.{1} RESTART WITH {2}".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"],
next_gid)
self.__basicExecute(sql)
def __createSequence(self):
"""
Create one sequence used to intermediary tables
"""
sql = "CREATE SEQUENCE {0}.{1} INCREMENT 1 MINVALUE 1 ".format(
self.cfg_data["jobber_schema"], self.cfg_data["sequence"])
sql += "MAXVALUE 9223372036854775807 START 1 CACHE 1"
self.__basicExecute(sql)
def __dropSequence(self):
"""
Drop the sequence used to intermediary tables
"""
sql = "DROP SEQUENCE {0}.{1}".format(self.cfg_data["jobber_schema"],
self.cfg_data["sequence"])
self.__basicExecute(sql)
def __basicExecute(self, sql):
"""
Execute a basic SQL statement.
"""
try:
self.db.execQuery(sql)
except Exception as error:
self.db.rollback()
raise DatabaseError('Database error:', error)
def __fetchExecute(self, sql):
"""
Execute a SQL statement and fetch the result data.
"""
data = False
try:
data = self.db.fetchData(sql)
except BaseException as error:
raise DatabaseError('Database error:', error)
return data