66 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 ...
she_weeds's user avatar
  • 12.5k
26 votes

Using IN-operator in query builder in QGIS

You could use something like: "Territory" IN ('India', 'China', 'Bhutan')
Joseph's user avatar
  • 75.8k
15 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 ...
Matthias Kuhn's user avatar
13 votes

Selecting all features except one using QGIS?

How about "ONScode" != '17UB' != means "not equal to".
Alex Hajnal's user avatar
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, ...
caiohamamura's user avatar
  • 1,031
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, ...
Midavalo's user avatar
  • 29.7k
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 ...
Richard Greenwood's user avatar
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 ...
Spacedman's user avatar
  • 63.9k
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 ...
Alex Tereshenkov's user avatar
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. ...
carpinchosaurio's user avatar
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.
etrimaille's user avatar
  • 7,250
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"
BERA's user avatar
  • 72.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 ...
Babel's user avatar
  • 71.2k
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.
Marco Reliquias's user avatar
8 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.
Kadir ┼×ahbaz's user avatar
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 ...
christoph's user avatar
  • 5,645
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 '
Kalak's user avatar
  • 3,848
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 ...
Tyler's user avatar
  • 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 ...
pLumo's user avatar
  • 6,489
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 ...
Ian Turton's user avatar
  • 81.6k
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 ...
BBG_GIS's user avatar
  • 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'
Erik's user avatar
  • 16.3k
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( ...
Cushen's user avatar
  • 2,928
6 votes
Accepted

Change entire label style after certain extent

Just figured it out. In the layer properties, go to the "Labels" tab, and set the Method to "Define classes of features and label each class differently". I set two different classes ("Default" and "...
Alex92's user avatar
  • 461
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 ...
Kazuhito's user avatar
  • 30.8k
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:...
Senshi's user avatar
  • 1,767
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 ...
Hornbydd's user avatar
  • 43.4k
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;
eurojam's user avatar
  • 10.8k
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 ...
Babel's user avatar
  • 71.2k
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 (...
Babel's user avatar
  • 71.2k

Only top scored, non community-wiki answers of a minimum length are eligible