php - Distance matrix table, when not found, do something -

my application matching people based on distance. distance calculated google maps api distance matrix. it's grouping locations of people , making 1 query 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 be: from: new york los angeles chicago

to: los angeles san diego dallas austin

using google api same cities every refresh can optimised. thinking creating table holds 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 want create library (i'm using codeigniter) first checks table, if there no results should use google api , store result next time.

how code function this? must efficient, therefor i'm seeking advice. don't want have unnecessary loops obviously.

the function should called throwing , array in it.

the function table:

    public function get_distance($from, $to) {          $result_array = array();          if (is_array($from)) {             foreach($from $index => $origin) {                 $query = $this->db->select("`from` `origin`, `to` `destination`, distancetime, distancekm, 'true' result", false)                                     ->from('distancematrix')                                     ->where('from', $origin)                                     ->where_in('to', $to);                  $query_result = $query->get()->result_array();                  $opposite = $this->db->select("`from` `destination`, `to` `origin`, distancetime, distancekm, 'true' 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 see: 1. query every combination, if not found, put in not found array. not found array later used api.

2. not sure yet...

what options see?

thanks in advance!

i had "similar" problem in 1 of apps needed top 200 tweets ~500 celebs (fixed list). top 200 tweets kept on updating needed call twitter stream apis. solved in similar way: creating db tables store information , ran cron jobs everyday refresh data in tables.

you situation bit more challenging. $to , $from can have lot of possibilities. don't have fixed list (like celebs example). 1 problem see scaling. planning keep data cache in db vast. can optimized: can merge nearby cities , count them one. enough> , in long run solution might not work out.

option 1 mentioned ok:

a) check locally
(optimize as can, eg:
a.1) making opposite query good: since don't want store twice.
a.2) if checking distance between x , z, , have distances between x , y , y , z can use information approx distance. etc)

b) if not found, mark not found

c) query api

d) results , store locally.

you should fine solution , can improve on making optimizations a). if planning scale app may encounter problems later.