Symbolizing Feature Class using related table values

7395
8
Jump to solution
05-18-2018 06:58 AM
AndresCastillo
MVP Regular Contributor

I am branching off this thread:

https://community.esri.com/message/772784-re-collector-use-related-layer-to-symbolise-feature

I have a manhole inspections fc, related to a inspection history table, using the Parent's 'GlobalID' field (aka gid)/ and Child's 'Parent guid' field in a one to many relationship,
and the table has attachments using gid/Parent guid.

I am trying to symbolize the parent feature class based off fields from the child table.

So far, I've attempted two methods:

1.
I attempted the sql script from the FC in arcmap using this definition query:
SELECT * FROM ssManholeInspections WHERE:
GlobalID IN (SELECT ParentGUID from ssManholeInspectionsHistory)

The resulting fc ends up with 4292 records, even though the inspection history table has 4665 records.
2.
Joining the table to the fc also results in 4292 records, with the 'Validate Join' Button producing a Join Validation Report
Dialog window that says:
The number of matching records for the join:
- 4292 of 7454 records matched by joining [GlobalID] from <ssManholeInspections> with [ParentGUID] from <ssManholeInspectionsHistory>.
Matching records may not appear in table view due to join validation errors.
Cardinality Warning:
Join Validation detected 4665 non-distinct matching records when using [GlobalID] from <ssManholeInspections> with [ParentGUID] from <ssManholeInspectionsHistory>.  For geodatabase data, ArcMap will join all matching records, although only the first matching record will be displayed in the attribute table of <ssManholeInspections>.  This relationship is 1:Many or Many:Many. When working with 1:M and M:M relationships, it is recommended that relates or Relationship Classes be used instead of joins.  Refer to the ArcGIS Desktop Help topic: About joining and relating tables for best practices on creating relationships between tables.
It is fine that 7454 records did not join because not all manholes have been inspected yet.

In both cases, only the first matching record is returned, which is incorrect.

It seems that I can only display the latest record (kind of like a 1:1 join), so I would have to include in the definition query the ability to pick the latest inspection date (which is updated using the editor tracking create date field, named 'InsStart' with Data type of Date) per unique foreign key (Parent GUID).

I suppose the correct query should also yield 4292 records, but it should return the records with the latest inspection date per ParentGUID, instead of  returning only the first matching record. 

In order to verify the expected count, I used the summary statistics tool on max date, case field guid.

This gave me the 4292 records I needed.

Yet, this is static, and I would like to see these records in real time as the data is edited.

 

These threads also talk about my question, but I would like help to put it into context.

https://community.esri.com/thread/90206

https://community.esri.com/thread/194907-select-records-with-most-recent-date-for-groups-of-points

https://community.esri.com/thread/169714

Tags (145)
1 Solution

Accepted Solutions
AndresCastillo
MVP Regular Contributor

Attached is the solution, as a word document

(See this correct reply in the context of this thread to see the attachment)

Note that there are 17 pages.

As an alternative to my word document, which uses ArcGIS server, ESRI's  Paul Barker

tells us of how to solve this when relying on ArcGIS online.

Here is his post:

Visualizing related data with Join Features in ArcGIS Online 

https://www.esri.com/arcgis-blog/products/product/mapping/visualizing-related-data-with-join-feature... 

Here is another alternative:

https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2020/03/25/easy-how-to-symbology-... 

Joseph Rhodes

Let me know if you have any questions.

View solution in original post

8 Replies
AndresCastillo
MVP Regular Contributor

The following was created using SQL Management studio:

SELECT ss.GlobalID AS 'FC_Global_ID', ss.OBJECTID AS 'FC_LONGINTEGEROID', ssh.ParentGUID AS 'Parent_GUID', MAX(ssh.InsStart) AS 'Inspection_Date_Time' FROM Sanitary.sde.ssManholeInspections_evw AS ss JOIN Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh ON ss.GlobalID = ssh.ParentGUID GROUP BY ss.GlobalID, ssh.ParentGUID, ss.OBJECTID‍

Create Database view gp tool

Register the view with the geodatabase:

https://community.esri.com/groups/geodatabase/blog/2016/12/14/new-at-105-registering-a-database-view...

