def exec_gnaf_sqlscripts(sql_script, database=None):
if database is None: database = gnaf.database_name
logging.debug('create gnaf tables')
sql = os.path.join(r'M:\estamap\DEV', 'sql', 'GNAF', sql_script)
logging.debug('running sql script: {}'.format(sql))
dbpy.exec_script(gnaf.server, database, sql)
logging.info('Executed sql script: {}'.format(sql))
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 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 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)