topic Re: Adding Labels Using Related Data in Python Questions

Adding Labels Using Related Data

JeffDavis7 — Tue, 13 Oct 2020 17:54:47 GMT

Hello,

I've tried to utilize the code I found posted by Richard Fairhurst that allows you to create labels based on data contained within a related table to no avail. I keep getting the veritable "No features found. Could not verify expression". Everyone's favorite error message. BTW...a big shout out to Richard for posting this code and doing all he can to help others with this quest.

I am attempting to add the information contained under the Table field "Name" to individual polygons.

Any insight on what I am doing wrong would be most appreciated.

Thank you.

Jeff

Here is the logistics of my relationship class setup:

Origin Table: DBOPlotOccJoin

Primary Key: UserField4

Foreign Key: GIS_ID

Destination Table: BSACemeteriesCopy

Table Label Field: Name

Here is Richard's python code that I have augmented:

# Initialize a global dictionary for a related feature class/table

relateDict = {}

def FindLabel ( [UserField4] 😞

# declare the dictionary global so it can be built once and used for all labels

global relateDict

# only populate the dictionary if it has no keys

if len(relateDict) == 0:

# Provide the path and table name to the relate feature class/table

relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"

# create a field list with the relate field first (POLYID),

# followed by sort field(s) (SCHOOLID), then label field(s) (CROSS_STREET)

relateFieldsList = ["GIS_ID", "Name"]

# process a da search cursor to transfer the data to the dictionary

with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:

for relateRow in relateRows:

# store the key value in a variable so the relate value

# is only read from the row once, improving speed

relateKey = relateRow[0]

# if the relate key of the current row isn't found

# create the key and make it's value a list of a list of field values

if not relateKey in relateDict:

# [searchRow[1:]] is a list containing

# a list of the field values after the key.

relateDict[relateKey] = [relateRow[1:]]

else:

# if the relate key is already in the dictionary

# append the next list of field values to the

# existing list associated with the key

relateDict[relateKey].append(relateRow[1:])

# delete the cursor, and row to make sure all locks release

del relateRows, relateRow

# store the current label feature's relate key field value

# so that it is only read once, improving speed

labelKey = [UserField4]

# start building a label expression.

# My label has a bold key value header in a larger font

expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)

# determine if the label key is in the dictionary

if labelKey in relateDict:

# sort the list of the list of fields

sortedList = sorted(relateDict[labelKey])

# add a record count to the label header in bold regular font

expression += '\n<FNT name="Arial" size="10"><BOL>School Count = {}</BOL></FNT>'.format(len(sortedList))

# process the sorted list

for fieldValues in sortedList:

# append related data to the label expression

expression += '\n{0} - {1} - {2} - {3}'.format(fieldValues[0], fieldValues[1], fieldValues[2], fieldValues[3])

# clean up the list variables after completing the for loop

del sortedList, fieldValues

else:

expression += '\n<FNT name="Arial" size="10"><BOL>School Count = 0</BOL></FNT>'

# return the label expression to display

return expression

Re: Adding Labels Using Related Data

RandyBurton — Sun, 18 Oct 2020 20:49:01 GMT

I noticed that your origin table may be a join, so it is possible that the field names have been modified because of the join. I would also confirm that UserField4 and GIS_ID have some matching values. Are you using Desktop or Pro?

Since this is based on Richard Fairhurst‌'s code, I'll tag him into the conversation.

Re: Adding Labels Using Related Data

JeffDavis7 — Mon, 19 Oct 2020 10:50:01 GMT

Thank you Randy for responding to my post. I really appreciate your time.

Yes. That is correct. The origin table is based on a join between two tables; one representing Plots while the other is Occupants. The field names both before and after the join have remained consistent and yes, UserField4 and GIS_ID have matching values. I am using Desktop.

Re: Adding Labels Using Related Data

RandyBurton — Wed, 21 Oct 2020 04:10:02 GMT

I'm still confused with your tables. Is 'DBOPlotOccJoin' the related table? And, is 'BSACemeteriesCopy' the feature layer you are symbolizing? Perhaps a few sample rows of the feature layer and the related table would clear up my confusion.

