Select to view content in your preferred language

ArcGIS Pro Definition Query to select a group of rows with all the same attribute

1028
8
Jump to solution
06-16-2023 03:09 PM
Labels (2)
JohnEsch1
New Contributor III

I work with a water well data with a related table of lithologies. So a given well can have 1 or many related lithologies. The WELLID is a unique for a given well.  70000000017  has 4 lithologies where 70000000018  has 2. Each lithology can be either glacial drift (AQTYPE=D) or bedrock (AQTYPE=R). Some wells never tagged bedrock so they have all AQTYPE=D. Others have generally the upper part as AQTYPE=D and the lower part as  AQTYPE=R. I am trying to write an ArcGIS Pro Definition Query that will just select the records if all the lithologies for a given well are AQTYPE=D. So when done it would select 70000000017, 70000000018, and 70000000019  because all AQTYPE=D. But it would not select 70000000020 or 70000000021 because they both have some AQTYPE=R. Thanks

WELLIDSEQ_NUMPRIM_LITHDEPTHTHICKNESSAQTYPE
700000000171.00000Clay42.0000042.00000D
700000000172.00000Sand60.0000018.00000D
700000000173.00000Sand & Clay94.0000034.00000D
700000000174.00000Sand125.0000031.00000D
700000000181.00000Clay50.0000050.00000D
700000000182.00000Sand118.0000068.00000D
700000000191.00000Sand30.0000030.00000D
700000000192.00000Clay50.0000020.00000D
700000000193.00000Gravel63.0000013.00000D
700000000201.00000Clay60.0000060.00000D
700000000202.00000Clay & Gravel90.0000030.00000D
700000000203.00000Clay & Stones105.0000015.00000D
700000000204.00000Sandstone130.0000025.00000R
700000000211.00000Sand & Gravel20.0000020.00000D
700000000212.00000Sand & Clay50.0000030.00000D
700000000213.00000Clay & Stones72.0000022.00000D
700000000214.00000Limestone92.0000020.00000R
700000000215.00000Sandstone122.0000030.00000R
0 Kudos
1 Solution

Accepted Solutions
MapGrrl
New Contributor III

I just saw your sample data. I had to import it into a GDB, since that's the only way the subquery will work, but then I use this syntax:

WELLID NOT IN (SELECT WELLID FROM Ottawa_lith_dbf_test WHERE AQTYPE='R')

View solution in original post

0 Kudos
8 Replies
MapGrrl
New Contributor III

How many AQTYPE fields are in the data?

Why can't you use AQTYPE="D"?

Could you use WELLID LIKE "7000000001%"?

Or a compound query: WELL LIKE "7000000001%" AND AQTYPE="D"?

0 Kudos
MapGrrl
New Contributor III

Nevermind, I think I understand what's happening now. You need more of an If-Then Statement, but I'm not sure DQ supports. Let me investigate further.

0 Kudos
MapGrrl
New Contributor III

I'm probably not 100% on syntax, but something like:

WHERE WELLID IN (SELECT WELLID FROM TableName WHERE AQTYPE=D)

0 Kudos
JohnEsch1
New Contributor III

A given county may 10,000-30,000 water wells. WELL LIKE "7000000001%" AND AQTYPE="D" would just give me the ATYPE="D" lithologies, but this well may also have AQTYPE="R" I am trying to filter for just wells  where all the lithologies for a given well are AQTYPE=D. Thanks

0 Kudos
MapGrrl
New Contributor III

This may be more of a Data Schema issue than a query issue. My R-focused brain says "use dplyr to spread the data so that each row represents one well, and then have multiple columns for each variable associated with the Seq Num"... I'm not sure that's possible using just OOTB Pro without a Python Script (I'm sure there's a dplyr equivalent library out there for Py). This is a good question though!

0 Kudos
MapGrrl
New Contributor III

Are there only the two types, R and D? If so, use a query to find all the wells that HAVE AQTYPE=R, then use the subquery to ID the rest of the wells based on what's NOT IN the list of wells with AQTYPE=R. Again, syntax might be a bit off, but something like:

WHERE WELLID NOT IN (SELECT WELLID FROM TableName WHERE AQTYPE=R)

https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-...

0 Kudos
MapGrrl
New Contributor III

I just saw your sample data. I had to import it into a GDB, since that's the only way the subquery will work, but then I use this syntax:

WELLID NOT IN (SELECT WELLID FROM Ottawa_lith_dbf_test WHERE AQTYPE='R')

0 Kudos
JohnEsch1
New Contributor III

This worked great, thanks MapGrrl!

0 Kudos