67
votes
Identifying duplicate attributes in field using QGIS
Another graphical, dynamic and most importantly simple way to detect duplicate attributes: use QGIS's expression builder.
Highlight duplicates in attribute table:
Suppose FieldWithDuplicates is the ...
- 12.7k
26
votes
Using IN-operator in query builder in QGIS
You could use something like:
"Territory" IN ('India', 'China', 'Bhutan')
- 76.1k
16
votes
Accepted
QGIS query syntax differs between attribute table and feature filter
Your observation is correct, The Query Builder does not use the same syntax as the rest of QGIS.
Throughout QGIS, the syntax is based on QGIS Expressions a customized SQL dialect. This is portable ...
- 28.1k
13
votes
Selecting all features except one using QGIS?
How about "ONScode" != '17UB'
!= means "not equal to".
- 758
12
votes
Identifying duplicate attributes in field using QGIS
I'm wondering why didn't anybody recommend to use only the Select By Expression tool with the query:
COUNT(1, "attribute")>1
Then all features with repeated attribute will be selected, ...
- 1,041
12
votes
Accepted
ArcGIS query specific character(s) in specific part of a field
An underscore _ is used in some databases as a single character place wildcard. A percent % is wildcard for all character places.
Therefore, something like "FIELD1" LIKE '_____00%' should work, ...
- 29.9k
12
votes
Accepted
Multiple geometry types in one column not recognized
I'll answer my own question but credit goes to user30184 for giving me a couple clues. The problem seems to be that the first few thousand rows in my table are LineStrings and apparently QGIS isn't ...
11
votes
Accepted
Querying Esri File Geodatabase?
Personal geodatabase are great because they can be accessed outside of ArcGIS. But they are slow and, in practice, the performance starts degrading after they get larger than 400-500MB in size. If you ...
- 30k
11
votes
Saving Overpass query results to GeoJSON file with Python
You could do this using overpass that already returns a geojson object and then, if you want, you can use the geojson package to convert this object into str (string object) or save it in a file.
...
- 211
10
votes
Where is Query Builder in QGIS 3.0?
In QGIS3, the query builder button is located in the Layer Properties dialog, then in the Source tab, and finally in the bottom right corner of Provider feature filter.
- 7,387
10
votes
Accepted
QGIS - how to label values of two different polygons in one polygon setting
You can use overlay_within:
'State ' ||
array_first(
overlay_within(layer:='State', expression:=Statename)) || '\n' ||
'District ' || "Districtname"
- 76.3k
9
votes
Can you use SQL in QGIS?
You can use Db Manager -> Virtual Layer to use SQL (SpatiaLite) in any type(datasource) of layer.
- 788
9
votes
Can you use SQL in QGIS?
You can use Execute SQL tool.
Select/add data source(s)
Use SQL query using input1 instead of layer/datasource name.
The tool returns a layer containing the query result.
- 77.8k
8
votes
Accepted
QGIS point layer: Select first point in each category
You can use select by expression with this expression - or you can use the expression as a condition in a larger expression:
time = array_min (array_agg ( time, group_by:=category))
time and category ...
- 73.8k
8
votes
Accepted
Using eval function in Virtual Layer in QGIS
This is an interesting question that has piqued my interest. As I'm working in QGIS Windows, I set SPATIALITE_SECURITY=relaxed in the global Windows environment.
After many trials and errors, I found ...
- 6,038
8
votes
Replace single and double quotes with QGIS expressions
you can use this expression:
replace(replace("fieldname", char(34), ''), char(39), '')
char(34) corresponds to the double quote "
char(39) corresponds to the simple quote '
- 3,929
7
votes
Accepted
PostGIS / OSM: Faster query to find nearest line of points
Would you be able to add the result of putting "EXPLAIN ANALYZE" before the query in your question? Then I can update my answer with suggestions.
Of course, you will need GIST indexes on your table's ...
- 208
7
votes
Accepted
Why my query returns record type instead of original data type in postgresql?
You perform st_makeline/st_astext on the whole subquery which you named the_geom. (the result of a subquery is of type record).
But you just want one column of your record, so you should name the ...
- 6,549
7
votes
Accepted
Using the logical operator 'AND' in the query builder
In this case, you would want OR instead of AND.
If you read the expression back to yourself you can see that "when the legend equals dual carriageway and legend equals single carriageway" is ...
- 82.7k
7
votes
Accepted
Removing a number from attribute column
Create a new Field with Text format
on the new field use Field calculator and select python .
double click the field that has numbers.
Type [1:] after field name .
For example if your field name ...
- 5,825
7
votes
Selecting all features except one using QGIS?
Easy way: Select '17UB' manually, then invert the selected features.
By code: "ONScode" NOT LIKE '17UB'
- 16.8k
7
votes
QGIS - how to label values of two different polygons in one polygon setting
Combining the answer from BERA with HTML formatting
we can achieve both:
Values from a second layer with overlay_within
Different color for each line
As follows:
'State: ' ||
array_first(
...
- 3,177
6
votes
QGIS 2.18 Training Manual Exercise 1.2.1 - Query Builder Problem
Good catch! I have to wonder why NULL is inside double-quotation marks in the linked Training Manual. (Just a typo, maybe. Associated image shows single-quotations.)
Please try with single-quotation ...
- 31.1k
6
votes
Accepted
How to make projected geometry and update it to postgresql?
As user30184 outlined:
It's a very common task, and there's plenty of documentation: ST_Transform.
To apply this, you need to figure out the EPSG codes of your projections.
UTM 35N probably is EPSG:...
- 1,777
6
votes
Accepted
Using wildcard to find similar values between two fields with ArcPy cursor
Firstly you CAN do a wild card search of your data with a select by attribute tool, I created a short blog over on ESRI Geonet which you should read.
In your case the expression would be:
FullStreet ...
- 44.2k
6
votes
Accepted
Selecting all features from two polygon layers in one Virtual Layer in QGIS
You should union the 2 selects like:
SELECT b.geometry
FROM building b
UNION
SELECT p.geometry
FROM plot p;
- 11.7k
6
votes
Matching colour of point to colour of polygon based on shared attribute using QGIS
For the color, set a data-driven override > Assistant (see left screenshot). In the opening dialog panel (right screenshot), for the source select the code attribute, load Values from/to with the ...
- 73.8k
6
votes
Accepted
Replace single and double quotes with QGIS expressions
Use regular expressions with regex_replace() function. The single quote ' has to be masked by a backslash: \'. Use this expression to get rid of single ' and double quotes ":
regexp_replace (...
- 73.8k
5
votes
OSM: Getting the correct Overpass Turbo query using a keyword (and values)
In overpass turbo, click on "Wizard", enter tourism=* in the search field and then click on "build and run query" to execute your query.
You may of course navigate to your area of interest and hit ...
- 4,647
5
votes
How to filter features in a layer?
Tested on QGIS 3.10.2-A Coruna
How to apply a filter
* Right-click on the layer listed in panel Layers
* Choose Filter...
* The window Query Builder is displayed
How to build a query in Query ...
- 241
Only top scored, non community-wiki answers of a minimum length are eligible