On 2/14/16, Gary Briggs <chunky at icculus.org> wrote:
>
> "For every row in that table, I want the entire row in that same table
> within a certain distance [eg 25], with the lowest "val". For rows
> with the same "val", I want to use lowest distance as a tie breaker."
>
Untested code follows.
I'l build up the answer in stages. First, a query that finds the
entry with 25 of @x, at y that as the minimum "val" with distance as a
tie-breaker.
SELECT id
FROM foo
WHERE distance(@x, at y,x,y)<=25
ORDER BY val, distance(@x, at y,x,y)
LIMIT 1;
For every row, find the ID of the row in the same table within 25 of
the row and with the minumum val, distance. (This is almost what you
want, but only returns the IDs of the rows, not the full row.)
SELECT
a.id AS a_id,
(SELECT b.id
FROM foo AS b
WHERE b.id!=a.id
AND distance(a.x,a.y,b.x,b.y)<=25
ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
FROM foo AS a
Use the output of the previous query to find the full rows:
SELECT a_id, c.*
FROM foo AS c,
(SELECT
a.id AS a_id,
(SELECT b.id
FROM foo AS b
WHERE b.id!=a.id
AND distance(a.x,a.y,b.x,b.y)<=25
ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
FROM foo AS a)
WHERE c.id=b_id;
I repeat: The above is untested. It is also, clearly, an O(N*N)
algorithm at best. If performance is a secondary goal, an R-Tree
index may be useful here.
--
D. Richard Hipp
drh at sqlite.org
