This script uses ESRI ArcMap spatial queries to look at the highest advertised download speed and number of providers (for mobile and fixed wireless technologies separately) within each Census designated community area polygon for Utah. Data is generated in a .csv table for a single NTIA broadband submission period. Running the script against tw…

SBI Community Broadband Overview.py

import arcpy
import csv
from arcpy import da

arcpy.env.overwriteOutput = True

bbRound= '8'
bbGeodatabaseLocation = r'Database Connections\ubbadmin@ubbmap@gdb10.agrc.utah.gov.sde'
##bbGeodatabaseLocation = r'I:\PROJECTS\Broadband\Round2 Submission\UT_SBDD_20101011.gdb\NATL_Broadband_Map'

censusPlacePolygonsgonsPath = r'Database Connections\dc_agrc@SGID10@gdb10.agrc.utah.gov.sde\SGID10.DEMOGRAPHIC.CensusPlaces2010'
censusPlacePolygonsFL = arcpy.MakeFeatureLayer_management(censusPlacePolygonsgonsPath, 'censusPlacePolygonsFL')
censusPlaceFlds = ['NAME10']

roadSegments = bbGeodatabaseLocation + '\\' + 'BB_Service_RoadSegment'
wireless = bbGeodatabaseLocation + '\\' + 'BB_Service_Wireless'
blocks = bbGeodatabaseLocation + '\\' + 'BB_Service_CensusBlock'

#Make feature layers
roadSegments = arcpy.MakeFeatureLayer_management(roadSegments, 'roadSegmentsFL')
wirelessMobileFL = arcpy.MakeFeatureLayer_management(wireless, 'wirelessMobileFL', '"TRANSTECH" = 80')
wirelessFixedFL = arcpy.MakeFeatureLayer_management(wireless, 'wirelessFixedFL', '"TRANSTECH" = 70' or '"TRANSTECH" = 71')
blocksFixedFL = arcpy.MakeFeatureLayer_management(blocks, 'blocksFixedFL')

outBB_CSV = r'I:\PROJECTS\Broadband\BBMapofMonth\Oct2013\BroadbandSummary_Round' + bbRound + '.csv'
outBB = open(outBB_CSV, 'wb')
writer = csv.writer(outBB)
writer.writerow(['CITY','R' + bbRound + '_MOBILEHI','R' + bbRound + '_MOBILEPROVCNT','R' + bbRound + '_MOBILEPROV','R' + bbRound + '_FIXEDHI','R' + bbRound + '_FIXEDPROVCNT','R' + bbRound + '_FIXEDPROV'])

BBFields = ['DBANAME', 'MAXADDOWN']

CensusPlaceROWS = arcpy.da.SearchCursor(censusPlacePolygonsFL, censusPlaceFlds)
cnt = 0

