include/geoloc.inc.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2006 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22
  23 // {{{ liste les pays ou les régions d'un pays
  24 /** donne la liste déroulante des pays
  25  * @param $current pays actuellement selectionné
  26  */
  27 function geoloc_country($current) {
  28     global $globals;
  29     $res  = $globals->xdb->iterRow('SELECT a2,pays FROM geoloc_pays ORDER BY pays');
  30     $html = "";
  31     while (list($my_id, $my_pays) = $res->next()) {
  32         $html .= sprintf("<option value=\"%s\" %s>%s</option>\n",
  33                 $my_id, ($current==$my_id?"selected='selected'":""), $my_pays);
  34     }
  35     return $html;
  36 }
  37
  38 function _geoloc_country_smarty($params){
  39   if(!isset($params['country']))
  40     return;
  41   return geoloc_country($params['country']);
  42 }
  43 $GLOBALS['page']->register_function('geoloc_country', '_geoloc_country_smarty');
  44
  45 /** donne la liste deroulante des regions pour un pays
  46  * @param $pays le pays dont on veut afficher les regions
  47  * @param $current la region actuellement selectionnee
  48  */
  49 function geoloc_region($country,$current) {
  50     global $globals;
  51     $res  = $globals->xdb->iterRow('SELECT region,name FROM geoloc_region where a2={?} ORDER BY name', $country);
  52     $html = "<option value=\"\"></option>";
  53     while (list($regid, $regname) = $res->next()) {
  54         $html .= sprintf("<option value=\"%s\" %s>%s</option>\n",
  55                 $regid, ($current==$regid?"selected='selected'":""), $regname);
  56     }
  57     return $html;
  58
  59 }
  60 function _geoloc_region_smarty($params){
  61   if(!isset($params['country']))
  62     return;
  63   if(!isset($params['region']))
  64     return;
  65   return geoloc_region($params['country'], $params['region']);
  66 }
  67 $GLOBALS['page']->register_function('geoloc_region', '_geoloc_region_smarty');
  68 // }}}
  69
  70 // {{{ get_address_infos($txt)
  71 /** retrieve the infos on a text address
  72  * store on the fly the info of the city concerned
  73  * @param $txt the raw text of an address
  74  */
  75 function get_address_infos($txt) {
  76     global $globals;
  77     $url = $globals->geoloc->webservice_url."address.php?txt=".urlencode(utf8_encode($txt)."&precise=1");
  78     if (!($f = @fopen($url, 'r'))) return false;
  79     $keys = explode('|',fgets($f));
  80     $vals = explode('|',fgets($f));
  81     $infos = array();
  82     foreach ($keys as $i=>$key) if($vals[$i]) $infos[$key] = ($key == 'sql')?$vals[$i]:utf8_decode(strtr($vals[$i], array(chr(197).chr(147) => "&oelig;")));
  83     if ($infos['sql'])
  84        $globals->xdb->execute("REPLACE INTO geoloc_city VALUES ".$infos['sql']);
  85     if ($infos['display'])
  86        $globals->xdb->execute("UPDATE geoloc_pays SET display = {?} WHERE a2 = {?}", $infos['display'], $infos['country']);
  87     return $infos;
  88 }
  89 // }}}
  90
  91 // {{{ get_cities_maps($array)
  92 /* get all the maps id of the cities contained in an array */
  93 function get_cities_maps($array)
  94 {
  95     global $globals;
  96     implode("\n",$array);
  97     $url = $globals->geoloc->webservice_url."findMaps.php?datatext=".urlencode(utf8_encode(implode("\n", $array)));
  98     if (!($f = @fopen($url, 'r'))) return false;
  99     $maps = array();
 100     while (!feof($f))
 101     {
 102         $l = trim(fgets($f));
 103         $tab = explode(';', $l);
 104         $i = $tab[0];
 105         unset($tab[0]);
 106         $maps[$i] = $tab;
 107     }
 108     return $maps;
 109 }
 110 // }}}
 111
 112 // {{{ get_new_maps($url)
 113 /** set new maps from url **/
 114 function get_new_maps($url)
 115 {
 116         if (!($f = @fopen($url, 'r'))) return false;
 117         global $globals;
 118         $globals->xdb->query('TRUNCATE TABLE geoloc_maps');
 119         $s = '';
 120   while (!feof($f)) {
 121         $l = fgetcsv($f, 1024, ';', '"');
 122         foreach ($l as $i => $val) if ($val != 'NULL') $l[$i] = '\''.addslashes($val).'\'';
 123         $s .= ',('.implode(',',$l).')';
 124   }
 125         $globals->xdb->execute('INSERT INTO geoloc_maps VALUES '.substr($s, 1));
 126         return true;
 127 }
 128
 129 // {{{ get_address_text($adr)
 130 /** make the text of an address that can be read by a mailman
 131  * @param $adr an array with all the usual fields
 132  */
 133 function get_address_text($adr) {
 134     $t = "";
 135     if ($adr['adr1']) $t.= $adr['adr1'];
 136     if ($adr['adr2']) $t.= "\n".$adr['adr2'];
 137     if ($adr['adr3']) $t.= "\n".$adr['adr3'];
 138     $l = "";
 139     if ($adr['display']) {
 140         $keys = explode(' ', $adr['display']);
 141         foreach ($keys as $key) {
 142             if (isset($adr[$key]))
 143                 $l .= " ".$adr[$key];
 144             else
 145                 $l .= " ".$key;
 146         }
 147         if ($l) $l = substr($l, 1);
 148     }
 149     else
 150     {
 151         if ($adr['country'] == 'US' || $adr['country'] == 'CA' || $adr['country'] == 'GB') {
 152             if ($adr['city']) $l .= $adr['city'].",\n";
 153             if ($adr['region']) $l .= $adr['region']." ";
 154             if ($adr['postcode']) $l .= $adr['postcode'];
 155         } else {
 156             if ($adr['postcode']) $l .= $adr['postcode']." ";
 157             if ($adr['city']) $l .= $adr['city'];
 158         }
 159     }
 160     if ($l) $t .= "\n".trim($l);
 161     if ($adr['country'] != '00' && (!$adr['countrytxt'] || $adr['countrytxt'] == strtoupper($adr['countrytxt']))) {
 162         global $globals;
 163         $res = $globals->xdb->query("SELECT pays FROM geoloc_pays WHERE a2 = {?}", $adr['country']);
 164         $adr['countrytxt'] = $res->fetchOneCell();
 165     }
 166     if ($adr['countrytxt']) $t .= "\n".$adr['countrytxt'];
 167     return trim($t);
 168 }
 169 // }}}
 170
 171 // {{{ compare_addresses_text($a, $b)
 172 /** compares if two address matches
 173  * @param $a the raw text of an address
 174  * @param $b the raw text of a complete valid address
 175  */
 176 function compare_addresses_text($a, $b) {
 177     $ta = strtoupper(preg_replace(array("/[0-9,\"'#~:;_\- ]/", "/\r\n/"), array("", "\n"), $a));
 178     $tb = strtoupper(preg_replace(array("/[0-9,\"'#~:;_\- ]/", "/\r\n/"), array("", "\n"), $b));
 179
 180     $la = explode("\n", $ta);
 181     $lb = explode("\n", $tb);
 182
 183     if (count($lb) > count($la) + 1) return false;
 184     foreach ($la as $i=>$l) if (levenshtein($l, $lb[$i]) > 3) return false;
 185     return true;
 186 }
 187
 188 // }}}
 189
 190 function empty_address() {
 191     return Array(
 192         "adr1" => "",
 193         "adr2" => "",
 194         "adr3" => "",
 195         "cityid" => NULL,
 196         "city" => "",
 197         "postcode" => "",
 198         "region" => "",
 199         "regiontxt" => "",
 200         "country" => "00",
 201         "countrytxt" => "");
 202 }
 203
 204 // create a simple address from a text without geoloc
 205 function cut_address($txt) {
 206     $txt = str_replace("\r\n", "\n", $txt);
 207     ereg("^([^\n]*)(\n([^\n]*)(\n(.*))?)?$", trim($txt), $a);
 208     return array("adr1" => trim($a[1]), "adr2" => trim($a[3]), "adr3" => trim(str_replace("\n", " ", $a[5])));
 209 }
 210
 211 // {{{ localize_addresses($uid)
 212 /* localize all the address of a user and modify the database
 213  * if the new address match with the old one
 214  * @param $uid the id of the user
 215  */
 216 function localize_addresses($uid) {
 217     global $globals;
 218     $res = $globals->xdb->iterator("SELECT * FROM adresses WHERE uid = {?} and (cityid IS NULL OR cityid = 0)", $uid);
 219     $erreur = Array();
 220
 221     while ($a = $res->next()) {
 222         $new = get_address_infos($ta = get_address_text($a));
 223         if (compare_addresses_text($ta, get_address_text($new))) {
 224             $globals->xdb->execute("UPDATE adresses SET
 225                 adr1 = {?}, adr2 = {?}, adr3 = {?},
 226                 cityid = {?}, city = {?}, postcode = {?},
 227                 region = {?}, regiontxt = {?}, country = {?},
 228                 glat = {?}, glng = {?}
 229                 WHERE uid = {?} AND adrid = {?}",
 230                 $new['adr1'], $new['adr2'], $new['adr3'],
 231                 $new['cityid'], $new['city'], $new['postcode'],
 232                 $new['region'], $new['regiontxt'], $new['country'],
 233                 $new['precise_lat'], $new['precise_lon'],
 234                 $uid, $a['adrid']);
 235                 $new['store'] = true;
 236                 if (!$new['cityid']) $erreur[$a['adrid']] = $new;
 237         } else {
 238             $new['store'] = false;
 239             $erreur[$a['adrid']] = $new;
 240         }
 241     }
 242     return $erreur;
 243 }
 244 // }}}
 245
 246 // {{{ synchro_city($id)
 247 /** synchronise the local geoloc_city base to geoloc.org
 248  * @param $id the id of the city to synchronize
 249  */
 250  function synchro_city($id) {
 251     global $globals;
 252     $url = $globals->geoloc->webservice_url."cityFinder.php?method=id&id=".$id."&out=sql";
 253     if (!($f = @fopen($url, 'r'))) return false;
 254     $s = fgets($f);
 255     if ($s)
 256         return $globals->xdb->execute("REPLACE INTO geoloc_city VALUES ".$s) > 0;
 257  }
 258  // }}}
 259
 260 // {{{ function fix_cities_not_on_map($limit)
 261 function fix_cities_not_on_map($limit=false)
 262 {
 263     global $globals;
 264     $missing = $globals->xdb->query("SELECT c.id FROM geoloc_city AS c LEFT JOIN geoloc_city_in_maps AS m ON(c.id = m.city_id) WHERE m.city_id IS NULL".($limit?" LIMIT $limit":""));
 265     $maps = get_cities_maps($missing->fetchColumn());
 266     if ($maps)
 267     {
 268         $values = "";
 269         foreach ($maps as $cityid => $maps_c)
 270             foreach ($maps_c as $map_id)
 271                 $values .= ",($cityid, $map_id, '')";
 272         $globals->xdb->execute("REPLACE INTO geoloc_city_in_maps VALUES ".substr($values, 1));
 273     }
 274     else
 275         return false;
 276
 277     $maxlevelquery = $globals->xdb->query("SELECT MAX(level) FROM geoloc_maps");
 278     $maxlevel = $maxlevelquery->fetchOneCell();
 279     for ($level = $maxlevel; $level >= 0; $level--)
 280         $globals->xdb->query("
 281             UPDATE geoloc_city AS gc
 282         INNER JOIN geoloc_city_in_maps AS gcim ON(gc.id = gcim.city_id)
 283         INNER JOIN geoloc_maps AS gm ON(gm.id = gcim.map_id AND gm.level = {?})
 284          LEFT JOIN geoloc_city_in_maps AS gcim2 ON(gc.id = gcim2.city_id AND gcim2.infos = 'smallest')
 285                SET gcim.infos = 'smallest'
 286              WHERE gcim2.city_id IS NULL", $level);
 287
 288     return true;
 289 }
 290 // }}}
 291
 292
 293 function geoloc_to_x($lon, $lat) { return deg2rad(1) * $lon *100; }
 294
 295 function geoloc_to_y($lon, $lat) {
 296         if ($lat < -75) return latToY(-75);
 297         if ($lat > 75) return latToY(75);
 298   return -100 * log(tan(pi()/4 + deg2rad(1)/2*$lat));
 299 }
 300
 301 function size_of_city($nb) { $s = round(log($nb + 1)*2,2); if ($s < 1) return 1; return $s; }
 302 function size_of_territory($nb) { return size_of_city($nb); }
 303
 304 function geoloc_getData_subcities($mapid, $SFields, &$cities, $direct=true) {
 305     global $globals;
 306     for ($i_mapfield=0; $i_mapfield < count($SFields) ; $i_mapfield++) if ($SFields[$i_mapfield]->fieldFormName == 'mapid') break;
 307     $SFields[$i_mapfield] = new MapSField('mapid', array('gcim.map_id'), array('adresses','geoloc_city_in_maps'), array('am','gcim'), array(getadr_join('am'), 'am.cityid = gcim.city_id'), $mapid);
 308
 309     $fields = new SFieldGroup(true, $SFields);
 310     $where = $fields->get_where_statement();
 311     if ($where) $where = " AND ".$where;
 312
 313     $cityres = $globals->xdb->iterator("
 314         SELECT  gc.id,
 315                 gc.lon / 100000 AS x, gc.lat/100000 AS y,
 316                 gc.name,
 317                 COUNT(u.user_id) AS pop,
 318                 SUM(u.promo % 2) AS yellow
 319           FROM auth_user_md5 AS u
 320     INNER JOIN auth_user_quick AS q ON(u.user_id = q.user_id)
 321             ".$fields->get_select_statement()."
 322                                  LEFT JOIN geoloc_city AS gc ON(gcim.city_id = gc.id)
 323          WHERE ".($direct?"gcim.infos = 'smallest'":"1")."
 324          $where
 325       GROUP BY gc.id,gc.alias ORDER BY pop DESC");
 326     while ($c = $cityres->next())
 327         if ($c['pop'] > 0)
 328         {
 329             $city = $c;
 330             $city['x'] = geoloc_to_x($c['x'], $c['y']);
 331             $city['y'] = geoloc_to_y($c['x'], $c['y']);
 332             $city['size'] = size_of_city($c['pop']);
 333             $cities[$c['id']] = $city;
 334         }
 335 }
 336
 337 function geoloc_getData_subcountries($mapid, $SFields, $minentities) {
 338     global $globals;
 339     $countries = array();
 340     $cities = array();
 341
 342     if ($mapid === false)
 343         $wheremapid = "WHERE gm.parent IS NULL";
 344     else
 345         $wheremapid = "WHERE   gm.parent = {?}";
 346     $submapres = $globals->xdb->iterator(
 347         "SELECT  gm.map_id AS id, gm.name, gm.x, gm.y, gm.xclip, gm.yclip,
 348                 gm.width, gm.height, gm.scale, 1 AS rat
 349         FROM    geoloc_maps AS gm
 350         ".$wheremapid, Env::get('mapid',''));
 351
 352     while ($c = $submapres->next())
 353     {
 354         $country = $c;
 355         $country['name'] = utf8_decode($country['name']);
 356         $country['color'] = 0xFFFFFF;
 357         $country['swf'] = $globals->geoloc->webservice_url."maps/mercator/map_".$c['id'].".swf";
 358         $countries[$c['id']] = $country;
 359     }
 360
 361     if ($mapid === false) return array($countries, $cities);
 362
 363         geoloc_getData_subcities(Env::getInt('mapid'), $SFields, $cities);
 364         $nbcities = count($cities);
 365         $nocity = $nbcities == 0;
 366
 367     for ($i_mapfield=0; $i_mapfield < count($SFields) ; $i_mapfield++) if ($SFields[$i_mapfield]->fieldFormName == 'mapid') break;
 368         $SFields[$i_mapfield] = new MapSField('mapid', array('map.parent'), array('adresses','geoloc_city_in_maps','geoloc_maps'), array('am','gcim','map'), array(getadr_join('am'), 'am.cityid = gcim.city_id', 'map.map_id = gcim.map_id'));
 369
 370         $fields = new SFieldGroup(true, $SFields);
 371         $where = $fields->get_where_statement();
 372         if ($where) $where = " WHERE ".$where;
 373
 374         $countryres = $globals->xdb->iterator("
 375             SELECT  map.map_id AS id,
 376                     COUNT(u.user_id) AS nbPop,
 377                     SUM(u.promo % 2) AS yellow,
 378                     COUNT(DISTINCT gcim.city_id) AS nbCities,
 379                     SUM(IF(u.user_id IS NULL,0,am.glng)) AS lonPop,
 380                     SUM(IF(u.user_id IS NULL, 0,am.glat)) AS latPop
 381               FROM  auth_user_md5 AS u
 382         INNER JOIN  auth_user_quick AS q ON(u.user_id = q.user_id)
 383                     ".$fields->get_select_statement()."
 384              $where
 385           GROUP BY  map.map_id ORDER BY NULL", $hierarchy);
 386
 387         $maxpop = 0;
 388         $nbentities = $nbcities + $countryres->total();
 389         while ($c = $countryres->next())
 390         {
 391             $c['latPop'] /= $c['nbPop'];
 392             $c['lonPop'] /= $c['nbPop'];
 393             $c['rad'] = size_of_territory($c['nbPop']);
 394             if ($maxpop < $c['nbPop']) $maxpop = $c['nbPop'];
 395             $c['xPop'] = geoloc_to_x($c['lonPop'], $c['latPop']);
 396             $c['yPop'] = geoloc_to_y($c['lonPop'], $c['latPop']);
 397             $countries[$c['id']] = array_merge($countries[$c['id']], $c);
 398
 399             $nbcities += $c['nbCities'];
 400         }
 401
 402         if ($nocity && $nbcities < $minentities)
 403         {
 404             foreach($countries as $i => $c)
 405             {
 406                 $countries[$i]['nbPop'] = 0;
 407                 if ($c['nbCities'] > 0)
 408                     geoloc_getData_subcities($c['id'], $SFields, $cities, false);
 409             }
 410         }
 411
 412         foreach ($countries as $i => $c) if ($c['nbPop'] > 0)
 413         {
 414             $lambda = pow($c['nbPop'] / $maxpop,0.3);
 415             $countries[$i]['color'] = 0x0000FF + round((1-$lambda) * 0xFF)*0x010100;
 416         }
 417
 418     return array($countries, $cities);
 419 }
 420 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker:
 421 ?>