As of May 31, 2023, we have updated our Code of Conduct.

Hot answers tagged

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 ...
she_weeds's user avatar
  • 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')
Joseph's user avatar
  • 74.6k
25 votes

Using IN-operator in query builder in QGIS

You could use something like: "Territory" IN ('India', 'China', 'Bhutan')
Joseph's user avatar
  • 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 ...
Bernd V.'s user avatar
  • 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 ...
Paul Ramsey's user avatar
  • 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 ...
Matthias Kuhn's user avatar
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. ...
Dennis Bauszus's user avatar
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.
Rob Skelly's user avatar
  • 3,071
13 votes

Selecting all features except one using QGIS?

How about "ONScode" != '17UB' != means "not equal to".
Alex Hajnal's user avatar
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.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 ...
Richard Greenwood's user avatar
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, ...
caiohamamura's user avatar
  • 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 ...
Spacedman's user avatar
  • 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 ...
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
  • 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"
BERA's user avatar
  • 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 ...
DPSSpatial_BoycottingGISSE's user avatar
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'
HeikkiVesanto's user avatar
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
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
  • 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 '
Louis Cottereau's user avatar
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 ...
underdark's user avatar
  • 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 ...
ndawson's user avatar
  • 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 ...
Stella's user avatar
  • 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 ...
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,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 ...
Ian Turton's user avatar
  • 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 ...
BBG_GIS's user avatar
  • 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'
Erik's user avatar
  • 15.1k

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