def get_bcgw_column_key(db_table,user_name,user_pass):
# estimate a unique id column for an oracle table if OBJECTID does not exist
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
sql = f"SELECT cols.column_name \
FROM all_tab_cols cols where cols.table_name = '{table}' and cols.COLUMN_NAME like \'OBJECTID\'"
q.exec(sql)
if q.first():
key_c = q.value(0)
else:
sql = f"SELECT COLUMN_NAME FROM all_tab_cols where table_name = '{table}' \
order by COLUMN_ID FETCH FIRST 1 ROWS ONLY"
q.exec(sql)
if q.first():
key_c = q.value(0)
db.close()
return key_c
def get_bcgw_geomcolumn(db_table,user_name,user_pass):
# get the name of the geometry column for oracle table
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
query ="SELECT COLUMN_NAME from all_tab_columns where OWNER = '{}' AND TABLE_NAME = '{}' AND DATA_TYPE = 'SDO_GEOMETRY'".format(owner,table)
q.exec(query)
q.first()
geom_c = q.value(0)
db.close()
return geom_c
def get_bcgw_table_geomtype(db_table,geom_column_name,user_name,user_pass):
# get geometry type from oracle table - oracle stores multiple types so
# this returns the maximum type ie multiline, multipolygon, multipoint if
# present in geometry
owner,table = db_table.split('.')
driver ="QOCISPATIAL"
conn_name = "bcgw_conn"
if not QSqlDatabase.contains(conn_name):
db = QSqlDatabase.addDatabase(driver,conn_name)
else:
db = QSqlDatabase.database(conn_name)
db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov')
db.setUserName(user_name)
db.setPassword(user_pass)
db.open()
if not db.open():
print ("Failed Connection from find_bcgw_the_geom")
q = QSqlQuery(db)
query = f"SELECT MAX(t.{geom_column_name}.GET_GTYPE()) AS geometry_type from {owner}.{table} t"
q.exec(query)
q.first()
type_num = q.value(0)
if type_num == 1:
geom_t = 'Point'
elif type_num == 2:
geom_t = 'LineString'
elif type_num == 3:
geom_t = 'Polygon'
elif type_num == 7:
geom_t = 'MultiPolygon'
elif type_num ==5:
geom_t = 'MulitPoint'
elif type_num ==6:
geom_t = 'MultiLineString'
else:
db.close()
raise TypeError
db.close()
return geom_t
