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]

Yes- I used Join Field. I did try to Calculate Fields within the Model but for some reason the Arcade Script was not working. However in the dashboard, I have values in the Table as BLANK or 0 treated the same so it doesn't necessarily matter. I used: 

When(IsEmpty([FieldName]), 0, 1)

It works 99.999% of the time I used that script but it doesn't want to work for me on this table. 

The newest issue, which isn't related to this thread is that the model I have built will run as a model but when I schedule the tool to run I 100% of the time get an error back. 

[Bud]

@JustinH 

You mentioned you used an Arcade field calculation. Do you have the same issue with a Python field calculation or a VBScript field calculation?

[JustinH]

I did not try any Python Expressions within the model

[RobertKrisher]

If the point is inside the polygon(s) you should be able to use the Spatial Join GP tool with the One to Many option. You can then use the field map to determine how to handle conflicts, in this case you would take the max value of the field from the polygon and put it on the point. 

If the relationship between the point/polygon(s) isn't spatial you can still achieve something similar using the Join Field tool with a similar set of options.

[Bud]

If I remember correctly, the Summary Statistics tool might be useful with this type of approach too.

[JustinH]

Unfortunately there is not spatial relationship between the points and Polygons. It could be helpful to provide some context. I had tried to use Join Field before in the model. It worked really well. I would get the same point output as the inputs. However, what the join was doing was randomly selecting features when there were were >1 features that had the same ID field we use to join the tables. Sometimes, it does not matter, and there can be more than one feature that has the same ID field but the target field are all zeros. When there is atleast one feature in the target field that is equal to 1, I need the join to choose this feature. Hopefully this is helpful in finding a solution! 

[RobertKrisher]

If we're sticking to model builder, try using the dissolve tool on your point layer. Pick your foreign key fireld as the dissolve field, then pick the values you want to push as your statistic fields (min, max, etc). You can then use the join field tool on the dissolved layer.

In situations like this where I've found my data to be inconsistent or strange, I have often resorted to writing a python script to handle my data strangeness. I use a search cursor to iterate through all the values in my source data, creating a dictionary to store the values I want to push to the target layer. This allows me to ignore or discard any values that aren't relevant to my analysis. I then use an update cursor to push the values onto their corresponding features. The benefit of this approach is that a) you have complete control over how data is pushed between the two datasets and b) you can keep detailed logs about any anomalies (how many features in the source had invalid values, how many features from the source didn't have a corresponding target, how many features in the target didn't have a corresponding source, etc).

[Bud]

Edited.

Questions:

1. What kind of geodabase? For example, Oracle 18c 10.7.1 enterprise geodatabase.
2. What version of ArcGIS Pro?
3. Can you mock up the result you want in an Excel screenshot?
4. If you create a join from the point layer to the polygon layer (via Add Join), what kind of join is it? One-to-one or one-to-many? Can you check to see what the Join section in the layer properties says? Did Pro force a one-to-one join, despite the data being one-to-many?
   1. Indicate what caused type of join to be used (1:1 vs 1:M)
   2. Choose if join will be one-to-first or 1:Many
5. Does the joined data need to be editable, or can you use a read-only mechanism like a database view (file GDB, mobile GDB, or enterprise GDB) or a query layer (enterprise GDB only)?

Options:

• Full-blown SQL Query: Database view or query layer
• Or, you could use the Sort geoprocessing tool to sort the polygon layer by the common ID and also sort descending on the binary field (so that the BINARY=1 rows are first in each grouping). That would produce a static output (a separate feature class). Then you could do the join from your points layer to the new sorted polygon layer. If the join is one-to-one/one-to-first, then the join will use the first/lowest ObjectID from each ID grouping. https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/sort.htm

Related:

• Join — Control what related record gets used
• As you might have noticed, joins and tabular analysis are quite buggy in Pro. Is ArcGIS Pro the right tool for tabular/join-based geodatabase analysis?

[JustinH]

Thanks Bud for your willingness to help with this issue. Unfortunately there are a lot of question that I have no idea what the answer is. 

1. Database - Not positive, I pull all of the data from an AGOL Feature Service that our organization is hosting. 

2. I am currently using ArcGIS Pro 3.3 

3. I think the result is sort of confusing what we want.... Lets see if I can do this. 

You can see that my Join Field (A1) has multiple repeat values but the output table pulls the B column that has a one instead of a zero. 

Table 1        
A1  B1  
1  0
1  1
1  0
2  0
2  0
3  1
3  0
3  1 

Table 2 
A2 
1
2
3

Output 
A2   A1   B1
1    1    1
2    2    0
3    3    1

 4. Here is what happens when I do a join of the Points to the Polygons. (There are many more records of polygons than points). This Join seems to work well and I may have to work from this data. Working backwards may be the key Because the 1:M join does work where as the other way around it does not. 

5. The output data will need to be updated on a regular basis because I pull from the updated feature services, make this join and then push to a new feature service that compiles all of the data. 

[Bud]

@JustinH 

Can you review this video where I use the Sort tool and confirm if it is what you want (or not)?

The resulting join could be exported to a static feature class. And then you could replace any nulls (nulls exist when there isn't a match) with zeros using the Field Calculator.

The sample data didn't produce any nulls, but it sounds like your real data would produce nulls that you'd want to change to zeros.