|
POST
|
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?
... View more
05-21-2018
12:36 PM
|
0
|
0
|
14229
|
|
POST
|
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-with-the-geodatabase 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?commentID=774426#comment-774426 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.
... View more
05-21-2018
09:41 AM
|
0
|
1
|
14230
|
|
POST
|
Hi William, can you help me put my question into this context? See my question: https://community.esri.com/thread/215046-symbolizing-feature-class-using-related-table-values
... View more
05-18-2018
09:31 AM
|
0
|
0
|
8313
|
|
POST
|
At this time, I will branch here: https://community.esri.com/message/773010-symbolizing-feature-class-using-related-table-values Thank you again Jerry Corum!
... View more
05-18-2018
08:33 AM
|
0
|
0
|
4168
|
|
POST
|
Thank you, I started a new question here: https://community.esri.com/message/773010-symbolizing-feature-class-using-related-table-values
... View more
05-18-2018
07:00 AM
|
0
|
0
|
6985
|
|
POST
|
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
... View more
05-18-2018
06:58 AM
|
2
|
10
|
21362
|
|
POST
|
Yes, you are right. I suppose the correct query will yield 4292 records, but the query should return the records with the latest 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.
... View more
05-17-2018
03:44 PM
|
0
|
1
|
4168
|
|
POST
|
Mr. Bixby, Maybe you can contribute to a thread I am involved in with Jerry Corum on May 17, 2018 12:11 PM: https://community.esri.com/message/772784-re-collector-use-related-layer-to-symbolise-feature?commentID=772784#comment-772784 I think we are trying to use a SQL query with no python.
... View more
05-17-2018
01:08 PM
|
0
|
2
|
6985
|
|
POST
|
By the way, thank you very much for specifying how to denote a versioned table in sql. Since I can only show the current record, then 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') per unique foreign key (Parent GUID). These thread talk about this, 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 How would I verify I end up with the desired query?
... View more
05-17-2018
11:57 AM
|
0
|
10
|
4168
|
|
POST
|
Thank you for your reply. This seems like a beautiful solution. Please explain what you mean "I usually use the database management tools to prototype the view's SQL Script" What are the database management tools? Also, are you not using views anymore? Please explain what you mean "This nicely gets around Oracle's 1,000 value limit for "in" statements as well by using the related table for the values" Does this mean that your related records per category are each less than 1000 values? 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.
... View more
05-17-2018
08:40 AM
|
0
|
12
|
14085
|
|
POST
|
Can you update the url of the ' Update on Symbolizing Features by Related Record Values in ArcGIS' link?
... View more
05-17-2018
06:29 AM
|
2
|
0
|
1952
|
|
POST
|
Can you update the url to the wordpress site? It shows a 404 error.
... View more
05-17-2018
06:24 AM
|
2
|
0
|
1952
|
|
POST
|
Can you expand on making a view? Do you mean to use the 'make table view' tool?
... View more
05-17-2018
06:12 AM
|
0
|
15
|
14085
|
|
POST
|
A stackoverflow thread mentions Multivariate rendering: https://gis.stackexchange.com/questions/6446/symbolising-one-to-many-relationships-using-arcgis-desktop Other possible resources: https://developers.arcgis.com/javascript/3/jssamples/smartmapping_multivariate_college.html http://desktop.arcgis.com/en/arcmap/10.4/tools/spatial-analyst-toolbox/an-overview-of-the-multivariate-tools.htm http://resources.esri.com/help/9.3/ArcGISDesktop/dotnet/C6A1D788-E1B5-4b25-B7DC-058518D37DCF.htm http://resources.arcgis.com/en/help/arcobjects-net/conceptualhelp/index.html#/d/000100000m76000000.htm https://developers.arcgis.com/javascript/latest/sample-code/visualization-sm-multivariate/index.html Also, in the feature class' symbology properties, you might symbolize by 'categories>unique values, many fields', or 'multiple attributes>quantity by category'.
... View more
05-17-2018
05:59 AM
|
0
|
0
|
1687
|
| Title | Kudos | Posted |
|---|---|---|
| 1 | 10-03-2024 07:58 AM | |
| 1 | 01-13-2025 10:30 AM | |
| 1 | 04-18-2025 04:53 PM | |
| 1 | 02-21-2025 09:01 AM | |
| 1 | 12-14-2024 12:05 AM |
| Online Status |
Offline
|
| Date Last Visited |
2 weeks ago
|