Definition query/Select Layer By Location

760
7
03-11-2019 01:09 PM
TonyAlmeida
Occasional Contributor

I have over 2k parcels i need to create mail listings for. I have created a tool script that works great for individual listings or multiple parcels but i have to type each one in so that tool script won't work. . I have the list of parcels in a .dbf. how can use python to link all these parcels to a script, is that even possible?

Thanks.

0 Kudos
7 Replies
RandyBurton
MVP Regular Contributor

Can you provide a little more description of the workflow of your project?  Do you want to select a group of parcels by location and link them to a table of owner information?  What do you type into your tool for individual and multiple listings?

0 Kudos
TonyAlmeida
Occasional Contributor

I need to notify all the owners within 600 ft of about 500 properties individually to 2k. I want to be able to some how make these listings with out having to type/sarch each of the those 500 individually.

The tool has a arcpy.GetParameterAsText(0), the script takes that number and selects layer by attributes then selects layer by location with the search distance, then exports the selected feature layers attributes to a .xls. I currently use this to do a simple listing but i am trying to avoid having to type all 500 in.

thanks.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

have you geocoded the addresses? assuming you have them, then that would become your 'point' file

0 Kudos
RandyBurton
MVP Regular Contributor

I am making an assumption that the 500 parcels are in some kind of a list.  I think that you should be able to loop through your list (or a file containing the list) and call your tool.  I haven't done any testing, but the idea would be something like:

import arcpy

# Load the toolbox and get the tool's parameters, using the tool
#  name ( ie. "myToolbox" - not the tool label "My Toolbox" )
arcpy.ImportToolbox(r"C:\Projects\MyToolbox.tbx", "myToolbox")

# read your list file

for item in listFile:

    arcpy.NameOfTool_myToolbox(item) # your tool's parameters

As an alternative, you might be able to adapt your script tool to use a list file  as an input parameter instead of a manual entry. I suppose you could get a lot of duplicate parcels when you work your way through the list.  If so, you could add a flag field to your parcel layer and select all parcels within your buffer distance of the flagged parcels.

0 Kudos
TonyAlmeida
Occasional Contributor

ok, i like the idea of passing the excel list as an input parameter. How would i pass the list as a Parameter to the script?

import arcpy, string

# Allow processes to overwrite exisiting data
arcpy.OverwriteOutput = True

#Script arguments
TAX = arcpy.GetParameterAsText(0)
if TAX == '#' or not TAX:
    TAX = "C:/Temp/TaxParcels" # provide a default value if unspecified

values = arcpy.GetParameterAsText(1)
#if SELECT_BY == '#' or not SELECT_BY:
    #SELECT_BY = "\"PIN\" = 'PIN0392800000'" # provide a default value if unspecified

fieldName = "PIN"
values = values.split(";")  # split values into list
values = ["'{0}'".format(v) for v in values] # add single quotes
whereClause = "{0} IN ({1})".format(fieldName, ",".join(values))

Search_Distance = arcpy.GetParameterAsText(2)
if Search_Distance == '#' or not Search_Distance:
    Search_Distance = "300 Feet" # provide a default value if unspecified

Listing_xls = arcpy.GetParameterAsText(3)
if Listing_xls == '#' or not Listing_xls:
    Listing_xls = "C:\Temp\Listing.xls" # provide a default value if unspecified

# Local variables:
tax_Layer = "tax_Layer"
tax_Layer__2_ = "tax_Layer"
TaxParcels = "C:/Temp/TaxParcels" 
tax2_Layer = "tax2_Layer"
tax2_Layer__2_ = "tax2_Layer"

# Process: Make Feature Layer
arcpy.MakeFeatureLayer_management(TAX, tax_Layer)

# Process: Make Feature Layer (2)
arcpy.MakeFeatureLayer_management(TaxParcels, tax2_Layer)

# Process: Select Layer By Attribute
arcpy.SelectLayerByAttribute_management(tax2_Layer, "NEW_SELECTION", whereClause)

