Filter based on relationship class?

6010
22
Jump to solution
12-22-2017 08:03 AM
JasonCarter
New Contributor III

Hello,

I have a feature class of point data and a CSV file containing analytical chemistry results to each point (Groundwater Wells). Both are stored in an ArcGIS Pro file geodatabase. There are multiple results, on different dates, associated with the same well (point) in the CSV file. I have created a relationship class between the feature and the CSV file that links the results to the Well. 

My question is - how can I filter the feature class points based on the analytical results? I'd like to filter by values greater than or less than a result, and then only show points that meet the criteria.

Also, is it possible to do this in ArcGIS online without hosting as a feature service? 

Thank you.

1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

Hi Jason Carter ,

Try this expression on the locations point featureclass:

CODE IN (SELECT CODE FROM RelTbl WHERE FINDING > 0.005)

In this case CODE is the field that the relationshipclass between the points featureclass and the related table is based on. RelTbl is the name of my related table. FINDING is the field with the values.

These points (in green) will be filtered:

Which are these locations:

Validation:

Do a select by attributes on the related table with the following query:

FINDING > 0.005

7 records get selected:

Select the related records:

Results in the same 3 locations:

View solution in original post

22 Replies
XanderBakker
Esri Esteemed Contributor

You can use a subquery to filter features based on a condition defined on related records: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

0 Kudos
JasonCarter
New Contributor III

Thank you. I've never done that before. I tried to create a query but the related table is not recognized. 

Do I write the query on the feature layer or the related table? For instance I have the feature layer of Wells and the related table of analytical results. I want to show well point data where analytical results are > a value.

So

SELECT * FROM wells WHERE (SELECT result FROM chemical_results WHERE result > 0.005) but this doesn't work for me. 

0 Kudos
XanderBakker
Esri Esteemed Contributor

Can you share (part) of your data so I can test?

0 Kudos
JasonCarter
New Contributor III

Yes, sure. The common key is CODE. Not every code in the location data has a result. I've done the relationship class. I just want to query the location set by the value of the results (FINDING), say > 0.005

Thank you

Location data:

CODELONGITUDELATITUDE
4910014-031-122.67838.23603
4910014-033-122.67738.2028
4910014-034-122.66738.19611
4910014-035-122.67438.19501
4910014-039-122.67238.2087
4910014-040-122.6738.20591
4910014-041-122.70838.22682
4910014-027-122.67838.22556
4910014-030-122.68838.22437
4910014-014-122.67938.22806
4910014-006-122.70438.21326
4910014-007-122.67838.19559
4910014-008-122.67838.19579
4910014-009-122.68838.21145
4910014-010-122.68138.22087
4910014-011-122.68938.22092
4910014-004-122.69638.21297
4910014-013-122.68938.2062
4910014-002-122.69838.21007
4910014-015-122.69938.23045
4910014-016-122.69438.22082
4910014-018-122.69238.23437
4910014-021-122.67638.22038
4910014-022-122.67138.21691
4910014-012-122.68738.23444
4910014-029-122.67638.23139
4910014-005-122.67538.21342
4910014-044-122.67838.23387
4910014-001-122.69138.21583
4910020-002-122.88438.36565
4910020-025-122.87338.38035
4910020-026-122.87338.3804
4910020-050-122.8738.38629
4910020-051-122.8738.38629
4910020-001-122.88738.36671
4910020-003-122.88138.36625
4910020-006-122.76838.25637
4910020-007-122.87338.37973
4910020-008-122.87338.38037
4910020-012-122.87938.36916
4910020-052-122.8738.38639
4910020-017-122.87738.36937
4910020-015-122.87738.36844
4910020-018-122.87738.37141
4910020-019-122.80638.29292
4910020-020-122.79138.27819

Results:

