class DataDao:
#constructor
def __init__(self):
relative_path = os.path.abspath(os.path.dirname(__file__) + '/config')
self.db = PsqlDB(relative_path, 'db.cfg', 'publish')
def makeClusters(self,
schema="public",
working_table="deter_forest_monitor",
buffer_len=0.000540541):
"""
Discover deforestation scenarios using clusters to isolate each one.
Before thing we try to drop the old cluster table.
The buffer_len parameter is used to calculate the proximity between polygons to define if its is putting together on one cluster.
Its value depends of the projection of the geographic data on analised table. The default value, 0.000540541, is in degrees and corresponds to 60 meters.
Other configuration to compute clusters is the minimal points of proximity among polygons, minpoints, defined direct on code.
To filter the candidates to compose the clusters, we have two more parameters,
interval_filter used to obtain polygons up to 6 months old and
classname_filter used to get only deforestation polygons.
"""
minpoints = 2
interval_filter = "6 month"
classname_filter = "'DESMATAMENTO_VEG','DESMATAMENTO_CR','MINERACAO'"
sql = "DROP TABLE IF EXISTS public.deforestation_cluster;"
self.__execSQL(sql)
sql = "CREATE TABLE public.deforestation_cluster AS "
sql += "SELECT cluster_id, ST_UNION(geom) AS cluster_geom, array_agg(parcel_id) AS ids_in_cluster FROM ( "
sql += "SELECT tb1.id as parcel_id, ST_ClusterDBSCAN(tb1.geom, eps := {0}, minpoints := {1}) over () AS cluster_id, tb1.geom ".format(
buffer_len, minpoints)
sql += "FROM {0}.{1} as tb1 ".format(schema, working_table)
sql += "WHERE tb1.view_date::date >= (now() - '{0}'::interval)::date ".format(
interval_filter)
sql += "AND tb1.classname in ({0}) ".format(classname_filter)
sql += ") sq "
sql += "GROUP BY cluster_id;"
self.__execSQL(sql)
def getClusters(self):
"""
Read the cluster's id by dissolved polygons.
"""
sql = "SELECT cluster_id FROM public.deforestation_cluster WHERE cluster_id IS NOT NULL GROUP BY cluster_id"
return self.__fetch(sql)
def computeParametersByClusterId(self,
cluster_id,
schema="public",
working_table="deter_forest_monitor"):
"""
Compute the contiguity and the deforestation speed and update values on working table.
cluster_id used to isolate one deforestation scenario
buffer_len is the offset used to identify direct relations among polygons of the one specifique scenario
"""
sql = "WITH statistic_pols AS ( "
sql += "SELECT COUNT(*) as num_pols, SUM(areamunkm) as area, MIN(view_date::date) as start_date, MAX(view_date::date) as end_date, "
sql += "CASE WHEN (MAX(view_date::date)-MIN(view_date::date)) > 0 THEN (MAX(view_date::date)-MIN(view_date::date)) ELSE 1 END as delta_t "
sql += "FROM {0}.{1} ".format(schema, working_table)
sql += "WHERE id in (SELECT unnest(ids_in_cluster) FROM public.deforestation_cluster where cluster_id={0}) ".format(
cluster_id)
sql += ") "
sql += "UPDATE {0}.{1} ".format(schema, working_table)
sql += "SET contiguity=1, speed=(area/delta_t) "
sql += "FROM statistic_pols "
sql += "WHERE num_pols > 1 AND id in (SELECT unnest(ids_in_cluster) FROM public.deforestation_cluster WHERE cluster_id={0}) ".format(
cluster_id)
self.__execSQL(sql)
"""
Compute the percent of participation of one polygon into their cluster.
"""
sql = "WITH cluster_data AS ( "
sql += "SELECT ST_area(cluster_geom::geography)/1000000 as cluster_area, unnest(ids_in_cluster) as ids "
sql += "FROM public.deforestation_cluster WHERE cluster_id={0} ".format(
cluster_id)
sql += ") "
sql += "UPDATE public.deter_forest_monitor "
sql += "SET participation=(ST_area(geom::geography)/1000000*100)/cluster_area "
sql += "FROM cluster_data cd "
sql += "WHERE id in (cd.ids) "
self.__execSQL(sql)
def resetParameters(self,
schema="public",
working_table="deter_forest_monitor"):
"""
Reset contiguity to prevent the case when polygons that lost the cluster.
- contiguity (integer)
- participation (double precision)
- speed (double precision)
"""
sql = "UPDATE {0}.{1} SET contiguity=0, participation=0.0, speed=0.0;".format(
schema, working_table)
self.__execSQL(sql)
def createParameters(self,
schema="public",
working_table="deter_forest_monitor"):
"""
Prepare the table with new columns to receive new data if these columns don't exists.
- contiguity (integer)
- participation (double precision)
- speed (double precision)
"""
sql = "ALTER TABLE {0}.{1} ADD COLUMN IF NOT EXISTS contiguity integer DEFAULT 0;".format(
schema, working_table)
self.__execSQL(sql)
sql = "ALTER TABLE {0}.{1} ADD COLUMN IF NOT EXISTS participation double precision DEFAULT 0.0;".format(
schema, working_table)
self.__execSQL(sql)
sql = "ALTER TABLE {0}.{1} ADD COLUMN IF NOT EXISTS speed double precision DEFAULT 0.0;".format(
schema, working_table)
self.__execSQL(sql)
def __fetch(self, sql):
data = None
try:
self.db.connect()
data = self.db.fetchData(sql)
except BaseException:
# by default return None
return data
finally:
self.db.close()
return data
def __execSQL(self, sql):
try:
self.db.connect()
data = self.db.execQuery(sql)
self.db.commit()
except BaseException as error:
raise error
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 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 UuidTableDao:
#constructor
def __init__(self):
relative_path = os.path.abspath(os.path.dirname(__file__) + '/config')
self.db = PsqlDB(relative_path,'db.cfg','publish')
def confirmUUID(self, uuid):
"""
Verify if UUID is in the table.
@return exists, true if UUID exists on table or false otherwise
"""
sql = "SELECT num_downloads "
sql += "FROM public.downloads_by_uuid "
sql += "WHERE uuid='{0}'".format(uuid)
ret=self.__fetch(sql)
if (ret!=None and ret['num_downloads']>=0):
return True
else:
return False
def increaseDownloadByUUID(self, uuid):
"""
Increments the number of downloads using this UUID.
@return void
"""
sql = "UPDATE public.downloads_by_uuid "
sql += "SET num_downloads="
sql += "(SELECT num_downloads + 1 FROM public.downloads_by_uuid WHERE uuid='{0}') ".format(uuid)
self.__execSQL(sql)
def storeClientIP(self, uuid, ip):
"""
Store the client IP.
@return void
"""
sql = "INSERT INTO public.request_by_ip(id_download, ip) VALUES "
sql += "((select id from public.downloads_by_uuid where uuid='{0}'), '{1}')".format(uuid,ip)
self.__execSQL(sql)
def __fetch(self, sql):
data = None
ret_data = {
'num_downloads':-1,
}
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={
'num_downloads':data[0][0]
}
return ret_data
def __execSQL(self, sql):
try:
self.db.connect()
self.db.execQuery(sql)
self.db.commit()
except BaseException:
self.rollback()
raise DatabaseError('Query execute issue', sql)
finally:
self.db.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