Sorting by X/Y location, by nearest item

Question

I got a list of items (450), with X/Y locations on a map, in a SQLite db (looks like this https://imagizer.imageshack.us/v2/991x240q90/912/VIkE4j.png )

the map is flat and looks like this http://imagizer.imageshack.us/a/img537/1814/bUoiKP.jpg

i want to sort this list by the X/Y locations, so the next item is the 1 closest to the previous item.

how can i sort them like this?

(also, would be nice to know, but is not important: can i do this sort in a SQLite query alone?)

Are X/Y refering to coordinates on a globe (3d) or on a flat surface (2d)? On a flat surface Pytagoras would be your friend. For globe you will need a bit more calculations, and Google would be your friend. (Or me, but you need to tell me first what you have already done.) Also is there a point of origin from where you measure the distance to all locations and sort them? — nl-x, Mar 02 '15 at 12:23
flat surface, i guess. there's no Z, and it looks like this http://imagizer.imageshack.us/a/img537/1814/bUoiKP.jpg — hanshenrik, Mar 02 '15 at 12:26
That looks like a globe. And recent studies have proven that that is not a flat surface. (Also you will not fall off the earth, if you travel too far west.) To have accurate distances, you would have to calculate accordingly. — nl-x, Mar 02 '15 at 12:28
You have a bit of a flaw in your worded logic. If you try to fix the flaw it will show you that the real question is a bit more difficult than you think. Because you don't just want the nearest item to the previous item. This will always get stuck in a loop where A is nearest to B, and B is nearest to A. So every time you will have to exclude the previous items. It is doable in SQL, but then you will need to use some kind of temp table and work through it. Maybe your easier off doing it outside SQL. — nl-x, Mar 02 '15 at 12:33
Ah, ok. if point of origin is 0,0, and you want the distance to that point for every location, that is doable... Again, Pytagoras for 2d, and more advanced for 3d. — nl-x, Mar 02 '15 at 12:34

hanshenrik · Answer 1 · 2015-03-03T01:24:14.327

this seems to work. https://github.com/divinity76/onlinkshit/commit/15d24a48c73ea4a9298805bbfa3a6aa2d80e2460 does anyone see anything wrong?

    function sort_by_xy_distance($input_list)
    {
        $ret = array();
        $a = $input_list[0];
        array_push($ret, $input_list[0]);
        $input_list[0] = null;
        $i = 1;
        for ($i = 1; $i < count($input_list); ++$i) {
//            if ($input_list[$i] == null) {
//                echo 'already added to list..';
//                continue;
//           }
            $ii = 1;
            $tmpdistance = 0;
            $nearest = array(
                'index' => -1,
                'distance' => PHP_INT_MAX
            );
            for ($ii = 1; $ii < count($input_list); ++$ii) {
                if ($input_list[$ii] == null || $ii == $i) {
                    //echo 'already added to list..';
                    continue;
                }
                $tmpdistance = abs($input_list[$ii]['x'] - $a['x']) + abs($input_list[$ii]['y'] - $a['y']);
                if ($tmpdistance < $nearest['distance']) {
                    $nearest['index'] = $ii;
                    $nearest['distance'] = $tmpdistance;
                }
            }
            assert($nearest['index'] != -1);
            array_push($ret, $input_list[$nearest['index']]);
            $a = $input_list[$nearest['index']];
            $input_list[$nearest['index']] = null;
        }
        return $ret;
    }

here is fully copy & paste runnable testcode with real data:

http://pastebin.com/raw.php?i=ST3saHj2 (too big to post on stackoverflow with its 30000 character/post limit)

looking at both the numbers posted by php, and looking at the result in game, it looks like everything is sorted correctly. the shortest path is found.

Sorting by X/Y location, by nearest item

1 Answers1