2

I have two attributes that contain a value of ' ', among others, which are not selected when I apply an SQL statement in my python script. Trying the same statement in ArcMap does result in them being selected.

I have an attribute "BYGNSTYPE" which has as distinct values:

  • ' ' <-
  • 'Bygning' <-
  • 'Drivhus' <-
  • 'Ikke tildelt'
  • 'Pelsdyr Opdræt' <-
  • 'Tank/Silo'

The values with an arrow next to them are the ones I want. When I use the WHERE clause: BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo', ArcMap returns: 4,517,327 records.

When I apply the clause in my script using GDAL it returns 4,514,343 records.

sql =  "SELECT * FROM BYGNING WHERE BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo'"
selectData = inData.ExecuteSQL(sql)
print sql
if selectData is None:
    print 'No data is selected...'
    sys.exit(1)
else:
    print 'Finished selecting data'
print "Selected features: %s " % selectData.GetFeatureCount()

When providing the oposite statement it also does not select records where BYGNSTYPE = ' '. How can I select ' ' values in Python GDAL using SQL?

FIXED:

Adding 'OR BYGNSTYPE IS NULL' to the SQL statement in Python/GDAL results in the same records being selected.

  • 1
    First I would test with AND BYGNSTYPE is not null and next by using the SQLite SQL dialect. – user30184 Mar 9 '17 at 15:26
  • I tried it, but I get the same result. – Tins Mar 9 '17 at 15:42
  • Make a small dataset with about ten records, test that you can reproduce the issue and put it somewhere for download. – user30184 Mar 9 '17 at 15:44
  • I made a subset of 30 polygons containing the same distinct values as above. However, the same issue comes up. GDAL does not select ' ' values. The data can be found here: www55.zippyshare.com/v/AkzfUVQG/file.html. – Tins Mar 9 '17 at 16:09
  • 1
    Actually, perhaps OR IS NULL could do it. I can't test myself right now. – user30184 Mar 9 '17 at 16:41
2

In SQL language NULL has a special meaning and OGR seems to behave correctly when it does not include NULLs when you make a search as "something else than 'Ikke tildelt' or 'Tank/Silo'. You must explicitly add NULL values to the query as

SELECT * FROM BYGNING WHERE (BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo') OR BYGNSTYPE IS NULL;

ArcMap seems to deal with NULLs in a different way which is OK if it does not claim that it is using SQL. See reference https://www.w3schools.com/sql/sql_null_values.asp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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