Select to view content in your preferred language

1:M Join to Pull feature with Specific Attribute information

1125
26
Jump to solution
08-20-2024 02:28 PM
Labels (2)
JustinH
Frequent Contributor

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!  

combing 3 related records of a polygon layer ato a point layer with the same ID 2024-08-20 14_24_55-$.png

 

2 Solutions

Accepted Solutions
Ed_
by MVP Regular Contributor
MVP Regular Contributor

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.

Question | Analyze | Visualize

View solution in original post

0 Kudos
JustinH
Frequent Contributor

 UPDATE: This works. Not sure what has happened while I was on vacation but it no longer duplicates rows. Dissolving the Polygon layer and summing the stats at Max does work to chose but this only works because I am using a binary field. 

Attached is my current workflow that has gotten me the closest, it does seem to work to make sure the attributes align with whether or not an Activity Id has at least one submission that has a Planned Boundary. The only problem is when I go to Join the tables, it creates duplicates every time. I then delete Identical and I end up losing 20 features in the process. I feel like this is the closest I have gotten. 

JustinH_1-1724790899474.png

 

JustinH_0-1724790854485.png

 

 

View solution in original post

0 Kudos
26 Replies
JustinH
Frequent Contributor

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!!! 

0 Kudos
Ed_
by MVP Regular Contributor
MVP Regular Contributor

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.

Question | Analyze | Visualize
0 Kudos
Bud
by
Esteemed Contributor

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

0 Kudos
JustinH
Frequent Contributor

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
by
Esteemed Contributor

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.
0 Kudos
JustinH
Frequent Contributor

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

0 Kudos
Bud
by
Esteemed Contributor

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

0 Kudos
JustinH
Frequent Contributor

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

0 Kudos
Bud
by
Esteemed Contributor
  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?
0 Kudos