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 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)
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
exec_gnaf_sqlscripts('03RemoveDuplicates_GNAF_tables.sql')
#adding primary key to tables in gnaf databases
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')