Hot answers tagged

50 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 ...
user avatar
  • 7,429
32 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') Hope this helps!
user avatar
  • 72.4k
20 votes

Using "IN" in Query builder in QGIS

You could use something like: "Territory" IN ('India', 'China', 'Bhutan')
user avatar
  • 72.4k
15 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 ...
user avatar
  • 3,062
14 votes
Accepted

How to apply a filter to multiple vector layers in QGIS?

This is how I'd do it with PyQGIS: In your QGIS ToC, turn off those layers that you want to keep untouched, i.e., leave visible only the layers you want to set the filter to. Now open a QGIS Python ...
user avatar
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 ...
user avatar
  • 19.4k
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 ...
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.
user avatar
  • 3,051
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, ...
user avatar
  • 29.2k
12 votes

Selecting all features except one using QGIS?

How about "ONScode" != '17UB' != means "not equal to".
user avatar
11 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. ...
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, ...
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 ...
user avatar
  • 52k
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 ...
user avatar
10 votes
Accepted

How to extend a straight line in postgis?

I seems very similar to post "postgis, extrapolate a line". If I avoid repetition of cited post, I think you just need to extrapolate beyond your extreme points. In a query you get something like this ...
user avatar
  • 323
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.
user avatar
  • 5,685
10 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 ...
user avatar
9 votes
Accepted

PostGIS doesn't use spatial index with ST_Intersects

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, ...
user avatar
  • 13.2k
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 ...
user avatar
9 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. ...
user avatar
8 votes

PostGIS doesn't use spatial index with ST_Intersects

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 ...
user avatar
  • 2,754
7 votes

What is the precision of SELECT DISTINCT on PostGIS geometry column?

Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected? In Paul's answer, I ...
user avatar
7 votes
Accepted

How can I extract all distinct values from a PostGIS Raster?

There is an easier and more efficient way of doing this. Works for both PostGIS 2.1 and 2.0. Just use the ST_ValueCount function. http://postgis.net/docs/manual-2.1/RT_ST_ValueCount.html that will ...
user avatar
  • 10.1k
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 ...
user avatar
  • 26.3k
7 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'
user avatar
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 ...
user avatar
  • 1,283
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 ...
user avatar
  • 208
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 ...
user avatar
  • 75k
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 ...
user avatar
  • 5,777
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'
user avatar
  • 12.6k

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