Creating multiple address points for each unit using arcpy

Question

I have a point feature class of addresses and an excel spreadsheet of all the suites associated with each address. I need to create a point for each suite belonging to an address. Is there a way, using arcpy, to scan the excel, match addresses, and then explode each point to create a new point for each suite?

I would like to create a tool so that I don't have to go through each record manually but i'm not sure where to begin. I've done some searching but most of the information I have found is about deleting duplicate points not creating them. Has anyone come across this sort of problem before or know of any good tools that do this?

I am using Arc 10.2.2

Answer 1

Here is the general code overview:

1. Convert excel table to dbf or feature class table
2. Create empty suite point feature class (with all the holder fields that you may want populated)
3. Run search cursor against table from step one, against field linking address layer to table layer (include all columns that you may write info to new suite point layer)
4. Embed search cursor within step 2 cursor on address layer (pulling in XY shape token and field linking address layer to table layer
5. Do a condition statement to check if linked ID from table == linked ID from address layer
6. If so, pull xy locations from address layer token and write point geometry to new point feature class using a insert cursor (add also write other field attributes e.g. table:Name column to suite:Name column)
7. Continue looping....

This should give you a suite point layer with points on top of points sourced from original excel info.

Answer 2

Sounds like you have a one to many relationship, i.e. one address point can have many suites tied to it? Do the addresses in the excel file match the format in the feature class?

Here's what I would do IF the address format is the same between the spreadsheet and the feature class:

1. make a copy of the address points to be safe and add a field for the suite # Use a search cursor on the feature class to build a dictionary like this {address : OID}

2. Use openpyxl or xlrd to read the addresses from the excel table into a nested list: [[address, suite #], [address, suite #], ...]

3. Once you have done these steps, use (untested) code below to insert the appropriate amount of suites:

   # get suites into a dictionary matched by address psuedo code
   # address_dict is address dictionary from point feature class
   # suite_list is nested list of suites [[address, suite #], [address, suite #], ...]
   suite_dict = {}
   for addr in address_dict.keys():
       suites = []
       for add, suite in suite_list:
           if add == addr:
               suites.append(suite)
       suite_dict[add] = suites
   
   # now put these suite addresses into the point feature class
   # addr_points is your address feature class
   addr_field = 'Your_Address_field'
   new_suite_field = 'new_suite_field'
   all_fields = [f for f in arcpy.ListFields(addr_points)]
   field_names = [f.name for f in all_fields]
   i = field_names.index(addr_field)
   
   # find list of address that need to be duplicated
   adds = [address_dict[a] for a in suite_dict.keys() if a in address_dict]
   
   # first get copies of rows for all addresses so they can be inserted
   fields = [f.name for f in all_fields if f.type != 'OID']
   with arcpy.da.SearchCursor(addr_points, fields) as rows:
       pts_dict = dict((r[i], r) for r in rows if r[i] in adds)
   
   # add the suite field
   arcpy.AddField_management(addr_points, new_suite_field, 'TEXT') # or whatever type this will be
   
   # here's where the magic happens
   with arcpy.da.InsertCursor(addr_points, fields + [new_suite_field]) as irows:
       for address, suites in suite_dict.iteritems():
           for suite in suites:
               irows.insertRow(pts_dict[address] + (suite,)) #concatenates suite to row tuple
   print 'done'