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.
Solved! Go to Solution.
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:
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
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.
Can you share (part) of your data so I can test?
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:
CODE | LONGITUDE | LATITUDE |
4910014-031 | -122.678 | 38.23603 |
4910014-033 | -122.677 | 38.2028 |
4910014-034 | -122.667 | 38.19611 |
4910014-035 | -122.674 | 38.19501 |
4910014-039 | -122.672 | 38.2087 |
4910014-040 | -122.67 | 38.20591 |
4910014-041 | -122.708 | 38.22682 |
4910014-027 | -122.678 | 38.22556 |
4910014-030 | -122.688 | 38.22437 |
4910014-014 | -122.679 | 38.22806 |
4910014-006 | -122.704 | 38.21326 |
4910014-007 | -122.678 | 38.19559 |
4910014-008 | -122.678 | 38.19579 |
4910014-009 | -122.688 | 38.21145 |
4910014-010 | -122.681 | 38.22087 |
4910014-011 | -122.689 | 38.22092 |
4910014-004 | -122.696 | 38.21297 |
4910014-013 | -122.689 | 38.2062 |
4910014-002 | -122.698 | 38.21007 |
4910014-015 | -122.699 | 38.23045 |
4910014-016 | -122.694 | 38.22082 |
4910014-018 | -122.692 | 38.23437 |
4910014-021 | -122.676 | 38.22038 |
4910014-022 | -122.671 | 38.21691 |
4910014-012 | -122.687 | 38.23444 |
4910014-029 | -122.676 | 38.23139 |
4910014-005 | -122.675 | 38.21342 |
4910014-044 | -122.678 | 38.23387 |
4910014-001 | -122.691 | 38.21583 |
4910020-002 | -122.884 | 38.36565 |
4910020-025 | -122.873 | 38.38035 |
4910020-026 | -122.873 | 38.3804 |
4910020-050 | -122.87 | 38.38629 |
4910020-051 | -122.87 | 38.38629 |
4910020-001 | -122.887 | 38.36671 |
4910020-003 | -122.881 | 38.36625 |
4910020-006 | -122.768 | 38.25637 |
4910020-007 | -122.873 | 38.37973 |
4910020-008 | -122.873 | 38.38037 |
4910020-012 | -122.879 | 38.36916 |
4910020-052 | -122.87 | 38.38639 |
4910020-017 | -122.877 | 38.36937 |
4910020-015 | -122.877 | 38.36844 |
4910020-018 | -122.877 | 38.37141 |
4910020-019 | -122.806 | 38.29292 |
4910020-020 | -122.791 | 38.27819 |
Results:
CODE | DATE | FINDING |
4910020-030 | 7/15/2003 | 0 |
4910020-030 | 8/11/2003 | 0 |
4910020-030 | 9/16/2003 | 0 |
4910020-030 | 12/17/2003 | 0 |
4910020-030 | 2/18/2004 | 0 |
4910020-030 | 3/18/2004 | 0.5 |
4910020-030 | 4/6/2004 | 0 |
4910020-030 | 5/11/2004 | 0 |
4910020-030 | 10/12/2004 | 0 |
4910020-030 | 11/9/2004 | 0.5 |
4910020-030 | 12/7/2004 | 0 |
4910020-030 | 1/5/2005 | 0 |
4910020-030 | 2/10/2005 | 0.005 |
4910020-030 | 3/8/2005 | 0 |
4910020-030 | 4/13/2005 | 0 |
4910020-030 | 4/13/2005 | 0.005 |
4910020-030 | 5/12/2005 | 0.005 |
4910020-030 | 6/20/2005 | 0.005 |
4910020-030 | 8/16/2005 | 0.005 |
4910020-030 | 9/28/2005 | 0.005 |
4910020-030 | 10/25/2005 | 0.005 |
4910020-030 | 5/25/2006 | 0.005 |
4910020-030 | 6/19/2006 | 0.005 |
4910020-030 | 9/18/2006 | 0.005 |
4910020-030 | 12/11/2006 | 0.005 |
4910020-030 | 3/27/2007 | 0.010 |
4910020-030 | 6/19/2007 | 0.005 |
4910020-030 | 9/17/2007 | 0.005 |
4910020-030 | 12/4/2007 | 0.036 |
4910020-030 | 3/3/2008 | 0.005 |
4910020-030 | 6/18/2008 | 0.005 |
4910020-030 | 9/8/2008 | 0.005 |
4910020-030 | 12/10/2008 | 0.005 |
4910020-030 | 6/9/2009 | 0.005 |
4910020-030 | 9/16/2009 | 0.005 |
4910020-030 | 3/16/2010 | 0.005 |
4910020-030 | 6/8/2010 | 0.005 |
4910020-030 | 9/1/2010 | 0.005 |
4910020-030 | 12/14/2010 | 0.005 |
4910020-030 | 3/15/2011 | 0.005 |
4910020-030 | 9/19/2011 | 0.005 |
4910020-030 | 12/15/2011 | 0.005 |
4910020-030 | 3/13/2012 | 0.005 |
4910020-030 | 6/12/2012 | 0.005 |
4910020-030 | 9/17/2012 | 0.005 |
4910020-030 | 12/10/2012 | 0.005 |
4910020-030 | 3/11/2013 | 0.005 |
4910020-030 | 6/12/2013 | 0.005 |
4910020-030 | 9/3/2013 | 0.005 |
4910020-030 | 12/3/2013 | 0.005 |
4910020-030 | 3/5/2014 | 0.005 |
4910020-030 | 6/10/2014 | 0.005 |
4910020-030 | 9/23/2014 | 0.005 |
4910020-030 | 12/9/2014 | 0.005 |
4910020-030 | 3/4/2015 | 0.005 |
4910020-030 | 6/2/2015 | 0.005 |
4910020-030 | 9/9/2015 | 0.005 |
4910020-030 | 12/17/2015 | 0.005 |
4910020-030 | 3/9/2016 | 0.005 |
4910020-030 | 6/27/2016 | 0.005 |
4910020-030 | 9/27/2016 | 0.005 |
4910020-030 | 12/14/2016 | 0.005 |
4910020-030 | 3/14/2017 | 0.005 |
4910020-030 | 6/7/2017 | 0.005 |
4910020-030 | 9/13/2017 | 0.005 |
4910014-001 | 4/26/1989 | 0 |
4910014-001 | 7/25/1989 | 0 |
4910014-001 | 10/3/1989 | 0 |
4910014-001 | 3/6/1990 | 0 |
4910014-001 | 2/5/1991 | 0 |
4910014-001 | 2/25/1999 | 0 |
4910014-001 | 9/20/2005 | 0 |
4910014-002 | 3/6/1990 | 0 |
4910014-002 | 1/15/1991 | 0 |
4910014-002 | 2/25/1999 | 0 |
4910014-002 | 9/20/2005 | 0 |
4910014-004 | 5/9/1989 | 0 |
4910014-004 | 7/11/1989 | 0 |
4910014-004 | 9/12/1989 | 0 |
4910014-004 | 3/6/1990 | 0 |
4910014-004 | 1/15/1991 | 0 |
4910014-004 | 2/25/1999 | 0 |
4910014-004 | 9/20/2005 | 0 |
4910014-005 | 5/9/1989 | 0 |
4910014-005 | 7/11/1989 | 0 |
4910014-005 | 9/12/1989 | 0 |
4910014-005 | 3/6/1990 | 0 |
4910014-005 | 4/16/1991 | 0 |
4910014-005 | 2/25/1999 | 0 |
4910014-005 | 9/20/2005 | 0 |
4910014-006 | 4/26/1989 | 0 |
4910014-006 | 7/25/1989 | 0 |
4910014-006 | 10/3/1989 | 0 |
4910014-006 | 3/6/1990 | 0 |
4910014-006 | 2/5/1991 | 0 |
4910014-006 | 2/25/1999 | 0 |
4910014-006 | 9/20/2005 | 0 |
4910014-007 | 7/25/1989 | 0 |
4910014-007 | 9/12/1989 | 0 |
4910014-007 | 3/6/1990 | 0.0057 |
4910014-007 | 1/29/1991 | 0 |
4910014-007 | 5/25/1999 | 0 |
4910014-007 | 6/8/1999 | 0 |
4910014-007 | 6/29/1999 | 0 |
4910014-007 | 9/19/2005 | 0 |
4910014-008 | 4/26/1989 | 0.006 |
4910014-008 | 5/16/1989 | 0 |
4910014-008 | 7/25/1989 | 0 |
4910014-008 | 9/12/1989 | 0 |
4910014-008 | 3/6/1990 | 0 |
4910014-008 | 1/29/1991 | 0 |
4910014-008 | 2/25/1999 | 0 |
4910014-008 | 9/19/2005 | 0 |
4910014-009 | 5/2/1989 | 0 |
4910014-009 | 5/2/1989 | 0 |
4910014-009 | 7/11/1989 | 0 |
4910014-009 | 9/12/1989 | 0 |
4910014-009 | 5/21/1991 | 0 |
4910014-009 | 3/2/1999 | 0 |
4910014-009 | 9/19/2005 | 0 |
4910014-010 | 8/1/1989 | 0 |
4910014-010 | 9/12/1989 | 0 |
4910014-010 | 3/6/1990 | 0 |
4910014-010 | 2/12/1991 | 0 |
4910014-010 | 3/2/1999 | 0 |
4910014-010 | 9/19/2005 | 0 |
4910014-011 | 5/2/1989 | 0 |
4910014-011 | 8/1/1989 | 0 |
4910014-011 | 9/12/1989 | 0 |
4910014-011 | 3/6/1990 | 0 |
4910014-011 | 2/12/1991 | 0 |
4910014-011 | 2/12/1991 | 0.009 |
4910014-011 | 2/25/1999 | 0 |
4910014-011 | 9/19/2005 | 0 |
4910014-012 | 5/2/1989 | 0 |
4910014-012 | 8/1/1989 | 0 |
4910014-012 | 9/12/1989 | 0 |
4910014-012 | 12/3/1991 | 0 |
4910014-012 | 2/25/1999 | 0 |
4910014-012 | 9/20/2005 | 0 |
4910014-013 | 6/27/1989 | 0 |
4910014-013 | 8/8/1989 | 0 |
4910014-013 | 9/12/1989 | 0 |
4910014-013 | 3/6/1990 | 0 |
4910014-013 | 1/15/1991 | 0 |
4910014-013 | 2/25/1999 | 0 |
4910014-013 | 9/20/2005 | 0 |
4910014-014 | 5/2/1989 | 0 |
4910014-014 | 5/2/1989 | 0 |
4910014-014 | 8/1/1989 | 0 |
4910014-014 | 9/12/1989 | 0 |
4910014-014 | 3/6/1990 | 0 |
4910014-014 | 1/29/1991 | 0 |
4910014-014 | 3/2/1999 | 0 |
4910014-014 | 9/19/2005 | 0 |
4910014-015 | 5/9/1989 | 0 |
4910014-015 | 8/8/1989 | 0 |
4910014-015 | 3/15/1990 | 0 |
4910014-015 | 1/29/1991 | 0 |
4910014-015 | 3/1/1999 | 0 |
4910014-015 | 12/20/2005 | 0 |
4910014-016 | 5/2/1989 | 0 |
4910014-016 | 8/8/1989 | 0 |
4910014-016 | 9/12/1989 | 0 |
4910014-016 | 3/6/1990 | 0 |
4910014-016 | 2/5/1991 | 0 |
4910014-016 | 5/25/1999 | 0 |
4910014-016 | 9/19/2005 | 0 |
4910014-018 | 8/8/1989 | 0 |
4910014-018 | 9/12/1989 | 0 |
4910014-018 | 3/15/1990 | 0 |
4910014-018 | 7/30/1991 | 0 |
4910014-018 | 1/12/1993 | 0 |
4910014-018 | 8/31/1999 | 0 |
4910014-018 | 9/20/2005 | 0 |
4910014-021 | 5/2/1989 | 0 |
4910014-021 | 8/8/1989 | 0 |
4910014-021 | 9/12/1989 | 0 |
4910014-021 | 3/6/1990 | 0 |
4910014-021 | 2/9/1993 | 0 |
4910014-021 | 3/1/1999 | 0 |
4910014-021 | 9/20/2005 | 0 |
4910014-022 | 5/9/1989 | 0 |
4910014-022 | 8/8/1989 | 0 |
4910014-022 | 9/12/1989 | 0 |
4910014-022 | 3/20/1990 | 0 |
4910014-022 | 3/16/1993 | 0 |
4910014-022 | 3/1/1999 | 0 |
4910014-022 | 9/20/2005 | 0 |
4910014-027 | 4/26/1989 | 0 |
4910014-027 | 7/25/1989 | 0 |
4910014-027 | 9/12/1989 | 0 |
4910014-027 | 3/6/1990 | 0 |
4910014-027 | 1/29/1991 | 0 |
4910014-027 | 2/2/1993 | 0 |
4910014-027 | 3/1/1999 | 0 |
4910014-027 | 9/20/2005 | 0 |
4910014-029 | 4/26/1989 | 0 |
4910014-029 | 7/25/1989 | 0 |
4910014-029 | 9/12/1989 | 0 |
4910014-029 | 3/6/1990 | 0 |
4910014-029 | 1/15/1991 | 0 |
4910014-029 | 1/20/1993 | 0 |
4910014-029 | 3/1/1999 | 0 |
4910014-029 | 9/20/2005 | 0 |
4910014-030 | 7/25/1989 | 0 |
4910014-030 | 9/12/1989 | 0 |
4910014-030 | 3/6/1990 | 0 |
4910014-030 | 1/15/1991 | 0 |
4910014-030 | 1/12/1993 | 0 |
4910014-030 | 3/1/1999 | 0 |
4910014-030 | 9/20/2005 | 0 |
4910014-031 | 4/26/1989 | 0 |
4910014-031 | 7/25/1989 | 0 |
4910014-031 | 9/12/1989 | 0 |
4910014-031 | 3/6/1990 | 0 |
4910014-031 | 1/15/1991 | 0 |
4910014-031 | 1/20/1993 | 0 |
4910014-031 | 3/1/1999 | 0 |
4910014-031 | 11/9/2004 | 0 |
4910014-031 | 9/20/2005 | 0 |
4910014-033 | 6/27/1989 | 0 |
4910014-033 | 7/5/1989 | 0 |
4910014-033 | 9/12/1989 | 0 |
4910014-033 | 3/6/1990 | 0 |
4910014-033 | 2/5/1991 | 0 |
4910014-033 | 9/2/1993 | 0 |
4910014-033 | 8/31/1999 | 0 |
4910014-033 | 9/20/2005 | 0 |
4910014-034 | 7/28/1989 | 0 |
4910014-034 | 10/9/1990 | 0 |
4910014-034 | 3/12/1991 | 0 |
4910014-034 | 3/2/1993 | 0 |
4910014-034 | 3/1/1999 | 0 |
4910014-034 | 9/20/2005 | 0 |
4910014-035 | 3/15/1990 | 0 |
4910014-035 | 10/9/1990 | 0 |
4910014-035 | 2/26/1991 | 0 |
4910014-035 | 7/29/1991 | 0 |
4910014-035 | 11/30/1993 | 0 |
4910014-035 | 3/2/1999 | 0 |
4910014-035 | 9/20/2005 | 0 |
4910014-039 | 11/27/1990 | 0 |
4910014-039 | 6/16/1992 | 0 |
4910014-039 | 3/2/1993 | 0 |
4910014-039 | 3/1/1999 | 0 |
4910014-039 | 9/20/2005 | 0 |
4910014-040 | 10/11/1990 | 0 |
4910014-040 | 3/2/1993 | 0 |
4910014-040 | 3/1/1999 | 0 |
4910014-040 | 9/20/2005 | 0 |
4910014-041 | 7/9/1992 | 0 |
4910014-041 | 8/10/1993 | 0 |
4910014-041 | 11/30/1993 | 0 |
4910014-041 | 2/15/1994 | 0 |
4910014-041 | 5/17/1994 | 0 |
4910014-041 | 3/1/1999 | 0 |
4910014-041 | 9/19/2005 | 0 |
4910014-044 | 6/8/1999 | 0 |
4910014-044 | 12/21/1999 | 0 |
4910014-044 | 3/21/2000 | 0 |
4910014-044 | 6/20/2000 | 0 |
4910014-044 | 9/19/2000 | 0 |
4910014-044 | 9/19/2005 | 0 |
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:
Is this possible using Arcade in AGOL? I'm looking for interactive input of values that drive the query.
Hi DTreering@invenergyllc.com_invenergyllc .
Can you elaborate a little more on what you are looking for?
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.
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.