Publishing Geospatial data with a joined table

Question

Ok,  here is the senario:

* I have a some line and point data, (Water Lines and Hydrants, amoung others).  Both the water line and hydrant data have attributes, but not the ones that are needed.  Athese feature classes are contained in a Postgre database. These feature class have been published to a Portal and are to be consumed in a dashboard.
* I have an excel table (from a third party) that has a number of attribute data the we desire as we want to use them as part of the previously mentioned Dashboard.
* After publishing both FC and the table, I tried to join them within a Web Map.  A big issue is that such a join creates a hosted feature set that is no longer connected to the orginal publish feature class. This is important as there are edits being done to the Enterprise Data sets and FC.
* I have tried to join the FC and table in ArcGIS Pro before publishing, but it errors out 99999.

Overall, Im not sure I am thinking about the process in the correct way.  Any ideas anyone?

jcarlson · Answer

It's true, there will be an objectID field. But if you're publishing the service from a query, you only need to include one unique identifier. You can leave the objectID on the imported table for the database's own internal management, but the query layer need not expose that field.

SELECT
  a.objectid,
  a.some_field,
  b.another_field,
  b.etcetera,
  a.shape
FROM first_table a
LEFT JOIN second_table b ON a.shared_field = b.shared_field

James_Armstrong · Answer

Josh,  thanks to the insight.  I believe this is the process that I need.  The current issue is that when importing the table (csv) into the Enterprise GDB, an Object-id field is created.  This seems to casue a duplicated field.  Unfortunatly, I dont seem to be able to delete the ObjectID field in the new table.  Ay ideas?

jcarlson · Answer

We had a similar situation in the past, joining parcel data to outside assessment data. What we did:

* Copy the outside data into the Postgres database as a table* May want to script it out if this is updated frequently

* Create a Query Layer that joins the tables together
* Publish the service

The resulting service will show the fields like they were "baked in", so to speak. End users will have no way of knowing that the source of the data is actually multiple tables. Since the query layer references the database, as the underlying tables are updated, so too will the query layer update.