Hot answers tagged query
11
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')
11
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 ...
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
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 ...
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 ...
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
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
...
5
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 = ...
5
Some useful stuff here for 1.8 and older
http://hub.qgis.org/projects/quantum-gis/wiki/How_do_I_do_that_in_QGIS
http://hub.qgis.org/wiki/quantum-gis/Calculating_field_values
http://hub.qgis.org/wiki/quantum-gis/List_of_Field_Calculator_Functions
4
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, ...
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
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, ...
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
Underdark has composed some nice examples for labeling expressions:
Easier Conditional Labels in QGIS
Here are some helpful links for querying:
Query Attributes
Working with Attribute Table - Basic Queries
If you are interested in coding then you can start with this resource:
pyQGIS Developer Cookbook
4
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 ...
4
One approach, which is a little more basic is to split this into two queries:
SELECT SUM(ST_Area(geom))/count(*) as avg_area
FROM parcela;
Then with this returned value (say it is 500), use this in your next query:
SELECT OBJECTID
FROM parcela
WHERE ST_Area(geom) > 500; -- this returns parcels greater than average
Another approach, performing this ...
4
Here is a window function alternative:
SELECT OBJECTID
FROM (
SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER () AS filter
FROM parcela
) AS ss
WHERE filter;
Note: I've replaced Sum(ST_Area(geom))/count(*) with a more readable avg(ST_Area(geom)).
With a window function, you have more power to your query, such as find all the parcels ...
4
You can use the "Execute as Batch Process" function the Sextante Toolbox provides.
It's easiest if you first define a new Model based on the MMQGIS Select tool using the Sextante Modeler to fill in the parameters:
After saving the model, you can right-click on it in the Sextante Toolbox and select "Execute as batch process". In the window that opens, ...
4
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 ...
4
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 ...
3
The first thing I see is your test ='t' That I think will fail because ST_Contains() returns a boolean, so you don't need any explicit test.
Also, make sure you specify your geometry properly. The docs say ST_Contains(geometry geomA, geometry geomB):
Returns true if and only if no points of B lie in the exterior of A,
and at least one point of the ...
3
MS Access is not able to do spatial operations, at least not in the way those used to using OGR standard spatial databases may be familiar with.
Functions such as ST_Within, ST_Intersects and the dedicated geometry types simply don't exist.
Now, that's not to say that you can't work with co-ordinate values, but you'll have to treat them as normal decimal / ...
3
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, ...
3
GeoRSS is purely a push service. It has no processing functions associated with it.
If you're using ArcObjects for instance, you could push the GeoRSS datq into a FeatureClass and then perform the operations you require through their tool box. Alternatively, there are third party API's that may be of use to you (Search this forum for open source tools).
...
3
Think you need additional module like XQuery or JQuery
Here is a good example (MarkLogic API 4.2) using XQuery
http://api.xqueryhacker.com/#georss:circle
If using Google Maps to display then GeoXML is using JQuery to do spatial queries
http://code.google.com/p/geoxml/
3
As Mr Chimp says, MapInfo doesn't like more complex joins in queries. In this case you will have to split the query into two:
1
WHERE RationalisedStreets.Link_ID = RationalisedPointAddress.Link_ID And
RationalisedMtdArea.Area_ID = RationalisedStreets.L_Area_ID
2
WHERE RationalisedStreets.Link_ID = RationalisedPointAddress.Link_ID And
...
Only top voted, non community-wiki answers of a minimum length are eligible