# Process: Select Layer By Location
arcpy.SelectLayerByLocation_management(tax_Layer, "WITHIN_A_DISTANCE", tax2_Layer__2_, Search_Distance, "NEW_SELECTION")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
RandyBurton
MVP Regular Contributor

Here's an example of passing the list in an Excel workbook:

import arcpy, xlrd

fileName = arcpy.GetParameterAsText(0) # Input, Data Type 'File', File Filter 'xls; xlsx'

arcpy.AddMessage("File Name: {}".format(fileName))

# open workbook
xls = xlrd.open_workbook(fileName)
# get the first worksheet - see xlrd docs for opening .sheet_by_name('name')
sheet = xls.sheet_by_index(0)
num_rows = sheet.nrows - 1
curr_row = 0 # we will skip row 0, assuming that it is a header
while curr_row < num_rows:
    curr_row += 1 # advance to next row
    pin = sheet.row_values(curr_row)[0] # read first column
    arcpy.AddMessage("Processing: {}".format(pin))

    # do stuff with pin‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Results:

Executing: PinTest C:\pin_nums.xlsx
Start Time: Thu Mar 14 19:54:44 2019
Running script PinTest...
File Name: C:\pin_nums.xlsx
Processing: PIN0392800001
Processing: PIN0392800002
Processing: PIN0392800003
Processing: PIN0392800004
Processing: PIN0392800005
Processing: PIN0392800006
Processing: PIN0392800007
Processing: PIN0392800008
Processing: PIN0392800009
Processing: PIN0392800010
Completed script PinTest...
Succeeded at Thu Mar 14 19:54:44 2019 (Elapsed Time: 0.03 seconds)

Tool parameter is set as:

Parameter setup

My test workbook is attached.  Hope this helps.

forestknutsen1
MVP Regular Contributor

This may be helpful as an example. It solves a problem that sounds a little like yours. It finds address for sending out notifications to property owners of upcoming tree trimming around power lines. It takes a sub network in a power distribution system identified by a feeder ID. Buffers it to 20 feet finds all of the parcels that the buffer hits. Then gets the owner address from a point feature class and a join. It also dumps out a bunch of feature classes to a FGDB for the mapping folks. So, they can quickly makeup maps for target ares. 

You can see a select by location used in the select_append_by_buffer function....

"""
Tree Trim Generator
"""


import arceditor
import arcpy
import os
import traceback

arcpy.env.overwriteOutput = True

# sde paths dev
sde_pime_overhead = r"xxx"
sde_pime_underground = r"xxx"
sde_sec_overhead = r"xxx"
sde_sec_underground = r"xxx"
sde_support = r"xxx"
sde_moa_parcels = r"xxx"
sde_moa_address = r"xxx"
sde_kpb_parcels = r"xxx"
sde_kpb_address = r"xxx"
sde_service_point = r"xxx"
sde_feeders = r"xxx"
sde_cis_table = r"xxx"
feeder_list_path = r"xxx"
buffer_merge = "xxx"
buffer_dissolve = "xxx"
gdb_kpb_address = r"xxx"
working_gdb = r"Tree_Trim_Data.gdb"

# output feature class names
prime_overhead_buffer = "prime_overhead_buffer"
prime_underground_buffer = "prime_underground_buffer"
sec_overhead_buffer = "sec_overhead_buffer"
sec_underground_buffer = "sec_underground_buffer"

conductors = {sde_pime_overhead: prime_overhead_buffer, sde_pime_underground: prime_underground_buffer,
              sde_sec_overhead: sec_overhead_buffer, sde_sec_underground: sec_underground_buffer}

feeder_buffer = "feeder_buffer"
prime_moa_parcels = "moa_parcels"
prime_support = "prime_support"


def get_location_name(name):
    name = os.path.basename(name)
    if "." in name:
        name = name.split(".")[0].lower() + "_" + name.split(".")[1] + "_location"
    else:
        name = "kpbadm_" + name + "_location"
    return name


