topic Re: 1:M Join to Pull feature with Specific Attribute information in Data Management Questions

1:M Join to Pull feature with Specific Attribute information

JustinH — Tue, 20 Aug 2024 21:28:16 GMT

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!

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Tue, 20 Aug 2024 21:33:48 GMT

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

Re: 1:M Join to Pull feature with Specific Attribute information

Ed_ — Tue, 20 Aug 2024 21:38:35 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

RobertKrisher — Tue, 20 Aug 2024 21:39:17 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Wed, 21 Aug 2024 17:37:55 GMT

Edited.

Questions:

What kind of geodabase? For example, Oracle 18c 10.7.1 enterprise geodatabase.
What version of ArcGIS Pro?
Can you mock up the result you want in an Excel screenshot?
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
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

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?

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Fri, 30 Aug 2024 16:36:26 GMT

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/1366709/highlight/true#M27874 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

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Wed, 21 Aug 2024 10:42:27 GMT

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

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Wed, 21 Aug 2024 15:16:21 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Wed, 21 Aug 2024 15:23:37 GMT

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!

Re: 1:M Join to Pull feature with Specific Attribute information

RobertKrisher — Wed, 21 Aug 2024 15:55:27 GMT

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).

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Wed, 21 Aug 2024 16:24:02 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Wed, 21 Aug 2024 16:25:00 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Thu, 22 Aug 2024 00:55:27 GMT

@JustinH

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

(view in My Videos)

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.

Re: 1:M Join to Pull feature with Specific Attribute information

JonathanNeal — Wed, 21 Aug 2024 18:11:43 GMT

@JustinH Just in case it is needed, we also have the Add Spatial Join tool. In the Pro 3.3 release we added a check box to permanently transfer the attributes; which seems suitable to your initial comment.

For @Bud's comments (Lots of thanks for continually bringing attention to things; definitely guiding the target we are shooting for.):
The Add Join tool has a long legacy and is quite suitable for the matching data in Pro. All analysis of those matched data should first be persisted to some form of data storage is the standard expectation. For any changes to Add Join we need to carefully change anything that many users are counting on for consistent behavior. Fundamental user scripts could be relying on the "buggy", consistent behavior we have. (Such as 1:first behavior between different workspaces).
My goals with Add Join are to keep it consistent/documented until the legacy of quirks is controllable with parameters that the users can adjust for themselves to suite.

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Thu, 22 Aug 2024 01:17:44 GMT

@JonathanNeal

“My goals with Add Join are to keep it consistent/documented until the legacy of quirks is controllable with parameters that the users can adjust for themselves to suite.”

Makes sense. For example, we have discussed Join — Control what related record gets used as a “controllable parameter” that would be very helpful.

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Thu, 22 Aug 2024 04:37:07 GMT

Idea: Using a single tool: Sort within groupings, join, and fill gaps

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Fri, 23 Aug 2024 21:10:47 GMT

@JustinH, I wonder if you could adapt @jcarlson's Arcade field calculator answer to suit your needs:

Field Calculation Between Related Tables - ArcGIS Pro
It shouldn't be too hard. In Arcade, you can access related tables with the function FeatureSetByRelationshipName, and the result of that function is already filtered to the related records. You'd need some way to filter the result to a single feature. Here's me just grabbing the first record in a related table.
var can2 = FeatureSetByRelationshipName($feature, 'Relate1', ['the_field_you_want'], false)

// get the first feature
var can2_feat = First(can2)

// return value
return can2_feat['the_field_you_want']
Just be sure you get the relationship name right in that first line, and ask for whatever fields you need to do your work. Assuming it's just pulling a single value, though, it's a pretty straightforward operation.

Maybe instead of getting the First() related value, you could get the max() related value. Just a blind guess.

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Tue, 27 Aug 2024 20:25:07 GMT

Thanks, Bud. If this were to allow the Join field to access the "First" submission, I think it could work. My other work around was to dissolve the occurrences based on the Activity ID and then print the Max value. This has worked well but when I go to join the Data, It creates duplicates. I then delete the duplicates and still end up losing something small like 10 submissions. I am not totally positive how to work around it yet but that seemed to do the trick.

Re: 1:M Join to Pull feature with Specific Attribute information

JustinH — Tue, 27 Aug 2024 20:57:22 GMT

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.

Re: 1:M Join to Pull feature with Specific Attribute information

Bud — Wed, 28 Aug 2024 01:47:20 GMT

Another option for anyone who’s interested:

Export the data to a mobile geodatabase. Create a database view using SQLite SQL to get the result that you want. SQLite is a fully functioning SQL dialect, unlike FGDB SQL.

This Oracle SQL query could be adapted to SQLite SQL:

--Oracle SQL
select
    t2.a2,
    t1.a1,
    case when t1.b1 is not null then t1.b1 else 0 end as b1
from
    table_2 t2
left join
    (select a1, b1, row_number() over (partition by a1 order by b1 desc) as rn from table_1) t1
    on t2.a2=t1.a1
where
    (t1.rn = 1 or t1.rn is null)

Source: Using a single tool: Sort within groupings, join, and fill gaps