for CensusPlaceROW in CensusPlaceROWS:

    print CensusPlaceROW[0]
    city = CensusPlaceROW[0]
    whereClause = """ "{0}" = '{1}' """.format(censusPlaceFlds[0], city)

    censusPlaceROW_FL2 = arcpy.MakeFeatureLayer_management(censusPlacePolygonsFL, 'censusPlaceROW_FL2', whereClause)

    ## PART 1 -----------
    ## DO MOBILE Wireless

    ##initialize counters
    highSpeedMobile = 0
    mobileProviderList= []
    mobileOut = []

    try:

        arcpy.SelectLayerByLocation_management(wirelessMobileFL, 'INTERSECT', censusPlaceROW_FL2)
        wirelessMobileROWS = arcpy.da.SearchCursor(wirelessMobileFL, BBFields)
        for wirelessMobileROW in wirelessMobileROWS:
            provName = wirelessMobileROW[0].replace(',','')
            speed = int(wirelessMobileROW[1])
            if speed > highSpeedMobile:
                highSpeedMobile = speed

                if not provName in mobileProviderList:
                    mobileProviderList.insert(0,provName)
            else:
                if not provName in mobileProviderList:
                    mobileProviderList.append(provName)

        print city + str(highSpeedMobile) + str(len(mobileProviderList))
        mobileProvStr = str(mobileProviderList).replace("]","").replace("[","").replace("u'","").replace("'","").replace(" ","").replace(",","|")
        mobileOut = [city, highSpeedMobile, str(len(mobileProviderList)), mobileProvStr]

    except:

        mobileOut = [city,"0","0","none"]
        print "  " + city + " has no mobile broadband"


    ## PART 2 ----------
    ## DO FIXED TECHNOLOGIES

    ##initialize counters

    highSpeedFixed = 0
    fixedProviderList= []
    fixedOut = []

    ## first fixed via fixed wireless
    try:

        arcpy.SelectLayerByLocation_management(wirelessFixedFL, 'INTERSECT', censusPlaceROW_FL2)
        wirelessFixedROWS = arcpy.da.SearchCursor(wirelessFixedFL, BBFields)
        for wirelessFixedROW in wirelessFixedROWS:
            provName = wirelessFixedROW[0].replace(',','')
            speed = int(wirelessFixedROW[1])
            if speed > highSpeedFixed:
                highSpeedFixed = speed

                if not provName in fixedProviderList:
                    fixedProviderList.insert(0,provName)
            else:
                if not provName in fixedProviderList:
                    fixedProviderList.append(provName)

        print city + str(highSpeedFixed) + str(len(fixedProviderList))  + " (after fixed wireless)"

    except:

        print "  " + city + " has no fixed wireless"


    ## now fixed via census blocks
    try:

        arcpy.SelectLayerByLocation_management(blocksFixedFL, 'COMPLETELY_WITHIN', censusPlaceROW_FL2)
        blocksFixedROWS = arcpy.da.SearchCursor(blocksFixedFL, BBFields)
        for blocksFixedROW in blocksFixedROWS:
            provName = blocksFixedROW[0].replace(',','')
            speed = int(blocksFixedROW[1])
            if speed > highSpeedFixed:
                highSpeedFixed = speed

                if not provName in fixedProviderList:
                    fixedProviderList.insert(0,provName)
            else:
                if not provName in fixedProviderList:
                    fixedProviderList.append(provName)

        print city + str(highSpeedFixed) + str(len(fixedProviderList)) + " (after blocks)"

    except:

        print "  " + city + " has no censusblock bb"

    ## last fixed via road segments
    try:

        arcpy.SelectLayerByLocation_management(roadSegmentsFixedFL, 'COMPLETELY_WITHIN', censusPlaceROW_FL2)
        roadSegmentsFixedROWS = arcpy.da.SearchCursor(roadSegmentsFixedFL, BBFields)
        for roadSegmentsFixedROW in roadSegmentsFixedROWS:

            provName = roadSegmentsFixedROW[0].replace(',','')
            speed = int(roadSegmentsFixedROW[1])
            if speed > highSpeedFixed:
                highSpeedFixed = speed

                if not provName in fixedProviderList:
                    fixedProviderList.insert(0,provName)
            else:
                if not provName in fixedProviderList:
                    fixedProviderList.append(provName)


        print city + str(highSpeedFixed) + str(len(fixedProviderList)) + " (after roads)"

    except:

        print "  " + city + " has no road segments bb"


    if fixedProviderList == []:

        fixedOut = ["0","0","none"]

    else:
        fixedProvStr = str(fixedProviderList).replace("]","").replace("[","").replace("u'","").replace("'","").replace(" ","").replace(",","|")
        fixedOut = [highSpeedFixed, str(len(fixedProviderList)),fixedProvStr]


    outRow = mobileOut + fixedOut
    print outRow

    writer.writerow(outRow)

    cnt = cnt + 1

    ## if cnt == 1:
    ##    break

outBB.close()