label related table - in one staked label

[Anonymous User]

Original User: alnesbit

Hello all,

I am hoping someone who knows python can figure this out. I have a similar posting here http://forums.arcgis.com/threads/68883-Related-table-labels . There was an old arcscripts that worked at 9.3 here http://arcscripts.esri.com/details.asp?dbid=12298

I have been able to create a new feature class from using the Make Query Table tool that has all of my related values for my points in one feature class. What I really need know is a way to create one balloon label for each point that is a staked label of all of the related table values. Can I do this with python code or a label expression somehow? In some cases I have 6 or more balloon labels for each of my points and they are overlapping and hard to read.

I am attaching a screen shot of my map book as it is labeled with the many labels for each point. Then I am showing the table that the labels are coming from - showing the many assemblies for each pole.

The last thing I will add is an idea of python labeling code. This python label expression doesn't work but I thought maybe someone out there who knew code better than me could get it to work.

def FindLabel ( [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [POLE_PRIKEY]  ):   if long( [POLE_PRIKEY] ) = long( [POLE_PRIKEY] ):      return [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] + '\n' + [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] + '\n'  + [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]    else:      return [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] 

I appreciate any help!
Thank you,

[MathewCoyle]

I'm not sure I understand what you are trying to do with this line (besides you needing == isntead of =).

if long( [POLE_PRIKEY] ) = long( [POLE_PRIKEY] ):

Something like this should work though.

def FindLabel([FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [POLE_PRIKEY]):
    if long( [POLE_PRIKEY] ) == long( [POLE_PRIKEY] ):
        labelText = '{0}\n{1}\n{2}'.format([FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT], [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT])
        return labelText
    else:
        labelText = [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]
        return labelText 

In terms of accessing the data from a relate, I think you will need to look into using a join.

[Anonymous User]

Original User: alnesbit

Mathew,

Thank you for the response. Your code did change my labels but not exactly what I was looking for. I will explain a little bit more about what I'm trying to do.

I think I need some sort of loop through the attribute table to find when the Pole.Prikey is the same, those are the assembly units I want labeled all together in one balloon. We ended up just making 3 lines of the same label. I will attach a screen shot. So for example, in the first screen shot of my attribute table I would like 204, PM2A, HAFO (4-10T), PF3-3, PM11, and PE1-2 all together staked in one balloon label.

I will attach a screen shot of more of my feature class attribute table. In the previous posting I only showed one selected pole.

And to give some background, I started out with a point feature class that had a related table of 1-M Pole.Prikey to Assembly units. Then after I used the Make Query Table tool (in ArcToolbox) to create a 1-M join, I got this new feature class which is a 1-M join. It's a pretty slick advancement for 10 or 10.1. Talked about here http://blogs.esri.com/esri/supportcenter/2012/05/10/a-quick-tip-on-performing-a-1m-join/ So now I'm just trying to label this new table in the best way.

Thank you. I appreciate any help,

[MathewCoyle]

There is no way I can think of to reference multiple rows as a single label using label expressions. You might be able to get around it by having a separate script that creates the labels in a text file and then the label expression references that. I can't imagine it would be very good performance wise though, especially if there are a lot of features. A better way may be to create a table that has a single field for each label line. So for our sample row field1 = 204, field2 = PM2A, field 3 = HAFO (4-10T) etc. Depending on how your data is set up you could try pivot tables
http://resources.arcgis.com/en/help/main/10.1/index.html#//0017000000n8000000

Either way you'd probably want to create an annotation layer to reference instead of dynamically labelling each time.

[Anonymous User]

Original User: alnesbit

There is no way I can think of to reference multiple rows as a single label using label expressions.

Ok I was afraid that was the case. Yes having my table set up differently, like you mentioned, would allow me to label then. I don't know of a fast way to get the table in that form, other than manually calculating the new fields one pole at a time. There are a lot of point features so that might be a crazy task. I'll try the pivot table link and see if that helps. I'll just hope that sometime in the future someone can come up with some new code to do this.

Thanks,

[MathewCoyle · Accepted solution]

I also came across this method. Seems like a similar issue. It's a process you'd probably want to automate as well if your labels ever change.

http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes?p...

[MathewCoyle]

I possibly found a way to do it. Not all that fast though. You'll have to change all the field names to your data. It will return a label for every feature populated by every item in your target field that match the key field value.

def FindLabel([QS], [TWP]):
    val_list = []
    keyField = "TWP"
    keyVal = [TWP]
    field = "QS"
    fieldVal = [QS]
    layer = "ats"
    with arcpy.da.SearchCursor(
            layer, field, "{0} = {1}".format(keyField, keyVal)) as cursor:
        for row in cursor:
            val1 = row[0]
            val_list.append(val1)
    returnVal = "\n".join(val_list)

    return returnVal

[Anonymous User]

Original User: alnesbit

I possibly found a way to do it. Not all that fast though. You'll have to change all the field names to your data. It will return a label for every feature populated by every item in your target field that match the key field value.

Well I changed the code to match my data but we still have the problem of it labeling for each line of the attribute table - not just giving me one label per Pole.Prikey. This is the code I used: 

def FindLabel( [POLE_PRIKEY] , [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT] ):
    val_list = []
    keyField = "POLE_PRIKEY"
    keyVal = [POLE_PRIKEY] 
    field = "FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT"
    fieldVal = [FIBER_ASSEMBLY_UNITS_ASSEMBLY_UNIT]
    layer = "TEST"
    with arcpy.da.SearchCursor(
            layer, field, "{0} = {1}".format(keyField, keyVal)) as cursor:
        for row in cursor:
            val1 = row[0]
            val_list.append(val1)
    returnVal = "\n".join(val_list)

    return returnVal

I will attach a screen shot of the labels as well.

But I am having some success with your other post and link to the other forum http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes . I hadn't seen that forum posting before and it's an interesting idea that I am tweeking and working with. If I get it to work I will let you know. I'm really close to getting it.

What's stopping me so far is an automated way to Delete Identical. I know there is an awesome new tool at 10 but I only have ArcEditor, not ArcInfo as required for that tool. Would you know of a way to Delete Identical without having ArcInfo?

[MathewCoyle]

In the label placement properties did you try checking "remove duplicates"?

Here is a link to finding duplicates without ArcInfo. You would then need to delete or export non-identical features to a new table.
http://forums.arcgis.com/threads/4021-ArcGIS-10-Pre-Release-finding-duplicates?p=12085&viewfull=1#po...

[Anonymous User]

Original User: alnesbit

Mathew,

I got it! Thank you so much for all of your help! Here is my workflow - it's crazy but it works. (Oh and that code you gave me that I tried did work after I said to Remove Duplicates labels, however, once I put it to the test on my whole data, not just a 2 pole subset, it was labeling and labeling and...so you were right, it was going to take too long. If I let it label all day it might have worked.)

I select out a subset of my data - a service area, and export it to a new feature class naming it "Data1". Then I export it again, the same thing, and name it "Data2".

Then I use the Spatial Join tool in the toolbox and follow the steps explained by Chris Fox in the forum you mentioned http://forums.arcgis.com/threads/4675-Stacking-labels-multiple-records-single-point-two-attributes . I use my Data1 and my Data2 feature classes, my merge rule is Join, my delimiter is a comma and at the bottom I picked "Are Identical To". That gives me a new feature class which I named "Data_spj".

Then in Data_spj I delete duplicate POLE.PRIKEY records by using the python field calculator code explained by Kent Marten here http://forums.arcgis.com/threads/4021-ArcGIS-10-Pre-Release-finding-duplicates and in the Knowledge Base Article 38700.

That gives me my feature class with one field for labeling. I use Maplex to label staked labels force split at comma, no split at space, constrain to left, minimum characters per line 7, max number of lines 8, place overlapping, reduce font size, offset 10,  allow labels to overlap border.

I'm using data driven pages so I will add the rest of my steps in case anyone can use it. I add a new field to my Data_spj feature class called "Grid" and do a spatial join to get the Grid that each point is in into that field. Then I go to Layers - Properties - Data Frame - Clip options - Clip to current Data driven page extent. Exclude layers - my grid and my "Data_spj". Then go to the Properties of my "Data_spj" feature class - Definition Query - click on Page Definition - Enable - Page Name field "Grid", Show features that Match.

So after all of that, I get the perfect labels. Screen shot attached. Hope this helps someone in the future.

Thanks again!!