41
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 attribute containing duplicates ( TreeID in the screenshot below).
Enable Conditional Formatting (see red arrow below) with the following condition:
count(1,&...
30
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!
20
You could use something like:
"Territory" IN ('India', 'China', 'Bhutan')
18
I'm surprised it's quite so coarse, but there it is. It's not DISTINCT, per se, it's the '=' operator, which is defined for geometry as 'equality of the index keys' which means practically 'equality of the 32-bit bounding boxes'.
You can see the same effect just using '=' directly,
select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0....
15
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 fails with an OGR-error.
Use -- for one-line comments
and /* ... */ for multi-line comments
It turns the letters to a green color in the expression builder ...
14
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 console and paste this code:
layers = iface.mapCanvas().layers()
for layer in layers:
layer.setSubsetString('"osm_user" = \'donlaser\'')
This should do ...
13
$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";
13
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 be found, thus:
SELECT pts.*
FROM pts LEFT JOIN polys
ON ST_Contains(polys.geom, pts.geom)
WHERE polys.id IS NULL;
The un-matched rows in a left join are ...
13
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.
13
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 between almost all parts of QGIS where you can enter a filter, calculate a value... This syntax is parsed and evaluated directly in QGIS (and parts of it may be ...
12
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, depending on the type of database. That would use five character wildcards, then your two zeros, then another wildcard for the rest of the string.
12
How about "ONScode" != '17UB'
!= means "not equal to".
11
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. The problem is that the dataset contains single geometries but some boundaries are multi geometries in nature. My task is to identify and union these ...
11
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 (a Numeric Input) and the tree height (a String Input). The tree layer is then selected by its height attribute. The selected trees are then buffered by the ...
11
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 can - do migrate to the file geodatabases.
There are many ways you can query your data using SQL-like syntax:
Select By Attributes geoprocessing tool or a ...
10
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.
10
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 examining all of the rows and finding the other geometry types in the table.
The solution, at least in QGIS 3.18.2, is to check the "Don't resolve type of ...
9
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, you can then filter, save the selection or do whatever you need to.
9
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 should work:
SELECT ST_MakeLine(ST_TRANSLATE(a, sin(az1) * len, cos(az1) *
len),ST_TRANSLATE(b,sin(az2) * len, cos(az2) * len))
FROM (
SELECT a, b, ...
9
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 like this:
(MSSQL Server syntax)
declare @polygon geometry = 'POLYGON((-9486683.581 4810152.256, -9282073.762 4821688.121, -9262037.786 4625578.413, -9477576....
answered Aug 14 '15 at 19:30
DPSSpatial_BoycottingGISSE
17.7k3 gold badges56 silver badges99 bronze badges
8
If you want to test whether some points (or shapes, etc) fall within a given distance of a given location, use the geographic version of ST_DWithin().
From http://www.postgis.org/docs/ST_DWithin.html:
boolean ST_DWithin
(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);
Thus,
SELECT
ST_DWithin(
...
8
The docs specify that ST_Split returns a collection of geometries. You can confirm this using some test data:
WITH rect AS (SELECT 'POLYGON ((0 0, 10 0, 10 1, 0 1, 0 0))'::geometry as geom),
line AS (SELECT 'LINESTRING (3 0, 3 1)'::geometry as geom)
SELECT ST_GeometryType(ST_Split(rect.geom, line.geom)) FROM rect, line
-- returns '...
8
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, will cause the indexes to be used and avoid a full table scan.
SELECT *
FROM
osm_addr2 AS addr,
(SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...
8
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 geometry, geom2 geometry)
RETURNS boolean
AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)'
LANGUAGE 'sql' IMMUTABLE;
And so the query gets rewritten to use the ...
7
There easily might be multiple queries that return the same result. Using the following data:
FID STNAME DIR
0 BLUE ST N
1 BLUE ST S
2 BLUE ST N
3 BLUE ST S
4 BLUE ST S
5 BLUE ST S
6 BLUE ST W
7 BLUE ST W
8 BLUE ST W
9 BLUE ST W
10 BLUE ST E
All these queries return the same thing:
"FID" < 6
"FID" < 6 AND "STNAME" = 'BLUE ST'
"...
7
This is listed in the pyqgis cookbook.
PyQGIS Developer Cookbook 2.18
PyQGIS Developer Cookbook 3
You need to make use of the optional where clause.
uri = QgsDataSourceURI()
# set host name, port, database name, username and password
uri.setConnection("localhost", "5432", "dbname", "johny", "xxx")
# set database schema, table name, geometry column and ...
7
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 proposed using SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); but MAX() is slow, apparently requiring a table scan.
Instead, I found this to be ...
7
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 give you both the pixel value and number of pixels that have that value.
So would be for your case
SELECT DISTINCT (pvc).value
FROM (SELECT ST_ValueCount(...
7
The alternative is to use an AND/OR statement:
"Column_Name" = 'Value_1' OR "Column_Name" = 'Value_2'
7
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 returned when visiting the ArcGIS REST Administrator Directory, the hosted feature service is shared with 'Everyone' (public).
In the item details page of the ...
Only top voted, non community-wiki answers of a minimum length are eligible