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


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

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

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


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


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

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


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

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


2

Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected? In Paul's answer, I proposed using SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); but MAX() is slow, apparently requiring a table scan. Instead, I found this to be ...


2

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)' LANGUAGE 'sql' IMMUTABLE; And so the query gets rewritten to use ...


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

The documentation for ST_AsLatLonText explains that you can use an optional second argument to specify the format. In your case, it would be: UPDATE members SET latlong_degree = ST_AsLatLonText (the_geom, 'D°M''S"C') Note that the ' mark for the minutes has to be double quoted to avoid it just terminating the string.


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

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

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

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

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

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


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'


1

You could use ST_LineMerge http://postgis.net/docs/ST_LineMerge.html First you need join all your ways into one MultiLineString using ST_DUMP http://postgis.net/docs/ST_Dump.html and ST_Multy Afterwards use ST_LineMerge to get a set of Linestrings of maximum length sewed from segments.


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



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