database - SQL query to get neighbours from table with two dimensional data -

i have table, contains partitioning of world map. partition done splitting area rectangles, called 'tile'. usual, rectangle has bottom-left , upper-right corners, both referred floating point coordinates.

the task this:

for each tile neighbour right , neighbour top, set of {tile_id, id_of_top_neighbour, id_of_right_n}.

by right neighbour of tile meant such tile b, has closest min_x coordinate a's max_x coord, while y same.

description of table:

integer tile_id; --tile id. pk. real    min_x;  --x coordinate of bottom-left point real    min_y;  --y coordinate of bottom-left point real    max_x;  --x coordinate of upper-right point real    max_y;  --y coordinate of upper-right point 

failed solution:

first, tried sort 1 coordinate, iterate result set on java side , perform additional select each row. performance inadequate.

now wonder if pure sql solution possible , quicker @ runtime...

appreciated or ideas beforehand.

edit: there gap between 2 tiles, (e.g. right neighbour) b.min_x - a.max_x > 0. hovewer, 2 tiles cannot intersect more, boundary.

we using postgres 8.3

windowing functions , ctes make pretty easy do. think both available in 8.4 , above. suggest upgrade. tested solution on 9.0:

with tile_data (     select         tile_id,         min_x,         min_x + 0.9 max_x,         min_y,         min_y + 0.9 max_y         (      select 1 tile_id, 0.0 min_x, 0.0 min_y union      select 2, 1.0, 0.0 union      select 3, 2.0, 0.0 union      select 4, 0.0, 1.0 union      select 5, 1.0, 1.0 union      select 6, 2.0, 1.0 union      select 7, 0.0, 2.0 union      select 8, 1.0, 2.0 union      select 9, 2.0, 2.0      ) ), right_neighbor_tiles (     select         tile_id,         other_tile_id right_neighbor_tile_id         (         select              a.tile_id,              b.tile_id other_tile_id,              row_number() over(partition a.tile_id order b.min_x - a.min_x) distance_rank                      tile_data         inner join             tile_data b         on             a.min_x < b.min_x              , a.min_y = b.min_y     ) ranked_tiles_right             distance_rank = 1 ), up_neighbor_tiles (     select         tile_id,         other_tile_id up_neighbor_tile_id         (         select             a.tile_id,             b.tile_id other_tile_id,             row_number() over(partition a.tile_id order a.min_y - b.min_y) distance_rank                      tile_data         inner join             tile_data b         on             a.min_y > b.min_y              , a.min_x = b.min_x     ) ranked_tiles_up             distance_rank = 1 ) select      a.*,     b.right_neighbor_tile_id,     c.up_neighbor_tile_id     tile_data left join     right_neighbor_tiles b on     a.tile_id = b.tile_id left join     up_neighbor_tiles c on     a.tile_id = c.tile_id

result:

tile_id  min_x  max_x  min_y  max_y  right_neighbor_tile_id   up_neighbor_tile_id 1        0      0.9    0      0.9    2 2        1      1.9    0      0.9    3 3        2      2.9    0      0.9 4        0      0.9    1      1.9    5                        1 5        1      1.9    1      1.9    6                        2 6        2      2.9    1      1.9                             3 7        0      0.9    2      2.9    8                        4 8        1      1.9    2      2.9    9                        5 9        2      2.9    2      2.9                             6