Hot answers tagged query
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 ...
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
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
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
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
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
Remember that pgrouting is not really optimised for query speed, you should use some other tools if you need real-time performance. In principle it might be possible to modify internal implementations the algorithms to calculate many routes from single point, but I suggest to check this out from pgrouting mailing list.
3
Its best to get this data straight from the database if you want it for every COLUMN_B grouping.
This query in a database like Oracle would be (other databases will be similar):
select count(*), MAX(column_A), column_B from TABLE_NAME group by column_B
That will give:
4, 999, aaa
4, 999, bbb
To do it in ArcGIS is fairly easy.
Use the Select by ...
3
If you are familiar with R, you could also solve this problem with your favorite R editor, which is easy as pie.
For example like this:
library(raster);library(rgdal)
ras <- raster(...)
pol <- readOGR(...)
extraction <- extract(ras,pol)
statistics <- lapply(extraction,table)
# Further analyze the data (mean, sum per polygon feature or sth. ...
3
@Geoist is right. You'll have much more control over this using Python -- you can nest as many loops as you damn well please.
Pseudo Code:
Create a search cursor
Loop through each record with a for loop
Compose a whereClause using the ObjectID (unique identifier)
Make feature layer
Select by location
etc. (delete feature layer to end the loop)
Code ...
3
There might be syntax problems , but here is example howto use subquery.
UPDATE Towns t
SET t.length = r.Roads_km
,t.count = r.Roads_Count
,t.updatetime = r.update_time
FROM (
-- subquery
SELECT
m."TownName"
,sum(ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS Roads_Km
, count(*) AS ...
3
You're correct that your problem is that in order to sort, then limit to the closest, you have to generate the distance to each of the 100k points, which is extremely time consuming.
Luckily, there's help. First, you want to make sure that your geometry column is indexed, if it's not, then you need to index it, or this won't work.
Then you want to use the ...
3
If you have a road no., section no. and length field in both your spreadsheet and your shapefile, and they correspond (match), then you could try to join the spreadsheet to your shapefile by doing the following:
Make a 'combined' field that is the combination of road no., section no. and length in both your Excel spreadsheet and your shapefile. This ...
3
I checked your data. As I already mentioned in my comments and like Rayner said in his answer, you have to make your own unique ID in both, the shapefile and the excel.
For the shapefile:
Open attributetable, activate toggle editting and open the fieldcalculator.
Set all parameters like showed in the picture above and you wil get a new column filled ...
3
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 ...
3
Maybe try to do this without using an UPDATE. Since you're joining all the records in one table to another just do a create table query to make a brand new up to date table. My suspicion is that the update query could be driving the point/polygon join backwards and not nesting the loop with polygons outside points, thereby missing a big performance ...
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
Depending on the distribution of your data, you might get some very good speedups just by indexing the date_of_data column.
You can use the EXPLAIN ANALYZE syntax to figure out if your indexes are being used or not.
3
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
