2

I have been working on some Definition Queries for layers in ArcGIS (10.2.0.3348). The layers connect to data in a File Geodatabase. Now I have attempted to implement the following query (1), that returns 3051 records when I open the attribute table. However, when I run queries 2 and 3, I get 3280 and 2 records respectively. I would have expected the difference in the number or records between query 1 and 2 to be 2 records, as per the result of query 3, but obviously, this is not the case. I guess my understanding of the processing of these queries by the database engine is wrong, and especially the "AND NOT" part of it, but can someone give a better insight why these 3 queries don't match up in terms of number of records?

Also note the result of query 4: it seems the database engine ignores the extra statements against osm_level, osm_layer and osm_location, as the number of records in query 4 is equal to the difference in query 1 and 2?

What am I missing? I have also tried to restructure the query to the one under 5, but that returns the same number of records as query 1...

I realize I could put the required values for "railway" in the IN statement, instead of trying to exclude with the "AND NOT", but I have some specific reason to maintain this structure (catch all for unknown values that get symbolized by the "all other values" item in an ArcGIS layer), and need to live within the limitations of the Definition Query and Layer framework of ArcGIS.

(I would have loved to post this question on the new GeoNet as well, but just can't work with all the clutter...)

QUERY 1:

(railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT (railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location IN ('underground','underwater')))

Number of returned records: 3051

QUERY 2:

railway NOT IN (Null,'') AND osmSupportingElement = 'no'

Number of returned records: 3280

QUERY 3:

(railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location IN ('underground','underwater')))

Number of returned records: 2

QUERY 4:

railway IN ('platform')

Number of returned records: 229

QUERY 5:

((railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT ((railway IN ('platform') AND osm_level LIKE ('%-%')) OR (railway IN ('platform') AND osm_layer LIKE ('%-%')) OR (railway IN ('platform') AND osm_location IN ('underground','underwater'))))

Number of returned records: 3051

Improve this question
2
  • Out of curiosity, what happens if you switch the order of the statements in the second clause of query 1: (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location IN ('underground','underwater')) AND (railway IN ('platform'))?
    – nmpeterson
    Commented Sep 3, 2014 at 15:20
  • That again gives me 3051 records like in query 1 and 5...
    – Marco_B
    Commented Sep 3, 2014 at 16:27

2 Answers 2

Reset to default
1

The reason they don't add up is that in query 1, you have a condition Query 1: (this) AND NOT (that).

Queries 2 and three have only one condition Query 2: (this) Query 3: (that)

You undoubtedly have some records where (this) is true AND (that) is true and also where they are both false.

Try modifying query 1 (take out NOT)

(railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND (railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location IN ('underground','underwater')))

and see what you get. Also try breaking the queries into smaller pieces and you may come by the answer on your own (see my comment).

Improve this answer
3
  • That gives me 2 records, the same ones as in query 3. I am actually trying to exclude all below ground railway platforms from the query result, that is the reason for the negation statement. There are other options I can do to prevent having to combine this in one query, but I was just confused by the query results, and want to understand them. Of course, adding negations etc., can make interpretation of results harder, I think I am not the only one who gets confused by his own statements every now and then...
    – Marco_B
    Commented Sep 3, 2014 at 15:00
  • 1
    I agree. This is a puzzler. I'm at a disadvantage not having the data. Sometimes it helps to break it down into smaller chunks. Like you said "adding negations etc., can make [things] harder". Perhaps try to go through query 1 (or query 5) in steps, changing the selection method to "select from current selection" to see if you can stumble on the reason things don't add up. I have to say I'm seeing it the way you are. The difference seems like it should be 2 records.
    – jbchurchill
    Commented Sep 3, 2014 at 15:08
  • Thanks for the comment and confirming I may be interpreting this right. There are limitations in what you can do with Definition Queries, as they only represent the WHERE clause part of the SQL statements, but I still don't get this...
    – Marco_B
    Commented Sep 3, 2014 at 16:33
-1

I have now also tried the following statement, which verifies OK in ArcGIS. It removes the last IN statement, replacing by OR. This again gives me 3051 records...

(railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT ((railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location = 'underground')) OR (railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location = 'underwater')))
Improve this answer
6
  • Did this solve it for you? Are you sure OR is what you want for this last query? This changes the nature of the query because you have taken them out of the parentheses and now the OR operators at the end can override the AND operator. This AND that OR other gets read like (This AND that) or other
    – jbchurchill
    Commented Sep 3, 2014 at 17:32
  • If you look carefully at the parentheses, you will note that (if I didn't make a mistake), that I didn't change the nature of the "AND NOT that" statement, as the OR that replaces the IN, is completely within its own parentheses, so we have something like (this AND NOT (X=A OR X=B)) instead of (this AND NOT (X IN (A,B)))
    – Marco_B
    Commented Sep 3, 2014 at 18:24
  • 1
    This should really be an edit to your question, rather than posted as an answer since it doesn't solve the problem.
    – Chris W
    Commented Sep 3, 2014 at 19:48
  • Post your data and we'll figure it out.
    – ianbroad
    Commented Sep 3, 2014 at 21:03
  • "Post your data", how do I do this? I don't see an option for uploading attachements, can I only link to something on a web server somewhere?
    – Marco_B
    Commented Sep 4, 2014 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.