Where clause using result of expression

SarahFranklin

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

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")