62
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 ...
- 10k
40
votes
Accepted
QGIS Filtering Multiple Values using Query Builder
One method is to use the IN function followed by your list of values all inside brackets.
E.g.
"Column_Name" IN ('Value_1', 'Value_2', 'Value_N')
- 74.6k
25
votes
Using IN-operator in query builder in QGIS
You could use something like:
"Territory" IN ('India', 'China', 'Bhutan')
- 74.6k
16
votes
Accepted
Is it possible to use comments in QGIS Query Builder with ESRI shapefiles?
It doesn't work with ESRI shapefile. However, commenting in expressions works in QGIS 2.14 with SpatiaLite, SQlite and GeoPackage. For most other formats available in the Save as...-dialog, the query ...
- 3,197
14
votes
Accepted
PostGIS - ST_Within or ST_Disjoint performance issues
Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to ...
- 19.7k
14
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 ...
- 27.3k
13
votes
Identifying duplicate attributes in field using QGIS
This is a good question onto which I just stumbled myself. I don't like any of the answers which have been given so far. I have a valid dataset with unique IDs that are non sequential and non integer. ...
- 2,257
13
votes
Accepted
Distance in meters between two geographic coordinates
Your coordinates are out of order. If you reverse the order of coordinates in the first query, postgis says: 708.55982691.
In postgis it's lon, lat, not lat, lon.
- 3,071
13
votes
Selecting all features except one using QGIS?
How about "ONScode" != '17UB'
!= means "not equal to".
- 748
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.5k
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
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,021
11
votes
Accepted
How to build a query/expression to select specific points within a buffer? (in QGIS)
This can be done using the Processing Graphical Modeler to set up a custom chain. Here's a pic:
From left to right we have three inputs - the tree layer (a Vector Layer Input Object), the buffer size ...
- 59.4k
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 ...
- 29.7k
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.
- 6,921
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"
- 63.9k
9
votes
Accepted
SQL Server Spatial Querying
If you've got a polygon you want to use as a declared variable and intersect it with a table containing existing geometry, your query (including your polygon variable declaration) would look something ...
9
votes
QGIS Filtering Multiple Values using Query Builder
The alternative is to use an AND/OR statement:
"Column_Name" = 'Value_1' OR "Column_Name" = 'Value_2'
- 16.1k
8
votes
Can you use SQL in QGIS?
You can use Db Manager -> Virtual Layer to use SQL (SpatiaLite) in any type(datasource) of layer.
- 806
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 ...
- 4,721
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 '
- 2,540
7
votes
Accepted
How to search within a string to extract data from OSM other_tags?
I'd suggest an alternative approach: Download raw OSM data and use OGR to prepare your data in such a way that the relevant tags are not mashed together in the "other_tags" column. This can be ...
- 83.6k
7
votes
Accepted
Selecting entities based on first character of field being a letter using QGIS?
You can do this easily using a regular expression match. Try using the expression:
regexp_match( "ADDRESS", '^[A-Za-z].*')
This will select all records where the ADDRESS field begins with an upper ...
- 27.1k
7
votes
Accepted
Selecting more than 1,000 features from AGOL hosted feature service?
Log in to www.arcgis.com with administrator credentials.
Click My Content, and navigate to the intended feature service. Open the feature service.
If the error, "There are no services available" is ...
- 1,300
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,329
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 ...
- 79.1k
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,805
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'
- 15.1k
Only top scored, non community-wiki answers of a minimum length are eligible