Enable Editing privileges

My concern is that I'd rather not create a database view, as I heard it slows performance.

If I must use a view, then I might use it as an intermediary which loads/overwrites the refreshed data daily into a table with the same schema as the view.

This makes the data static on a daily basis

For more information about this, read this post I contributed to:

https://community.esri.com/message/774426-re-versioned-views-versus-sql-views-with-a-spatial-column?...

I'd rather create a definition query in the arcmap layer properties, because then I believe this would be a live query on the child table, that I can then join directly to the parent feature class to symbolize off of.

Reply
0 Kudos
AndresCastillo
MVP Regular Contributor

Based on this documentation, I found out that there is a limitation in using subqueries:

http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/sql-reference-for-query-expressions...

Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables.

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

I started to pursue using subqueries to set a definition query on the child table within arcmap to choose the latest inspection record per id.

OBJECTID in (select MAX(OBJECTID) from Sanitary.sde.ssManholeInspectionsHistory_evw GROUP BY ParentGUID)

Or

InsStart in (SELECT MAX(InsStart) FROM Sanitary.sde.ssManholeInspectionsHistory_evw GROUP BY ParentGUID)

Or

NOT EXISTS (SELECT 1 FROM Sanitary.sde.ssManholeInspectionsHistory_evw f WHERE 'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID' = 'f.ParentGUID' AND 'f.InsStart' > 'Sanitary.sde.ssManholeInspectionsHistory_evw.InsStart')

This third query seems as if it should work, but I have not been able to get the desired records from it. 

The first two subqueries returned the desired records for me, yet, when I went to join the queried child table to the parent feature class, using the parent 'global id' field, and child 'ParentGUID' field as the unique identifier, I get this error message from the Join Validation Report:

The number of matching records for the join:
- Unexpected error encountered.
- No matches found by joining [GlobalID] from <Sanitary.SDE.ssManholeInspections> with [ParentGUID] from <Sanitary.SDE.ssManholeInspectionsHistory>.

Any ideas?

Reply
0 Kudos
BrandonKeller
New Contributor II

I am doing a very similar workflow and I solved it through the use of a SQL view.  I am joining my MH's to my inspections and in order to return the last inspection (by date) in the related table I use the MAX function. In my view I also calculate the total inspections on each MH and the age of the last inspection.

,MAX(inspections.CREATEDON) AS Last_Inspection
,COUNT(*) AS Number_Inspections

,DATEDIFF(DAY,a.CREATEDON,GETDATE()) + 1 AS INSPAGEDAYS 

Hope this helps!

Reply
0 Kudos
AndresCastillo
MVP Regular Contributor

Hi Brandon.

I'm not quite sure what you mean.

Can you explain and break down your SQL script for me?

Would it be easier to explain over the phone?

Reply
0 Kudos
RickBoggs1
New Contributor III

@ Andres Castillo Were you ever able to figure out the solution to this? I have the same question.

Thanks

Reply
0 Kudos
AndresCastillo
MVP Regular Contributor

Hi Rick,

Sorry for the delayed reply.

I have to update this thread with the solution.

I have to organize my documentation to strip out sensitive information so I can share.

In the meantime, we can do a walk-through via private message.

AndresCastillo
MVP Regular Contributor

Attached is the solution, as a word document

(See this correct reply in the context of this thread to see the attachment)

Note that there are 17 pages.

As an alternative to my word document, which uses ArcGIS server, ESRI's  Paul Barker

tells us of how to solve this when relying on ArcGIS online.

Here is his post:

Visualizing related data with Join Features in ArcGIS Online 

https://www.esri.com/arcgis-blog/products/product/mapping/visualizing-related-data-with-join-feature... 

Here is another alternative:

https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2020/03/25/easy-how-to-symbology-... 

Joseph Rhodes

Let me know if you have any questions.

View solution in original post

AbiDhakal
Occasional Contributor

Hello folks,

I'm in a similar situation. I have a feature service with a related record and want to symbolize my feature using the latest record from the related table not as a snapshot but real-time.

I will really appreciate your help.

Thank you.

Abi 

Reply
0 Kudos