SQL Query v6
Posted: 11 May 2009, 16:57
I'm writing a SQL stored procedure, that determines the possibility that a certain geographical point is actually supposed to be the same as another point in the database. I have a database containing numerous geographic locations as points, and need to filter these, based on this probability, to fewer points.
i.e. when 2 GPS devices log the same point, the coordinates usually differ depending on GPS accuracy.
So, "I've devised a cunning plan" to give each point a score, which relates to the probability that it is actually another point, but because of inaccuracy, is logged as a seperate point.
I intend to give each point a score based on a couple of factors, among which is proximity to all the other points.
Thus, if point 1 (p1) is within 2 km of point 2 (p2), it gets a score of 50. Within 10km, 25. Within 25km, 10 and so forth. (Just an example; I'll use a logarithmic equation to assign a score based on the proximity).
Also, for each of the other conditions (like, the coordinate was logged within a month of each other), I'll assign another score, which is added to the total score.
Ultimately, I want this :
Point A - Point B - Probability that Point A is actually Point B
1 2 99.9
1 3 85.0
1 4 70.9
etc...
The process of assigning a score to a certain condition is trivial... And maybe this is common knowledge to SP Wizards, but how would I select some points, and test each against p1 for the conditions? Basically, how do you do a foreach in a SQL SP?
i.e. when 2 GPS devices log the same point, the coordinates usually differ depending on GPS accuracy.
So, "I've devised a cunning plan" to give each point a score, which relates to the probability that it is actually another point, but because of inaccuracy, is logged as a seperate point.
I intend to give each point a score based on a couple of factors, among which is proximity to all the other points.
Thus, if point 1 (p1) is within 2 km of point 2 (p2), it gets a score of 50. Within 10km, 25. Within 25km, 10 and so forth. (Just an example; I'll use a logarithmic equation to assign a score based on the proximity).
Also, for each of the other conditions (like, the coordinate was logged within a month of each other), I'll assign another score, which is added to the total score.
Ultimately, I want this :
Point A - Point B - Probability that Point A is actually Point B
1 2 99.9
1 3 85.0
1 4 70.9
etc...
The process of assigning a score to a certain condition is trivial... And maybe this is common knowledge to SP Wizards, but how would I select some points, and test each against p1 for the conditions? Basically, how do you do a foreach in a SQL SP?