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)