If you have already joined the two tables into one that you are symbolizing, you won't need the steps in Richard's code. You would just pass multiple fields to the 'FindLabel' function and add some formatting. Again, some sample rows would be helpful; it can be made-up data as it is the structure that I'm trying to understand.

Re: Adding Labels Using Related Data

JeffDavis7 — Wed, 21 Oct 2020 10:11:38 GMT

That is correct...DBOPlotOccJoin is the file geodatabase table and BSACemeteriesCopy is the feature layer being used for symbolizing. I've attached a screen capture depicting both of these tables with the UserField4 and the GIS_ID fields present. You can see the "many" in the DBOPlotOccJoin to the "one" in BSACemeteriesCopy in the capture.

Re: Adding Labels Using Related Data

RandyBurton — Sat, 11 Dec 2021 06:36:34 GMT

Thanks for the screen capture. I am going to make one assumption: the field "Name" is in the "DBOPlotOccJoin" table. If that is the case, then here is a simplified example of the labels and data I used for my testing.

The following code gets pasted in the expression section of the Label Expression window. Be sure to check 'Advanced' and select Python as the 'Parser' before pasting.

# Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [GIS_ID] ):
  # declare the dictionary global so it can be built once and used for all labels
  global relateDict
  # only populate the dictionary if it has no keys
  if len(relateDict) == 0:
    # Provide the path and table name to the relate feature class/table
    relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"
    # create a field list with the relate field first (GIS_ID),
    # followed by sort field(s) (Name), then label field(s) (...)
    relateFieldsList = ["UserField4", "Name"]   
# process a da search cursor to transfer the data to the dictionary
    with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
      for relateRow in relateRows:
        # store the key value in a variable so the relate value
        # is only read from the row once, improving speed
        relateKey = relateRow[0]
        # if the relate key of the current row isn't found
        # create the key and make it's value a list of a list of field values
        if not relateKey in relateDict:
          # [searchRow[1:]] is a list containing
          # a list of the field values after the key.
          relateDict[relateKey] = [relateRow[1:]]
        else:
          # if the relate key is already in the dictionary
          # append the next list of field values to the
          # existing list associated with the key
          relateDict[relateKey].append(relateRow[1:])
    # delete the cursor, and row to make sure all locks release
    del relateRows, relateRow
  # store the current label feature's relate key field value
  # so that it is only read once, improving speed
  labelKey = [GIS_ID]
  # start building a label expression.
  # My label has a bold key value header in a larger font
  expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)
  # determine if the label key is in the dictionary
  if labelKey in relateDict:
    # sort the list of the list of fields
    sortedList = sorted(relateDict[labelKey])
    # add a record count to the label header in bold regular font
    # expression += '\n<FNT name="Arial" size="10"><BOL>Name Count = {}</BOL></FNT>'.format(len(sortedList))
    # process the sorted list
    for fieldValues in sortedList:
       # append related data to the label expression
       # expression += '\n{0} - {1} - {2} - {3}'.format(fieldValues[0], fieldValues[1], fieldValues[2], fieldValues[3])
       expression += '\n{0}'.format(fieldValues[0])
    # clean up the list variables after completing the for loop
    del sortedList, fieldValues
  else:
    # expression += '\n<FNT name="Arial" size="10"><BOL>Name Count = 0</BOL></FNT>'
    pass
  # return the label expression to display
  return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Line 3 is the field in the feature that we are using for labeling and linking to the related table: GIS_ID.

Line 12 contains the linking field in the related table along with the related data used for the labels: UserField4 and Name

Line 41 is sorting the dictionary values, in this case the "Names". You may wish to comment this line out if you do not want the names sorted.

I commented out line 43 as this adds a line to the label indicating how many names are associated with the feature.

Line 47 was also commented out, as there is only one field value being used, in this case "Name".

The else block, lines 51-53, was left in, but the name count is not added to the label.

Hope this helps.

Re: Adding Labels Using Related Data

