32
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("...
28
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....
18
You could use something like:
"Territory" IN ('India', 'China', 'Bhutan')
14
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 ...
13
$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";
13
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
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
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
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
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 ...
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.
8
WITH state as (SELECT geom FROM States
WHERE name = 'Oklahoma'
)
SELECT States.* FROM States,state WHERE ST_Touches(state.geom, States.geom)
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 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, ...
7
1) Select your polygon of interest - you can do this manually or with one of the selection tools.
2) Select your 'marginal' points using the Select By Attributes tool with the Method drop-down set to 'Add to current selection'.
You will now have a selected polygon in one layer and selected points in another layer.
3) Use the Select By Location tool to ...
7
Two more ideas:
1) The read-only Overpass API. I don't know that it'll work for the whole US in one pass, but maybe if you extend the time out enough...? For a smaller area (.5 degree max) and with the benefit of a (minimal) UI, you can access Overpass via the XAPI Query Builder. There, you can put amenity=hospital in the tag search, select your area, ...
7
Vector->Geoprocessing Tools->Dissolve does work for lines and will return a multi-geometry where the lines do not have coincident vertices.
Yet another option is available from PostGIS or SpatiaLite using the ST_LineMerge function.
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
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