def get_feederids():
    feederids = []
    with open(feeder_list_path, "r") as feeders_in:
        for line in feeders_in:
            feederids.append(line.strip())
    feederids_val = []
    with arcpy.da.SearchCursor(sde_feeders, ["FEEDERID"]) as feeder:
        for row in feeder:
            feederids_val.append(row[0])
    test = list(set(feederids) - set(feederids_val))
    if len(test) != 0:
        raise ValueError("Input feeders not valid!:\n\t{}".format(" ".join(test)))
    return feederids


def get_oid(source):
    oid = None
    with arcpy.da.SearchCursor(source, ["OBJECTID"]) as select_serach:
        for row in select_serach:
            oid = row[0]
            break
    return oid


def add_feeder_field(location):
    arcpy.AddField_management(location, "FEEDERID", "TEXT", field_length=20)


def setup_target_features(source):
    arcpy.AddMessage("Makeing local schema for " + os.path.basename(source))
    location = get_location_name(source)
    oid = get_oid(source)
    layer = "layer"
    arcpy.MakeFeatureLayer_management(source, layer)
    arcpy.SelectLayerByAttribute_management(layer, where_clause="""OBJECTID = {}""".format(oid))
    arcpy.CopyFeatures_management(layer, location)
    arcpy.TruncateTable_management(location)
    if "Service" not in os.path.basename(source):
        add_feeder_field(location)


def setup_target_tables(source):
    location = get_location_name(source)
    oid = get_oid(source)
    arcpy.TableSelect_analysis(source, location, """OBJECTID = {}""".format(oid))
    arcpy.TruncateTable_management(location)
    add_feeder_field(location)


def update_feederids(target, feederid):
    add_feeder_field(target)
    with arcpy.da.UpdateCursor(target, ["FEEDERID"]) as feeder_cursor:
        for row in feeder_cursor:
            row[0] = feederid
            feeder_cursor.updateRow(row)


def clip_append(source, parcel_temp, feederid):
    arcpy.AddMessage("Clipping " + os.path.basename(source) + " for feeder " + feederid)
    location = get_location_name(source)
    location_temp = location + "_temp"
    arcpy.Clip_analysis(source, parcel_temp, location_temp)
    if "Service" not in os.path.basename(source):
        update_feederids(location_temp, feederid)
    arcpy.Append_management(location_temp, location, "NO_TEST")
    arcpy.Delete_management(location_temp)


def select_append_by_buffer(source, buffer_lyr, f_id):
    arcpy.AddMessage("Selecting " + os.path.basename(source) + " for feeder " + f_id)
    location = get_location_name(source)
    location_temp = location + "_temp"
    source_layer = "source"
    arcpy.MakeFeatureLayer_management(source, source_layer)
    arcpy.SelectLayerByLocation_management(source_layer, select_features=buffer_lyr)
    arcpy.CopyFeatures_management(source_layer, location_temp)
    add_feeder_field(location)
    update_feederids(location_temp, f_id)
    if "Parcel" in os.path.basename(source):
        clip_append(sde_service_point, location_temp, f_id)
        clip_append(sde_moa_address, location_temp, f_id)
        clip_append(sde_kpb_address, location_temp, f_id)
    arcpy.Append_management(location_temp, location, 'NO_TEST')
    arcpy.Delete_management(location_temp)


def kpb_globalid(kpb):
    arcpy.CopyFeatures_management(kpb, gdb_kpb_address)
    arcpy.AddGlobalIDs_management(gdb_kpb_address)


