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

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

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

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


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

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

Overpass does not have this feature now. In Overpass Turbo You can export the data in geojson format and then open it in http://geojson.io to remove unwanted columns.


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

You can do this with a SQL Statement in MapInfo either thru the SQL Select dialog or thru the MapBasic window. If the polygons in your development table overlap, you might need to combine these to find the correct percentage, that is avoiding a common area to get calculated more than once in your percentage. If you need to combine the development objects ...


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

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

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


1

Following the 2nd example from ST_MakeLine, you could join the nodes from your routing output, to nodes representing a Postgis point, and use this to create a line, something like: SELECT ST_MakeLine(gm.the_geom ORDER BY rt.seq) As route_geom FROM route rt inner join geometry gm on rt.node = gm.node ; where route is the output from pgRouting and geometry ...


1

Here, "Fire.isochrones_2002_BigDesert_21122002.OBJECTID" is interpreted as a single field name. What are the different attributes? : Fire, isochrones_2002, OBJECTID? The problem is probably coming from the dots in the field name.


1

This is almost not a GIS question, but you will need to aggregate your geometries. Right? Thought so. The following query does not use a subquery as I thought would be required earlier. You will simply GROUP BY the value columns. All columns not being grouped will need to be aggregated, and that's where the SpatiaLite function GUnion comes in. Note that ...


1

The query you need: SELECT a.*, e.ensemble_id FROM address as a, ensemble as e WHERE ST_Within(a.geom_point, e.geom_polygon)


1

As @AlexTereshnekov commented, the problem may be caused by your spatial data storage choice. I just used the expression below successfully in a file geodatabase feature class: OID BETWEEN 1 AND 100 but in a shapefile this expression reported that it was invalid SQL: "FID" BETWEEN 1 AND 100 My testing used ArcGIS 10.2.2 for Desktop


1

I guess you added in your classpath gt-epsg-postgres instead of gt-epsg-hsql? Delete gt-epsg-postgresql-11.0.jar from your classpath and add gt-epsg-hsql-11.0.jar into it. EDIT(Inspired by comments): For making the answer more understandable for others: GeoTools was set to store the EPSG projection database into PostgreSQL database. The default is to use a ...


1

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


1

You could use regular expressions to do this: regexp_match( "NRN",'(\\b|_)(1|2|23|31)(\\b|_)') This will match either a "1", "2", "23" or "31" which is surrounded by "word boundaries" ( \b") or underscores. (A word boundary is either the start or end of the string, or certain whitespace characters). New numbers to match can be inserted into the ...



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