Tag Info

Hot answers tagged

6

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


4

ST_GeomFromText('POINT(22.256 39.569)') is not going to give you a geometry in EPSG:4326. From the documentation: There are 2 variants of ST_GeomFromText function, the first takes no SRID and returns a geometry with no defined spatial reference system. The second takes a spatial reference id as the second argument and returns an ST_Geometry that ...


4

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


4

You can build queries by using the Query Builder or an alternative is to use the Expression string builder (Layer Properties > Style > select Rule-based > add a rule > insert your command in the Filter box or click the '...' button to bring up the string builder interface. In both cases, I do not think QGIS supports the comments function when creating ...


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

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


3

Let me respectfully disagree, but I don't think that the Hausdorff distance function is the appropriate way to snap a GPX track to a street network. Rather you should be looking into methods for map matching, e.g. Different approaches for map matching : links / ideas?


3

There are various forms of ST_Value and the one that will probably help you is the ST_Value(rast, band, x, y) one. Modifying one of the examples from the docs, you can dump all the values for each pixel using generate_series in the x and y direction, and then use group by (or distinct) on those to get the list of unique pixel values. SELECT count(b1val), ...


3

Here is a piece of code based on @artwork21's proposed workflow. It uses arcpy.da cursors so you need to have at least a 10.1 license. I've assumed the fields have the following format: height: numeric area_id: numeric or text (see comments in code) highest: numeric (short) import arcpy points = r"D:\test.gdb\points" ## make a list of unique area_id ...


3

I would recommend to use python for this, the general code flow would go something like this: Use arcpy.da.search cursor to iterate through AREAid column Nest another arcpy.da.search cursor (with sql expression of AREAid value to filter by) to get HEIGHT values and use a max() method on the cursor to get max value (e.g. highValue = max(cursor)) Within ...


3

ST_StartPoint is the correct function to find single nodes at the start of a Linestring, however it does not work with MultiLinestring, so you will need to use ST_Dump to get the constituent Linestrings. If I have understood you question correctly, you then want all start points which are not also end points for more than one line, ie, points where two ...


2

The reason your code was failing was you had multiple "if" statements without an "end if" for each of them. It looks like you wanted "elseif" for the second, third, and fourth checks. Function FindLabel ( [_Seq1_TractDepthSequence], [_Seq1_BegInterval] ,[_Seq1_EndInterval], [_Seq1_DeckCorpRITot], [_Seq1_DeckCorpORRITot] , [_Seq1_DeckCorpIntTot] , ...


2

Use "LIKE" with percentages. In my shapefile of countries I can select any country with a name like "This and that" with: "CNTRY_NAME" LIKE '% and %' this selects only "Bosnia and Herzegovina". If I remove the spaces: "CNTRY_NAME" LIKE '%and%' I get Iceland, England and all the others with "and" in them. That all works for shapefiles - the help for ...


2

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


2

The below function returns the number of alpha characters in a string: def NumAlhpa(fieldName): return len([s for s in fieldName if not s.isdigit()]) you can easily integrate this into the Field calculator: YourField = NumAlpha( !yourColumnName! )


2

I would suggest you avoid procedures that are documented as being "legacy" when working with new databases. Cross-database queries in SQL-Server are inefficient, and should be avoided as general practice, but if you really want to create such a view, the table should have a native geometry type (GEOMETRY or GEOGRAPHY). Then all you need to do is register ...


2

I am going to write this in VB.Net since that is what I know, and hopefully you can figure out the C# way to do it: To find all the layers, use IEnumLayer interface: Dim pEnumLayer As IEnumLayer = My.ArcMap.Document.ActiveView.FocusMap.Layers(Nothing, True) The second argument determines if this drills down into composite layers. To get the layer from ...


2

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this: SELECT A.* FROM osm_addr2 AS addr, osm_addr2 AS POI WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry); There's a relevant example in the manual (last two SQL examples), where a subquery is ...


2

If I got you right, you can achieve that by running a Python script. You can follow this workflow: Open QGIS, load a vector layer and select it (activate it) in the ToC. Observe this function: def selectByExpression(): cLayer = iface.mapCanvas().currentLayer() expr = QgsExpression( "\"COD_DANE\"=13" ) it = cLayer.getFeatures( ...


2

In the absence of knowing the error messages are you getting, here's my take on some initial improvements -- I'm new to this too. SELECT * FROM mytable,node WHERE node.tags @> ARRAY['subway', 'station', 'uic_ref'] and ST_DWithin(geom, ST_SetSRID(ST_MakePoint(mylon, mylat),3857), radius)


2

Okay, I found out why the lat lng data was that strange in this case. For importing the data with osm2pgsql in slime node the additional table named planet_osm_nodes is getting generated and that was the one I used. Without the additional parameter -l in the import statement the coordinates are getting projected in the Sperical Mercator projection EPSG:3857. ...


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

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

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

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


1

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan. SELECT * FROM osm_addr2 AS addr, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...


1

I recommend two Queries, since the results from focnetworkpart and node are not related in anyway (at least according to your desciption). The Problem you will or already ran into is the combination of both results. So you are better of first asking one table for results and then the other. Then you can work with both results. Also be aware that ST_DWithin ...


1

Found the solution. Albeit without SQL style joining, but this structure operates in the same way and is clearer so it is preferable. Double click on the layer you wish to join extra attributes to; Click on 'Joins' on the left; Click the '+' symbol at the bottom to add a new join to the double clicked layer; Select the join layer (layer you wish to take ...


1

You could get the geometry by joining your query result's id2/edge with your layers's geometry ID (gid): SELECT s.seq, s.node, s.edge, s.cost, b.gid, b.geom FROM ( SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra(' SELECT gid AS id, source::integer, target::integer, ...



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