CODEDATEFINDING
4910020-0307/15/20030
4910020-0308/11/20030
4910020-0309/16/20030
4910020-03012/17/20030
4910020-0302/18/20040
4910020-0303/18/20040.5
4910020-0304/6/20040
4910020-0305/11/20040
4910020-03010/12/20040
4910020-03011/9/20040.5
4910020-03012/7/20040
4910020-0301/5/20050
4910020-0302/10/20050.005
4910020-0303/8/20050
4910020-0304/13/20050
4910020-0304/13/20050.005
4910020-0305/12/20050.005
4910020-0306/20/20050.005
4910020-0308/16/20050.005
4910020-0309/28/20050.005
4910020-03010/25/20050.005
4910020-0305/25/20060.005
4910020-0306/19/20060.005
4910020-0309/18/20060.005
4910020-03012/11/20060.005
4910020-0303/27/20070.010
4910020-0306/19/20070.005
4910020-0309/17/20070.005
4910020-03012/4/20070.036
4910020-0303/3/20080.005
4910020-0306/18/20080.005
4910020-0309/8/20080.005
4910020-03012/10/20080.005
4910020-0306/9/20090.005
4910020-0309/16/20090.005
4910020-0303/16/20100.005
4910020-0306/8/20100.005
4910020-0309/1/20100.005
4910020-03012/14/20100.005
4910020-0303/15/20110.005
4910020-0309/19/20110.005
4910020-03012/15/20110.005
4910020-0303/13/20120.005
4910020-0306/12/20120.005
4910020-0309/17/20120.005
4910020-03012/10/20120.005
4910020-0303/11/20130.005
4910020-0306/12/20130.005
4910020-0309/3/20130.005
4910020-03012/3/20130.005
4910020-0303/5/20140.005
4910020-0306/10/20140.005
4910020-0309/23/20140.005
4910020-03012/9/20140.005
4910020-0303/4/20150.005
4910020-0306/2/20150.005
4910020-0309/9/20150.005
4910020-03012/17/20150.005
4910020-0303/9/20160.005
4910020-0306/27/20160.005
4910020-0309/27/20160.005
4910020-03012/14/20160.005
4910020-0303/14/20170.005
4910020-0306/7/20170.005
4910020-0309/13/20170.005
4910014-0014/26/19890
4910014-0017/25/19890
4910014-00110/3/19890
4910014-0013/6/19900
4910014-0012/5/19910
4910014-0012/25/19990
4910014-0019/20/20050
4910014-0023/6/19900
4910014-0021/15/19910
4910014-0022/25/19990
4910014-0029/20/20050
4910014-0045/9/19890
4910014-0047/11/19890
4910014-0049/12/19890
4910014-0043/6/19900
4910014-0041/15/19910
4910014-0042/25/19990
4910014-0049/20/20050
4910014-0055/9/19890
4910014-0057/11/19890
4910014-0059/12/19890
4910014-0053/6/19900
4910014-0054/16/19910
4910014-0052/25/19990
4910014-0059/20/20050
4910014-0064/26/19890
4910014-0067/25/19890
4910014-00610/3/19890
4910014-0063/6/19900
4910014-0062/5/19910
4910014-0062/25/19990
4910014-0069/20/20050
4910014-0077/25/19890
4910014-0079/12/19890
4910014-0073/6/19900.0057
4910014-0071/29/19910
4910014-0075/25/19990
4910014-0076/8/19990
4910014-0076/29/19990
4910014-0079/19/20050
4910014-0084/26/19890.006
4910014-0085/16/19890
4910014-0087/25/19890
4910014-0089/12/19890
4910014-0083/6/19900
4910014-0081/29/19910
4910014-0082/25/19990
4910014-0089/19/20050
4910014-0095/2/19890
4910014-0095/2/19890
4910014-0097/11/19890
4910014-0099/12/19890
4910014-0095/21/19910
4910014-0093/2/19990
4910014-0099/19/20050
4910014-0108/1/19890
4910014-0109/12/19890
4910014-0103/6/19900
4910014-0102/12/19910
4910014-0103/2/19990
4910014-0109/19/20050
4910014-0115/2/19890
4910014-0118/1/19890
4910014-0119/12/19890
4910014-0113/6/19900
4910014-0112/12/19910
4910014-0112/12/19910.009
4910014-0112/25/19990
4910014-0119/19/20050
4910014-0125/2/19890
4910014-0128/1/19890
4910014-0129/12/19890
4910014-01212/3/19910
4910014-0122/25/19990
4910014-0129/20/20050
4910014-0136/27/19890
4910014-0138/8/19890
4910014-0139/12/19890
4910014-0133/6/19900
4910014-0131/15/19910
4910014-0132/25/19990
4910014-0139/20/20050
4910014-0145/2/19890
4910014-0145/2/19890
4910014-0148/1/19890
4910014-0149/12/19890
4910014-0143/6/19900
4910014-0141/29/19910
4910014-0143/2/19990
4910014-0149/19/20050
4910014-0155/9/19890
4910014-0158/8/19890
4910014-0153/15/19900
4910014-0151/29/19910
4910014-0153/1/19990
4910014-01512/20/20050
4910014-0165/2/19890
4910014-0168/8/19890
4910014-0169/12/19890
4910014-0163/6/19900
4910014-0162/5/19910
4910014-0165/25/19990
4910014-0169/19/20050
4910014-0188/8/19890
4910014-0189/12/19890
4910014-0183/15/19900
4910014-0187/30/19910
4910014-0181/12/19930
4910014-0188/31/19990
4910014-0189/20/20050
4910014-0215/2/19890
4910014-0218/8/19890
4910014-0219/12/19890
4910014-0213/6/19900
4910014-0212/9/19930
4910014-0213/1/19990
4910014-0219/20/20050
4910014-0225/9/19890
4910014-0228/8/19890
4910014-0229/12/19890
4910014-0223/20/19900
4910014-0223/16/19930
4910014-0223/1/19990
4910014-0229/20/20050
4910014-0274/26/19890
4910014-0277/25/19890
4910014-0279/12/19890
4910014-0273/6/19900
4910014-0271/29/19910
4910014-0272/2/19930
4910014-0273/1/19990
4910014-0279/20/20050
4910014-0294/26/19890
4910014-0297/25/19890
4910014-0299/12/19890
4910014-0293/6/19900
4910014-0291/15/19910
4910014-0291/20/19930
4910014-0293/1/19990
4910014-0299/20/20050
4910014-0307/25/19890
4910014-0309/12/19890
4910014-0303/6/19900
4910014-0301/15/19910
4910014-0301/12/19930
4910014-0303/1/19990
4910014-0309/20/20050
4910014-0314/26/19890
4910014-0317/25/19890
4910014-0319/12/19890
4910014-0313/6/19900
4910014-0311/15/19910
4910014-0311/20/19930
4910014-0313/1/19990
4910014-03111/9/20040
4910014-0319/20/20050
4910014-0336/27/19890
4910014-0337/5/19890
4910014-0339/12/19890
4910014-0333/6/19900
4910014-0332/5/19910
4910014-0339/2/19930
4910014-0338/31/19990
4910014-0339/20/20050
4910014-0347/28/19890
4910014-03410/9/19900
4910014-0343/12/19910
4910014-0343/2/19930
4910014-0343/1/19990
4910014-0349/20/20050
4910014-0353/15/19900
4910014-03510/9/19900
4910014-0352/26/19910
4910014-0357/29/19910
4910014-03511/30/19930
4910014-0353/2/19990
4910014-0359/20/20050
4910014-03911/27/19900
4910014-0396/16/19920
4910014-0393/2/19930
4910014-0393/1/19990
4910014-0399/20/20050
4910014-04010/11/19900
4910014-0403/2/19930
4910014-0403/1/19990
4910014-0409/20/20050
4910014-0417/9/19920
4910014-0418/10/19930
4910014-04111/30/19930
4910014-0412/15/19940
4910014-0415/17/19940
4910014-0413/1/19990
4910014-0419/19/20050
4910014-0446/8/19990
4910014-04412/21/19990
4910014-0443/21/20000
4910014-0446/20/20000
4910014-0449/19/20000
4910014-0449/19/20050
0 Kudos
XanderBakker
Esri Esteemed Contributor

