def register_new_road_routeno(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results)) sql_stmt = ''' select distinct route_no from dbo.ROAD where route_no is not null order by route_no ''' new_routenos = [] for enum, (route_no, ) in enumerate(cursor.execute(sql_stmt), 1): # parse route_no road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_route_name( route_no) if not em.check_roadname(road_name=road_name_parsed, road_type=road_type_parsed, road_suffix=road_suffix_parsed): new_routenos.append( (road_name_parsed, road_type_parsed, road_suffix_parsed)) em.create_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag) logging.info('Num New Road Names: {}'.format(len(new_routenos))) results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results))
def calc_road_infrastructure_rnid(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() sql_stmt = ''' select UFI, NAME from ROAD_INFRASTRUCTURE where NAME is not null and NAME <> 'TEMP' order by NAME ''' with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_INFRASTRUCTURE_RNID') as sbc: for enum, (ufi, name) in enumerate(cursor.execute(sql_stmt)): # parse road name road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( name, '-', '', 0) # check parsed roadname roadname = em.check_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed) sbc.add_row((ufi, roadname.ROAD_NAME_ID)) if enum % 10000 == 0: logging.info(enum) sbc.flush() logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def calc_address_gnaf_rnid(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() sql_stmt = ''' select ADDRESS_DETAIL_PID, STREET_NAME, STREET_TYPE_CODE, STREET_SUFFIX_CODE from ADDRESS_GNAF ''' with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ADDRESS_GNAF_RNID') as sbc: logging.info('looping address_gnaf') route_flag = 0 for enum, (pid, road_name, road_type, road_suffix) in enumerate(cursor.execute(sql_stmt)): road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( road_name, road_type, road_suffix, route_flag) # check parsed roadname roadname = em.check_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed) sbc.add_row((pid, roadname.ROAD_NAME_ID)) if enum % 10000 == 0: logging.info(enum) sbc.flush() logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def register_new_roadinfrastructure_name(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results)) sql_stmt = ''' select distinct NAME from dbo.ROAD_INFRASTRUCTURE where NAME is not null and NAME <> 'TEMP' order by NAME ''' new_names = [] for enum, (name, ) in enumerate(cursor.execute(sql_stmt), 1): # parse road name road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( name, '-', '', 0) if not em.check_roadname(road_name=road_name_parsed, road_type=road_type_parsed, road_suffix=road_suffix_parsed): new_names.append( (road_name_parsed, road_type_parsed, road_suffix_parsed)) em.create_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag) logging.info('Num New Road Names: {}'.format(len(new_names))) results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results))
def create_road_bearing_table(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) sql_script = os.path.join(em.path, 'sql', 'transport', 'create_road_bearing.sql') logging.info('running sql script: {}'.format(sql_script)) dbpy.exec_script(em.server, em.database_name, sql_script)
def import_road_patch(estamap_version): if estamap_version == 'CORE': raise Exception('use another estamap version') logging.info('environment') em_core = gis.ESTAMAP('CORE') em = gis.ESTAMAP(estamap_version) if arcpy.Exists(os.path.join(em.sde, 'ROAD_PATCH')): arcpy.Delete_management(os.path.join(em.sde, 'ROAD_PATCH')) logging.info('importing ROAD_PATCH: {}'.format(os.path.join(em_core.sde, 'ROAD_PATCH'))) arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em_core.sde, 'ROAD_PATCH'), out_path=em.sde, out_name='ROAD_PATCH') road_patch_count = arcpy.GetCount_management(os.path.join(em.sde, 'ROAD_PATCH')).getOutput(0) logging.info('ROAD_PATCH count: {}'.format(road_patch_count))
def create_road_infrastructure_detail_table(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) sql_script = os.path.join(em.path, 'sql', 'detail_tables', 'create_road_infrastructure_detail.sql') logging.info('running sql script: {}'.format(sql_script)) dbpy.exec_script(em.server, em.database_name, sql_script)
def calc_address_roadinfra(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) conn = dbpy.create_conn_pyodbc(em.server, em.database_name) logging.info('dropping tables:') if dbpy.check_exists('ADDRESS_ROADINFRA', conn): logging.info('ADDRESS_ROADINFRA') conn.execute('drop table ADDRESS_ROADINFRA') logging.info('creating ADDRESS_ROADINFRA') conn.execute(''' CREATE TABLE [dbo].[ADDRESS_ROADINFRA]( [PFI] [nvarchar](10) NOT NULL, [UFI] [int] NULL ) ON [PRIMARY] ''') conn.commit() logging.info('reading ADDRESS FIDs') address_fids = {} with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ADDRESS'), field_names=['PFI', 'OBJECTID']) as sc: for pfi, oid in sc: address_fids[oid] = pfi logging.info('reading ROAD_INFRASTRUCTURE FIDs') roadinfra_fids = {} with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI', 'OBJECTID']) as sc: for ufi, oid in sc: roadinfra_fids[oid] = ufi logging.info('Generate Near Table analysis...') arcpy.GenerateNearTable_analysis( in_features=os.path.join(em.sde, 'ADDRESS'), near_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), out_table='in_memory\\near_table') logging.info('inserting') with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'ADDRESS_ROADINFRA') as sbc, \ arcpy.da.SearchCursor(in_table='in_memory\\near_table', field_names=['IN_FID', 'NEAR_FID']) as sc: for enum, (address_fid, roadinfra_fid) in enumerate(sc): sbc.add_row( (address_fids[address_fid], roadinfra_fids[roadinfra_fid])) if enum % 100000 == 0: logging.info(enum) sbc.flush() logging.info(enum)
def calc_locality_detail(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'LOCALITY'), field_names=['PFI', 'NAME', 'SHAPE@', 'SHAPE@AREA', 'SHAPE@LENGTH']) as sc, \ arcpy.da.InsertCursor(in_table=os.path.join(em.sde, 'LOCALITY_DETAIL'), field_names=['PFI', 'RING_COUNT', 'SEGMENT_COUNT', 'AREA_SIZE', 'PERIMETER_SIZE', 'SOUNDEX']) as ic: for row in sc: pfi, name, geom, area_size, perimeter_size = row ring_count = geom.boundary().partCount segment_count = geom.boundary().pointCount - ring_count soundex = gis.generate_soundex(name) ic.insertRow((pfi, ring_count, segment_count, area_size, perimeter_size, soundex))
def setup_locality(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) arcpy.MakeFeatureLayer_management(in_features=os.path.join( em.sde, 'LOCALITY'), out_layer='in_memory\\locality_layer') arcpy.SelectLayerByAttribute_management( in_layer_or_view='in_memory\\locality_layer', selection_type='NEW_SELECTION', where_clause="NAME = 'VIC'") if int( arcpy.GetCount_management('in_memory\\locality_layer').getOutput( 0)) == 1: logging.info('removing VIC polygon') arcpy.DeleteFeatures_management('in_memory\\locality_layer')
def register_new_road_roadname(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results)) sql_stmt = ''' select distinct road_name as ROAD_NAME, road_type as ROAD_TYPE, road_suffix as ROAD_SUFFIX from dbo.ROAD where road_name is not null union select distinct road_name_1 as ROAD_NAME, road_type_1 as ROAD_TYPE, road_suffix_1 as ROAD_SUFFIX from dbo.ROAD where road_name_1 is not null union select distinct road_name_2 as ROAD_NAME, road_type_2 as ROAD_TYPE, road_suffix_2 as ROAD_SUFFIX from dbo.ROAD where road_name_2 is not null union select distinct road_name_3 as ROAD_NAME, road_type_3 as ROAD_TYPE, road_suffix_3 as ROAD_SUFFIX from dbo.ROAD where road_name_3 is not null union select distinct road_name_4 as ROAD_NAME, road_type_4 as ROAD_TYPE, road_suffix_4 as ROAD_SUFFIX from dbo.ROAD where road_name_4 is not null union select distinct road_name_5 as ROAD_NAME, road_type_5 as ROAD_TYPE, road_suffix_5 as ROAD_SUFFIX from dbo.ROAD where road_name_5 is not null union select distinct road_name_6 as ROAD_NAME, road_type_6 as ROAD_TYPE, road_suffix_6 as ROAD_SUFFIX from dbo.ROAD where road_name_6 is not null union select distinct road_name_7 as ROAD_NAME, road_type_7 as ROAD_TYPE, road_suffix_7 as ROAD_SUFFIX from dbo.ROAD where road_name_7 is not null order by ROAD_NAME''' new_roadnames = [] route_flag = 0 for enum, (road_name, road_type, road_suffix) in enumerate(cursor.execute(sql_stmt), 1): # parse roadname road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( road_name, road_type, road_suffix, route_flag=route_flag) if not em.check_roadname(road_name=road_name_parsed, road_type=road_type_parsed, road_suffix=road_suffix_parsed): new_roadnames.append( (road_name_parsed, road_type_parsed, road_suffix_parsed)) em.create_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag) logging.info('Num New Road Names: {}'.format(len(new_roadnames))) results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results))
def register_new_locality(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() new_localities = [] for row in cursor.execute(''' SELECT 'SDRN' as [VER_FCODE], NAME as [VER_TOWN_NAME], STATE as [VER_STATE], [LOCALITY_DETAIL].[SOUNDEX] as [SOUNDEX], [LOCALITY].[PFI] as [SOURCE_PK] FROM LOCALITY LEFT JOIN LOCALITY_DETAIL ON LOCALITY.PFI = LOCALITY_DETAIL.PFI WHERE LOCALITY.NAME not in (SELECT VER_TOWN_NAME from LOCALITY_MSLINK_REGISTER) '''): fcode, name, state, sdx, pk, = row dataset = 'VICMAP_' + em.vicmap_version new_localities.append((fcode, name, state, sdx, dataset, pk)) logging.info('new locality: {}'.format(name)) if len(new_localities) > 0: for new_locality in new_localities: cursor.execute( ''' INSERT INTO [dbo].[LOCALITY_MSLINK_REGISTER] ([VER_FCODE] ,[VER_TOWN_NAME] ,[VER_STATE] ,[VER_TOWN_SDX] ,[SOURCE_DATASET] ,[SOURCE_PK]) VALUES (?, ?, ?, ?, ?, ?) ''', new_locality) logging.info('inserting new locality successful: {}'.format( cursor.rowcount)) cursor.commit()
def calc_road_detail(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() logging.info('calc geom attr') with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'SHAPE@'], sql_clause=(None, 'ORDER BY PFI')) as sc, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_DETAIL') as sbc: for enum, (pfi, geom) in enumerate(sc): sbc.add_row((pfi, geom.length, geom.pointCount - 1)) if enum % 10000 == 0: logging.info(enum) logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count start: {}'.format(sbc.count_finish))
def register_new_roadinfrastructure(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() sql = ''' INSERT INTO ROAD_INFRASTRUCTURE_MSLINK_REGISTER (UFI, SOURCE_DATASET, SOURCE_PK) SELECT DISTINCT UFI, 'TR_ROAD_INFRA.{vm}' AS SOURCE_DATASET, UFI AS SOURCE_PK FROM ROAD_INFRASTRUCTURE WHERE UFI NOT IN (SELECT UFI FROM DBO.ROAD_INFRASTRUCTURE_MSLINK_REGISTER) ORDER BY ROAD_INFRASTRUCTURE.UFI '''.format(vm=em.vicmap_version) results = cursor.execute(sql) num_inserted = results.rowcount logging.info('Road Infrastructure registered: {}'.format(num_inserted))
def import_vicmap_data(estamap_version, vicmap_version): vm = gis.VICMAP(vicmap_version) em = gis.ESTAMAP(estamap_version) arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(3111) import_features = [] for vm_fc, em_fc in import_list.iteritems(): ## if arcpy.Exists(os.path.join(em.sde, em_fc)): ## print '4' ## vm_count = arcpy.GetCount_management(os.path.join(vm.sde, vm_fc)).getOutput(0) ## em_count = arcpy.GetCount_management(os.path.join(em.sde, em_fc)).getOutput(0) ## if vm_count == em_count: ## continue ## arcpy.Delete_management(os.path.join(em.sde, em_fc)) logging.info('importing: {}'.format(os.path.join(em.sde, em_fc))) arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join( vm.sde, vm_fc), out_path=em.sde, out_name=em_fc)
def register_new_addressgnaf_roadname(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results)) sql_stmt = ''' select distinct STREET_NAME, STREET_TYPE_CODE, STREET_SUFFIX_CODE from ADDRESS_GNAF ''' new_roadnames = [] route_flag = 0 for enum, (road_name, road_type, road_suffix) in enumerate(cursor.execute(sql_stmt), 1): # parse roadname road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( road_name, road_type, road_suffix, route_flag=route_flag) if not em.check_roadname(road_name=road_name_parsed, road_type=road_type_parsed, road_suffix=road_suffix_parsed): new_roadnames.append( (road_name_parsed, road_type_parsed, road_suffix_parsed)) em.create_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag) logging.info('Num New Road Names: {}'.format(len(new_roadnames))) results = cursor.execute( 'select count(*) from ROAD_NAME_REGISTER').fetchval() logging.info('total ROAD_NAME_REGISTER: {}'.format(results))
def import_transport_disconnected(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) if arcpy.Exists(os.path.join(em.path, 'Routing', 'TransportSpatialValidation_withPatch.gdb', 'ROAD_disconnected_ALL')): logging.info('importing ROAD_disconnected_ALL as ROAD_DISCONNECTED') if arcpy.Exists(os.path.join(em.sde, 'ROAD_DISCONNECTED')): arcpy.Delete_management(os.path.join(em.sde, 'ROAD_DISCONNECTED')) arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.path, 'Routing', 'TransportSpatialValidation_withPatch.gdb', 'ROAD_disconnected_ALL'), out_path=em.sde, out_name='ROAD_DISCONNECTED') road_disconnected_count = arcpy.GetCount_management(os.path.join(em.sde, 'ROAD_DISCONNECTED')).getOutput(0) logging.info('ROAD disconnected count: {}'.format(road_disconnected_count)) if arcpy.Exists(os.path.join(em.path, 'Routing', 'TransportSpatialValidation_withPatch.gdb', 'ROAD_INFRASTRUCTURE_disconnected_ALL')): logging.info('importing ROAD_INFRASTRUCTURE_disconnected_ALL as ROAD_INFRASTRUCTURE_DISCONNECTED') if arcpy.Exists(os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_DISCONNECTED')): arcpy.Delete_management(os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_DISCONNECTED')) arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.path, 'Routing', 'TransportSpatialValidation_withPatch.gdb', 'ROAD_INFRASTRUCTURE_disconnected_ALL'), out_path=em.sde, out_name='ROAD_INFRASTRUCTURE_DISCONNECTED') road_infrastructure_disconnected_count = arcpy.GetCount_management(os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_DISCONNECTED')).getOutput(0) logging.info('ROAD_INFRASTRUCTURE disconnected count: {}'.format(road_infrastructure_disconnected_count))
def calc_road_alias(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() sql_stmt = ''' select PFI, ROAD_NAME, ROAD_TYPE, ROAD_SUFFIX, ROAD_NAME_1, ROAD_TYPE_1, ROAD_SUFFIX_1, ROAD_NAME_2, ROAD_TYPE_2, ROAD_SUFFIX_2, ROAD_NAME_3, ROAD_TYPE_3, ROAD_SUFFIX_3, ROAD_NAME_4, ROAD_TYPE_4, ROAD_SUFFIX_4, ROAD_NAME_5, ROAD_TYPE_5, ROAD_SUFFIX_5, ROAD_NAME_6, ROAD_TYPE_6, ROAD_SUFFIX_6, ROAD_NAME_7, ROAD_TYPE_7, ROAD_SUFFIX_7, ROUTE_NO, STRUCTURE_NAME from ROAD ''' with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_ALIAS') as sbc: for enum, row in enumerate(cursor.execute(sql_stmt)): pfi, road_name_0, road_type_0, road_suffix_0, \ road_name_1, road_type_1, road_suffix_1, \ road_name_2, road_type_2, road_suffix_2, \ road_name_3, road_type_3, road_suffix_3, \ road_name_4, road_type_4, road_suffix_4, \ road_name_5, road_type_5, road_suffix_5, \ road_name_6, road_type_6, road_suffix_6, \ road_name_7, road_type_7, road_suffix_7, \ route_no, structure_name = row rns = [ (road_name_0, road_type_0, road_suffix_0, 0), (road_name_1, road_type_1, road_suffix_1, 0), (road_name_2, road_type_2, road_suffix_2, 0), (road_name_3, road_type_3, road_suffix_3, 0), (road_name_4, road_type_4, road_suffix_4, 0), (road_name_5, road_type_5, road_suffix_5, 0), (road_name_6, road_type_6, road_suffix_6, 0), (road_name_7, road_type_7, road_suffix_7, 0), (route_no, '-', '', 1), (structure_name, '-', '', 0), ] pfi_unique = set() for alias_num, (road_name, road_type, road_suffix, route_flag) in enumerate(rns): if not road_name: continue # parse road_name if route_flag == 1: road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_route_name( road_name) else: road_name_parsed, road_type_parsed, road_suffix_parsed, route_flag = em.parse_roadname( road_name, road_type, road_suffix, route_flag) # check parsed roadname roadname = em.check_roadname(road_name_parsed, road_type_parsed, road_suffix_parsed) if (pfi, roadname.ROAD_NAME_ID) not in pfi_unique: pfi_unique.add((pfi, roadname.ROAD_NAME_ID)) sbc.add_row( (pfi, roadname.ROAD_NAME_ID, alias_num, route_flag)) if enum % 10000 == 0: logging.info(enum) sbc.flush() logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def create_locality_centroid(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(3111) ingr_sr = gis.ingr_spatial_reference() ingr_uor_sr = gis.ingr_uor_spatial_reference() if arcpy.Exists(os.path.join(em.sde, 'LOCALITY_CENTROID')): logging.info('deleting existing locality centroid fc') arcpy.Delete_management(os.path.join(em.sde, 'LOCALITY_CENTROID')) logging.info('creating locality centroid fc') arcpy.CreateFeatureclass_management(out_path='in_memory', out_name='locality_centroid_temp', geometry_type='POINT') arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='PFI', field_type='LONG') # vicgrid coords arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='X_VICGRID', field_type='DOUBLE', field_precision=12, field_scale=3) arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='Y_VICGRID', field_type='DOUBLE', field_precision=12, field_scale=3) # ingr coords arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='X_INGR', field_type='DOUBLE', field_precision=12, field_scale=3) arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='Y_INGR', field_type='DOUBLE', field_precision=12, field_scale=3) # ingr uor coords arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='X_INGR_UOR', field_type='DOUBLE', field_precision=12, field_scale=3) arcpy.AddField_management(in_table='in_memory\\locality_centroid_temp', field_name='Y_INGR_UOR', field_type='DOUBLE', field_precision=12, field_scale=3) arcpy.FeatureToPoint_management( in_features=os.path.join(em.sde, 'LOCALITY'), out_feature_class='in_memory\\locality_centroid') logging.info('calc coordinates...') with arcpy.da.SearchCursor(in_table='in_memory\\locality_centroid', field_names=['PFI', 'SHAPE@']) as sc, \ arcpy.da.InsertCursor(in_table='in_memory\\locality_centroid_temp', field_names=['PFI', 'SHAPE@', 'X_VICGRID', 'Y_VICGRID', 'X_INGR', 'Y_INGR', 'X_INGR_UOR', 'Y_INGR_UOR', ]) as ic: for enum, (pfi, geom) in enumerate(sc): if enum % 100 == 0: logging.info(enum) geom_ingr = geom.projectAs(ingr_sr) geom_ingr_uor = geom.projectAs(ingr_uor_sr) ic.insertRow((pfi, geom, geom.centroid.X, geom.centroid.Y, geom_ingr.centroid.X, geom_ingr.centroid.Y, geom_ingr_uor.centroid.X, geom_ingr_uor.centroid.Y)) logging.info(enum) logging.info('exporting...') arcpy.FeatureClassToFeatureClass_conversion( in_features='in_memory\\locality_centroid_temp', out_path=em.sde, out_name='LOCALITY_CENTROID')
def calc_road_turn(estamap_version, temp_lmdb='C:\\temp\\turnangles_lmdb'): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() logging.info('create lmdb db') if os.path.exists(temp_lmdb): shutil.rmtree(temp_lmdb) env = lmdb.Environment(path=temp_lmdb, map_size=1000000000, readonly=False, max_dbs=4) road_bearings_db = env.open_db('road_bearings', dupsort=True) road_pfis_db = env.open_db('road_pfis', dupsort=True) roads_at_ufi_db = env.open_db('roads_at_ufi', dupsort=True) road_infrastructure_db = env.open_db('road_infrastructure') logging.info('read bearings') with env.begin(write=True, db=road_bearings_db) as road_bearings_txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_BEARING'), field_names=['PFI', 'ENTRY_BEARING', 'EXIT_BEARING', 'ENTRY_BEARING_FLIP', 'EXIT_BEARING_FLIP']) as sc: for enum, (pfi, entry_bear, exit_bear, entry_bear_flip, exit_bear_flip) in enumerate(sc, 1): pfi = str(pfi) road_bearings_txn.put(pfi + 'ENTRY', '{:.5f}'.format(entry_bear)) road_bearings_txn.put(pfi + 'EXIT', '{:.5f}'.format(exit_bear)) road_bearings_txn.put(pfi + 'ENTRY_FLIP', '{:.5f}'.format(entry_bear_flip)) road_bearings_txn.put(pfi + 'EXIT_FLIP', '{:.5f}'.format(exit_bear_flip)) if enum % 10000 == 0: logging.info(enum) logging.info('read roads') with env.begin(write=True, db=road_pfis_db) as road_pfis_txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'FROM_UFI', 'TO_UFI']) as sc: for enum, (pfi, from_ufi, to_ufi) in enumerate(sc, 1): road_pfis_txn.put(str(pfi), str(from_ufi) + ',' + str(to_ufi)) if enum % 10000 == 0: logging.info(enum) logging.info('read road_infrastructure') with env.begin(write=True, db=road_infrastructure_db) as road_infrastructure_txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI', 'FEATURE_TYPE_CODE', 'CONPFI1', 'CONPFI2']) as sc: for enum, (ufi, ftc, conpfi1, conpfi2) in enumerate(sc, 1): road_infrastructure_txn.put( str(ufi), str(ftc) + ',' + str(conpfi1) + ',' + str(conpfi2)) if enum % 10000 == 0: logging.info(enum) logging.info('find roads at ufi') with env.begin(db=road_pfis_db) as road_pfis_txn, \ env.begin(write=True, db=roads_at_ufi_db) as roads_at_ufi_txn: cursor_road = road_pfis_txn.cursor() for enum_pfi, (pfi, ufis) in enumerate(cursor_road.iternext(), 1): from_ufi, to_ufi = ufis.split(',') roads_at_ufi_txn.put(from_ufi, pfi) roads_at_ufi_txn.put(to_ufi, pfi) logging.info('iterating permutations and insert') with env.begin(db=roads_at_ufi_db) as roads_at_ufi_txn, \ env.begin(db=road_bearings_db) as road_bearings_txn, \ env.begin(db=road_pfis_db) as road_pfis_txn, \ env.begin(db=road_infrastructure_db) as road_infrastructure_txn, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_TURN') as sbc: cursor_keys = roads_at_ufi_txn.cursor() cursor_values = roads_at_ufi_txn.cursor() road_infrastructure_cursor = road_infrastructure_txn.cursor() pfis_at_ufi = set() for enum_ufi, ufi in enumerate(cursor_keys.iternext_nodup(), 1): try: ufi_ftc, ufi_conpfi1, ufi_conpfi2 = road_infrastructure_cursor.get( ufi).split(',') except: print ' ', ufi continue ## if ufi == '51580977': ## print road_infrastructure_cursor.get(ufi) ## print ufi_conpfi1, ufi_conpfi2 cursor_values.set_key(ufi) pfis_at_ufi.clear() for pfi in cursor_values.iternext_dup(): pfis_at_ufi.add(pfi) # compute turn angles only if more than one unique road segment at ufi if len(pfis_at_ufi) > 1: for from_pfi, to_pfi in itertools.permutations(pfis_at_ufi, 2): # check if valid turn ## if ufi == '51580977': ## print from_pfi, to_pfi if ufi_ftc.lower() == 'tunnel': if from_pfi in (ufi_conpfi1, ufi_conpfi2): if to_pfi not in (ufi_conpfi1, ufi_conpfi2): continue if to_pfi in (ufi_conpfi1, ufi_conpfi2): if from_pfi not in (ufi_conpfi1, ufi_conpfi2): continue # check if current ufi is same as from_pfi's from_ufi if ufi == road_pfis_txn.get(from_pfi).split(',')[0]: from_bearing = float( road_bearings_txn.get(from_pfi + 'ENTRY')) else: from_bearing = float( road_bearings_txn.get(from_pfi + 'ENTRY_FLIP')) # check if current ufi is same as to_pfi's from_ufi if ufi == road_pfis_txn.get(to_pfi).split(',')[0]: to_bearing = float( road_bearings_txn.get(to_pfi + 'ENTRY')) else: to_bearing = float( road_bearings_txn.get(to_pfi + 'ENTRY_FLIP')) angle = from_bearing - to_bearing if angle < -180: angle = angle + 360 elif angle > 180: angle = angle - 360 sbc.add_row((ufi, from_pfi, to_pfi, angle, from_bearing, to_bearing)) if enum_ufi % 10000 == 0: logging.info(enum_ufi) sbc.flush() logging.info(enum_ufi) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def calc_locality_nodeid(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) arcpy.env.outputCoordinateSystem = arcpy.SpatialReference(3111) cursor = em.conn.cursor() logging.info('creating in_memory feature class') if arcpy.Exists(os.path.join(em.sde, 'LOCALITY_NODEID')): arcpy.Delete_management(os.path.join(em.sde, 'LOCALITY_NODEID')) arcpy.CreateFeatureclass_management(out_path='in_memory', out_name='locality_nodeid_temp', geometry_type='POINT') arcpy.AddField_management(in_table='in_memory\\locality_nodeid_temp', field_name='PFI', field_type='LONG') arcpy.AddField_management(in_table='in_memory\\locality_nodeid_temp', field_name='ROAD_INFRA_UFI', field_type='LONG') logging.info('loading geoms into memory') road_infra_xys = {} ## for enum, (ufi, x, y) in enumerate(cursor.execute('select ufi, shape.STX, shape.STY from ROAD_INFRASTRUCTURE')): ## if enum % 100000 == 0: ## logging.info(enum) ## road_infra_xys[ufi] = (x, y) with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI', 'SHAPE@X', 'SHAPE@Y'], sql_clause=(None, 'ORDER BY UFI')) as sc_infra: for enum, (ufi, x, y) in enumerate(sc_infra, 1): road_infra_xys[ufi] = (x, y) logging.info(enum) logging.info('num loaded: {}'.format(len(road_infra_xys))) logging.info('build rtree') ## def stream_load_infra(): ## with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), ## field_names=['UFI', 'SHAPE@X', 'SHAPE@Y'], ## sql_clause=(None, 'ORDER BY UFI')) as sc_infra: ## for enum, (ufi, x, y) in enumerate(sc_infra, 1): ## yield (ufi, (x,y,x,y), None) ## if enum % 100000 == 0: ## logging.info(enum) ## logging.info(enum) def stream_load_infra(): for ufi, (x, y) in road_infra_xys.iteritems(): yield (ufi, (x, y, x, y), None) idx_infra = rtree.index.Index(stream_load_infra()) logging.info('stream end') logging.info('looping locality') sql = ''' select locality.pfi, locality.name, locality.shape, locality_centroid.shape from locality left join locality_centroid on locality.pfi = locality_centroid.pfi order by locality.name ''' with arcpy.da.InsertCursor(in_table='in_memory\\locality_nodeid_temp', field_names=['PFI', 'ROAD_INFRA_UFI', 'SHAPE@']) as ic: for pfi, name, loc_poly, loc_cent in cursor.execute(sql): geom_poly = shapely.prepared.prep(shapely.wkt.loads(loc_poly)) geom_cent = shapely.wkt.loads(loc_cent) road_infra_in_scope = idx_infra.intersection( geom_poly.context.bounds) road_infra_in_locality = [] for ufi in road_infra_in_scope: road_infra_geom = shapely.geometry.Point(*road_infra_xys[ufi]) road_infra_geom.ufi = ufi if geom_poly.intersects(road_infra_geom): road_infra_in_locality.append(road_infra_geom) if len(road_infra_in_locality) > 0: _, nearest_point = shapely.ops.nearest_points( geom_cent, shapely.geometry.MultiPoint(road_infra_in_locality)) ic.insertRow((pfi, road_infra_in_locality[ road_infra_in_locality.index(nearest_point)].ufi, arcpy.Point(nearest_point.x, nearest_point.y))) if ufi == -1: logging.info('no ufi: ' + name) logging.info('exporting...') arcpy.FeatureClassToFeatureClass_conversion( in_features='in_memory\\locality_nodeid_temp', out_path=em.sde, out_name='LOCALITY_NODEID')
def calc_road_xstreet(estamap_version, temp_lmdb='c:\\temp\\road_xstreet', temp_traversal_lmdb='c:\\temp\\road_xstreet_traversal'): logging.info('environment') em = gis.ESTAMAP('DEV') logging.info('create temp fgdb for ROAD_XSTREET_VALIDATION') if arcpy.Exists(os.path.join(r'c:\temp\road_xstreet_validation.gdb')): arcpy.Delete_management(r'c:\temp\road_xstreet_validation.gdb') arcpy.CreateFileGDB_management(out_folder_path=r'c:\temp', out_name='road_xstreet_validation.gdb') arcpy.CreateFeatureclass_management( out_path=r'c:\temp\road_xstreet_validation.gdb', out_name='ROAD_XSTREET_VALIDATION', geometry_type='POLYLINE', spatial_reference=arcpy.SpatialReference(3111)) arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), field_name='PFI', field_type='LONG') arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), field_name='NODE_TYPE', field_type='TEXT', field_length=4) arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), field_name='TRAVERSAL_DIST', field_type='FLOAT', field_precision=12, field_scale=3) logging.info('create temp fgdb for ROAD_XSTREET_ROAD') if arcpy.Exists(os.path.join(r'c:\temp\road_xstreet_road.gdb')): arcpy.Delete_management(r'c:\temp\road_xstreet_road.gdb') arcpy.CreateFileGDB_management(out_folder_path=r'c:\temp', out_name='road_xstreet_road.gdb') arcpy.CreateFeatureclass_management( out_path=r'c:\temp\road_xstreet_road.gdb', out_name='ROAD_XSTREET_ROAD', geometry_type='POLYLINE', spatial_reference=arcpy.SpatialReference(3111)) arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), field_name='PFI', field_type='LONG') arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), field_name='NODE_TYPE', field_type='TEXT', field_length=4) arcpy.AddField_management(in_table=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), field_name='XSTREET_PFI', field_type='LONG') logging.info('creating temp lmdb: {}'.format(temp_lmdb)) if os.path.exists(temp_lmdb): shutil.rmtree(temp_lmdb) env = lmdb.Environment(path=temp_lmdb, map_size=1500000000, readonly=False, max_dbs=10) road_db = env.open_db('road', dupsort=True) road_geom_db = env.open_db('road_geom') road_bearing_db = env.open_db('road_bearing', dupsort=True) road_turn_db = env.open_db('road_turn', dupsort=True) road_alias_db = env.open_db('road_alias', dupsort=True) road_infrastructure_db = env.open_db('road_infrastructure', dupsort=True) logging.info('read ROAD') with env.begin(write=True, db=road_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'FROM_UFI', 'TO_UFI', 'FEATURE_TYPE_CODE']) as sc: for enum, row in enumerate(sc): txn.put(str(row[0]), ','.join([str(_) for _ in row[1:]])) if enum % 100000 == 0: logging.info(enum) logging.info(enum) logging.info('read ROAD geom') with env.begin(write=True, db=road_geom_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'SHAPE@WKB']) as sc: for enum, (pfi, wkb) in enumerate(sc): txn.put(str(pfi), str(wkb)) if enum % 100000 == 0: logging.info(enum) logging.info(enum) logging.info('read ROAD_BEARING') with env.begin(write=True, db=road_bearing_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_BEARING'), field_names=['PFI', 'ENTRY_BEARING', 'EXIT_BEARING', 'ENTRY_BEARING_FLIP', 'EXIT_BEARING_FLIP']) as sc: for enum, (pfi, entry_bear, exit_bear, entry_bear_flip, exit_bear_flip) in enumerate(sc): pfi = str(pfi) txn.put(pfi + 'ENTRY', '{:.5f}'.format(entry_bear)) txn.put(pfi + 'EXIT', '{:.5f}'.format(exit_bear)) txn.put(pfi + 'ENTRY_FLIP', '{:.5f}'.format(entry_bear_flip)) txn.put(pfi + 'EXIT_FLIP', '{:.5f}'.format(exit_bear_flip)) if enum % 100000 == 0: logging.info(enum) logging.info(enum) logging.info('read ROAD_TURN') with env.begin(write=True, db=road_turn_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_TURN'), field_names=['UFI', 'FROM_PFI', 'TO_PFI', 'ANGLE', 'FROM_BEARING', 'TO_BEARING']) as sc: for enum, (ufi, from_pfi, to_pfi, angle, from_bearing, to_bearing) in enumerate(sc): txn.put( str(ufi), ','.join([ str(o) for o in (from_pfi, to_pfi, angle, from_bearing, to_bearing) ])) if enum % 100000 == 0: logging.info(enum) logging.info(enum) logging.info('read ROAD_ALIAS') with env.begin(write=True, db=road_alias_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_ALIAS'), field_names=['PFI', 'ROAD_NAME_ID', 'ALIAS_NUMBER']) as sc: for enum, (pfi, rnid, alias_num) in enumerate(sc): txn.put(str(pfi), str(rnid) + ',' + str(alias_num)) if enum % 100000 == 0: logging.info(enum) logging.info(enum) logging.info('read ROAD_INFRASTRUCTURE') with env.begin(write=True, db=road_infrastructure_db) as txn, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI', 'FEATURE_TYPE_CODE']) as sc: for enum, (ufi, ftc) in enumerate(sc): txn.put(str(ufi), str(ftc)) if enum % 100000 == 0: logging.info(enum) logging.info(enum) ############## logging.info('preparation') with env.begin(db=road_db) as road_txn, \ env.begin(db=road_turn_db) as road_turn_txn, \ env.begin(db=road_alias_db) as road_alias_txn, \ env.begin(db=road_infrastructure_db) as road_infrastructure_txn, \ env.begin(db=road_geom_db) as road_geom_txn, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_XSTREET') as sbc_xstreet, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_XSTREET_TRAVERSAL') as sbc_xstreet_traversal: road_cursor = road_txn.cursor() road_geom_cursor = road_geom_txn.cursor() road_cursor_iter = road_txn.cursor() road_turn_cursor = road_turn_txn.cursor() road_alias_cursor = road_alias_txn.cursor() road_infrastructure_cursor = road_infrastructure_txn.cursor() road_infrastructure_cursor_iter = road_infrastructure_txn.cursor() # convienience functions get_road_nodes_cursor = road_txn.cursor() def get_road_nodes(pfi): return get_road_nodes_cursor.get(pfi).split(',')[:-1] get_road_rnids_cursor = road_alias_txn.cursor() def get_road_rnids(pfi): rnids = [] get_road_rnids_cursor.set_key(pfi) for values in get_road_rnids_cursor.iternext_dup(): rnid, alias_num = values.split(',') rnids.append((rnid, alias_num)) return sorted(rnids, key=lambda x: x[-1]) get_road_ftc_cursor = road_txn.cursor() def get_road_ftc(pfi): return get_road_ftc_cursor.get(pfi).split(',')[-1] get_connecting_pfis_rt_cursor = road_turn_txn.cursor() get_connecting_pfis_ri_cursor = road_infrastructure_txn.cursor() def get_connecting_pfis(ufi, pfi): connecting_pfis = [] get_connecting_pfis_rt_cursor.set_key(ufi) for values in get_connecting_pfis_rt_cursor.iternext_dup(): from_pfi, to_pfi, angle, from_bearing, to_bearing = values.split( ',') if from_pfi == pfi: connecting_pfis.append([to_pfi, angle]) return sorted(connecting_pfis, key=lambda x: abs(float(x[-1]))) get_road_altnode_cursor = road_txn.cursor() def get_road_altnode(pfi, current_node): from_ufi, to_ufi, pfi_ftc = get_road_altnode_cursor.get(pfi).split( ',') if current_node == from_ufi: return to_ufi else: return from_ufi def get_traversal(pfi, ufi): traversal_pfis = get_connecting_pfis(ufi, pfi) traversal_pfis_sort_180 = sorted( traversal_pfis, key=lambda x: abs(180 - abs(float(x[-1])))) if len(traversal_pfis) == 0: # no roads connecting return 'ROAD_END', None, None else: # determine best traversal pfi_rnid = get_road_rnids(pfi)[0][0] # 1. road has SAME_RNID and PFI is not UNNAMED if pfi_rnid <> '1312': for con_pfi, con_angle in traversal_pfis_sort_180: con_pfi_rnids = get_road_rnids(con_pfi) if pfi_rnid in [rnid for rnid, an in con_pfi_rnids]: traversal_desc = 'SAME_RNID' traversal_pfi = con_pfi traversal_ufi = get_road_altnode(con_pfi, ufi) return 'SAME_RNID', con_pfi, get_road_altnode( con_pfi, ufi) # 2. road angle closest to 180 degrees ## traversal_pfis_sort_180 = sorted(traversal_pfis, key=lambda x: abs(180 - abs(float(x[-1])))) traversal_pfi = traversal_pfis_sort_180[0][0] return 'CLOSE_TO_180', traversal_pfi, get_road_altnode( traversal_pfi, ufi) def process_node(pfi, ufi, node_type): pfi_rnid = get_road_rnids(pfi)[0][0] # get PFI RNID (primary rnid) pfi_from_ufi, pfi_to_ufi = get_road_nodes(pfi) traversal = [] xstreet = [] traversal_order = 0 traversal_desc = 'BEGIN' traversal_pfi = pfi if node_type == 'FROM': traversal_ufi = pfi_from_ufi else: traversal_ufi = pfi_to_ufi xstreet_pfi = None xstreet_rnid = None while True: # get connecting PFI at FROM_UFI from_ufi_pfis = get_connecting_pfis(traversal_ufi, traversal_pfi) traversal.append([ pfi, node_type, traversal_order, traversal_pfi, traversal_ufi, len(from_ufi_pfis), traversal_desc ]) if len(from_ufi_pfis) == 0: traversal_desc = 'ROAD_END' break # determine if suitable XSTREET for from_ufi_pfi, from_ufi_angle in from_ufi_pfis: from_ufi_pfi_rnids = get_road_rnids(from_ufi_pfi) from_ufi_pfi_ftc = get_road_ftc(from_ufi_pfi) from_ufi_pfi_rnids_only = [ rnid for rnid, an in from_ufi_pfi_rnids ] if '1312' in from_ufi_pfi_rnids_only: # road is UNNAMED continue if pfi_rnid in from_ufi_pfi_rnids_only: # road has same RNID continue if from_ufi_pfi_ftc == 'TUNNEL': # road type is a TUNNEL continue xstreet_pfi = from_ufi_pfi xstreet_rnid = from_ufi_pfi_rnids[0][0] traversal_desc = 'XSTREET' break if traversal_desc == 'XSTREET': traversal.append([ pfi, node_type, traversal_order, traversal_pfi, traversal_ufi, len(from_ufi_pfis), traversal_desc ]) break # determine next suitable traversal if XSTREET not found traversal_desc, traversal_pfi, traversal_ufi = get_traversal( traversal_pfi, traversal_ufi) # add loop check here if traversal_order > 50: # exit if traversal too long traversal_desc = 'MORE_THAN_50' break traversal_order = traversal_order + 1 ## traversal.append([pfi, node_type, traversal_order, traversal_pfi, traversal_ufi, len(from_ufi_pfis), traversal_desc]) return xstreet_pfi, xstreet_rnid, traversal with arcpy.da.InsertCursor(in_table=os.path.join(r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), field_names=['PFI', 'NODE_TYPE', 'TRAVERSAL_DIST', 'SHAPE@WKB']) as ic_valid, \ arcpy.da.InsertCursor(in_table=os.path.join(r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), field_names=['PFI', 'NODE_TYPE', 'XSTREET_PFI', 'SHAPE@WKB']) as ic_road: logging.info('looping roads') for enum_road, pfi in enumerate( road_cursor.iternext(keys=True, values=False)): # get PFI RNID (primary rnid) pfi_rnid = get_road_rnids(pfi)[0][0] pfi_from_ufi, pfi_to_ufi = get_road_nodes(pfi) from_xstreet_pfi, from_xstreet_rnid, from_traversal = process_node( pfi, pfi_from_ufi, 'FROM') to_xstreet_pfi, to_xstreet_rnid, to_traversal = process_node( pfi, pfi_to_ufi, 'TO') # # insert FROM traversal # for f_traversal in from_traversal: sbc_xstreet_traversal.add_row(f_traversal) from_geoms = [] for f_traversal in from_traversal: from_geoms.append( shapely.wkb.loads(road_geom_cursor.get( f_traversal[3]))) from_merged_line = shapely.ops.linemerge(from_geoms) # measure actual traversal distance (subtract base road length) from_traversal_dist = from_merged_line.length - shapely.wkb.loads( road_geom_cursor.get(pfi)).length if from_xstreet_pfi: # (subtract xstreet road length) ## from_traversal_dist = from_traversal_dist - shapely.wkb.loads(road_geom_cursor.get(from_xstreet_pfi)).length # add the xstreet geom from_xstreet_geom = shapely.wkb.loads( road_geom_cursor.get(from_xstreet_pfi)) from_geoms.append(from_xstreet_geom) # insert into ROAD_XSTREET_ROAD ic_road.insertRow([ pfi, 'FROM', from_xstreet_pfi, shapely.wkb.loads( road_geom_cursor.get(from_xstreet_pfi)).wkb ]) from_merged_line_final = shapely.ops.linemerge(from_geoms) ic_valid.insertRow([ pfi, 'FROM', from_traversal_dist, from_merged_line_final.wkb ]) ## # # insert TO traversal # for t_traversal in to_traversal: sbc_xstreet_traversal.add_row(t_traversal) to_geoms = [] for t_traversal in to_traversal: to_geoms.append( shapely.wkb.loads(road_geom_cursor.get( t_traversal[3]))) to_merged_line = shapely.ops.linemerge(to_geoms) # measure actual traversal distance (subtract base road) to_traversal_dist = to_merged_line.length - shapely.wkb.loads( road_geom_cursor.get(pfi)).length if to_xstreet_pfi: # (subtract xstreet road length) ## to_traversal_dist = to_traversal_dist - shapely.wkb.loads(road_geom_cursor.get(to_xstreet_pfi)).length # add the xstreet geom to_xstreet_geom = shapely.wkb.loads( road_geom_cursor.get(to_xstreet_pfi)) to_geoms.append(to_xstreet_geom) # insert into ROAD_XSTREET_ROAD ic_road.insertRow([ pfi, 'TO', to_xstreet_pfi, shapely.wkb.loads( road_geom_cursor.get(to_xstreet_pfi)).wkb ]) to_merged_line_final = shapely.ops.linemerge(to_geoms) ic_valid.insertRow( [pfi, 'TO', to_traversal_dist, to_merged_line_final.wkb]) ## sbc_xstreet.add_row([ pfi, pfi_rnid, pfi_from_ufi, from_xstreet_rnid, from_xstreet_pfi, pfi_to_ufi, to_xstreet_rnid, to_xstreet_pfi ]) if enum_road % 10000 == 0: logging.info(enum_road) sbc_xstreet.flush() sbc_xstreet_traversal.flush() logging.info(enum_road) logging.info('indexes') arcpy.AddIndex_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), fields='PFI', index_name='PFI', ascending=True) arcpy.AddIndex_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), fields='NODE_TYPE', index_name='NODE') arcpy.AddIndex_management(in_table=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION'), fields='TRAVERSAL_DIST', index_name='DIST') arcpy.AddIndex_management(in_table=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), fields='PFI', index_name='PFI', ascending=True) arcpy.AddIndex_management(in_table=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'), fields='XSTREET_PFI', index_name='XPFI', ascending=True) logging.info('spatial indexes') arcpy.RemoveSpatialIndex_management(in_features=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION')) arcpy.AddSpatialIndex_management(in_features=os.path.join( r'c:\temp\road_xstreet_validation.gdb', 'ROAD_XSTREET_VALIDATION')) arcpy.RemoveSpatialIndex_management(in_features=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD')) arcpy.AddSpatialIndex_management(in_features=os.path.join( r'c:\temp\road_xstreet_road.gdb', 'ROAD_XSTREET_ROAD'))
sys.argv.append('--gnaf_version=201805') sys.argv.append('--estamap_version=19' ) ##ENSURE ESTAMAP DATABASE HAS GOT VICTORIA POLYGON TABLE with log.LogConsole(level='WARNING'): logging.debug('parsing args') args = docopt(__doc__) logging.debug('variables') gnaf_version = args['--gnaf_version'] estamap_version = args['--estamap_version'] log_file = args['--log_file'] log_path = args['--log_path'] gnaf = gis.GNAF(gnaf_version) em = gis.ESTAMAP(estamap_version) conn_estamap = dbpy.create_conn_pyodbc(em.server, em.database_name) cursor_estamap = conn_estamap.cursor() with log.LogFile(log_file, log_path): logging.debug('start') try: # cursor_estamap.execute(r"""IF OBJECT_ID('dbo.ADDRESS_GNAF', 'U') IS NOT NULL DROP TABLE dbo.ADDRESS_GNAF""" ) # cursor_estamap.commit() # drop and recreate gnaf tables exec_gnaf_sqlscripts('01Create_GNAF_tables.sql') #bulk load of gnaf datasets into gnaf database gnaf_bulk_import() print 'Bulk Load of GNAF tables completed' # Filter for duplicate rows on the loaded GNAF tables
def create_EMsde_db(estamap_version, auth_file): logging.info('checking python interpreter is 32 bit') if sys.maxsize > 2**32: raise Exception('Please use Python 32 bit.') logging.info('local variables') em = gis.ESTAMAP(estamap_version) logging.info('creating SDE geodatabase: ' + em.database_name) arcpy.CreateEnterpriseGeodatabase_management( database_platform="SQL_Server", instance_name="TDB03", database_name=em.database_name, account_authentication="OPERATING_SYSTEM_AUTH", database_admin="#", database_admin_password="******", sde_schema="DBO_SCHEMA", gdb_admin_name="#", gdb_admin_password="******", tablespace_name="#", authorization_file=auth_file) logging.info('create database connection') conn = arcpy.ArcSDESQLExecute('TDB03', 'sde:sqlserver:TDB03') logging.info('grant permissions to users') sql = ''' USE [{db}]; CREATE USER [ESTA\ArcGISSOC] FOR LOGIN [ESTA\ArcGISSOC]; ALTER ROLE [db_datareader] ADD MEMBER [ESTA\ArcGISSOC]; CREATE USER [ESTA\ESTAMIS] FOR LOGIN [ESTA\ESTAMIS]; ALTER ROLE [db_datareader] ADD MEMBER [ESTA\ESTAMIS]; '''.format(db=em.database_name) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('get server property info') logical_name_db = conn.execute( '''select name from sys.master_files where database_id = db_id('{db}') and type_desc = 'ROWS' ''' .format(db=em.database_name)) logical_name_log = conn.execute( '''select name from sys.master_files where database_id = db_id('{db}') and type_desc = 'LOG' ''' .format(db=em.database_name)) size_db = conn.execute( '''select (size*8)/1024 as size from sys.master_files where database_id = db_id('{db}') and type_desc = 'ROWS' ''' .format(db=em.database_name)) if size_db != 15360: logging.info('alter database size: 15gb') sql = ''' COMMIT; ALTER DATABASE [{db}] MODIFY FILE ( NAME = '{name_db}', SIZE = 30GB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ); BEGIN TRANSACTION; '''.format(db=em.database_name, name_db=logical_name_db, name_log=logical_name_log) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('alter database recovery') sql = ''' COMMIT; ALTER DATABASE [{db}] SET RECOVERY SIMPLE WITH NO_WAIT; BEGIN TRANSACTION; '''.format(db=em.database_name) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('creating SDE connection file: ' + em.sde) if not os.path.exists(em.sde): arcpy.CreateArcSDEConnectionFile_management( out_folder_path=os.path.split(em.sde)[0], out_name=os.path.split(em.sde)[1], server='TDB03', service='sde:sqlserver:TDB03', database=em.database_name, account_authentication="OPERATING_SYSTEM_AUTH", version="dbo.DEFAULT", save_version_info="SAVE_VERSION", )
def calc_road_bearing(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) cursor = em.conn.cursor() logging.info('get coords') pfis = [] x_entry = [] y_entry = [] x_exit = [] y_exit = [] with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'SHAPE@WKB'], spatial_reference=gis.ingr_spatial_reference(), sql_clause=(None, 'ORDER BY PFI')) as sc: for enum, (pfi, geom_wkb) in enumerate(sc, 1): geom = shapely.wkb.loads(str(geom_wkb)) first_point = geom.coords[0] entry_point = geom.interpolate(2.5) dx_entry = entry_point.x - first_point[0] dy_entry = entry_point.y - first_point[1] last_point = geom.coords[-1] exit_point = geom.interpolate(geom.length - 2.5) dx_exit = last_point[0] - exit_point.x dy_exit = last_point[1] - exit_point.y pfis.append(pfi) x_entry.append(dx_entry) y_entry.append(dy_entry) x_exit.append(dx_exit) y_exit.append(dy_exit) if enum % 10000 == 0: logging.info('{}'.format(enum)) logging.info('{}'.format(enum)) logging.info('calc bearings and insert') with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_BEARING') as sbc: for enum, (pfi, entry_angle, exit_angle) in enumerate(zip(pfis, np.arctan2(np.array(y_entry), np.array(x_entry)) * 180 / np.pi, np.arctan2(np.array(y_exit), np.array(x_exit)) * 180 / np.pi)): ## if entry_angle < 0: ## entry_bearing = (90 - entry_angle) % 360 ## else: ## entry_bearing = (450 - entry_angle) % 360 ## if exit_angle < 0: ## exit_bearing = (90 - exit_angle) % 360 ## else: ## exit_bearing = (450 - exit_angle) % 360 ## if entry_angle < 0: ## entry_bearing = int((90 - entry_angle) % 360) ## else: ## entry_bearing = int((450 - entry_angle) % 360) ## if exit_angle < 0: ## exit_bearing = int((90 - exit_angle) % 360) ## else: ## exit_bearing = int((450 - exit_angle) % 360) if entry_angle < 0: entry_bearing = float((90 - entry_angle) % 360) else: entry_bearing = float((450 - entry_angle) % 360) if exit_angle < 0: exit_bearing = float((90 - exit_angle) % 360) else: exit_bearing = float((450 - exit_angle) % 360) sbc.add_row((pfi, entry_bearing, exit_bearing, (exit_bearing + 180) % 360, (entry_bearing + 180) % 360)) if enum % 10000 == 0: logging.info(enum) sbc.flush() logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def calc_address_detail(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) ingr_sr = gis.ingr_spatial_reference() ingr_uor_sr = gis.ingr_uor_spatial_reference() logging.info('reading locality geoms') locality_geoms = {} with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'LOCALITY'), field_names=['PFI', 'NAME', 'SHAPE@WKB']) as sc: for pfi, locality_name, wkb in sc: locality_geoms[pfi] = (locality_name, shapely.prepared.prep(shapely.wkb.loads(str(wkb)))) logging.info(len(locality_geoms)) logging.info('building locality rtree') def stream_load_locality(): for pfi, (locality_name, geom) in locality_geoms.iteritems(): yield (pfi, geom.context.bounds, None) locality_rtree = rtree.index.Index(stream_load_locality()) logging.info('reading lga geoms') lga_geoms = {} with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'LGA'), field_names=['PFI', 'NAME', 'SHAPE@WKB']) as sc: for pfi, lga_name, wkb in sc: lga_geoms[pfi] = (lga_name, shapely.prepared.prep(shapely.wkb.loads(str(wkb)))) logging.info(len(lga_geoms)) logging.info('building lga rtree') def stream_load_lga(): for pfi, (lga_name, geom) in lga_geoms.iteritems(): yield (pfi, geom.context.bounds, None) lga_rtree = rtree.index.Index(stream_load_lga()) logging.info('looping ADDRESS...') with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ADDRESS'), field_names=['PFI', 'SHAPE@X', 'SHAPE@Y'], sql_clause=(None, 'ORDER BY PFI')) as sc, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ADDRESS'), field_names=['PFI', 'SHAPE@X', 'SHAPE@Y'], spatial_reference=ingr_sr, sql_clause=(None, 'ORDER BY PFI')) as sc_ingr, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ADDRESS_DETAIL') as sbc: total_area = shapely.geometry.Point(0,0).buffer(2.5).area for enum, (row_vg, row_ingr) in enumerate(itertools.izip(sc, sc_ingr)): addr_pfi, x_vicgrid, y_vicgrid = row_vg _, x_ingr, y_ingr = row_ingr x_ingr_uor = x_ingr * 100.0 y_ingr_uor = y_ingr * 100.0 geom = shapely.geometry.Point(x_vicgrid, y_vicgrid) # locality localities = [] locality_in_scope = locality_rtree.intersection(geom.bounds) for pfi in locality_in_scope: if locality_geoms[pfi][1].contains(geom): localities.append(pfi) locality_name = 'UNKNOWN' locality_percent = 0.0 if len(localities) == 1: locality_name = locality_geoms[localities[0]][0] locality_percent = 100.0 elif len(localities) > 1: logging.info('within 2 localities: {}'.format(addr_pfi)) localities_ranked = [] # determine largest locality by area if contained within multiple for pfi in localities: locality_name, locality_geom = locality_geoms[pfi] geom_buffer = geom.buffer(2.5) area_geom = locality_geom.context.intersection(geom_buffer) locality_percent = area_geom.area / total_area localities_ranked.append((pfi, locality_name, locality_geom, locality_percent)) locality_name, locality_percent = max(localities_ranked, key=lambda x: x[-1]) # lga lgas = [] lga_in_scope = lga_rtree.intersection(geom.bounds) for pfi in lga_in_scope: lga_name, lga_geom = lga_geoms[pfi] if lga_geom.contains(geom): lgas.append(lga_name) lga_name = 'UNKNOWN' if len(lgas) == 1: lga_name = lgas[0] elif len(lgas) > 1: lga_name = sorted(lgas)[0] # self intersections # todo, check dependency if required intersect_count = 0 sbc.add_row((addr_pfi, locality_name, locality_percent, x_vicgrid, y_vicgrid, x_ingr, y_ingr, x_ingr_uor, y_ingr_uor, lga_name, intersect_count)) if enum % 1000 == 0: logging.info(enum) if enum % 100000 == 0: sbc.flush() logging.info(enum) logging.info('count start: {}'.format(sbc.count_start)) logging.info('count finish: {}'.format(sbc.count_finish))
def transport_spatial_validation(estamap_version, with_patch=False): logging.info('environment') em = gis.ESTAMAP(estamap_version) arcpy.env.XYTolerance = '0.01 Meters' fgdb_name = 'TransportSpatialValidation_{}.gdb'.format('withPatch' if with_patch else 'exclPatch') fgdb = os.path.join(em.path, 'Routing', fgdb_name) if not os.path.exists(os.path.join(em.path, 'Routing')): logging.info('creating Routing folder: {}'.format(os.path.join(em.path, 'Routing'))) os.makedirs(os.path.join(em.path, 'Routing')) logging.info('creating fgdb: {}'.format(os.path.join(em.path, 'Routing', fgdb_name))) if arcpy.Exists(os.path.join(em.path, 'Routing', fgdb_name)): arcpy.Delete_management(os.path.join(em.path, 'Routing', fgdb_name)) arcpy.CreateFileGDB_management(out_folder_path=os.path.join(em.path, 'Routing'), out_name=fgdb_name) logging.info('creating feature dataset') arcpy.CreateFeatureDataset_management(out_dataset_path=os.path.join(em.path, 'Routing', fgdb_name), out_name='FD', spatial_reference=arcpy.SpatialReference(3111)) logging.info('importing ROAD') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.sde, 'ROAD'), out_path=os.path.join(em.path, 'Routing', fgdb_name, 'FD'), out_name='ROAD') road_count = arcpy.GetCount_management(os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'ROAD')).getOutput(0) logging.info('ROAD records: {}'.format(road_count)) logging.info('importing ROAD_INFRASTRUCTURE') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), out_path=os.path.join(em.path, 'Routing', fgdb_name, 'FD'), out_name='ROAD_INFRASTRUCTURE') road_infrastructure_count = arcpy.GetCount_management(os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'ROAD_INFRASTRUCTURE')).getOutput(0) logging.info('ROAD_INFRASTRUCTURE records: {}'.format(road_infrastructure_count)) in_source_feature_classes = 'ROAD SIMPLE_EDGE NO;ROAD_INFRASTRUCTURE SIMPLE_JUNCTION NO' if with_patch: in_source_feature_classes = in_source_feature_classes + ';ROAD_PATCH SIMPLE_EDGE NO' logging.info('importing ROAD_PATCH') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.sde, 'ROAD_PATCH'), out_path=os.path.join(em.path, 'Routing', fgdb_name, 'FD'), out_name='ROAD_PATCH', where_clause='"MERGE_TRANSPORT" = 1') road_patch_count = arcpy.GetCount_management(os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'ROAD_PATCH')).getOutput(0) logging.info('ROAD_PATCH records: {}'.format(road_patch_count)) ## logging.info('importing ROAD_INFRASTRUCTURE_PATCH') ## arcpy.MakeFeatureLayer_management(in_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_PATCH'), ## out_layer='in_memory\\road_infrastructure_patch_layer', ## where_clause='"MERGE_TRANSPORT" = 1') ## arcpy.FeatureClassToFeatureClass_conversion(in_features='in_memory\\road_infrastructure_patch_layer', ## out_path=os.path.join(em.path, 'Routing', fgdb_name, 'FD'), ## out_name='ROAD_INFRASTRUCTURE_PATCH') logging.info('creating geometric network: {}'.format(os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'geonet'))) arcpy.CreateGeometricNetwork_management(in_feature_dataset=os.path.join(em.path, 'Routing', fgdb_name, 'FD'), out_name='geonet', in_source_feature_classes=in_source_feature_classes, preserve_enabled_values='PRESERVE_ENABLED') ## arcpy.AddEdgeEdgeConnectivityRuleToGeometricNetwork_management(in_geometric_network=os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'geonet'), ## in_from_edge_feature_class="ROAD", ## from_edge_subtype="ROAD", ## in_to_edge_feature_class="ROAD", ## to_edge_subtype="ROAD", ## in_junction_subtypes="'ROAD_INFRASTRUCTURE : ROAD_INFRASTRUCTURE'", ## default_junction_subtype="ROAD_INFRASTRUCTURE : ROAD_INFRASTRUCTURE") ## ## arcpy.AddEdgeJunctionConnectivityRuleToGeometricNetwork_management(in_geometric_network=os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'geonet'), ## in_edge_feature_class="ROAD", ## edge_subtype="ROAD", ## in_junction_feature_class="ROAD_INFRASTRUCTURE", ## junction_subtype="ROAD_INFRASTRUCTURE", ## default_junction="DEFAULT", ## edge_min="", ## edge_max="", ## junction_min="", ## junction_max="") ## ## # DC by: find dc function ## ## logging.info('finding disconnected: ROAD') ## arcpy.FindDisconnectedFeaturesInGeometricNetwork_management(in_layer=os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'ROAD'), ## out_layer='in_memory\\ROAD_disconnected') ## if arcpy.Exists('in_memory\\ROAD_disconnected'): ## fdc_road_count = arcpy.GetCount_management('in_memory\\ROAD_disconnected').getOutput(0) ## logging.info('found ROAD disconnected: {}'.format(fdc_road_count)) ## ## arcpy.FeatureClassToFeatureClass_conversion(in_features='in_memory\\ROAD_disconnected', ## out_path=os.path.join(em.path, 'Routing', fgdb_name), ## out_name='ROAD_disconnected') ## ## logging.info('finding disconnected: ROAD_INFRASTRUCTURE') ## arcpy.FindDisconnectedFeaturesInGeometricNetwork_management(in_layer=os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'ROAD_INFRASTRUCTURE'), ## out_layer='in_memory\\ROAD_INFRASTRUCTURE_disconnected') ## if arcpy.Exists('in_memory\\ROAD_INFRASTRUCTURE_disconnected'): ## fdc_road_infrastructure_count = arcpy.GetCount_management('in_memory\\ROAD_INFRASTRUCTURE_disconnected').getOutput(0) ## logging.info('found ROAD_INFRASTRUCTURE disconnected: {}'.format(fdc_road_infrastructure_count)) ## ## arcpy.FeatureClassToFeatureClass_conversion(in_features='in_memory\\ROAD_INFRASTRUCTURE_disconnected', ## out_path=os.path.join(em.path, 'Routing', fgdb_name), ## out_name='ROAD_INFRASTRUCTURE_disconnected') # DC by: tracing logging.info('creating starting point') arcpy.CreateFeatureclass_management(out_path=os.path.join(em.path, 'Routing', fgdb_name), out_name='StartingPoint', geometry_type='POINT', spatial_reference=arcpy.SpatialReference(3111)) with arcpy.da.InsertCursor(in_table=os.path.join(em.path, 'Routing', fgdb_name, 'StartingPoint'), field_names=['SHAPE@']) as ic: pt = arcpy.Point(2497133.064, 2409284.931) ic.insertRow((pt,)) logging.info('Tracing Geometric Network...') arcpy.TraceGeometricNetwork_management(in_geometric_network=os.path.join(em.path, 'Routing', fgdb_name, 'FD', 'geonet'), out_network_layer='in_memory\\geonet_trace_output', in_flags=os.path.join(em.path, 'Routing', fgdb_name, 'StartingPoint'), in_trace_task_type='FIND_DISCONNECTED', in_trace_ends='NO_TRACE_ENDS', in_trace_indeterminate_flow='NO_TRACE_INDETERMINATE_FLOW', in_junction_weight_range_not='AS_IS', in_edge_weight_range_not='AS_IS') logging.info('Trace Complete. Checking counts and export.') group_layer = arcpy.mapping.Layer('in_memory\\geonet_trace_output') for layer in arcpy.mapping.ListLayers(group_layer): if not layer.isGroupLayer: layer_count = arcpy.GetCount_management(layer).getOutput(0) if int(layer_count) > 0: logging.info('found disconnected in layer: {}'.format(layer.name)) logging.info('count disconnected: {}'.format(layer_count)) arcpy.FeatureClassToFeatureClass_conversion(in_features=layer, out_path=os.path.join(em.path, 'Routing', fgdb_name), out_name=layer.name + '_disconnected_ALL') if arcpy.Exists(os.path.join(em.path, 'Routing', fgdb_name, 'ROAD_disconnected_ALL')): logging.info('Export ROAD disconnected UNNAMED') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.path, 'Routing', fgdb_name, 'ROAD_disconnected_ALL'), out_path=os.path.join(em.path, 'Routing', fgdb_name), out_name='ROAD_disconnected_UNNAMED', where_clause="ROAD_NAME = 'UNNAMED'") logging.info('Export ROAD disconnected NAMED Victoria') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.path, 'Routing', fgdb_name, 'ROAD_disconnected_ALL'), out_path=os.path.join(em.path, 'Routing', fgdb_name), out_name='ROAD_disconnected_NAMED_VIC', where_clause='''ROAD_NAME <> 'UNNAMED' and ( (LEFT_LOCALITY not like '%(NSW)%' or RIGHT_LOCALITY not like '%(NSW)%') AND (LEFT_LOCALITY not like '%(SA)%' or RIGHT_LOCALITY not like '%(SA)%') ) ''') logging.info('Export ROAD disconnected NAMED Interstate') arcpy.FeatureClassToFeatureClass_conversion(in_features=os.path.join(em.path, 'Routing', fgdb_name, 'ROAD_disconnected_ALL'), out_path=os.path.join(em.path, 'Routing', fgdb_name), out_name='ROAD_disconnected_NAMED_INTERSTATE', where_clause='''ROAD_NAME <> 'UNNAMED' and ( (LEFT_LOCALITY like '%(NSW)%' or RIGHT_LOCALITY like '%(NSW)%') OR (LEFT_LOCALITY like '%(SA)%' or RIGHT_LOCALITY like '%(SA)%') ) ''') logging.info('clean up trace group layer') arcpy.Delete_management('in_memory\\geonet_trace_output')
def export_transport_validated(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) conn = dbpy.create_conn_pyodbc(em.server, em.database_name) if arcpy.Exists(os.path.join(em.sde, 'ROAD_VALIDATED')): logging.info('deleting existing ROAD_VALIDATED') arcpy.Delete_management(os.path.join(em.sde, 'ROAD_VALIDATED')) if arcpy.Exists(os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_VALIDATED')): logging.info('deleting existing ROAD_INFRASTRUCTURE_VALIDATED') arcpy.Delete_management(os.path.join(em.sde, 'ROAD_INFRASTRUCTURE_VALIDATED')) logging.info('make feature layer: ROAD') arcpy.MakeFeatureLayer_management(in_features=os.path.join(em.sde, 'ROAD'), out_layer='in_memory\\road_layer', where_clause="PFI not in (SELECT pfi from ROAD_DISCONNECTED)") logging.info('field mapping') fms = arcpy.FieldMappings() keep_fields = ['PFI', 'CLASS_CODE', 'FEATURE_TYPE_CODE', 'FROM_UFI', 'TO_UFI', 'LEFT_LOCALITY', 'RIGHT_LOCALITY', ] for field in arcpy.ListFields(dataset='in_memory\\road_layer'): ## print field.name, field.type if field.name in keep_fields: fm = arcpy.FieldMap() fm.addInputField('in_memory\\road_layer', field.name) fms.addFieldMap(fm) logging.info('exporting ROAD_VALIDATED') arcpy.FeatureClassToFeatureClass_conversion(in_features='in_memory\\road_layer', out_path=em.sde, out_name='ROAD_VALIDATED', field_mapping=fms) logging.info('make feature layer: ROAD_INFRASTRUCTURE') ## arcpy.MakeFeatureLayer_management(in_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), ## out_layer='in_memory\\road_infra_layer', ## where_clause="""UFI in ( ## SELECT R.FROM_UFI AS UFI FROM ROAD R INNER JOIN ROAD_VALIDATED RV ON R.PFI = RV.PFI ## UNION ## SELECT R.TO_UFI AS UFI FROM ROAD R INNER JOIN ROAD_VALIDATED RV ON R.PFI = RV.PFI ## ) ## """) arcpy.MakeFeatureLayer_management(in_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), out_layer='in_memory\\road_infra_layer', where_clause="UFI not in (SELECT UFI FROM ROAD_INFRASTRUCTURE_DISCONNECTED)") logging.info('exporting ROAD_INFRASTRUCTURE_VALIDATED') arcpy.FeatureClassToFeatureClass_conversion(in_features='in_memory\\road_infra_layer', out_path=em.sde, out_name='ROAD_INFRASTRUCTURE_VALIDATED')
def calc_address_gnaf_roadinfra(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) conn = dbpy.create_conn_pyodbc(em.server, em.database_name) logging.info('dropping tables:') if dbpy.check_exists('ADDRESS_GNAF_ROADINFRA', conn): logging.info('ADDRESS_GNAF_ROADINFRA') conn.execute('drop table ADDRESS_GNAF_ROADINFRA') logging.info('creating ADDRESS_GNAF_ROADINFRA') conn.execute(''' CREATE TABLE [dbo].[ADDRESS_GNAF_ROADINFRA]( [PFI] [nvarchar](15) NOT NULL, [UFI] [int] NULL ) ON [PRIMARY] ''') conn.commit() ## logging.info('reading ADDRESS_GNAF FIDs') ## address_fids = {} ## with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ADDRESS_GNAF'), ## field_names=['PFI', 'OBJECTID']) as sc: ## for pfi, oid in sc: ## address_fids[oid] = pfi ## ## logging.info('reading ROAD_INFRASTRUCTURE FIDs') ## roadinfra_fids = {} ## with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), ## field_names=['UFI', 'OBJECTID']) as sc: ## for ufi, oid in sc: ## roadinfra_fids[oid] = ufi ## ## logging.info('Generate Near Table analysis...') ## arcpy.GenerateNearTable_analysis(in_features=os.path.join(em.sde, 'ADDRESS'), ## near_features=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), ## out_table='in_memory\\near_table') ## ## logging.info('inserting') ## with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'ADDRESS_ROADINFRA') as sbc, \ ## arcpy.da.SearchCursor(in_table='in_memory\\near_table', ## field_names=['IN_FID', 'NEAR_FID']) as sc: ## for enum, (address_fid, roadinfra_fid) in enumerate(sc): ## sbc.add_row((address_fids[address_fid], roadinfra_fids[roadinfra_fid])) ## if enum % 100000 == 0: ## logging.info(enum) ## sbc.flush() ## logging.info(enum) logging.info('creating rtree ROAD_INFRASTRUCTURE') def bulk_load_roadinfra(): with arcpy.da.SearchCursor(in_table=os.path.join( em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI', 'SHAPE@X', 'SHAPE@Y']) as sc: for enum, (ufi, x, y) in enumerate(sc): if enum % 100000 == 0: logging.info(enum) yield (int(ufi), (x, y, x, y), None) logging.info(enum) ri_rtree = rtree.Rtree(bulk_load_roadinfra()) logging.info('looping ADDRESS') with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'ADDRESS_GNAF_ROADINFRA') as sbc, \ arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ADDRESS_GNAF'), field_names=['ADDRESS_DETAIL_PID', 'SHAPE@X', 'SHAPE@Y']) as sc: for enum, (pfi, x, y) in enumerate(sc): ufi = list(ri_rtree.nearest((x, y, x, y)))[0] sbc.add_row((pfi, ufi)) if enum % 100000 == 0: logging.info(enum) sbc.flush() logging.info(enum)
def transport_aspatial_validation(estamap_version): logging.info('environment') em = gis.ESTAMAP(estamap_version) logging.info('finding starting UFI') cursor = em.conn.cursor() starting_node = cursor.execute("select TOP 1 UFI from ROAD_INFRASTRUCTURE order by shape.STDistance(geometry::STGeomFromText('POINT (2497133.064 2409284.931)', 3111))").fetchval() #2313747 logging.info('starting UFI: {}'.format(starting_node)) logging.info('create table: ROAD_VALIDATION_DISCONNECTED') sql_script = os.path.join(em.path, 'sql', 'transport', 'create_road_validation_disconnected.sql') logging.info('running sql script: {}'.format(sql_script)) dbpy.exec_script(em.server, em.database_name, sql_script) logging.info('create table: ROAD_VALIDATION_NETWORKED') sql_script = os.path.join(em.path, 'sql', 'transport', 'create_road_validation_networked.sql') logging.info('running sql script: {}'.format(sql_script)) dbpy.exec_script(em.server, em.database_name, sql_script) logging.info('creating graph') graph = nx.Graph() logging.info('loading nodes') with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_INFRASTRUCTURE'), field_names=['UFI',]) as sc: for enum, ufi in enumerate(sc): graph.add_node(ufi) if enum % 10000 == 0: logging.info(enum) logging.info(enum) logging.info('loading edges') with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD'), field_names=['PFI', 'FROM_UFI', 'TO_UFI']) as sc: for enum, (pfi, from_ufi, to_ufi) in enumerate(sc): graph.add_edge(int(from_ufi), int(to_ufi)) # add PFI to the edge graph[int(from_ufi)][int(to_ufi)][int(pfi)] = True if enum % 10000 == 0: logging.info(enum) logging.info(enum) logging.info('appending patch') with arcpy.da.SearchCursor(in_table=os.path.join(em.sde, 'ROAD_PATCH'), field_names=['FROM_UFI', 'TO_UFI'], where_clause='MERGE_TRANSPORT = 1') as sc: for enum, (from_ufi, to_ufi) in enumerate(sc): graph.add_edge(int(from_ufi), int(to_ufi)) # add PFI as -1 to the edge graph[int(from_ufi)][int(to_ufi)][-1] = True logging.info(enum) logging.info('finding connected') connected_nodes = nx.node_connected_component(graph, starting_node) graph_connected = graph.subgraph(connected_nodes) logging.info('finding disconnected') graph_disconnected = graph.fresh_copy() graph_disconnected.remove_nodes_from(connected_nodes) with dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_VALIDATION_NETWORKED') as sbc_networked, \ dbpy.SQL_BULK_COPY(em.server, em.database_name, 'dbo.ROAD_VALIDATION_DISCONNECTED') as sbc_disconnected: logging.info('looping network components...') edges_loaded = 0 for enum_component, subnodes in enumerate(nx.connected_components(graph)): subgraph = graph.subgraph(subnodes) if starting_node in subgraph: # CONNECTED to starting node for u, v, d in subgraph.edges(data=True): for enum, pfi in enumerate(d.keys()): sbc_networked.add_row((pfi,)) edges_loaded = edges_loaded + 1 if edges_loaded % 10000 == 0: sbc_networked.flush() logging.info(edges_loaded) else: # DISCONNECTED to starting node for u, v, d in subgraph.edges(data=True): for pfi in d.keys(): sbc_disconnected.add_row((pfi,)) edges_loaded = edges_loaded + 1 if edges_loaded % 10000 == 0: sbc_disconnected.flush() logging.info(edges_loaded) logging.info(edges_loaded)