I need to select points that have attributes that are within a relative number range of each other. For instance; given the following numbers: 95,705,139,65,537,266,525 and 188; I need to find all of those numbers that are within a range of 50, 100 and 150 of each other. I then need to find those that are within a distance of each other. I am using QGIS. Any ideas gratfully accepted. Thanks.
Since you have tagged the question "sql" I guess that sql is an option to solve this.
If I understand your question right, you want to find the 50 range case, 100 range case 150 range case in different queries?
There are other options for that but to look at the simplest first we do those assumptions:
In your table you have a geometry column for your points, we call it the_geom , an id column to identify the resulting records, we call id gid and a value column with the values you presented above, we call it the_value.
THen to make the 50 range query, with a fixed maximum distance of 100 (units of the projection you use) you can write query like this:
SELECT a.gid, b.gid FROM the_table a INNER JOIN the_table b ON ABS(a.the_value-b.the_value)<50 AND ST_DWithin(a.the_geom, b.the_geom, 100);
I wouldn't write any client code to get this. If it isn't huge tables it should be no problem to just create a view and use that in QGIS:
CREATE VIEW range50 AS SELECT a.gid, b.gid, a.the_geom, b.the_geom FROM the_table a INNER JOIN the_table b ON ABS(a.the_value-b.the_value)<50 AND ST_DWithin(a.the_geom, b.the_geom, 100);
Another approach here could be to create a table that can keep ranges and distances. Then you can easily put range classes in your resulting view, and you can put different distances for different ranges for instance. This table is also the right place to put information about how to style the result in QGIS like size of markers.
First create the table:
CREATE TABLE ranges (range int, distance int, marker_size int); INSERT INTO ranges(range, distance,marker_size) VALUES(50,100,4), (100, 125,8), (150, 200,14);
Then you can create the view like this (here we make a two-point multipoint as result):
CREATE VIEW dynamic_ranges AS SELECT a.gid, b.gid, r.marker_size, ST_Collect(a.the_geom, b.the_geom) as the_geom FROM the_table a, the_table b, ranges r WHERE ABS(a.the_value-b.the_value) <=r.range AND ST_DWithin(a.the_geom, b.the_geom,r.distance);
To get this running smooth you should put a gist-index on the point (the_geom) column and a btree index on the_value column.
I might add to Nicklas' well detailed answer: If you're looking to find points with the same table that are either a given distance from each other or have an attribute within a given value, then you can use the form:
SELECT a1.gid, a2.gid FROM the_table a1 INNER JOIN the_table a2 ON ABS(a1.the_value-a2.the_value)<50 AND ST_DWithin(a1.the_geom, a2.the_geom, 100);
Just work with the table twice using different aliases.