Tag Info

Hot answers tagged

8

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


4

What you need are indexes. You may have indexes on your tables right now but since you're using ST_TRANSFORM they're not being used. Indexing on transform is even mentioned on the function's page: http://postgis.net/docs/ST_Transform.html CREATE INDEX idx_address_3112 ON address_list USING gist (ST_Transform(wkb_geometry, 3112)) CREATE INDEX ...


4

I suggest you to install Group Stats plugin. Here is a quick tutorial: Search for the Group Stats icon Select the layer in which you have the Codes Select the attribute (in your case Code) Click on Calculate The Count will appear. If you go to preferences you should be able to select only Count avoiding the other parameters to be displayed (this is not ...


4

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

That's certainly possible. While you would use something like name = 'mystate' for one state, you can use name IN ('stateA','stateB','stateC') to get multiple ones.


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

I have only done this kind of thing with SQL Server but this link has some techniques that should help you accomplish what you want with PostGres. How to concatenate strings field in postgresql The steps you would need to carry out would be: Create a View in the database (using the techniques described in the link) that returns a table with two fields, ...


3

ESRI doesn't make a DBMS, they make middleware... But on databases, you should almost certainly use PostGres (PostGIS). This is the operation you want: ST_Contains. I think PHP is the easiest language to use for this, but get the query working in PGAdmin, then do it in your server side scripting language. Or whatever!


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

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

In QGIS, you can use the following in Select by expression: $area > 300 and $area < 500


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

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

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

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

Simply add the alias in quotes after the column name.


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


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

Looks like you just need to supply the field ID for both the greater than and less than operations. This should work: CELL_ID >= 106551 AND CELL_ID <= 247977


2

Some time ago we added time browsing support to the map viewport in order to be able to view netcdfs along a timeline (see https://code.google.com/p/jgrass/wiki/SummerOfCode2009 ). So if I got your problem right, I think you should be able to use directly the map window and pilot time through the navigation view. What will be necessary for you to do, is an ...


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



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