Hot answers tagged

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


7

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


6

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.


5

[timeout:25][bbox:{{bbox}}]; ( way["highway"="motorway"]; way["highway"="trunk"]; way["highway"="primary"]; ); out body; >; out skel qt; See it on overpass-turbo: http://overpass-turbo.eu/s/aTL


4

I'm having difficulty with the classification of "search by attribute" as a "language". To understand an answer to this startlingly broad question requires understanding of the meanings of a number of computer terms, including: Application software, programming language, data format, user interface, and access paradigm. ArcGIS is application software. It ...


4

I believe the general outline (partly tested so far) is: Find the points representing stream sources, as in this answer. Intersect with the polygons table to get a count of source vertices by polygon. Use ST_DumpPoints in conjunction with group by geometry to get a count of each point. The idea being to get a count of how many rivers meet at a given point. ...


4

In Postgres column and table names are not case sensitive and everything is converted to lower case before executing the query, see the docs. Therefore, SELECT SomeColumn FROM SomeTable becomes SELECT somecolumn FROM sometable unless you write it as SELECT "SomeColumn" FROM "SomeTable" In my view this is messy, especially when dealing with SQL ...


4

You can enter the following python command which will create an expression based on the selected feature ids on the active layer. '$id in ({})'.format( ','.join( str(id) for id in iface.activeLayer().selectedFeaturesIds() ) ) $id in (497483783,497483784,497483785) or something that works also with persistent values but needs to be finetuned for ...


3

Just as an update to reflect the fast development in QGIS: In QGIS 2.8.2 (I don't know when it was introduced), commenting in expressions now seems to work! Using -- for one-line comments and /* ... */ for multi-line comments turnes the letters to a green color in the expression builder window and they seem indeed to be commented out! Thanks to the ...


3

You can do this easily using a regular expression match. Try using the expression: regexp_match( "ADDRESS", '^[A-Za-z].*') This will select all records where the ADDRESS field begins with an upper or lowercase letter. The second argument is a regular expression - it specifies that the string has to begin '^' with a character in the range '[A-Za-z]', and ...


3

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


3

You can use the Summary Statistics GP tool for that. Choose Shape_length field to be the Statistics field, and Locality Name field to be the case field.


3

Make sure your response is a featurecollection. You can get more information on how to do this here: http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html So you probably can get along with something like SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, ...


3

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


2

I do this with: echo [% "id" %]," [% "name" %], [% "year" %] OBSERVATION: If you have two or more point in the same local, you will get only one response. I need the next response, if someone knows how to do: id name year (for point 3) id name year (for point 3) id name year (for point 3) example: 17 Elsa 1985 ...


2

Try AND instead of OR. If you want to deselect more layers you have to use AND. OR uses either 15% or 16% http://www.postgresql.org/docs/8.2/static/functions-logical.html 'Layer' NOT LIKE '15%' AND 'Layer' NOT LIKE '16%'


2

The question is a duplicate of the one here but it may not be so obvious so let me explain. ST_Within is defined as: -- Inlines index magic CREATE OR REPLACE FUNCTION ST_Within(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Contains($2,$1)' LANGUAGE 'sql' IMMUTABLE; As you can see the function does use ...


2

I always encourage to use field delimiters to access the fields in a right way: field_name = "UNIQUEID" input_value = 5 sql_exp = """{0} = {1}""".format(arcpy.AddFieldDelimiters('c:/data/file_gdb.gdb', field_name),input_value) print sql_exp >>> 'UNIQUEID=5' Another way to do this is to use the str.format(): sql_exp = """{0} = ...


2

As far as I know, a table cannot have two geometry types (unless the type is a collection). Instead of modifying your existant table, try creating a new one: The geometry column is like any other column, it has one value for each row of the table. You are trying to create one geometry from many geometries, so what you want is not update the geometry of your ...


2

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


2

I'd suggest an alternative approach: Download raw OSM data and use OGR to prepare your data in such a way that the relevant tags are not mashed together in the "other_tags" column. This can be achieved by customizing OGR's osmconf.ini file as documented in http://www.gdal.org/drv_osm.html. Look for the lines # keys to report as OGR fields ...


2

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!


2

The alternative is the use an AND/OR statement: "Column_Name" = 'Value_1' OR "Column_Name" = 'Value_2'


2

R's Spatial classes have select/extract idioms driven by the "[" operator - for extraction by index, number, name, logical map, or spatial overlay. I think this is close to what you mean. library(maptools) data(wrld_simpl) ## generate some random points n <- 5e4 d <- data.frame(lon = runif(n, -180, 180), lat = runif(n, -90, 90), x = seq(n)) ## ...


2

For a Python script, you could call tools such as Select by location from the Python console: import processing processing.runalg('qgis:selectbylocation', input_layer, intersect_layer, u'intersects', 0, output_layer) To see what parameters are required, you can call processing.alghelp("qgis:selectbylocation"): >>> ...


2

This is mostly a SQL question that fits better to stackoverfow. First error in your SQL is that you use "GROUP BY road_name" but you have not selected "road_name". Another error is that aggregate functions like count() can't generally be used in WHERE but it must be placed into HAVING or into a subquery. See ...


2

Bingo. It involved a little witchcraft piping two ogr2ogr instructions together via STDIN/OUT, and you'll need to adapt it to your purposes, but something like this should work: ogr2ogr -f "GeoJSON" /vsistdout/ -dialect sqlite -sql " SELECT fullname, COUNT(*) as ct FROM ROADS_3857 GROUP BY fullname " "C:\xGIS\Vector\ROADS_3857.shp" | ogr2ogr -f "CSV" -where ...


1

Indeed, you could create a python addin to do this for you! http://resources.arcgis.com/en/help/main/10.1/index.html#//014p00000025000000 Basically, you would be able to create a tool that could sit in your bosses tool bar. Using the tool your boss could click the map and get the data back for the area as you described. The tool would take care of all the ...


1

Vince is right. in the jsapi this is expressed as query.orderByFields https://developers.arcgis.com/javascript/jsapi/query-amd.html#orderbyfields in REST itself, the parameter is the same: http://resources.arcgis.com/en/help/arcgis-rest-api/index.html#/Query_Feature_Service_Layer/02r3000000r1000000/


1

I found an answer with a count & group by: select c.gid, c.*, count(c.gid) from c1 join c2 on st_disjoint(c1.geom, c2.geom) WHERE c2.nom_comm ILIKE 'blabla' GROUP BY c.gid having count(c.gid) >22 ; 22 means disjoint with all features of c2.



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