JeffDavis7 — Thu, 22 Oct 2020 11:17:01 GMT

First of all...the code works beautifully!! The display is exactly what I was looking for. Thank you so much, Randy for taking the time and assisting me. I really appreciate it!

Second...as a follow-up question...should there be any issues with the labels coming up in AGO? Since the labels look so great I decided to explore creating a custom application for my internal clients. Unfortunately, when I publish using the same Desktop project file that shows the labels perfectly, no labels appear within the application window. I realize this is not an AGO forum, but have you run into any issues with labels disappearing in AGO when using this label workflow?

Re: Adding Labels Using Related Data

RandyBurton — Thu, 22 Oct 2020 16:06:34 GMT

Glad to help. Regarding your follow-up question, I would not expect this procedure to work on AGO. I suggest asking the question "can Arcade create labels using data from related tables" in the ArcGIS Online forum. Hopefully Xander Bakker‌ can provide an answer for you.

Re: Adding Labels Using Related Data

JeffDavis7 — Thu, 22 Oct 2020 16:11:00 GMT

Okay. Thought I would check. I will ask that exact question on the AGO forum. Thank you so much again for your time and effort on this Randy!

Jeff

Re: Adding Labels Using Related Data

XanderBakker — Thu, 22 Oct 2020 16:43:36 GMT

Hi Jeff Davis and Randy Burton ,

Regarding the question "can Arcade create labels using data from related tables" I am afraid the answer is no... In the label profile Arcade has no access to related tables. You will need to add a field in the main featureset and fill it with the information of the related table(s) in order to show it as a label.

Re: Adding Labels Using Related Data

JeffDavis7 — Thu, 22 Oct 2020 16:50:36 GMT

Hi Xander,

Thanks for replying. Too bad. I was hoping to eliminate a little database work. Since it's a one to many relationship I'll not only need to add additional fields but create stacked polygons for the additional data. Doh...oh well...could be worse. I could be completely without this marvelous Randy Burton code!

Thanks again, Xander.

Jeff

Re: Adding Labels Using Related Data

RandyBurton — Thu, 22 Oct 2020 16:56:24 GMT

If it is helpful, I can post some code later that will read data from the feature and related table to create a new feature with one point per name that might meet your needs on AGO. You could also add dates or other information if it is in the related table.

Re: Adding Labels Using Related Data

JeffDavis7 — Thu, 22 Oct 2020 17:00:58 GMT

Randy...that would be amazing if it's not too much trouble for you!!! Thank you so much!!!

Re: Adding Labels Using Related Data

RandyBurton — Sat, 11 Dec 2021 06:36:39 GMT

Here is some test code that will give you an idea how to read the data from a feature and related table and create a point feature for use on AGO. I have made some assumptions, so you will probably need to do some tweaking.

import arcpy # if not in python window
import os

# Prep work : set up some parameters
# feature with geometry - specifically polygons
fc = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\BSACemeteriesCopy"

# related table with data
relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"
relateFieldsList = ["UserField4", "Name", "Born", "Died"] # Born and Died are optional, others can be added

# new point feature we will create
outPath = r'R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb'
outName = 'AGO_cemetery'
outFields = ['SHAPE@X', 'SHAPE@Y', 'Cemetery', 'Plot', 'Name', 'Born', 'Died']

# full path to new feature
outFC = os.path.join(outPath, outName)

# Step 1 : create a new feature
# spatial reference for new feature
sr = arcpy.Describe(fc).SpatialReference # same spatial reference as source feature

# create the new feature
arcpy.CreateFeatureclass_management(out_path = outPath, out_name = outName, geometry_type = "POINT",
                                    template = "#", has_m = "DISABLED", has_z = "DISABLED",
                                    spatial_reference = sr)

