I've set up an arcpy script that does the following things after the user selects a workspace and a CSV file through a couple Get Parameter as Text options in ArcMap:
- Creates an XY data file based on data in the CSV file
- Creates 3 different buffers on that point file
- Creates 3 different drive time trade areas on that point file
- Does a spatial append on those buffer files and the drive time files
- Pull nearby competition within the drive time areas
- Creates and exports that data to an Excel table
Here is the script.
#import arcpy and arcview library; overwrite any previous output
import arcpy
import arcview
arcpy.env.overwriteOutput = True
#set workspace to GetParameter field
myWorkspace = arcpy.GetParameterAsText(0)
arcpy.env.workspace = myWorkspace
print myWorkspace
print arcpy.env.workspace
#acquire extensions for Business Analyst and Network Analyst for use
arcpy.CheckOutExtension("BusinessPrem")
arcpy.CheckOutExtension("Network")
#set the spatial reference; 4269 is code for GCS_North_American_1983
spatialRef = arcpy.SpatialReference(4269)
#in order to use its tools, add Business Analyst toolbox to script
arcpy.AddToolbox("C:\Program Files (x86)\ArcGIS\Desktop10.3\Business Analyst\ArcToolbox\Toolboxes\Business Analyst Tools.tbx")
#create GetParameter for input csv table that has store location and lat/long
inputTable = arcpy.GetParameterAsText(1)
#assign variables for input table
xCoord = "Long"
yCoord = "Lat"
outLayer = "LocationXY_"
#create XY layer for location
arcpy.MakeXYEventLayer_management(inputTable, xCoord, yCoord, outLayer, spatialRef)
print(arcpy.GetCount_management(outLayer))
#create cursor to access csv table; acquire name of site location
cursor = arcpy.SearchCursor(outLayer)
for row in cursor:
location = row.Location
print location
#create file geodatabase for site's spatial data
folderPath = myWorkspace
geoDBname = "{0}.gdb".format(location)
print geoDBname
arcpy.CreateFileGDB_management(folderPath, geoDBname)
arcpy.env.workspace = geoDBname
print myWorkspace
print arcpy.env.workspace
#create layer name for converting XY layer to feature class data
fcLocation = outLayer + location
print fcLocation
arcpy.CopyFeatures_management(outLayer, fcLocation)
print ("FC Conv Done")
#create layer name for buffer data
#perform multi-ring buffer analysis on location
bufferString = "Buffer_"
fcBuffer = bufferString + fcLocation
arcpy.MultipleRingBuffer_analysis(fcLocation, fcBuffer, [0.5, 1, 3], "Miles",)
print ("Buff Done")
#create layer name for drive time data
#perform drive time analysis on location
driveTimeString = "DriveTime_"
fcDriveTime = driveTimeString + fcLocation
arcpy.DriveTime_ba(fcLocation, "Location", "All", "2.5; 5; 10", "Minutes", fcDriveTime)
print ("DT Done")
#Create variables for spatial overlay function
inputLayer = "C:\ArcGIS\Business Analyst\US_2015\Data\Demographic Data\esri_bg.bds"
demogFields = "INDMGMT_CY; A25I25_CY; A35I150_CY; A45I75_CY; MEDIA55_CY; \
MEDDIA35CY; A15NW250CY; AVGNWA65CY; MEDVAL_CY; POPGRWCYFY; \
N14_EMP; N17_BUS; N18_BUS; N34_EMP"
#create layer name for buffer append; append spatial data to buffers
bufferAppendString = "BufferAppend_"
fcBufferAppend = bufferAppendString + fcLocation
arcpy.SpatialOverlay_ba(inputLayer, fcBuffer, demogFields, fcBufferAppend)
print ("Buff Append Done")
#create layer name for drive time append; append spatial data to drive time
driveTimeAppendString = "DriveTimeAppend_"
fcDriveTimeAppend = driveTimeAppendString + fcLocation
arcpy.SpatialOverlay_ba(inputLayer, fcDriveTime, demogFields, fcDriveTimeAppend)
print ("DT Append Done")
#create layer name for buffer table export; export buffer data to Excel
#change workspace back to folder for Excel data
excelExtension = ".xls"
bufferAppendExcelString = "BufferAppendTable_"
bufferAppendTable = myWorkspace + "/" + bufferAppendExcelString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(fcBufferAppend, bufferAppendTable)
print ("Buffer Table Done")
#create layer name for drive time export; export drive time data to Excel
driveTimeAppendExcelString = "DriveTimeAppendTable_"
driveTimeAppendTable = myWorkspace + "/" + driveTimeAppendExcelString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(fcDriveTimeAppend, driveTimeAppendTable)
print ("Drive Time Table Done")
#create layers for summarize within function; pull number of c-stores within drive time polygon
joinSpatialConvStores = r'Y:\BI\KalibrateCompLoc\KGCompAllMarkets_Aug2016.shp'
targetFeaturePoly = fcDriveTimeAppend
businessPointsString = "CStoreCount_"
outPolyCStorePoints = businessPointsString + fcLocation
arcpy.SpatialJoin_analysis(targetFeaturePoly, joinSpatialConvStores, outPolyCStorePoints)
print ("CStore Count Done")
#create layer name for c-store count export; export c-store count to Excel
convStoreCountString = "CStoreCountTable_"
convStoreCountTable = myWorkspace + "/" + convStoreCountString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(outPolyCStorePoints, convStoreCountTable)
print ("CStore Table Done")
#return Business Analyst and Network Analyst extensions
arcpy.CheckInExtension("BusinessPrem")
arcpy.CheckInExtension("Network")
print ("FINISHED")
The point of this script is to run a site through ESRI in order to get all of the key demographic and competition data for a potential location in order to run through a regression model that we use to predict success.
At this point in time, we can only run a CSV file that has one row (location) through the Python tool. What I want to do is update the script to be iterative and be able to run multiple rows (locations) through the tool at once. Any thoughts on easy changes to make this possible? I've made a few attempts at this and have encountered quite a few errors. I can upload that script if people would like to see what I've tried.
