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.

  • 1
    is this a one time analysis or do you need code to run repeatedly? – Willy May 12 '12 at 8:36
  • Hi Willy; once I figure out the syntax; I'll be scripting it in Python. – Jim Norton May 13 '12 at 2:44

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:

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:

(range int,
distance int,
marker_size int);

INSERT INTO ranges(range, distance,marker_size) 
(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
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.

  • 1
    I think that is identical to the first query in my answer :-), with the difference that you call your tables a1, a2 and I called mine a, b. As you say, self joins is the way to go. – Nicklas Avén May 13 '12 at 14:39
  • 1
    Sorry, you're quite right, of course. I saw your 'a' and 'b' aliases and didn't notice that they referred to the same single table. I apologize for butting in... – Micha May 13 '12 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.