距离矩阵表，当找不到时，做一些事情

Question

My application is matching people based on distance. The distance is calculated with the Google Maps API Distance Matrix. It's grouping the locations of the people and then making 1 query with the API.

Example:

FROM: New York Los Angeles Chicago New York New York Los Angeles

TO: Los Angeles San Diego Dallas Dallas Dallas Austin

The array will be: FROM: New York Los Angeles Chicago

TO: Los Angeles San Diego Dallas Austin

Using the Google API for the same cities every refresh can be optimised. So I was thinking about creating a table that holds the cities.

CREATE TABLE IF NOT EXISTS `distancematrix` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` varchar(100) NOT NULL,
`to` varchar(100) NOT NULL,
`distancetime` int(11) NOT NULL,
`distancekm` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `from` (`from`),
KEY `to` (`to`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Now I want to create a library (I'm using CodeIgniter) that first checks the table, if there are no results it should use the Google API and store the result for the next time.

How would you code a function like this? It must be efficient, therefor I'm seeking advice. I don't want to have unnecessary loops obviously.

The function should be called by just throwing a from and a to array in it.

The function for the table:

    public function get_distance($from, $to) {

        $result_array = array();

        if (is_array($from)) {
            foreach($from as $index => $origin) {
                $query = $this->db->select("`from` as `origin`, `to` as `destination`, distancetime, distancekm, 'true' as result", false)
                                    ->from('distancematrix')
                                    ->where('from', $origin)
                                    ->where_in('to', $to);

                $query_result = $query->get()->result_array();

                $opposite = $this->db->select("`from` as `destination`, `to` as `origin`, distancetime, distancekm, 'true' as result", false)
                                            ->from('distancematrix')
                                            ->where_in('from', $to)
                                            ->where('to', $origin);
                $opposite_result = $opposite->get()->result_array();

                $merge_result = array_merge($query_result, $opposite_result);

                $result_array[$origin] = $merge_result;
            }
        }
        return $result_array;
    }

The options I see: 1. Do a query for every combination, if it is not found, put it in a not found array. This not found array is later used with API.

1. Not sure yet...

What options do you see?

Thanks in advance!

Answer 1

I had a "similar" problem in one of my apps where we needed top 200 tweets for ~500 celebs (fixed list). Their top 200 tweets kept on updating so we constantly needed to call the twitter stream apis. We solved this in a similar way: By creating DB Tables to store the information and ran CRON jobs everyday that would refresh the data in the tables.

You situation is a bit more challenging. $to and $from can have a LOT of possibilities. You don't have a fixed list (like the celebs from the example). One problem I see is scaling. You are planning to keep a data cache in your DB for something that is too vast. It can be optimized: You can merge nearby cities together and count them as one. But would that be enough> And in the long run this solution might not work out.

Option 1 you mentioned is ok:

a) Check locally
(Optimize this as much as you can, eg:
a.1) Making the opposite query is good: Since you don't want to store twice.
a.2) If you are checking distance between X and Z, and you have distances between X and Y and also Y and Z you can probably use that information to get the approx distance. etc)

b) If not found, mark it as not found

c) Query the API

d) Get results and store locally.

You should be fine with this solution and you can always improve on it by making optimizations to a). But if you are planning to scale with your app then you may encounter problems later.