def load2table(p_cursor, p_geojson):
#print('--- BO - load.laod2table. ---')
try:
p_cursor.execute('''drop table ld_geojson''')
except Exception as e:
None
try:
p_cursor.execute('''create table ld_geojson (ldjson clob, constraint chk_ld_geojson check (ldjson IS JSON))''')
except Exception as e:
logexcep('$$$ Error load.load2table(create ldgeojson) : {}'.format(e))
raise
try:
with open(p_geojson, 'r', encoding='UTF-8') as f:
geojson = f.read()
p_cursor.execute('''insert into ld_geojson (ldjson)
values ( :clobdata )''', clobdata=geojson)
p_cursor.execute("commit")
except Exception as e:
logexcep('$$$ Error load.load2table(insert into ldgeojson) : {}'.format(e))
raise
loginfo('--- EO - load2table. ---')
return
def get_arguments():
arg_parser = argparse.ArgumentParser(
description='`Ring of Oracle GeoJSON`')
msg = 'File of GeoJSON'
arg_parser.add_argument('geojson', help=msg)
msg2 = 'Table Name (default: geojson)'
arg_parser.add_argument('-t', '--table', default='geojson', help=msg2)
msg3 = 'Connection : <username>/<password>@<OracleATP-SID> '
arg_parser.add_argument('connection', help=msg3)
try:
args = arg_parser.parse_args()
file_ = args.geojson
table_ = args.table
connect_ = args.connection
csp1_ = connect_.split('@')
sid_ = csp1_[1]
csp2_ = csp1_[0].split('/')
un_ = csp2_[0]
except Exception as e:
logexcep(
"$$$ ERROR in connect string `<un>/<pw>@<Oracle-SID>`! Found: " +
connect_)
raise
return file_, table_.upper(), connect_, un_, sid_
def setmetadata4SDO(p_conn, p_cursor, p_table):
#loginfo('--- BO - setmetadata4SDO ---')
# verify the Feature Table
for row in p_cursor.execute('''select Table_name, Column_name
from user_tab_columns
where Data_type like 'SDO_GEOMETRY' AND Table_name=:i_table
order by Table_name''',
i_table=p_table):
try:
_cursor = p_conn.cursor()
# delete metadata(table)
_cursor.execute(''' delete from user_sdo_geom_metadata
where Table_name = :i_tabname AND Column_name = :i_colname''',
i_tabname=row[0],
i_colname=row[1])
# get the SRID
_sql = f"select s.geometry.sdo_srid " \
f" from {row[0]} s" \
f" where rownum<2 "
_cursor.execute(_sql)
_row = _cursor.fetchone()
# set metadata: row[0]:table_name, row[1]:column_name, _row[0]:srid
set_cursor = p_conn.cursor()
defmeta = f"insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)" \
f" select '{row[0]}' as table_name, '{row[1]}' as column_name, " \
f" mdsys.sdo_dim_array( " \
f" mdsys.SDO_DIM_ELEMENT('X', minX, maxX, 0.05), " \
f" mdsys.SDO_DIM_ELEMENT('Y', minY, maxY, 0.05) " \
f") as diminfo, " \
f" {_row[0]} as srid " \
f"from ( " \
f"select trunc(min(t.x)-1.0) minX, round(max(t.x)+1.0) maxX, " \
f" trunc(min(t.y)-1.0) minY, round(max(t.y)+1.0) maxY " \
f"from {row[0]} b, table(sdo_util.getvertices(b.{row[1]})) t)"
set_cursor.execute(defmeta)
set_cursor.execute('commit')
set_cursor.close
_cursor.close
except Exception as e:
logexcep('$$$ Error setmetadata4SDO: {}'.format(e))
raise
loginfo('--- EO - setmetadata4SDO. ---')
return
def dbopen(p_connstr):
try:
os.environ["NLS_LANG"] = ".AL32UTF8"
global conn
conn = cx_Oracle.connect(p_connstr,
encoding="UTF-8",
nencoding="UTF-8")
global cursor
cursor = conn.cursor()
schema = p_connstr.split('/', 1)
except Exception as e:
logexcep('$$$ Error db.connet() : {}'.format(e))
raise
loginfo('--- EO - DB connection opened. ---')
return conn, cursor, schema[0]
def crindex4SDO(p_conn, p_cursor, p_table):
#loginfo('--- BO - crindex4SDO ---')
# verify the Feature Table
for row in p_cursor.execute('''select Table_name, Column_name
from user_tab_columns
where Data_type like 'SDO_GEOMETRY' AND Table_name=:i_table
order by Table_name''',
i_table=p_table):
try:
_cursor = p_conn.cursor()
for _row in _cursor.execute(
'''select Index_name from user_sdo_index_info
where Table_name= :i_table''',
i_table=p_table):
try:
_drpix = f'drop index IX_SDO_{_row[0]} '
drpix_cursor = p_conn.cursor()
drpix_cursor.execute(_drpix)
drpix_cursor.close
except Exception as e:
logexcep('$$$ Error drop SDO index: {}'.format(e))
raise
_cursor.close
try:
defindex = f'create index IX_SDO_{row[0]} on {row[0]}' \
f' ( {row[1]} ) indextype is mdsys.spatial_index_V2'
ix_cursor = p_conn.cursor()
ix_cursor.execute(defindex)
ix_cursor.close
except Exception as e:
logexcep('$$$ Error create SDO index: {}'.format(e) + ' : ' +
f'IX_SDO_{row[0]}')
raise
except Exception as e:
logexcep('$$$ Error crindex4SDO: {}'.format(e))
raise
loginfo('--- EO - crindex4SDO. ---')
return
#-------------------------------------------------
from orageojson.sdo.index import crindex4SDO
from orageojson.sdo.qualify import setmetadata4SDO
from orageojson.sdo.validate2 import validate_SDO2
from orageojson.view.map_intern_leaflet2 import map_intern_leaflet
from orageojson.log.flogger import flog, init_logging, loginfo, logexcep
#.............................
if __name__ == '__main__':
try:
init_logging('--- BO - File logging. ---')
file_, table_, connect_, un_, sid_ = get_arguments()
conn_, cursor_, schema_ = dbopen(connect_)
load2table(cursor_, file_)
properties_ = table2feature(cursor_, table_)
validate_SDO2(conn_, cursor_, table_)
setmetadata4SDO(conn_, cursor_, table_)
crindex4SDO(conn_, cursor_, table_)
ords_url_ = createords(conn_, cursor_, schema_, table_)
map_intern_leaflet(ords_url_, properties_)
except Exception as e:
logexcep('$$$ Error main : {}'.format(e))
finally:
dbclose()
loginfo('§§§ --- Fine. --- §§§')
#---------------------------- Fried.Matz --------------------------------
def table2feature(p_cursor, p_ftable):
#print('--- BO - table2feature. ---')
try:
drp_ftable = f'drop table {p_ftable} purge'
p_cursor.execute(drp_ftable)
except Exception as e:
None
try:
None
except Exception as e:
None
try:
_colist = ['']
_coldefs = str
_proplist = ['"<p>" + ']
_propdefs = str
for row in p_cursor.execute(
'''SELECT column_name||' '||column_type||' PATH \'''||xPATH||'\''' def,
'feature.properties.'||column_name||' + "</br>" ' from (
WITH sel_ AS (
SELECT JSON_DATAGUIDE(ldjson) js_guide
FROM ld_geojson
)
SELECT upper(substr(gj.jpath,23)) column_name,
CASE upper(gj.type)
WHEN 'STRING' THEN 'VARCHAR2' ||'('||gj.tlength||')'
WHEN 'NUMBER' THEN 'NUMBER' ||'('||gj.tlength||')'
WHEN 'OBJECT' THEN 'JSON'
ELSE 'VARCHAR2(256)'
END column_type,
upper(gj.type)||'('||gj.tlength||')' column_type2,
'$.'||substr(gj.jpath,12) xPATH
FROM sel_,
json_table(js_guide, '$[*]'
COLUMNS
jpath VARCHAR2(40) PATH '$."o:path"',
type VARCHAR2(10) PATH '$."type"',
tlength NUMBER PATH '$."o:length"') gj
WHERE gj.jpath like '$.features.properties.%'
ORDER BY gj.jpath
)
'''):
_colist.append(row[0] + ', ')
_proplist.append(row[1] + ' + ')
_coldefs = ''.join(_colist)
_proplist.append('"</p>"') #ok
_propdefs = ''.join(_proplist)
except Exception as e:
logexcep('$$$ Error load.table2feature(_coldefs) : {}'.format(e))
raise
try:
ftable = f"CREATE TABLE {p_ftable} AS " \
f" SELECT jt.* FROM ld_geojson, " \
f" json_table( ldjson, '$.features[*]' " \
f" COLUMNS ( {_coldefs} " \
f" geometry SDO_GEOMETRY PATH '$.geometry' )) jt "
p_cursor.execute(ftable)
# alter table including FID with automatic update it !
alt_ftable = f"alter table {p_ftable} add " \
f" fid NUMBER(10) GENERATED ALWAYS AS IDENTITY " \
f" (START WITH 1 INCREMENT BY 1) NOT NULL"
p_cursor.execute(alt_ftable)
# alter table including status (e.g. geometries) !
alt_ftable = f"alter table {p_ftable} add status VARCHAR2(1024) "
p_cursor.execute(alt_ftable)
# alter table including date of last change !
alt_ftable = f"alter table {p_ftable} add dlastchange DATE "
p_cursor.execute(alt_ftable)
except Exception as e:
logexcep('$$$ Error load.table2feature(create & alter): {}'.format(e))
flog('$$$ Error load.table2feature(create & alter): {}'.format(e))
flog(_coldefs)
raise
loginfo('--- EO - table2feature. ---')
return _propdefs
def createords(p_conn, p_cursor, p_schema, p_table):
#loginfo('--- BO - crgeojsonords. ---:' + p_table)
ords_url_ = None
c_ords_module_name = 'geo_api'
# create the GeoJSON Feature Collection View and the Oracle RESTful Data Service
for row in p_cursor.execute('''select Table_name, Column_name
from user_tab_columns
where Data_type like 'SDO_GEOMETRY' AND Table_name=:i_table
order by Table_name''',
i_table=p_table):
_cursor = p_conn.cursor()
_colist = ['']
_coldefs = str
# get the property column names of the Feature Table
for _row in _cursor.execute(
'''select '\'''||Column_name||'\'' value '|| Column_name
from user_tab_columns
where Data_type NOT like 'SDO_GEOMETRY' AND Table_name=:j_table
''',
j_table=p_table):
_colist.append(_row[0] + ',')
#print(_colist)
_coldefs = ''.join(_colist)
_coldefs = _coldefs[:-1:] + '),'
#print(_coldefs)
# change last , with )
_cursor.close()
try:
# create the native GeoJSON Feature Collection view
defview = f"create or replace view v_feature_{row[0]} as " \
f" select json_object( " \
f" 'type' value 'FeatureCollection', " \
f" 'features' value json_arrayagg( " \
f" json_object( " \
f" 'fid' value c.FID, " \
f" 'type' value 'Feature', " \
f" 'properties' value json_object( " \
f" {_coldefs} " \
f" 'geometry' value c.{row[1]}.get_geojson() format json " \
f" returning clob " \
f" ) returning clob " \
f" ) returning clob " \
f") as feature " \
f" from {row[0]} c"
view_cursor = p_conn.cursor()
view_cursor.execute(defview)
view_cursor.close()
except Exception as e:
logexcep('$$$ Error ords.createords(create view): {}'.format(e))
raise
try:
# create the native GeoJSON ORDS
plsql = f"BEGIN " \
f" ORDS.ENABLE_SCHEMA( " \
f" p_enabled => TRUE, " \
f" p_schema => upper(\'{p_schema}\'), " \
f" p_url_mapping_type => 'BASE_PATH', " \
f" p_url_mapping_pattern => \'{p_schema}\', " \
f" p_auto_rest_auth => FALSE); " \
f" END; "
plsql2 = f"BEGIN " \
f" ORDS.DEFINE_MODULE( " \
f" p_module_name => \'{c_ords_module_name}\', " \
f" p_base_path => '/geojson/', " \
f" p_items_per_page => 25, " \
f" p_status => 'PUBLISHED', " \
f" p_comments => 'Module for schema.FeatureTable'); " \
f" END; "
plsql3 = f"BEGIN " \
f" ORDS.DEFINE_TEMPLATE( " \
f" p_module_name => \'{c_ords_module_name}\', " \
f" p_pattern => 'media', " \
f" p_priority => 0, " \
f" p_etag_type => 'HASH', " \
f" p_etag_query => NULL, " \
f" p_comments => 'Template for schema.FeatureTable'); " \
f" END; " \
plsql4 = f"BEGIN " \
f" ORDS.DEFINE_HANDLER( " \
f" p_module_name => \'{c_ords_module_name}\', " \
f" p_pattern => 'media', " \
f" p_method => 'GET', " \
f" p_source_type => 'resource/lob', " \
f" p_items_per_page => 25, " \
f" p_mimes_allowed => '', " \
f" p_comments => 'Media Handler for schema.FeatureTable', " \
f" p_source => 'select ''application/json; charset=utf-8'', feature from v_feature_{p_table}' " \
f" ); " \
f"END; "
commit = '''COMMIT'''
ords_cursor = p_conn.cursor()
ords_cursor.execute(plsql)
ords_cursor.execute(plsql2)
ords_cursor.execute(plsql3)
ords_cursor.execute(plsql4)
ords_cursor.execute(commit)
# get the ORDS common base url
for _row in ords_cursor.execute(''' SELECT
name,
uri_prefix,
uri_template,
method,
source_type,
SOURCE
FROM
user_ords_modules a,
user_ords_templates b,
user_ords_handlers c
WHERE
a.id = b.module_id
AND b.id = c.template_id
AND a.name=:l_module
''',
l_module=c_ords_module_name):
ords_url_ = get_ords_base_url(
) + p_schema + '/' + _row[1] + _row[2]
flog(ords_url_, 'ORDS') # => `./flog-ORDS.log`
ords_cursor.close
except Exception as e:
logexcep('$$$ Error ords.createords(create ords): {}'.format(e))
raise
loginfo('--- EO - crgeojsonords. ---')
# return url
return ords_url_
def validate_SDO2 (p_conn, p_cursor, p_ftable):
#loginfo('--- BO - validate_SDO2 ---')
cnterrors_ = 0
counts_ = 0
# -- drop & create feature error table
err_cursor = p_conn.cursor()
try:
drp_ftable = f"drop table {p_ftable}_errors "
err_cursor.execute(drp_ftable)
except Exception as e:
None
try:
ferrtable = f"CREATE TABLE {p_ftable}_errors " \
f" AS SELECT * FROM {p_ftable} "
err_cursor.execute(ferrtable)
ferrtable = f"TRUNCATE TABLE {p_ftable}_errors "
err_cursor.execute(ferrtable)
err_cursor.execute('''COMMIT''')
except Exception as e:
logexcep('$$$ Error validate(create <tab>_errors) : {}')#.format(e))
raise
#loginfo('--- BEFORE TABLE - LOOP ---')
# verify the Feature Table
for row in p_cursor.execute('''SELECT Table_name, Column_name
FROM user_tab_columns
WHERE Data_type like 'SDO_GEOMETRY' AND Table_name=:i_table
ORDER BY Table_name ''',
i_table=p_ftable):
try:
val_cursor = p_conn.cursor()
val_block = f" DECLARE \n" \
f" l_geometry_fixed SDO_GEOMETRY; \n" \
f" -- Declare a custom exception for uncorrectable geometries \n" \
f" -- 'ORA-13199: the given geometry cannot be rectified ' \n" \
f" cannot_rectify exception; \n" \
f" pragma exception_init(cannot_rectify, -13199); \n" \
f" BEGIN \n" \
f" EXECUTE IMMEDIATE 'DROP TABLE {p_ftable}_errors'; \n" \
f" EXECUTE IMMEDIATE 'CREATE TABLE {p_ftable}_errors AS SELECT * from {p_ftable}'; \n" \
f" EXECUTE IMMEDIATE 'TRUNCATE TABLE {p_ftable}_errors'; \n" \
f" FOR g IN ( SELECT fid, f.rowid, f.{row[1]}, sdo_geom.validate_geometry_with_context({row[1]}, 0.005) result \n" \
f" FROM {p_ftable} f \n" \
f" WHERE sdo_geom.validate_geometry_with_context({row[1]}, 0.005) != 'TRUE' \n" \
f" ) \n" \
f" LOOP \n" \
f" BEGIN \n" \
f" l_geometry_fixed := sdo_util.rectify_geometry (g.{row[1]}, 0.005); \n" \
f" -- Update the base table with the rectified geometry \n" \
f" UPDATE {p_ftable} u SET geometry = l_geometry_fixed \n" \
f" WHERE u.rowid = g.rowid; \n" \
f" COMMIT; \n" \
f" EXCEPTION WHEN cannot_rectify THEN \n" \
f" -- Move error_record from `tab` to `tab_errors`, \n" \
f" -- set status in `tab_errors` \n" \
f" -- delete error_record in `tab` \n" \
f" INSERT INTO {p_ftable}_errors \n" \
f" SELECT * FROM {p_ftable} i \n" \
f" WHERE i.rowid = g.rowid; \n" \
f" UPDATE {p_ftable}_errors eu SET status = g.result \n" \
f" WHERE eu.rowid = g.rowid; \n" \
f" DELETE FROM {p_ftable} d WHERE d.rowid = g.rowid; \n" \
f" COMMIT; \n" \
f" END; \n" \
f" END LOOP validate_rectify; \n" \
f" END validate; \n"
val_cursor.execute(val_block)
val_cursor.execute('commit')
except Exception as e:
logexcep('$$$ Error validate: {}'.format(e))
raise
#loginfo("--- Get Count1 ---")
# if no valid SDO data then sense .
try:
_cursor = p_conn.cursor()
# get one
_sql = f"SELECT count(*) " \
f" FROM {row[0]}"
_cursor.execute(_sql)
counts_ = _cursor.fetchone()[0]
except Exception as e:
logexcep('$$$ Error validate: {}'.format(e))
raise
if (counts_ == 0):
logcrit('$$$ Error NO VALIDATED SDO data : Sense . $$$')
raise
#loginfo("--- Get Count2 ---")
# get one
try:
_sql = f"SELECT count(*) " \
f" FROM {row[0]}_errors"
_cursor.execute(_sql)
cnterrors_ = _cursor.fetchone()[0]
except Exception as e:
logexcep('$$$ Error validate: {}'.format(e))
raise
loginfo('--- EO - validate_SDO2 . Number of Records: {a} / Errors: {b}'.format(a=counts_, b=cnterrors_) )
return
# ------------------------------------