Tag Info

Hot answers tagged

7

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


6

There easily might be multiple queries that return the same result. Using the following data: FID STNAME DIR 0 BLUE ST N 1 BLUE ST S 2 BLUE ST N 3 BLUE ST S 4 BLUE ST S 5 BLUE ST S 6 BLUE ST W 7 BLUE ST W 8 BLUE ST W 9 BLUE ST W 10 BLUE ST E All these queries return the same thing: "FID" < 6 "FID" < 6 AND "STNAME" = 'BLUE ST' ...


6

1) Select your polygon of interest - you can do this manually or with one of the selection tools. 2) Select your 'marginal' points using the Select By Attributes tool with the Method drop-down set to 'Add to current selection'. You will now have a selected polygon in one layer and selected points in another layer. 3) Use the Select By Location tool to ...


6

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

The wildcard to use with LIKE depends on the data source. You are using square brackets as field name delimiters. So I suppose you are using a Personal Geodatabase. The wildcards you have to use to query a personal geodatabases are asterisk (*) for any number of characters and question mark (?) for one character. If you use SDE or file based geodatabase ...


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

The syntax of the query is usually dictated by the underlying database, and file geodatabases do not support '%[A-Z]%' style LIKE predicates. But, if it is just letters that have multiple cases, you should be able to compare the output of UPPER and LOWER functions for changes. If there aren't any, then there aren't any characters that have case. This will ...


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

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

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

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


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


3

Assuming you have a spatialindex created already on the polygon layer 'blayer', then your query would be: UPDATE strlayer SET fromBland =( SELECT name FROM blayer AS n, strlayer AS s WHERE strlayer.strid = s.strid AND n.blevel = 4 AND within(startpoint(s.geometry),n.geometry) AND s.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE ...


3

EPSG:277000 = OSGB 1936 / British National Grid proj4 is +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs. So You have points in degrees in metric systems and you use meters to find distance between users ? First use ST_SetSRID(...., 4326) (that would be WGS84 and it uses degrees ) then ...


3

To summarize the very useful comments by @Spacedman, @JeffreyEvans and @AriB.Friedman, but also to address the questioner's second problem about saving the generated subset and to provide a reproducible example for future audience, let me add a brief example to cover the whole topic. In fact, I'm much more often working with raster data than point or polygon ...


3

I think you could find some interesting information in this tutorial book It is recent enough (Fall 2012) If your data is stored in PostGIS you could look at this tutorial For your specific needs, look in your shapefile city database if there is descriptive info about the country. If not use the spatial plugin in Menu/Vector/Spatial Query and do a query ...


3

SELECT r.osm_id FROM osm_roads r, osm_places p WHERE p.name = 'Hometown' AND ST_Intersects(p.way, r.way); If you want only streets that are completely contained within the places polygon replace ST_Intersects with ST_Contains. Order matters to ST_Contains, it returns all geometries from the 2nd layer that are completely contained by the 1st. A third ...


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

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

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

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') """


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

If you are using ArcServer 10.1 you could create a Geoprocessing service which references either a model or script that you built to do this. The link above gives step by step instructions on how to create the model, then create the services and finally consume it in your app. Alternatively, there is this sample that uses a query to select records, you ...


2

Don't know if this is still of interest but I got it working doing something like this: (you would just need to replace myPoint with your feature) IProximityOperator proximityOperator = myPoint as IProximityOperator; ISpatialFilter spatialFilter = new SpatialFilterClass(); spatialFilter.Geometry = ...


2

Two more ideas: 1) The read-only Overpass API. I don't know that it'll work for the whole US in one pass, but maybe if you extend the time out enough...? For a smaller area (.5 degree max) and with the benefit of a (minimal) UI, you can access Overpass via the XAPI Query Builder. There, you can put amenity=hospital in the tag search, select your area, ...


2

As per documentation of function it can use both: http://postgis.net/docs/ST_DWithin.html boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid); boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters); boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean ...



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