Hot answers tagged

50

Have you tried replacing the "=" with "is"? I don't have QGIS in front of me, but that is pretty common database select syntax for working with NULL values. pedonal is NULL


22

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

Another graphical, dynamic and most importantly simple way to detect duplicate attributes: use QGIS's expression builder. Highlight duplicates in attribute table: Enable Conditional Formatting (see red arrow below) with the following condition: count("FieldWithDuplicates","FieldWithDuplicates") > 1 To group all duplicates at the top, right click the ...


16

You could use something like: "Territory" IN ('India', 'China', 'Bhutan')


15

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


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

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

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.


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

First, make sure you have an index on your geography column. It will speed up the spatial searches: CREATE INDEX geo_cities_geog_idx ON geo_cities USING GIST geog; VACUUM ANALYZE geo_cities(geog); Then, you can use ST_DWithin (with conversions from miles to metres) on a self-joined query: SELECT gc.*, ST_Distance(gc.geog, pt.geog)/1609.344 AS ...


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

$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";


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

Use the methods available on the ISQLSyntax interface to make your code workspace-independent. As the help on IQueryFilter.WhereClause explains, use the ISQLSyntax.GetSpecialCharacter method to return the delimited identifier prefix and suffix specific to your workspace and add them to your column identifiers. Example: ISQLSyntax sqlSyntax = (ISQLSyntax)((...


11

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 ...


11

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 ...


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 ...


10

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 ...


10

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 ...


8

You can preatty easily run a search by combining WFS anc CQL like in the following example: layerQueryRun: function() { var wfs = new OpenLayers.Protocol.HTTP({ url:WMSCONFIG.wfs_server_path+"?service=wfs&version=1.0&request=GetFeature&typename=" + youLayerTypeName, format: new OpenLayers.Format.GML.v3({}) }); // ...


8

What you should do is put your big multipolygon in a table as single polygons (with ST_Dump) and put an index on it. Something like: CREATE TABLE big_polygon as SELECT (ST_Dump( ST_GeomFromText('a multiypolygon geom goes here',4326))).geom as geom; -- It is always great to put a primary key on the table ALTER table big_polygon ADD Column gid serial PRIMARY ...


8

All you need to do is change AND to OR. Because AND is a valid logical operator the validation passes. So technically the query is correct, it just will not return any records.


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( ...


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