Best Practice For Joining Feature Layer (AGOL) and Table Data (AGOL)

Question

Problem

Given a Feature Layer of portal / item id `feature-layer-id` and a Table of portal/ item id `table-id` where:

* Both portal items have a field `CON_ID`.
* Both portal items are not owned or editable by me directly.
* There would be a left join; Feature -> Table.
* `CON_ID` is Unique on the Table.
* The table will be updated daily.
* The table has approximately 100,000 rows.
* The Feature Layer has approximately 2,300,000 rows.* The polygons are very small however, being mostly rectangles for building footprints, for example.

The overall project will be done using ReactJS / Vite.js, but this doesn't or shouldn't fully matter at the moment since it could very easily be using vanilla javascript.

Potential Solutions

I do have a few methods of attack I can take. I am able to create a client side feature graphics, querying from the table in batches until I get the feature layer I want, but that seems horribly inefficient and wrong. I don't yet have experience making web workers, which would alleviate user frustrations with their browser crashing, but would have to look into implementing that if this is literally the only way of doing this.

I could create a script to generate a GeoJSON file (and place it at a URL) pretty easily, but I feel like that's quite a bit more work for something that is probably already built in.

I could also create a new layer from joining both of these items via the analysis / join features function, but that will cost quite a few credits and I don't think is good idea for the long term, especially if more columns get added while I'm no longer working on this project.

Questions

I am posting because I genuinely think I'm missing a tool or a feature that makes this much easier, more efficient, or more intuitive / cleaner / scalable. Ideally, I'd like to use the dynamicDataSource property from the Feature Layer group, https://developers.arcgis.com/javascript/latest/api-reference/esri-layers-FeatureLayer.html#dynamicDataSource , but I don't have a workspace id that I can see that I can work with nor do I see a way to add the table data in naturally.

* What is the standard method to use when adding non-joined data onto the map that will be joined by a key field?
* Is it worth using Relates in this here instead of attempting to join? I don't personally feel so, but I am very much open to the idea.* If so, please link documentation or to a tutorial so I can experiment a little bit. I am not currently sure how to set up Relates using the ArcGIS Online interfaces yet, if it is the proper method of handling data like this.

I can clarify my questions if they do not make sense and I might have follow up questions depending on the answers I receive.

Thank you for your time.

JamesIng · Answer

Displaying the slim census tract on a map and then displaying related related data in a popup is definitely doable, as it just requires querying off a single related feature / table.

Rendering the census tract with related table data, is where the main issue is -
Unfortunately likely need to brute force this and join the data manually via some Javascript - which isn't too bad dependant on the view scale you're operating on. While the feature layer has 2 million + rows, if you're only dealing at a district or so level the number of features is going to be a lot less, and you can query against the view extent to only get the related features required instead of trying to brute force and process every record which will definitely be too many for Javascript to do in a browser.

Ligerzero910 · Answer

Sure!

Ultimately, as an example, I have three goals for this potential project.

1) I'd like to have base polygon file, let's say a slim (in terms of field count) Census Tracts in the US Feature Layer, and be able to join them on demand to other datasets, such as a census data table at the census tract level and / or another table / other tables with data at the census tract level to display in a popup.

In this example, [The User] would like to attach census data and one other dataset that is available at the census tract level.

2) I'd like to be able to render the polygons on an ESRI JS Map using data from any related table (or joined table).

Continuing this example, [The User] would like to see Total Population from the Census Tract Data Table using a Class Breaks Renderer (or a Simple Renderer with a Color Variable visual variable if that is easier to think about).

3) Be able to export joined / related data into a CSV or another format. I'm very confident I am able to do this in one way or another, either elegantly or by sheer force, and it is a bonus goal at this time.

I'm not good at writing, so please let me know if this does not make sense.

JamesIng · Answer

Would it be possible to provide a bit of context of what you're trying to achieve as the end result?
E.g: Is it to display content from the table on the related polygons in popup?