Calculating well densities in selected counties using ArcPy?

Question

I am trying to calculate:

1. total numbers of wells that fall into each county in a shapefile, and update the county table with the total count.
2. calculate the well density (how many wells there are per area unit, and update the table with the values obtained)

This is what I have so far but I have to set up the where_clause (the where clause should be set up to select the county that the cursor is on).

How should I sep up the where_clause to select the county and similarly select the wells that intersect on that county where the cursor is on?

import arcpy


WellsPath = "C:/Lesson5_Data/Wells.shp"
COpath = "C:/Lesson5_Data/COUNTIES.shp"

# Create a feature class for the wells

arcpy.MakeFeatureLayer_management (wellspath, "wells_lyr")
arcpy.MakeFeatureLayer_management (COpath, "county_lyr")

# Set up the field list to be used by the update cursor
fieldlist = "COUNTY"

countyname = "'ADAMS'"
where_clause = "COUNTY = " +  countyname
print where_clause

with arcpy.da.UpdateCursor(COpath, fieldlist) as countycursor:
    for row in countycursor:
        print row[0]
        countyCount =   int(arcpy.GetCount_management("county_lyr").getOutput(0))
        print "Counties selected: " + str(countyCount)
        arcpy.SelectLayerByLocation_management("wells_lyr", "INTERSECT", "county_lyr", "", "NEW_SELECTION")

I'm new to Python and I have no clue how to do this.

Answer 1

The Where Clause in the cursor will be case sensitive (see Building a query expression) so you'll need to allow for that.

Try this:

countyname = "ADAMS"
where_clause = """UPPER(COUNTY) = '{}'""".format(countyname)
print where_clause

with arcpy.da.UpdateCursor(COpath, fieldlist, where_clause) as countycursor:
    for row in countycursor:
        print row[0]
        countyCount =   int(arcpy.GetCount_management("county_lyr").getOutput(0))
        print "Counties selected: " + str(countyCount)
        arcpy.SelectLayerByLocation_management("wells_lyr", "INTERSECT", "county_lyr", "", "NEW_SELECTION")

I've removed the single quotes ' from countyname = "ADAMS" and added into the where clause. Also added an UPPER() into the where clause to force it to treat all values as upper case to match your countyname value. Values in a where clause SQL statement are case sensitive for everything but personal geodatabases, so best to force them all to upper or lower here.