Hi Jason Carter ,

Try this expression on the locations point featureclass:

CODE IN (SELECT CODE FROM RelTbl WHERE FINDING > 0.005)

In this case CODE is the field that the relationshipclass between the points featureclass and the related table is based on. RelTbl is the name of my related table. FINDING is the field with the values.

These points (in green) will be filtered:

Which are these locations:

Validation:

Do a select by attributes on the related table with the following query:

FINDING > 0.005

7 records get selected:

Select the related records:

Results in the same 3 locations:

deleted-user-H23pxZFaj1tR
New Contributor III

Is this possible using Arcade in AGOL?  I'm looking for interactive input of values that drive the query.

XanderBakker
Esri Esteemed Contributor

Hi DTreering@invenergyllc.com_invenergyllc .

Can you elaborate a little more on what you are looking for? 

0 Kudos
deleted-user-H23pxZFaj1tR
New Contributor III

HiXander Bakker‌,

I have a county polygon layer (PK=FIPS) related to a bridge table with two fields (FK1=FIPS, FK2=ReportID), which is related to a Reports table (PK=ReportID).  Using your fantastic guidance here, I was able to retrieve the reports from the second related table for the clicked $feature.FIPS by iterating over the FeatureSet returned from the bridge table, and sending a second query to the Reports table.  Bonus points: I have three other overlapping polygon geographies with bridge tables that are returning Reports by using Intersect functions, all from within the Counties layer.

Reports have dates and types, and I'd like to provide filter options to select reports based on user-specified values: a range for dates, and a single selection for type. 

XanderBakker
Esri Esteemed Contributor

Hi David Treering ,

I'm afraid that user defined filters based on a "virtual" field (Arcade expression) is not possible yet. Do you want to do this in a Web Map of in a Web App or Dashboard? I also wonder when you filter a layer in your web map, if this will affect the results of any Arcade expression. I haven't tried that yet.