SQL Query v6

[GrimStoner]

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?

[SykomantiS]

A cursor perhaps...? My SQL is a bit rusty, and cursors were never my strong point, but I guess that is what you're asking.