Tag Info

Hot answers tagged

9

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


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


5

Skip the trig, create view mypoints as select id, st_makepoint( st_x(st_endpoint(geom)) + (st_x(st_endpoint(geom))-st_x(st_startpoint(geom)))/2, st_y(st_endpoint(geom)) + (st_y(st_endpoint(geom))-st_y(st_startpoint(geom)))/2 ) as geom from mytable; then select geom from mypoints where id = 1; should work fine, for all values of id


5

If you want to test whether some points (or shapes, etc) fall within a given distance of a given location, use the geographic version of ST_DWithin(). From http://www.postgis.org/docs/ST_DWithin.html boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid); Thus, SELECT ST_DWithin( ST_GeomFromText( ...


4

The error you are seeing is because ST_MakeLine does not accept WKT style arguments. There are multiple usage options, but for you, probably something like: ST_MakeLine(ST_MakePoint(700264, 126376, 23700), ST_MakePoint(780844, 122481, 23700)) will be best. That may not fix the real problem (in that you aren't specifying the table that County multipolygon ...


4

Clip and Intersect are two similar, but different operations, but that's not your problem here. It looks to me like your Intersect worked fine, the problem is that the shape you're Intersecting with, the red frame, is a line object and not a polygon. So, the only road segments being output are the ones that directly touch that line. If you want to clip the ...


3

Simply add the alias in quotes after the column name.


3

you cannot use equal with more than one value use either in or OR to do this "\"STATION_ID\" IN ('CAP', 'DDM')" "\"STATION_ID\" = 'CAP' OR \"STATION_ID\" = 'DDM'" note that I recommand using """ to clarify your strings """ "STATION_ID" IN ('CAP', 'DDM') """


3

You have a couple of problems within your query. The usage of CTEs you completely prevent using your geometry index, which I hope you have on gps_nodes.geog. The usage of ST_DISTANCE does not use your geometry index as well. Use something like ST_DWITHIN. By the way ST_DWITHIN with your CTE construct does not use the geometry index. (not performance) I ...


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

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

I'm not sure this will work but take a look at ST_Difference in PostGIS. It doesn't take a geometry collection. Create a polygon that covers the entire area of interest. Merge the buffered areas/polygons into a single feature. Use ST_Difference to return the non-intersected part of the geometry from 1.


3

$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";


3

As seen in the comments, it was a bug. You can see the bug in the bugtracker. It was fixed in the 2.1.2 release However, being caught by that bug outlined a fundamental issue in our design, and made it possible to realize that the app would not be performing well using such a generic type. I would not advise to use GeometryCollections unless you know ...


3

I think you're making it too complicated. Try this instead: SELECT sum(building_pop) FROM Buildings WHERE ST_Distance(ST_SetSRID( ST_MakePoint(1116858.6062789 7086290.6680572), 900913), geom) < 10000 That would give you the sum of building_pop column of all the buildings that are less than 10,000 units away from the defined Point.


3

You best start with the workshop, and especially look at the chapter, which explains how to write a simple plpgsql wrapper function to return additional attributes such as street name for example. A query might look like this then: SELECT seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.osm_name FROM pgr_dijkstra(' SELECT id, ...


3

The above solution works great for me and was very quick. Using the above code and referenced code from the other post this is how I built it: # Local Variables OriginTable = "This must be a Table View or Feature Layer" DestinationTable = "This must be a Table View or Feature Layer" PrimaryKeyField = "Matching Origin Table Field" ForiegnKeyField = "Matching ...


3

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


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

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

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

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


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?


2

Best way is to use the geographic type. geography ST_Buffer(geography g1, float radius_of_buffer_in_meters); SELECT 1 as cartodb_id, ST_Transform( ST_Buffer( ST_GeomFromText( 'POINT(-58.427185 -34.624592)' , 4326 )::geography ) , 2000 ), 3857 ) as the_geom_webmercator By casting to geography the st_buffer will work ...


2

This is listed in the pyqgis cookbook. http://www.qgis.org/en/docs/pyqgis_developer_cookbook/loadlayer.html You need to make use of the optional where clause. uri = QgsDataSourceURI() # set host name, port, database name, username and password uri.setConnection("localhost", "5432", "dbname", "johny", "xxx") # set database schema, table name, geometry ...


2

Reviving this thread/issue, I found it can be usefull to combine, where possible, FGDB and PGDB. For instance, make a scratch-geodatabase a PGDB greatly helped performance of queries. The size of the PGDB should not increase too much, as mentioned above.


2

Yes, the downloads provide this ability. The 'only' thing missing from the download is centroid coordinate data, and the part-of relationships that lie outside the formal hierarchy. Consider Factual's World Geographies http://developer.factual.com/data-docs/#world-geographies as an alternative that contains centroids.


2

Something like this. Buffer and union all the polygons that fall in the box, then subtract that from the box: WITH buffers AS ( SELECT ST_Union(ST_Buffer(mypoly.geom, radius)) AS theunion FROM mypoly WHERE mypoly.geom && ST_MakeEnvelope(minx,miny,maxx,maxy) ) SELECT ST_Difference(ST_MakeEnvelope(minx,miny,maxx,mazy), theunion) FROM buffers;


2

Selection by attributes: "field_name" LIKE 'C%' Field_name is attribute that contains your annotations.


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



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