I can't understand why the following NOT LIKE
query removes not just the ones that end in '% Lake'
but blank records too for certain data formats within both PostgreSQL with PostGIS and QGIS. This happens to the original FileGDB and my converted PostGIS table but works when the dataset is converted to Shapefile format.
I first noticed this when trying to run the query on the PostGIS table from pgAdmin4 and psql but also tried the from DBManager (in QGIS)
SELECT * FROM fwa.fwa_assessment_watersheds_poly
WHERE "gnis_name_1" NOT LIKE '% Lake'
So I tested in QGIS as a definition query.
# FileGDB
# 11252 records returned (incorrect)
"GNIS_NAME_1" NOT LIKE '% Lake'
# PostGIS
# 11252 records returned (incorrect)
"gnis_name_1" NOT LIKE '% Lake'
# Shapefile
# 19358 records returned (correct!)
"GNIS_NAME_" NOT LIKE '% Lake'
You can see in the QGIS image below that the PostGIS dataset in orange removes additional blank records when on top of the Shapefile in light brown.
The source dataset is available at ftp://ftp.geobc.gov.bc.ca/sections/outgoing/bmgs/FWA_Public/FWA_BC.gdb.zip - 2.3 GB
I've uploaded some additional test data for testing at a smaller size - the first two are the entire datasets and the last two are subsets:
fwa_assessment_watersheds_poly.gdb.zip - 116.13 MB
fwa_assessment_watersheds_full.shp.zip - 95.47 MB
fwa_assessment_watersheds_selection.shp.zip - 2.38 MB
fwa_assessment_watersheds_selection.gdb.zip - 3.24 MB
System info:
Windows 10 64-bit
PostgreSQL 9.6.1
PostGIS 2.3.1
QGIS 2.18.1
Tried from ArchLinux with same software versions as above.
NULL
cannot be compared in aLIKE
orNOT LIKE
statement, so will always be excluded. To specifically include them you'd need to use something like"gnis_name_1" NOT LIKE '% Lake' OR "gnis_name_1" IS NULL