Stuck on ArcGIS Online Feature Layers and Relates: Need Expert Help

[JonJones1]

Hello Everyone,

I'm facing some challenges with relates and ArcGIS Online hosted feature layers. My boss is VERY keen to get this resolved, and I'm at a sticking point. Here's a breakdown:

Data I Have:

1. A feature class containing polygons for each county in the United States.
2. A CSV file with multiple rows of data for each county.
3. A unique numerical value shared between my CSV file and feature class to identify each county.
4. Arcgis Pro Desktop Basic

Objectives:

1. Create a hosted Feature Layer in ArcGIS Online that can be used in both ArcGIS Online maps and dashboards, containing all rows of data for each county from the CSV file.
2. Enable the updating of this feature layer as new versions of the CSV file become available.

Achievements and Limitations:

Scenario 1:

• Achievement: In ArcGIS Pro Basic, I successfully performed a relate to combine the data in my CSV file with the polygons in my feature class.
• Limitation: I can't publish this feature class to ArcGIS Online due to the relate. The error message is "24040 In-Memory relates are not supported."

Scenario 2:

• Achievement: I managed to join my CSV file to my feature layer by following guidance from this video, resulting in a new feature layer usable in ArcGIS Online Maps and Dashboards.
• Limitation: I can't quickly update or overwrite this feature layer with an updated version of the CSV file, meaning no updates can be made.

Requirements:

The ability to update or overwrite my feature layer in ArcGIS Online is critical, as it serves as the basic building block for almost all maps or dashboards I create. Creating a new feature layer and redirecting all existing maps and dashboards is not an option.

Closing Thoughts:

1. I only need to update my feature layer in ArcGIS Online around 5-6 times a year and am willing to manually perform tasks as required.
2. We're open to upgrading our ArcGIS Pro license from Basic to Standard if this enhances my options.
3. My boss has made it clear that purchasing ArcGIS Enterprise is not an option :(.

Can anyone offer some guidance?

[bbollin]

Hi @JonJones1! If you have a feature layer in ArcGIS online with the CSV rows already added in to the polygons like you mentioned AND they have the unique ID field then I think you should be able to update your data using Update data in ArcGIS Online: https://www.esri.com/arcgis-blog/products/arcgis-online/data-management/updating-data-in-arcgis-onli... here is a blog I wrote that will walk you through that experience. Just set the unique ID field and click update, then select the field you want to update. You can customize what changes and what remains static.

Does this address your question? If not let me know 🙂

[jcarlson]

I think we might be able to take care of this. Here are my thoughts:

That video you linked is pretty old, and as you note, creates a brand new layer. In more recent AGOL updates, it is now possible to join layers based on a shared attribute and keep the link to the source data. This portion of the documentation describes it:

https://doc.arcgis.com/en/arcgis-online/manage-data/create-hosted-views.htm#ESRI_SECTION1_FB2E757804...

Essentially, as long as you have that shared field in the feature service and the table, you've got everything you need to make that hosted feature layer view.

To prep for that, take your CSV and publish it as a Standalone Table layer. As @bbollin 's post mentions, you'll be able to use the Update Data option to overwrite your CSV table as needed, and the hosted join layer will reflect those changes immediately. You can pull the hosted join layer into your dashboards and maps, and you'll be all set!

Caveats:

If you choose to keep all matching rows and not do any data aggregation, you're going to end up with a layer that has lots of duplicate polygons. Depending on your symbology, this may not actually matter. It would be evident in a map's popup, however. Your final products (maps, dashboards, etc) will determine a lot there.

The hosted join would be a new service, despite pointing at the same data as the original polygon layer. But you'd only have to swap it into your maps once, and all future updates will happen in place.

[JonJones1]

Dear Sir,

Once again, you have outdone yourself! The depth of your response, especially the caveats you highlighted, has been incredibly enlightening. In my situation, this would have led to 66 duplicate instances of a county polygon due to my data table containing multiple rows of data for each feature. Thank you so much for bringing this to my attention.

I now understand that I should change directions. Initially, I had another post inquiring about labeling features from multiple rows of data, linked below. However, it now appears that the "cleanest" approach, at least for the current version of ArcGIS Online, is to maintain a single row of data per feature. However, it is acceptable to have my data in a separate table, but a user needs to be aware of the caveats.

In closing, I have one question: do you agree that maintaining a single row of data for each feature is not just simpler but perhaps also best practice for data in ArcGIS Online?

I am profoundly grateful for your guidance.

https://community.esri.com/t5/arcgis-online-questions/is-it-possible-in-arcgis-online-dashboards-to-...

[jcarlson]

To answer your question, yes, having a single row for each feature is preferable. The tools available in AGOL bring their own constraints, and it's not always possible to keep large / complex datasets clean and efficient, but whenever possible, you should strive for database normalization.

https://en.wikipedia.org/wiki/Database_normalization

So in this case, any information about the counties should be in its own table and not duplicated across rows. Information that repeats for a given feature in that table/layer should be in a separate table and link in using a key field.

For human users, it can be tempting to keep things in a big flat table, but your data will be better off in separate related tables.