# create fields (all text): Cemetery, Plot, Name, Born, Died
arcpy.AddField_management(in_table = outFC, field_name = 'Cemetery',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Cemetery", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Plot',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Plot", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Name',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Name", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Born',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 20, field_alias = "Born", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Died',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 20, field_alias = "Died", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")


# Step 2 : build the related dictionary
relateDict = {}

# read the related table's data into a dictionary
# the key will be the first field in the relateFieldsList
# the value will be a list of tuples starting with the second field in the relatedFields list and using index [0] 
with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
    for relateRow in relateRows:
        relateKey = relateRow[0]
        if not relateKey in relateDict:
            relateDict[relateKey] = [relateRow[1:]]
        else:
            relateDict[relateKey].append(relateRow[1:])

del relateRows, relateRow # clean up


# Step 3 : populate the new feature
# ready an insert cursor and loop through source feature and dictionary
insertCursor = arcpy.da.InsertCursor(outFC, outFields)
with arcpy.da.SearchCursor(fc,['SHAPE@', 'Cemetery', 'GIS_ID']) as cursor:
    for row in cursor:
        labelKey = row[2] # the link
        if labelKey in relateDict:
            sortedList = sorted(relateDict[labelKey])
            listCount = len(sortedList)
            # calculate values for point geometry
            xstep = (row[0].extent.XMax - row[0].extent.XMin)/listCount
            ystep = (row[0].extent.YMax - row[0].extent.YMin)/listCount
            xmin = row[0].extent.XMin + (xstep/2) # x coord for first point
            ymin = row[0].extent.YMin + (ystep/2) # y coord for first point
            # final data
            for fieldValues in sortedList:
                name = fieldValues[0]
                # assuming the dates are type text/string and not type date
                # otherwise some conversion will be required
                born = fieldValues[1]
                died = fieldValues[2]
                # print(xmin, ymin, row[1], labelKey, name, born, died)
                insertCursor.insertRow([xmin, ymin, row[1], labelKey, name, born, died])
                xmin += xstep # add step to x coord
                ymin += ystep # add step to y coord
        else: # not in dictionary
            pass # substitute with error code if necessary 

del insertCursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In the prep section (lines 4-15), the features and table are named along with the specific fields that are being accessed. Modify as needed.

Lines 20-49 create a new point feature to store the combined data from the source feature and the related table. All fields have been set as type text. It is possible that you may wish to omit the born and died fields or set them as dates. In my explorations of cemeteries and genealogy records, you don't often have a complete date for some of these events. A text field can handle a variety of situations at the cost of sorting, etc. I assumed the source feature is a polygon layer, and the new point feature will use the same spatial reference as the polygon layer. If the feature already exists, an error will occur.

Step 2 (lines 52-66) builds the relationship dictionary. It follows Richard Fairhurst's code for labels with few changes.

Step 3 (lines 69+) starts an insert cursor and combines the data from the source feature and related table. I selected the geometry data, the link to the related table, and any other fields of interest. If a link is found, points are added to the new feature; if not found, some error code can run to handle the issue.

The point geometry is calculated using the x-y min and max of the polygon's extent. The polygon is expected to be a simple rectangle or square. The number of expected points is the count of values in the dictionary (or sortedList). A proportion of the distance from x-y min to x-y max is added to the previous point. This results in a series of points being created in a lower-left to upper-right pattern. The points may not fall precisely on an individual grave.

You may need to make modifications for handling the birth and death dates in this section. In addition, modifications may be required if adding or dropping other fields.

The goal would be to create a new point feature that can be published on AGO. It should have the desired attributes so that someone could search for a name, etc. The name could be used for a label.

If you do not wish to use individual points for each name, another option would be to use a single text field and concatenate the desired information into that field. Some HTML line break codes <BR/> can be added to assist in the final formatting of a label.

Hope this helps.

Re: Adding Labels Using Related Data

JeffDavis7 — Fri, 23 Oct 2020 18:38:13 GMT

Thank you Randy for going to so much trouble and adding such incredible detail to your code. The directions you have supplied should be very helpful. So many times I encounter code through my cloud travels that leave a little too much for me to try and decipher and in the end it becomes a lost cause.

I will attempt to implement and run it through some trials and report back.

Again, many thanks Randy and have a great weekend!

Jeff

Re: Adding Labels Using Related Data

JeffDavis7 — Mon, 26 Oct 2020 15:59:13 GMT

Hi Randy,

I received the following error message when I attempted to run the script..."Index error: tuple index out of range".

Here is the code with some changes that I made...commenting out...etc..

Can you please take a look and see where I might have gone wrong?

Thanks.

Jeff

import arcpy # if not in python window
import os

# Prep work : set up some parameters
# feature with geometry - specifically polygons
fc = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\BSACemeteriesCopy"

# related table with data
relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"
relateFieldsList = ["UserField4", "Name"] # Born and Died are optional, others can be added

# new point feature we will create
outPath = r'R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb'
outName = 'AGO_cemetery'
outFields = ['SHAPE@X', 'SHAPE@Y', 'Cemetery', 'Plot', 'Name']

# full path to new feature
outFC = os.path.join(outPath, outName)

# Step 1 : create a new feature
# spatial reference for new feature
sr = arcpy.Describe(fc).SpatialReference # same spatial reference as source feature

# create the new feature
arcpy.CreateFeatureclass_management(out_path = outPath, out_name = outName, geometry_type = "POINT",
template = "#", has_m = "DISABLED", has_z = "DISABLED",
spatial_reference = sr)

# create fields (all text): Cemetery, Plot, Name, Born, Died
arcpy.AddField_management(in_table = outFC, field_name = 'Cemetery',
field_type = "STRING", field_precision = "#", field_scale = "#",
field_length = 50, field_alias = "Cemetery", field_is_nullable = "NULLABLE",
field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Plot',
field_type = "STRING", field_precision = "#", field_scale = "#",
field_length = 50, field_alias = "Plot", field_is_nullable = "NULLABLE",
field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Name',
field_type = "STRING", field_precision = "#", field_scale = "#",
field_length = 50, field_alias = "Name", field_is_nullable = "NULLABLE",
field_is_required = "NON_REQUIRED", field_domain = "#")
#arcpy.AddField_management(in_table = outFC, field_name = 'BirthDate',
#field_type = "STRING", field_precision = "#", field_scale = "#",
#field_length = 20, field_alias = "BirthDate", field_is_nullable = "NULLABLE",
#field_is_required = "NON_REQUIRED", field_domain = "#")
#arcpy.AddField_management(in_table = outFC, field_name = 'DeathDate',
#field_type = "STRING", field_precision = "#", field_scale = "#",
#field_length = 20, field_alias = "DeathDate", field_is_nullable = "NULLABLE",
#field_is_required = "NON_REQUIRED", field_domain = "#")

# Step 2 : build the related dictionary
relateDict = {}

# read the related table's data into a dictionary
# the key will be the first field in the relateFieldsList
# the value will be a list of tuples starting with the second field in the relatedFields list and using index [0]
with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
for relateRow in relateRows:
relateKey = relateRow[0]
if not relateKey in relateDict:
relateDict[relateKey] = [relateRow[1:]]
else:
relateDict[relateKey].append(relateRow[1:])

del relateRows, relateRow # clean up

# Step 3 : populate the new feature
# ready an insert cursor and loop through source feature and dictionary
insertCursor = arcpy.da.InsertCursor(outFC, outFields)
with arcpy.da.SearchCursor(fc,['SHAPE@', 'Cemetery', 'GIS_ID']) as cursor:
for row in cursor:
labelKey = row[2] # the link
if labelKey in relateDict:
sortedList = sorted(relateDict[labelKey])
listCount = len(sortedList)
# calculate values for point geometry
xstep = (row[0].extent.XMax - row[0].extent.XMin)/listCount
ystep = (row[0].extent.YMax - row[0].extent.YMin)/listCount
xmin = row[0].extent.XMin + (xstep/2) # x coord for first point
ymin = row[0].extent.YMin + (ystep/2) # y coord for first point
# final data
for fieldValues in sortedList:
name = fieldValues[0]
# assuming the dates are type text/string and not type date
# otherwise some conversion will be required
born = fieldValues[1]
died = fieldValues[2]
# print(xmin, ymin, row[1], labelKey, name, born, died)
insertCursor.insertRow([xmin, ymin, row[1], labelKey, name, born, died])
xmin += xstep # add step to x coord
ymin += ystep # add step to y coord
else: # not in dictionary
pass # substitute with error code if necessary

del insertCursor

Re: Adding Labels Using Related Data

RandyBurton — Sat, 11 Dec 2021 06:36:42 GMT

You missed a few lines that still refer to birth and death dates. In my code example, you should also comment out or remove the references in lines 88-92:

                # born = fieldValues[1]
                # died = fieldValues[2]
                # print(xmin, ymin, row[1], labelKey, name) # if printing, remove: born, died
                insertCursor.insertRow([xmin, ymin, row[1], labelKey, name])# remove: born, died‍‍‍‍‍‍‍‍‍‍‍‍

If you have created a new feature, you will need to either delete it or comment out the code section that creates it. Otherwise the code will error out saying the feature already exists.

It helps to use the syntax highlighter when sharing code. The best help is found here: Code Formatting... the basics++.

Re: Adding Labels Using Related Data

JeffDavis7 — Mon, 26 Oct 2020 18:55:24 GMT

Hi Randy,

Thank you for responding to my question.

I made the commenting out modifications you indicated and received a different error this time.

" no attribute 'extent'

Here is the code in case you need to see it again...

Thanks again.

Jeff

import arcpy # if not in python window
import os

# Prep work : set up some parameters
# feature with geometry - specifically polygons
fc = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\BSACemeteriesCopy"

# related table with data
relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"
relateFieldsList = ["UserField4", "Name"] # Born and Died are optional, others can be added

# new point feature we will create
outPath = r'R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb'
outName = 'AGO_cemetery'
outFields = ['SHAPE@X', 'SHAPE@Y', 'Cemetery', 'Plot', 'Name']

# full path to new feature
outFC = os.path.join(outPath, outName)

# Step 1 : create a new feature
# spatial reference for new feature
sr = arcpy.Describe(fc).SpatialReference # same spatial reference as source feature

# Step 2 : build the related dictionary
relateDict = {}

del relateRows, relateRow # clean up

# Step 3 : populate the new feature
# ready an insert cursor and loop through source feature and dictionary
insertCursor = arcpy.da.InsertCursor(outFC, outFields)
with arcpy.da.SearchCursor(fc,['SHAPE@', 'Cemetery', 'GIS_ID']) as cursor:
for row in cursor:
labelKey = row[2] # the link
if labelKey in relateDict:
sortedList = sorted(relateDict[labelKey])
listCount = len(sortedList)
# calculate values for point geometry
xstep = (row[0].extent.XMax - row[0].extent.XMin)/listCount
ystep = (row[0].extent.YMax - row[0].extent.YMin)/listCount
xmin = row[0].extent.XMin + (xstep/2) # x coord for first point
ymin = row[0].extent.YMin + (ystep/2) # y coord for first point
# final data
for fieldValues in sortedList:
name = fieldValues[0]
# assuming the dates are type text/string and not type date
# otherwise some conversion will be required
#born = fieldValues[1]
#died = fieldValues[2]
# print(xmin, ymin, row[1], labelKey, name) # if printing, remove: born, died
insertCursor.insertRow([xmin, ymin, row[1], labelKey, name])# remove: born, died
xmin += xstep # add step to x coord
ymin += ystep # add step to y coord
else: # not in dictionary
pass # substitute with error code if necessary

del insertCursor

Re: Adding Labels Using Related Data

JeffDavis7 — Sat, 11 Dec 2021 06:36:46 GMT

import arcpy # if not in python window
import os

# Prep work : set up some parameters
# feature with geometry - specifically polygons
fc = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\BSACemeteriesCopy"

# related table with data
relateFC = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\DBOPlotOccJoin"
relateFieldsList = ["UserField4", "Name"] # Born and Died are optional, others can be added

# new point feature we will create
outPath = r'R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb'
outName = 'AGO_cemetery'
outFields = ['SHAPE@X', 'SHAPE@Y', 'Cemetery', 'Plot', 'Name']

# full path to new feature
outFC = os.path.join(outPath, outName)

# Step 1 : create a new feature
# spatial reference for new feature
sr = arcpy.Describe(fc).SpatialReference # same spatial reference as source feature

# create the new feature
arcpy.CreateFeatureclass_management(out_path = outPath, out_name = outName, geometry_type = "POINT",
                                    template = "#", has_m = "DISABLED", has_z = "DISABLED",
                                    spatial_reference = sr)

# create fields (all text): Cemetery, Plot, Name, Born, Died
arcpy.AddField_management(in_table = outFC, field_name = 'Cemetery',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Cemetery", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Plot',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Plot", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
arcpy.AddField_management(in_table = outFC, field_name = 'Name',
                          field_type = "STRING", field_precision = "#", field_scale = "#",
                          field_length = 50, field_alias = "Name", field_is_nullable = "NULLABLE",
                          field_is_required = "NON_REQUIRED", field_domain = "#")
#arcpy.AddField_management(in_table = outFC, field_name = 'BirthDate',
                          #field_type = "STRING", field_precision = "#", field_scale = "#",
                          #field_length = 20, field_alias = "BirthDate", field_is_nullable = "NULLABLE",
                          #field_is_required = "NON_REQUIRED", field_domain = "#")
#arcpy.AddField_management(in_table = outFC, field_name = 'DeathDate',
                          #field_type = "STRING", field_precision = "#", field_scale = "#",
                          #field_length = 20, field_alias = "DeathDate", field_is_nullable = "NULLABLE",
                          #field_is_required = "NON_REQUIRED", field_domain = "#")


# Step 2 : build the related dictionary
relateDict = {}

# read the related table's data into a dictionary
# the key will be the first field in the relateFieldsList
# the value will be a list of tuples starting with the second field in the relatedFields list and using index [0] 
with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
    for relateRow in relateRows:
        relateKey = relateRow[0]
        if not relateKey in relateDict:
            relateDict[relateKey] = [relateRow[1:]]
        else:
            relateDict[relateKey].append(relateRow[1:])

del relateRows, relateRow # clean up


# Step 3 : populate the new feature
# ready an insert cursor and loop through source feature and dictionary
insertCursor = arcpy.da.InsertCursor(outFC, outFields)
with arcpy.da.SearchCursor(fc,['SHAPE@', 'Cemetery', 'GIS_ID']) as cursor:
    for row in cursor:
        labelKey = row[2] # the link
        if labelKey in relateDict:
            sortedList = sorted(relateDict[labelKey])
            listCount = len(sortedList)
            # calculate values for point geometry
            xstep = (row[0].extent.XMax - row[0].extent.XMin)/listCount
            ystep = (row[0].extent.YMax - row[0].extent.YMin)/listCount
            xmin = row[0].extent.XMin + (xstep/2) # x coord for first point
            ymin = row[0].extent.YMin + (ystep/2) # y coord for first point
            # final data
            for fieldValues in sortedList:
                name = fieldValues[0]
                # assuming the dates are type text/string and not type date
                # otherwise some conversion will be required
                #born = fieldValues[1]
                #died = fieldValues[2]
                # print(xmin, ymin, row[1], labelKey, name) # if printing, remove: born, died
                insertCursor.insertRow([xmin, ymin, row[1], labelKey, name])# remove: born, died
                xmin += xstep # add step to x coord
                ymin += ystep # add step to y coord
        else: # not in dictionary
            pass # substitute with error code if necessary 

del insertCursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Apologies Randy...here is the code with the syntax highlighter applied.

Jeff

Re: Adding Labels Using Related Data

RandyBurton — Sat, 11 Dec 2021 06:36:50 GMT

Can you confirm the feature type is a polygon and not a point?

# feature with geometry - specifically polygons
fc = r"R:\Jeff\City_Projects\Cemetery\CemeteryMgmt.gdb\BSACemeteriesCopy"‍‍