def extract(self):
self.logger.info('Starting extract from {}'.format(self.schema_table_name))
import geopetl
etl.fromoraclesde(self.conn, self.schema_table_name) \
.tocsv(self.csv_path, encoding='latin-1')
self.load_csv_to_s3()
os.remove(self.csv_path)
self.logger.info('Successfully extracted from {}'.format(self.schema_table_name))
etl.fromoraclesde(read_dsn, source_table_name, where="SDE.ST_ISEMPTY(SHAPE)=0") \
.cut('objectid', 'mapreg', 'stcod', 'house', 'suf', 'unit', 'stex', 'stdir', 'stnam',
'stdes', 'stdessuf', 'status', 'shape') \
.addfield('concatenated_address', lambda c: concatenate_dor_address(
{'house': c['house'], 'suf': c['suf'], 'stex': c['stex'], 'stdir': c['stdir'], 'stnam': c['stnam'],
'stdes': c['stdes'], 'stdessuf': c['stdessuf'], 'unit': c['unit'], 'stcod': c['stcod']})) \
.addfield('parsed_comps', lambda p: parser.parse(p['concatenated_address'])) \
.addfield('std_address_low', lambda a: a['parsed_comps']['components']['address']['low_num']) \
.addfield('std_address_low_suffix', lambda a: a['parsed_comps']['components']['address']['addr_suffix'] if
a['parsed_comps']['components']['address']['addr_suffix'] else a['parsed_comps']['components']['address']['fractional']) \
.addfield('std_high_num', lambda a: a['parsed_comps']['components']['address']['high_num']) \
.addfield('std_street_predir', lambda a: a['parsed_comps']['components']['street']['predir']) \
.addfield('std_street_name', lambda a: a['parsed_comps']['components']['street']['name']) \
.addfield('std_street_suffix', lambda a: a['parsed_comps']['components']['street']['suffix']) \
.addfield('std_address_postdir', lambda a: a['parsed_comps']['components']['street']['postdir']) \
.addfield('std_unit_type', lambda a: a['parsed_comps']['components']['address_unit']['unit_type']) \
.addfield('std_unit_num', lambda a: a['parsed_comps']['components']['address_unit']['unit_num']) \
.addfield('std_street_address', lambda a: a['parsed_comps']['components']['output_address']) \
.addfield('std_street_code', lambda a: a['parsed_comps']['components']['street']['street_code']) \
.addfield('std_seg_id', lambda a: a['parsed_comps']['components']['cl_seg_id']) \
.addfield('cl_addr_match', lambda a: a['parsed_comps']['components']['cl_addr_match']) \
.cutout('parsed_comps') \
.addfield('no_address',
lambda a: 1 if standardize_nulls(a['stnam']) is None or standardize_nulls(a['house']) is None else None) \
.addfield('change_stcod', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stcod'])) != str(
standardize_nulls(a['std_street_code'])) else None) \
.addfield('change_house', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['house'])) != str(
standardize_nulls(a['std_address_low'])) else None) \
.addfield('change_suf', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['suf'])) != str(
standardize_nulls(a['std_address_low_suffix'])) else None) \
.addfield('change_unit', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['unit'])) != str(
standardize_nulls(a['std_unit_num'])) else None) \
.addfield('change_stex', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stex'])) != str(
standardize_nulls(a['std_high_num'])) else None) \
.addfield('change_stdir', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdir'])) != str(
standardize_nulls(a['std_street_predir'])) else None) \
.addfield('change_stnam', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stnam'])) != str(
standardize_nulls(a['std_street_name'])) else None) \
.addfield('change_stdes', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdes'])) != str(
standardize_nulls(a['std_street_suffix'])) else None) \
.addfield('change_stdessuf',
lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdessuf'])) != str(
standardize_nulls(a['std_address_postdir'])) else 0) \
.tocsv('dor_parcel_address_analysis.csv', write_header=True)
except Exception as e:
print('Unhandled error on row: {}'.format(i))
# pprint(street)
print(traceback.format_exc())
sys.exit()
centerline_table = db['street_centerlines']
nodes_table = db['street_nodes']
print(nodes_table)
'''
WRITE
'''
print("Copying temporary street_nodes table...")
etl.fromoraclesde(con, nodes_table_name, fields=['objectid', 'streetcl_', 'node_id', 'int_id', 'intersecti'])\
.rename({'shape': 'geom'})\
.topostgis(pg_db, 'street_nodes')
print("Writing temporary centerline table...")
centerline_table.write(centerlines, chunk_size=50000)
intersections = []
error_count = 0
st_int_stmt =\
'''
with distinct_st1scns as
(
select distinct *
from
(
etl.fromoraclesde(read_dsn, source_table_name, where="SDE.ST_ISEMPTY(SHAPE)=0") \
.cut('objectid', 'mapreg', 'stcod', 'house', 'suf', 'unit', 'stex', 'stdir', 'stnam',
'stdes', 'stdessuf', 'status', 'shape') \
.addfield('concatenated_address', lambda c: concatenate_dor_address(
{'house': c['house'], 'suf': c['suf'], 'stex': c['stex'], 'stdir': c['stdir'], 'stnam': c['stnam'],
'stdes': c['stdes'], 'stdessuf': c['stdessuf'], 'unit': c['unit'], 'stcod': c['stcod']})) \
.addfield('parsed_comps', lambda p: parser.parse(p['concatenated_address'])) \
.addfield('std_address_low', lambda a: a['parsed_comps']['components']['address']['low_num']) \
.addfield('std_address_low_suffix', lambda a: a['parsed_comps']['components']['address']['addr_suffix'] if
a['parsed_comps']['components']['address']['addr_suffix'] else a['parsed_comps']['components']['address']['fractional']) \
.addfield('std_high_num', lambda a: a['parsed_comps']['components']['address']['high_num']) \
.addfield('std_street_predir', lambda a: a['parsed_comps']['components']['street']['predir']) \
.addfield('std_street_name', lambda a: a['parsed_comps']['components']['street']['name']) \
.addfield('std_street_suffix', lambda a: a['parsed_comps']['components']['street']['suffix']) \
.addfield('std_address_postdir', lambda a: a['parsed_comps']['components']['street']['postdir']) \
.addfield('std_unit_type', lambda a: a['parsed_comps']['components']['address_unit']['unit_type']) \
.addfield('std_unit_num', lambda a: a['parsed_comps']['components']['address_unit']['unit_num']) \
.addfield('std_street_address', lambda a: a['parsed_comps']['components']['output_address']) \
.addfield('std_street_code', lambda a: a['parsed_comps']['components']['street']['street_code']) \
.addfield('std_seg_id', lambda a: a['parsed_comps']['components']['cl_seg_id']) \
.addfield('cl_addr_match', lambda a: a['parsed_comps']['components']['cl_addr_match']) \
.cutout('parsed_comps') \
.addfield('no_address',
lambda a: 1 if standardize_nulls(a['stnam']) is None or standardize_nulls(a['house']) is None else None) \
.addfield('change_stcod', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stcod'])) != str(
standardize_nulls(a['std_street_code'])) else None) \
.addfield('change_house', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['house'])) != str(
standardize_nulls(a['std_address_low'])) else None) \
.addfield('change_suf', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['suf'])) != str(
standardize_nulls(a['std_address_low_suffix'])) else None) \
.addfield('change_unit', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['unit'])) != str(
standardize_nulls(a['std_unit_num'])) else None) \
.addfield('change_stex', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stex'])) != str(
standardize_nulls(a['std_high_num'])) else None) \
.addfield('change_stdir', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdir'])) != str(
standardize_nulls(a['std_street_predir'])) else None) \
.addfield('change_stnam', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stnam'])) != str(
standardize_nulls(a['std_street_name'])) else None) \
.addfield('change_stdes', lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdes'])) != str(
standardize_nulls(a['std_street_suffix'])) else None) \
.addfield('change_stdessuf',
lambda a: 1 if a['no_address'] != 1 and str(standardize_nulls(a['stdessuf'])) != str(
standardize_nulls(a['std_address_postdir'])) else 0) \
.tocsv('dor_parcel_address_analysis.csv', write_header=True)
except Exception as e:
print('Unhandled error on row: {}'.format(i))
# pprint(street)
print(traceback.format_exc())
sys.exit()
centerline_table = db['street_centerlines']
nodes_table = db['street_nodes']
print(nodes_table)
'''
WRITE
'''
print("Copying temporary street_nodes table...")
etl.fromoraclesde(con, nodes_table_name, fields=['objectid', 'streetcl_', 'node_id', 'int_id', 'intersecti'])\
.rename({'shape': 'geom'})\
.topostgis(pg_db, 'street_nodes')
print("Writing temporary centerline table...")
centerline_table.write(centerlines, chunk_size=50000)
intersections = []
error_count = 0
st_int_stmt =\
'''
with distinct_st1scns as
(
select distinct *
from
(
except Exception as e:
print('Unhandled error on row: {}'.format(i))
# pprint(street)
print(traceback.format_exc())
sys.exit()
centerline_table = db['street_centerlines']
nodes_table = db['street_nodes']
print(nodes_table)
'''
WRITE
'''
print("Copying temporary street_nodes table...")
etl.fromoraclesde(con, 'GIS_STREETS.Street_Nodes', fields=['objectid', 'node_id', 'int_id', 'intersecti'])\
.rename({'shape': 'geom'})\
.topostgis(pg_db, 'street_nodes')
print("Writing temporary centerline table...")
centerline_table.write(centerlines, chunk_size=50000)
intersections = []
error_count = 0
st_int_stmt =\
'''
with distinct_st1scns as
(
select distinct *
from
(