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 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 data_filtering(table):
lst_polygons = []
conn_estamap = dbpy.create_conn_pyodbc(gnaf.server, em.database_name)
conn_gnaf = dbpy.create_conn_pyodbc(gnaf.server, gnaf.database_name)
cursor_estamap = conn_estamap.cursor()
cursor_gnaf = conn_gnaf.cursor()
for wkt, in cursor_estamap.execute('SELECT SHAPE FROM VICTORIA_POLYGON'):
geom = shapely.wkt.loads(wkt)
lst_polygons.append(geom)
vic_polygon = shapely.ops.cascaded_union(lst_polygons)
vic_polygon_prepared = shapely.prepared.prep(vic_polygon)
vicgrid_proj = pyproj.Proj(init='EPSG:3111')
count = 0
count_false = 0
cursor_gnaf.execute('truncate table {0}_filteredPID'.format(table))
cursor_gnaf.commit()
start_time = datetime.datetime.now()
print start_time, ' - START Data Filtering for ', table
with dbpy.SQL_BULK_COPY(gnaf.server, gnaf.database_name,
table + '_FILTEREDPID') as sbc:
for row in cursor_gnaf.execute(
'SELECT {0}_PID,LONGITUDE, LATITUDE FROM {0}'.format(table)):
##for enum, row in enumerate(cursor_gnaf.execute('select address_detail_pid, latitude, longitude from address_default_geocode')):
pid, lon, lat = row
lon_p, lat_p = vicgrid_proj(lon, lat)
point = shapely.geometry.Point(lon_p, lat_p)
if vic_polygon_prepared.intersects(point):
count += 1
sbc.add_row([pid])
if count % 10000 == 0:
sbc.flush()
else:
count_false += 1
## if enum % 100000 == 0:
## print time.ctime(), enum
## print 'inside polygon =',count,', Ouside Polygon =', count_false
end_time = datetime.datetime.now()
elapsed = end_time - start_time
##if count >=0:
## print 'Total points inside Victoria Polygon = {0} : Percent Data = {1}%'.format((count),(count*100 / (count + count_false)))
## print 'Total points outside VIC polygon = {0} : : Percent Data = {1}%'.format((count_false),(count_false*100 / (count + count_false)))
print 'Time taken for filtering {0} : {1} \n'.format(table, elapsed)
return elapsed
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 gnaf_bulk_import():
gnaf_files = {
'NSW_ADDRESS_DEFAULT_GEOCODE_psv.psv': 'ADDRESS_DEFAULT_GEOCODE',
'NSW_ADDRESS_DETAIL_psv.psv': 'ADDRESS_DETAIL',
'NSW_ADDRESS_SITE_GEOCODE_psv.psv': 'ADDRESS_SITE_GEOCODE',
'NSW_LOCALITY_POINT_psv.psv': 'LOCALITY_POINT',
'NSW_LOCALITY_psv.psv': 'LOCALITY',
'NSW_STATE_psv.psv': 'STATE',
'NSW_STREET_LOCALITY_POINT_psv.psv': 'STREET_LOCALITY_POINT',
'NSW_STREET_LOCALITY_psv.psv': 'STREET_LOCALITY',
'SA_ADDRESS_DEFAULT_GEOCODE_psv.psv': 'ADDRESS_DEFAULT_GEOCODE',
'SA_ADDRESS_DETAIL_psv.psv': 'ADDRESS_DETAIL',
'SA_ADDRESS_SITE_GEOCODE_psv.psv': 'ADDRESS_SITE_GEOCODE',
'SA_LOCALITY_POINT_psv.psv': 'LOCALITY_POINT',
'SA_LOCALITY_psv.psv': 'LOCALITY',
'SA_STATE_psv.psv': 'STATE',
'SA_STREET_LOCALITY_POINT_psv.psv': 'STREET_LOCALITY_POINT',
'SA_STREET_LOCALITY_psv.psv': 'STREET_LOCALITY'
}
import_files = [
os.path.basename(f)
for f in glob.glob(os.path.join(gnaf.path, '*.psv'))
]
#print set(gnaf_files.keys())-set(import_files)
if (set(gnaf_files.keys()) - set(import_files)):
logging.warning(('GDB files not found/extracted',
set(gnaf_files.keys()) - set(import_files)))
# logging.warning('******************************* {}'.format(import_files))
sys.exit()
for filename, table in gnaf_files.items():
importFile = os.path.join(r'M:\estamap\DEV', 'Export', 'GNAF',
filename)
with dbpy.SQL_BULK_COPY(gnaf.server, gnaf.database_name, table, True) as sbc_gnaf, \
open(importFile, 'rb') as f:
reader = csv.reader(f, delimiter='|')
header = reader.next()
for e, row in enumerate(reader):
sbc_gnaf.add_row(row)
if e % 50000 == 0:
sbc_gnaf.flush()
print '{0} Load completed'.format(filename)
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 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 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 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)
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'))
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_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 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))