What I am trying to do is to have create a list of unique TAZs from the data already selected and iterate over that list and subselect all the rows that have that taz then check if they have the
Exclude identifier in the Exclude field, if so then remove 1 from the number of points else don't, have that iterate until there are no more values left in the list,
I tried inputting the below code into a Chat bot and it came out with the second code block however
it produces an error provided and don't know if the syntax is right as I only started learning Python and SQL yesterday, could someone help me figure out how to do this?
What I Gave
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
#obtain list of TAZs
code goes here
#loop through the list of TAZ's selecting each group of same TAZ each time
for value < count of unique TAZ's; value++
{
#select the current value of TAZ
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value')
if ("Exclude = 'Exclude'"){
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
}
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
}
print("calculations compleate")
#clear out slection
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
Chat Bot
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
# Obtain list of TAZs
unique_taz_values = []
with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor:
for row in cursor:
if row[0] not in unique_taz_values:
unique_taz_values.append(row[0])
# Loop through the list of TAZs, selecting each group of the same TAZ each time
for taz_value in unique_taz_values:
#select the current value of TAZ
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value')
if ("Exclude = 'Exclude'"):
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
else:
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
print("calculations compleate")
#clear out slection
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
Error Given
Traceback (most recent call last):
File "<string>", line 12, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11294, in SelectLayerByAttribute
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11291, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 160144: An expected Field was not found or could not be retrieved properly.
Failed to execute (SelectLayerByAttribute).
It will be easier for folks to address your question if you format your code. Please see these guidelines - https://community.esri.com/t5/community-help-documents/how-to-insert-code-in-your-post/ta-p/914552
thank you for the tip the formatting has be changed
What I am trying to do is to have create a list of unique TAZs from the data already selected and iterate over that list and subselect all the rows that have that taz then check if they have the
Exlcude identifyer in the Exclude field, if so then remove 1 from the number of points else dont, have that iterate untille there are no more values left in the list,
I tryed inputing the below code into a Chat bot and it came out with the second code block however
it produces an error providud and dont know if the syntax is right as I only started learning Python and SQL yesterday, could someone help me figure out how to do this?
What I Gave
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
#obtain list of TAZs
code goes here
#loop through the list of TAZ's selecting each group of same TAZ each time
for value < count of unique TAZ's; value++
{
#select the current value of TAZ
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value')
if ("Exclude = 'Exclude'"){
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
}
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
}
print("calculations compleate")
#clear out slection
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
Chat Bot
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
# Obtain list of TAZs
unique_taz_values = []
with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor:
for row in cursor:
if row[0] not in unique_taz_values:
unique_taz_values.append(row[0])
# Loop through the list of TAZs, selecting each group of the same TAZ each time
for taz_value in unique_taz_values:
#select the current value of TAZ
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value')
if ("Exclude = 'Exclude'"):
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
else:
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
print("calculations compleate")
#clear out slection
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
Error Given
Traceback (most recent call last):
File "<string>", line 12, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11294, in SelectLayerByAttribute
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11291, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 160144: An expected Field was not found or could not be retrieved properly.
Failed to execute (SelectLayerByAttribute).
Are you copying the code exactly as it appears in the box from the chat box? Your search cursor has some loops in it that are not formatted right, and if the indents aren't in there, it's not going to run right.
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
# Obtain list of TAZs
unique_taz_values = []
with arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG') as cursor:
for row in cursor:
if row[0] not in unique_taz_values:
unique_taz_values.append(row[0])
# Loop through the list of TAZs, selecting each group of the same TAZ each time
for taz_value in unique_taz_values:
#select the current value of TAZ
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', 'TAZ_REG = TAZ_REG.value')
if ("Exclude = 'Exclude'"):
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
else:
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
print("calculations complete")
#clear out slection
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
You should also be deleting your cursors when you're done to clean up memory, so at the end of this you would have something like:
del cursor
not sure why there is a replay with the post, but i am having a new issue where I cant get a python variable into the SQL query
this is the new code
import arcpy
#geoprocesing layer creation
#repair the geometry
arcpy.management.RepairGeometry('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'KEEP_NULL')
print("fetures repaired")
#create points
arcpy.management.FeatureToPoint('USE_real_master_Martin_County_3_10_23_TAZ_CRA','PointsWithin', 'INSIDE')
print("points layer created")
#Summarize points and feture layer
arcpy.analysis.SummarizeWithin('USE_real_master_Martin_County_3_10_23_TAZ_CRA', 'PointsWithin','SummerizedPoints')
print("SummerizedPoints created")
#selections and calulations of the data
# add calculation field
arcpy.management.AddField('SummerizedPoints','Area_Per_Point', 'DOUBLE')
print("Area_Per_Point feild created")
#cleaning data
#selection of all the condos
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'NEW_SELECTION', 'Point_Count > 2')
# Obtain list of TAZs
Unique_values = list(set(row[0] for row in arcpy.da.SearchCursor('SummerizedPoints', 'TAZ_REG')))
# Loop through the list of TAZs, selecting each group of the same TAZ each time
for taz_value in Unique_values:
#select the current value of TAZ
sql_query = f"TAZ_REG = '{taz_value}'"
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', sql_query)
if ("Exclude = 'Exclude'"):
#remove excluded
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'REMOVE_FROM_SELECTION', "Exclude = 'Exclude'")
#calculate the area of each point if excluded
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count! - 1)', 'PYTHON3')
else:
#if there are not any excludes
arcpy.management.CalculateField('SummerizedPoints', 'Area_Per_Point', '!MFM_ACRES! / (!Point_Count!)', 'PYTHON3')
print("calculations compleate")
#clear out slection
del cursor
arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'CLEAR_SELECTION')
print("Program Done")
The error, the error line is arcpy.management.SelectLayerByAttribute('SummerizedPoints', 'SUBSET_SELECTION', sql_query)
Traceback (most recent call last):
File "<string>", line 10, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11294, in SelectLayerByAttribute
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 11291, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).
Seems like it has an issue with your SQL query. Your code is below:
sql_query = f"TAZ_REG = '{taz_value}'"
See a working example below:
whereClause = "TAX_DIST = '" + str(taxDistrict) + "'"
Why do you have a "f" at the beginning of your query?
Also I believe you need to rework your quotes. I would suggest testing your query by adding a print statement after it - you may see that your quotes are not getting inserted as Text
Update:
So testing this small bit of code:
taxDistrict = 12
whereClause = "TAX_DIST = '" + str(taxDistrict) + "'"
print(whereClause)
I get this result printed to the console:
TAX_DIST = '12'
You can also try building the query in ArcPro, switching to the SQL view & using that to help check your syntax.