Hot answers tagged

29

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


19

Building a query expression. Check the Combining Expressions sub-heading to see how to use the AND/OR operators correctly. For your example you should implement "IN": "Name" IN ('Chatham','Chester')


12

Use "Name" IN ('Chatham','Chester Falls'...) Separate each with a comma and end with a closed parenthesis.


12

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


11

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


10

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


10

First off, yes you will definitely want to make sure your primary and foreign key fields are indexed on both tables. This lets the DBMS plan and execute queries against these fields much more efficiently. Secondly, you are calling SelectLayerByAttribute_management in a tight, nested loop (once per tree per treatment). This is highly inefficient, for several ...


10

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


9

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


9

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.


9

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


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

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

Underdark has composed some nice examples for labeling expressions: Easier Conditional Labels in QGIS Here are some helpful links for querying: Query Attributes Working with Attribute Table - Basic Queries If you are interested in coding then you can start with this resource: pyQGIS Developer Cookbook


8

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


7

This is a problem coming from that ST_Intersects has no tolerance. Even if double precision coordinates holds a lot of decimals they form a grid where the only places for the points is in the crossings. Often the line doesn't intersect with any of those crosses and there is no way any point will intersect the line exactly. The workaround is to use st_dwithin ...


7

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


7

Some useful stuff here for 1.8 and older http://hub.qgis.org/projects/quantum-gis/wiki/How_do_I_do_that_in_QGIS http://hub.qgis.org/wiki/quantum-gis/Calculating_field_values http://hub.qgis.org/wiki/quantum-gis/List_of_Field_Calculator_Functions


7

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


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

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

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


6

You can also use the IN (includes). Like... Tenement IN ('E59/1002', 'E59/1421') Tenement NOT IN ('E60/1002', 'E60/1421')


6

To answer the first part of your question, I think it helps to look at the additional text in the Creating Attribute Indexes help file about Multi-column indexes. The order in which fields appear in a multicolumn index is important. In a multicolumn index with column A preceding column B, column A will be used to conduct the initial search. Also, ...


6

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


6

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


6

When creating a spatial index on a table it is important to run "vacuum analyze <table>" after that. For finding nearest points you can use operator <-> introduced in PostGIS 2.0. It actually gives you the distance between two points. More info can be found here: http://workshops.opengeo.org/postgis-intro/knn.html SELECT id FROM nodes ORDER ...


6

No, probably not. I'm going to assume from the coordinates of your point that you are working in longitude/latitude coordinates, but that you want to express your distances in meters. Rather than building a real "circle", recognize that for the purpose of a true/false test you can express the query as a distance calculation. SELECT routes.* FROM routes ...


6

You can use the join method: selectionQRY = "RTE_1_NBR <> '" + "' AND RTE_1_NBR <> '".join(userSelectionList) + "'" A string will be created, in three parts. The middle part is the key, where code of the form string.join(list) will take all objects in the list, convert them to strings, and then append them all together, with the specified ...


6

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



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