1:M Join to Pull feature with Specific Attribute information

[JustinH]

Hello- 

I hope someone can help us with this data issue we are having. 

I have a Point Layer and a Polygon layer and I need to attach the Polygon Layer Attributes to the Point layer. However, there may be 3 polygons to one point. Currently, when I join the Polygons to the Points, it just randomly chooses one of the polygons to pull data from. We want to have a little more control on what data is being attached. 

We are mostly interested in one of the Fields from the Polygon layer. It is a binary layer basically saying if there is a boundary that was uploaded (Binary). There could be three polygons and they all have different attributes in this field (Ex: 0,0,1) or it could just be one polygon and we are not to worried about it. Our goal is to pull the data from the polygon that has a 1. If it does not have a one, print 0.   

I am having a hard time doing this with a Join, not sure if that is the way to approach this. In my head I want to just say, prioritize the join for when this field is = 1. Is there a way I can do this? 

I made a visual and I hope it is helpful. TIA!  

[JustinH]

Also- I need some sort of solution that I can add into Model Builder for me to automate updates when a Third Party pushes updates to the data. Thanks!!! 

[Ed_ · Accepted solution]

May be add a select by attribute where Poly Attr = 1 the  do the join to Activity > Pro will only join the selected queried polys to Activity.

[Bud]

The Add Join tool is one of the few geoprocessing tools that ignores selections. So, I don't think a selection would work in conjunction with Add Join.

But maybe the Join Field tool would honor a selection. I haven't checked. That tool creates a static output feature class, so I try not to use it. In comparison, Add Join creates a dynamic join, so I prefer it instead.

Edit:

Selections on the input or join tables are ignored in the Add Join tool. The Join Field tool supports selections. To only join with a selected subset, create a selection layer and use it as input to the Add Join tool. Join layer properties are copied when you create a selection layer.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm 

One might expect a definition query would work (on the polygon layer). WHERE BINARY = 1. But there is a gotcha. The definition query gets applied to the entire join/the entire attribute table, including both the input table and the join table. See Add Join — Indicate that a definition query on join table will effectively result in an inner join. So, if there are points that don't have matching polygons, then those points would be excluded due to the definition query, which may not be wanted.

We can try hiding/embedding the SQL expression using Make Query Table. The SQL expression behaves like a pre-filter, applied before the join is performed, which is what we want, unlike definition queries. https://community.esri.com/t5/arcgis-pro-ideas/consistent-visible-sql-expressions-and-query/idc-p/13... But we have to be careful not to forget about that SQL expression because it isn't visible or modifiable anywhere. I find it helps to describe the SQL expression in the layer's name. 

Are there points that don't have matching polygons? If yes, in those cases, would it be ok if the joined polygon binary value were displayed as NULL, not zero? Or is displaying a value of zero a must-have in that scenario?

Side note:
I came across an issue when testing definition queries: Esri Support Case #03704340 - ArcGIS Pro 3.3.1 - Join with definition query excludes row unexpectedly

[JustinH]

Thanks for the reply. I am not only trying to get these attributes that equal one, because sometime they don't exist. In that case, I want to pull the 0's. That is why I do not thing a DQ or a Selection will work.

[Bud]

I suppose you could do something like this:

• Using the join that has produced NULL instead of zero when there is a point without a matching polygon,
• Export to a static feature class. This step may not be needed, depending on what technique you use; the data might already be in a static output feature class.
• Then use the field calculator to replace the nulls with zeros.

[JustinH]

No Idea why my brain did not think this would work but it does. I just wanted to make it much more complicated 🙂

[Bud]

@JustinH There were a few techniques mentioned in this comment thread. Can you clarify what technique you're referring to?

[JustinH]

I'm Sorry- Selecting By Attribute = 1 and then Joining does in fact work as the others would all be "Null" or Zero. 

[Bud]

1. Did you perform the join using Join Field (honors selections in the joint able), not Add Join (doesn't honor selections)? 
2. Using the output, did you replace the nulls with zeros using the field calculator?