def find_features_by_feeder(feature_list, buff_prime):
    buffer_layer = "layer_buffer"
    arcpy.MakeFeatureLayer_management(buff_prime, buffer_layer)
    feeders = get_feederids()
    for feature in feature_list:
        setup_target_features(feature)
    for feeder in feeders:
        arcpy.SelectLayerByAttribute_management(buffer_layer, "NEW_SELECTION", """FEEDERID = '{}'""".format(feeder))
        select_append_by_buffer(feature_list[0], buffer_layer, feeder)
        select_append_by_buffer(feature_list[1], buffer_layer, feeder)
        select_append_by_buffer(feature_list[2], buffer_layer, feeder)
    arcpy.AddMessage("Dissolving identical...")
    arcpy.Dissolve_management("arcfm8_ServicePoint_location", "arcfm8_ServicePoint_location_dissolve", 
                                dissolve_field="SERVLOCNUMBER")
    arcpy.AddMessage("Joining CIS info...")
    arcpy.JoinField_management("arcfm8_ServicePoint_location_dissolve", "SERVLOCNUMBER", sde_cis_table, "SERVLOCNUMBER")

    arcpy.AddField_management("gisadm_Point_Address_location", "GLOBALID_2", "TEXT", field_length=100)
    with arcpy.da.UpdateCursor("gisadm_Point_Address_location", ["GLOBALID", "GLOBALID_2"]) as guid_cursor:
        for row in guid_cursor:
            row[1] = row[0]
            guid_cursor.updateRow(row)
    arcpy.Dissolve_management("gisadm_Point_Address_location", "gisadm_Point_Address_location_dissolve", 
                              dissolve_field="GLOBALID_2")
    arcpy.JoinField_management("gisadm_Point_Address_location_dissolve", "GLOBALID_2", "gisadm_Point_Address_location", 
                                 "GLOBALID_2")

    arcpy.AddField_management("kpbadm_PhysicalAddress_location", "GLOBALID_2", "TEXT", field_length=100)
    with arcpy.da.UpdateCursor("kpbadm_PhysicalAddress_location", ["GLOBALID", "GLOBALID_2"]) as guid_cursor:
        for row in guid_cursor:
            row[1] = row[0]
            guid_cursor.updateRow(row)
    arcpy.Dissolve_management("kpbadm_PhysicalAddress_location", "kpbadm_PhysicalAddress_location_dissolve", 
                              dissolve_field="GLOBALID_2")
    arcpy.JoinField_management("kpbadm_PhysicalAddress_location_dissolve", "GLOBALID_2", "kpbadm_PhysicalAddress_location", 
                                 "GLOBALID_2")
    arcpy.Dissolve_management("kpbadm_PhysicalAddress_location", "kpbadm_PhysicalAddress_location" + "_dissolve", 
                              dissolve_field="FEEDERID")

    arcpy.Dissolve_management("gisadm_Parcel_Areas_location", "gisadm_Parcel_Areas_location" + "_dissolve",
                               dissolve_field="FEEDERID")
    arcpy.Dissolve_management("kpbadm_Parcels_location", "kpbadm_Parcels_location" + "_dissolve", dissolve_field="FEEDERID")


if __name__ == "__main__":
    # let user know the data sources
    arcpy.AddMessage("MOA Parcels Source: "
                 + os.path.join(os.path.basename(os.path.dirname(sde_moa_parcels)),
                                os.path.basename(sde_moa_parcels)))

    # delete feature classes if they exist
    arcpy.env.workspace = working_gdb
    feature_class_list = arcpy.ListFeatureClasses()
    if feature_class_list is not None:
        for feature_class in feature_class_list:
            if arcpy.Exists(feature_class):
                arcpy.AddMessage("Deleting existing feature: " + feature_class)
                arcpy.Delete_management(feature_class)
    # do geoprocessing
    kpb_globalid(sde_kpb_address)
    for k, v in conductors.items():
        arcpy.AddMessage("Buffering: {}".format(os.path.basename(k)))
        arcpy.Buffer_analysis(k, v, 20, dissolve_field="FEEDERID", dissolve_option="LIST")
    arcpy.Merge_management([v for k, v in conductors.items()], buffer_merge)
    arcpy.Dissolve_management(buffer_merge, buffer_dissolve, "FEEDERID")
    #find_features_by_feeder([gdb_kpb_address], buffer_dissolve)
    find_features_by_feeder([sde_moa_parcels, sde_kpb_parcels, sde_support, sde_moa_address, gdb_kpb_address,
                             sde_service_point], buffer_dissolve)