29
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("...
26
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!
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....
16
All spatial table references are held in the geometry_columns metadata table. So try:
select * from geometry_columns
and you should get just the spatial tables
16
You could use something like:
"Territory" IN ('India', 'China', 'Bhutan')
15
Use Group Stats plugin and set the ID as a field classification.
You can see how many times each value has been entered in 'count' column.
14
A quick (although inelegant) way to do this is to go into Layer properties, select Style - Categorized using the column that you're interested in. Apply this, then right click on the layer in the layers window and check the Show Feature Count checkbox. Then expand the layer in the layers window and you can immediately see how many times each value has been ...
13
This illustration stuck with me, and helps me remember at the most basic level what precision vs. accuracy is.This is the source of the image, also containing a little more context. In general, Precision is the how close your grouping of measurements are. Accuracy is how close your measurement is to the actual measurement in the real world.
Blah238 is right,...
13
$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";
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.
12
This is an exciting question! How big is the raster you want to query? WKTRaster is stored in the database as a BLOB. In order to find the value at a specific point, from a known (x_0, y_0) corner coordinate row/column indices (i, j) are computed using (dx, dy) steps and rotation. With (i, j) known, the ST_Value() function can access the actual data at the ...
12
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 ...
12
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 ...
12
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
In QGIS you should be able to filter your data with an SQL expression in the Query Builder dialog. Just right click your layer in the table of contents and select "Query" ("Filter..." in the more recent versions).
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 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 ...
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 ...
9
Maybe something like this (I'll assume you have some primary key column "id" in each table):
SELECT A.id, A.code AS Code, A.sign AS Sign, B.id,
ST_Distance(A.geom, B.geom) AS Distance
FROM Table_A AS A, Table_B AS B
WHERE A.id IN (
SELECT X.id
FROM TableA as X, TableB as Y
-- Here's the important part: refer to the A table **outside** of the ...
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....
9
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.
8
If the IDs are consecutive, I would add a new temporary column with unique values like @Ship.shp suggested and then use the query builder to search for ID != uniqueID.
That would return the duplicates directly. After fixing the original IDs, remove the extra column or repeat the whole process as needed — it is not clear what kind of pattern your IDs must ...
8
WITH state as (SELECT geom FROM States
WHERE name = 'Oklahoma'
)
SELECT States.* FROM States,state WHERE ST_Touches(state.geom, States.geom)
8
Like so,
CREATE INDEX mytable_gix ON mytable USING GIST (Geography(ST_MakePoint(lon, lat)));
SELECT * FROM mytable
WHERE ST_DWithin(
Geography(ST_MakePoint(lon, lat)),
Geography(ST_MakePoint($qlon, $qlat)),
$radius_meters
);
Edit: If you have your data already in geometry points, but want to do a geography-style query:
CREATE INDEX ...
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 '...
7
You can use the set null tool in the spatial analyst toolbox to assign any cell values outside your desired range to null values. The result will be a new raster layer with only the cell values you wish to preserve.
The expression you should use will be along the lines of:
Value > -117 AND Value < -69
and the false raster should be the same as your ...
Only top voted, non community-wiki answers of a minimum length are eligible
