Use o operador “IN” sff | Please use the “IN” operator
EN | PT
It’s not the first time I see people that, to select feature by their fields values, use expressions like this:
"field" = 'value1' OR "field" = 'value2' OR "field" = 'value3' [OR ...]
A more practical and pretty way of doing this is by using the IN operator instead.
"field" IN ('value1','value2','value3'[,...])
This operator is available in almost every GIS software I know. In QGIS, it can be used even if there isn’t a small little button to click.
In fact, this is an abbreviation of what is used in SQL, where the operator is used in the WHERE statement:
SELECT *
FROM parks
WHERE "tipo" IN ('PI','CM','PJ');
Quick Tip: Using coalesce to check for NULL or zero
Here is a quick tip that you can use in QGIS expressions, T-SQL, or even PostgresSQL.
Normally if you have a column that you need query on to find all the NULL or zeros you have to do something like this:
COLA IS NULL OR COLA = 0
Well that isn’t too bad. Sure yeah it’s fine for one column but what if you have three and you need to check them all together
(COLA IS NULL OR COLA = 0) AND (COLB IS NULL OR COLB = 0) AND (COLC IS NULL OR COLC = 0)
That is pretty long and gets hard to read pretty quick.
To cut this down we can use the coalesce function – T-SQL, PostgresSQL, QGIS Expression. The coalesce function returns the first non-NULL value out of an expression, or list of values. So if you do something like this:
coalesce(NULL, "A", 0)
You will get “A” because the first value is NULL. The function will just evaluate each value/expression until something turns up that isn’t NULL.
Using that logic we can replace the above function with the following:
coalesce(COLA, 0) = 0 AND coalesce(COLB, 0) = 0 AND coalesce(COLC, 0) = 0
To me that is a lot clearer and readable.
Filed under: qgis Tagged: qgis, sql
Fast SQL Layer for QGIS
For everyone working with spatial databases in QGIS there comes a time when “Add PostGIS/SpatiaLite Layer” and “RT Sql Layer” start to be annoying. You always have to retype or copy-paste your SQL queries into the user interface if you need to change the tiniest thing in the layer’s definition.
This is where “Fast SQL Layer” can be a real time saver. Fast SQL Layer is a new plugin for QGIS by Pablo T. Carreira. It basically adds an SQL console for loading layers from PostGIS/SpatiaLite into QGIS. And it even comes with syntax highlighting!
Installation
Fast SQL Layer comes with one dependency: Pygments, which is used for syntax highlighting.
On Ubuntu, all you have to do is install it with apt-get:
sudo apt-get install python-pygments
For Windows with OSGeo4W, @Mike_Toews posted this on gis.stackexchange:
I downloaded and extracted Pygments-1.4.tar.gz, then in an OSGeo4W shell within the Pygments-1.4 directory, type python setup.py build then python setup.py install
Usage
When you activate the plugin in plugin manager, a dock widget will appear which contains the console and some fields for specifying the database connection that should be used. Then, you can simply write your SQL query and load the results with one click.
Fast SQL plugin
In this example, I renamed “gid” to “id”, but you can actually edit the values in the drop down boxes to adjust the column names for id and geometry:
A second layer loaded using Fast SQL plugin
It certainly needs some polishing on the user interface side but I really like it.
Selecting a Random Sample From PostgreSQL
Do you need a random sample of features in a Postgres table? Here is an example of how to select 1,000 random features from a table:
SELECT * FROM myTable WHERE attribute = 'myValue' ORDER BY random() LIMIT 1000;
