Tag Info

Hot answers tagged

14

Building a query expression. Check the Combining Expressions sub-heading to see how to use the AND/OR operators correctly. For your example you should implement "IN": "Name" IN ('Chatham','Chester')


13

This illustration stuck with me, and helps me remember at the most basic level what precision vs. accuracy is.This is the source of the image, also containing a little more context. In general, Precision is the how close your grouping of measurements are. Accuracy is how close your measurement is to the actual measurement in the real world. Blah238 is ...


10

First, make sure you have an index on your geography column. It will speed up the spatial searches: CREATE INDEX geo_cities_geog_idx ON geo_cities USING GIST geog; VACUUM ANALYZE geo_cities(geog); Then, you can use ST_DWithin (with conversions from miles to metres) on a self-joined query: SELECT gc.*, ST_Distance(gc.geog, pt.geog)/1609.344 AS ...


8

This is an exciting question! How big is the raster you want to query? WKTRaster is stored in the database as a BLOB. In order to find the value at a specific point, from a known (x_0, y_0) corner coordinate row/column indices (i, j) are computed using (dx, dy) steps and rotation. With (i, j) known, the ST_Value() function can access the actual data at the ...


7

This is a problem coming from that ST_Intersects has no tolerance. Even if double precision coordinates holds a lot of decimals they form a grid where the only places for the points is in the crossings. Often the line doesn't intersect with any of those crosses and there is no way any point will intersect the line exactly. The workaround is to use st_dwithin ...


7

I can get you part of the way there by assuming you have figured out how to request (a) the easternmost half of a set of points and (b) the northernmost half of a set of points. From these you can, of course, easily obtain (c) the westernmost half or (d) the southernmost half. (I don't know QGIS, but one way to do (a) in general is to request the median ...


7

Use the methods available on the ISQLSyntax interface to make your code workspace-independent. As the help on IQueryFilter.WhereClause explains, use the ISQLSyntax.GetSpecialCharacter method to return the delimited identifier prefix and suffix specific to your workspace and add them to your column identifiers. Example: ISQLSyntax sqlSyntax = ...


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

You can preatty easily run a search by combining WFS anc CQL like in the following example: layerQueryRun: function() { var wfs = new OpenLayers.Protocol.HTTP({ url:WMSCONFIG.wfs_server_path+"?service=wfs&version=1.0&request=GetFeature&typename=" + youLayerTypeName, format: new OpenLayers.Format.GML.v3({}) }); // ...


6

To answer the first part of your question, I think it helps to look at the additional text in the Creating Attribute Indexes help file about Multi-column indexes. The order in which fields appear in a multicolumn index is important. In a multicolumn index with column A preceding column B, column A will be used to conduct the initial search. Also, ...


6

When creating a spatial index on a table it is important to run "vacuum analyze <table>" after that. For finding nearest points you can use operator <-> introduced in PostGIS 2.0. It actually gives you the distance between two points. More info can be found here: http://workshops.opengeo.org/postgis-intro/knn.html SELECT id FROM nodes ORDER ...


6

No, probably not. I'm going to assume from the coordinates of your point that you are working in longitude/latitude coordinates, but that you want to express your distances in meters. Rather than building a real "circle", recognize that for the purpose of a true/false test you can express the query as a distance calculation. SELECT routes.* FROM routes ...


6

You can use the join method: selectionQRY = "RTE_1_NBR <> '" + "' AND RTE_1_NBR <> '".join(userSelectionList) + "'" A string will be created, in three parts. The middle part is the key, where code of the form string.join(list) will take all objects in the list, convert them to strings, and then append them all together, with the specified ...


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

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


5

Do you just need to know the max value for each unique entry in column B, or do you actually need to select the features with the maximum values on your map? If you just need an output table with headings [B], [Max_of_A], you can open the attribute table, then right-click column B and choose Summarize. In the list of summary statistics to be calculated, ...


5

This works for a "relate" (definded in an MXD). I'm not sure if it works for a "relationship class". Please try. If you work with a relate you make a selection in table A and transfer this selection to tabe B (this works in both directions of a relate): Make a selection in the "vegetation table". Open the attribute table of "vegetation table" Click the ...


5

First off, yes you will definitely want to make sure your primary and foreign key fields are indexed on both tables. This lets the DBMS plan and execute queries against these fields much more efficiently. Secondly, you are calling SelectLayerByAttribute_management in a tight, nested loop (once per tree per treatment). This is highly inefficient, for several ...


5

Like so, CREATE INDEX mytable_gix ON mytable USING GIST (Geography(ST_MakePoint(lon, lat))); SELECT * FROM mytable WHERE ST_DWithin( Geography(ST_MakePoint(lon, lat)), Geography(ST_MakePoint($qlon, $qlat)), $radius_meters ); Edit: If you have your data already in geometry points, but want to do a geography-style query: CREATE INDEX ...


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


5

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


4

What you should do is put your big multipolygon in a table as single polygons (with ST_Dump) and put an index on it. Something like: CREATE TABLE big_polygon as SELECT (ST_Dump( ST_GeomFromText('a multiypolygon geom goes here',4326))).geom as geom; -- It is always great to put a primary key on the table ALTER table big_polygon ADD Column gid serial PRIMARY ...


4

There are at least 9 top reasons to use File Geodatabase over Personal Geodatabase. Unfortunately, there are still a lot more reasons to keep the old PGDB around; your dilemma being one of them. (no ESRI publication on this topic) I believe the primary purpose of FGDB over PGDB is storage capacity and performance of spatial data (drawing speed, retrieval, ...


4

Maybe something like this (I'll assume you have some primary key column "id" in each table): SELECT A.id, A.code AS Code, A.sign AS Sign, B.id, ST_Distance(A.geom, B.geom) AS Distance FROM Table_A AS A, Table_B AS B WHERE A.id IN ( SELECT X.id FROM TableA as X, TableB as Y -- Here's the important part: refer to the A table **outside** of the ...


4

You can use the set null tool in the spatial analyst toolbox to assign any cell values outside your desired range to null values. The result will be a new raster layer with only the cell values you wish to preserve. The expression you should use will be along the lines of: Value > -117 AND Value < -69 and the false raster should be the same as your ...


4

This was an interesting article, but I wouldn't suggesting starting a new project along these lines, for the following reasons: The approach given in the Article uses The COM API, which has been Deprecated. The latest versions of Google Earth, can no longer be controlled by the COM API calls. Querying an MS access database is way more difficult than using ...


4

You could use the GRASS module v.rast.stats, which calculates univariate statistics from a raster map based on vector polygons and uploads the statistics to new attribute columns. The v.rast.stats2 module is an optimised version that might be more suitable if you are working with large datasets. Starspan is another option that allows one to do spatial ...


4

If your tables geom is not immersivly complex then I suggest a view would fit your needs better. CREATE or REPLACE VIEW myview AS SELECT m."TownName", sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS Roads_Km, count(*) AS Roads_Count, now() as 'Time' --Note: now() returns the time where the query ...


4

If your data is in a PostGIS or Spatialite database you can use the DB Manager plugin to run queries like that. You can also use it to generate layers from spatial queries if your query result includes a unique integer field for IDs and a geometry column. If your data is in shapefiles you can use sqlite's virtual table function. From the command prompt go ...



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