Where clause using result of expression

SarahFranklin · ‎11-01-2011

I am trying to write a python program using a "counties.shp" shapefile and a "hospitals.shape" shapefile. I need to find out which counties have more than one million population in 2000 and for the counties with more than one million people in 2000, find out how many hospitals are within each county.

Counties.shp has a field called POP2000 that lists the county's population
Hospitals.shp has a field called COUNTY that lists the county the hospital is found in.

I was able to do the first part by:

def findpop():
    rows = arcpy.SearchCursor("counties.shp")
    row = rows.next()
    popFld = "POP2000"
    while row:
        ctyPop = row.getValue(popFld)
        if ctyPop > 999999 :
            ctyName = row.NAME
            #print "County: ", ctyName, "\tPopulation: ", ctyPop
            ctylst.append(ctyName)
        row = rows.next()

but I am having trouble with the WHERE clause on the second part. I need to use the identified counties from the first operation for the next one. This is what I've come up with, but after making quite a mess of it, I'm of ideas.. can someone help?

SO say, County1 and County2 had more than 1 million people
I need to find out how many hospitals are in County 1
and how many are in County 2

ctyFld = "COUNTY"
where = ctyFld = ctylst
rows = arcpy.SearchCursor("hospital.shp, where")
row = rows.next()

hospTotal = 0
i = 0
while row :
    ctychck = ctylist
    where "
    hospTotal += 1
    i+=1
    row = rows.next()
print hospTotal

JakeSkinner · ‎11-02-2011

You could accomplish this by performing a spatial search rather than an attribute. Try using the 'SelectLayerByLocation_management' function to do this. Here is an example:

import arcpy
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"
env.overwriteOutput = True

county = "Counties"
hospitals = "Hospitals"

# Create feature layers for the selection
arcpy.MakeFeatureLayer_management(county, "county_FL", "POP2000 > 999999")
arcpy.MakeFeatureLayer_management(hospitals, "hospitals_FL")

# Find all hospitals within counties with a population >= 1,000,000
arcpy.SelectLayerByLocation_management("hospitals_FL", "INTERSECT", "county_FL")

# Create a feature class from the selection
arcpy.CopyFeatures_management("hospitals_FL", "hospitals_selection")

SarahFranklin · ‎11-05-2011

Your solution would work, but I need to develop a where clause to select an individual county.. here's my outline:

inCnty = "flcounties.shp"
inHos= "flhospit.shp"
arcpy.MakeFeatureLayer_management(inHos, "hospitals_FL", "POP2000 > 1000000")
# make a featurelayer for flhospit
arcpy.MakeFeatureLayer_management(inCnty, "county_FL")
# make a featurelayer for flcounties
popFld = "POP2000"
delimitedName = arcpy.AddFieldDelimiters(inCnty, popFld)
where = delimitedName + ">1000000"
rows = arcpy.SearchCursor(inCnty, where)
row = rows.next()
while row :
    # build a new where clause to get single county with pop 1M+ - this is where I'm stuck
    # use SelectLayerByAttribute_management for the single county from the county featurelayer
    # use SelectLayerByLocation_management to select out the hospitals within the single county
    # copy the selection as a new feature class (may be a temporary one)
    # use a new search cursor to the new feature class, loop through and get the count of hospitals
    row = rows.next()
del row
del rows

JakeSkinner · ‎11-08-2011

You can create another feature layer with the 'where' clause to select the individual county:

import arcpy
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"
env.overwriteOutput = True

county = "Counties"
hospitals = "Hospitals"

# Create feature layers for the selection
arcpy.MakeFeatureLayer_management(county, "county_FL", "POP2000 > 999999")
arcpy.MakeFeatureLayer_management("county_FL", "county_FL2", "NAME = 'CHESTER'")
arcpy.MakeFeatureLayer_management(hospitals, "hospitals_FL")

# Find all hospitals within counties with a population >= 1,000,000
arcpy.SelectLayerByLocation_management("hospitals_FL", "INTERSECT", "county_FL2")

# Create a feature class from the selection
arcpy.CopyFeatures_management("hospitals_FL", "hospitals_selection")