err.no Git - mapper/blob - src/poi.c

   1 #include <config.h>
   2
   3 #include <unistd.h>
   4 #include <stdlib.h>
   5 #include <string.h>
   6 #include <strings.h>
   7 #include <stddef.h>
   8 #include <locale.h>
   9 #include <math.h>
  10 #include <errno.h>
  11 #include <glib/gstdio.h>
  12 #include <gtk/gtk.h>
  13 #include <fcntl.h>
  14 #include <gconf/gconf-client.h>
  15 #include <libxml/parser.h>
  16 #include <libintl.h>
  17 #include <locale.h>
  18 #include <sqlite3.h>
  19
  20 #include "utils.h"
  21 #include "poi.h"
  22 #include "gps.h"
  23 #include "map.h"
  24 #include "mapper-types.h"
  25 #include "settings.h"
  26 #include "db.h"
  27 #include "osm.h"
  28 #include "osm-db.h"
  29 #include "latlon.h"
  30
  31 static sqlite3 *poidb;
  32
  33 /* POI Icon theme. "classic" or "square". Should be made into a configuration option */
  34 static gchar *theme="square";
  35 static gchar *theme_base=DATADIR "/icons/map-icons";
  36
  37 /* Hash table for caching POI icons */
  38 static GHashTable *poi_icon_hash = NULL;
  39
  40 struct _poi_categories {
  41         node_type_t type;
  42         const gchar *name, *desc, *icon, *color;
  43 };
  44
  45 /* The default POI categories */
  46 static struct _poi_categories default_poi_categories[] = {
  47         { NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles."  , "vehicle/parking", "#2020ff" },
  48         { NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles."  , "vehicle/fuel_station", "#4040f0" },
  49         { NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras."  , "vehicle/restrictions/speed_trap", "#ff0000" },
  50         { NODE_AMENITY_HOSPITAL, "Hospital", "Get medical help"  , "health/hospital", "#ff4040" },
  51         { NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs."  , "health/pharmacy", "#40f040" },
  52         { NODE_AMENITY_POLICE, "Police", "Police station" , "public/police", "#8570ff" },
  53         { NODE_TOURISM_HOTEL, "Hotel", "Places to stay temporarily or for the night." , "accommodation/hotel", "#ba20ba" },
  54         { NODE_TOURISM_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap." , "accommodation/hostel", "#ba30ba" },
  55         { NODE_TOURISM_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap." , "accommodation/motel", "#ba40ba" },
  56         { NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint." , "money/atm", "#40a040" },
  57         { NODE_AMENITY_BANK, "Bank", "Place to take care of your money." , "money/bank", "#50b550" },
  58         { NODE_AMENITY_POST, "Post office", "Place to handle mail." , "public/post_office", "#ff6868" },
  59         { NODE_AMENITY_POST_BOX, "Post box", "Send letters." , "public/post_box", "#ff6060" },
  60         { NODE_TOURISM_INFO, "Tourism info", "A place for tourists to get information." , "misc/information", "#4af04a" },
  61         { NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here." , "transport/taxi", "#50ffff" },
  62         { NODE_RAILWAY_STATION, "Railway station", "Transportation by train." , "transport/railway", "#fa7070" },
  63         { NODE_RAILWAY_HALT, "Railway halt", "" , "transport/railway_small", "#f27777" },
  64         { NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus." , "transport/bus", "#f07070" },
  65         { NODE_AMENITY_BOAT, "Harbour", "Transportation by boat." , "transport/ferry", "#9090ee" },
  66         { NODE_AIRPORT_TERMINAL, "Airport", "Transportation by air." , "transport/airport", "#909099" },
  67         { NODE_TOURISM_CAMP_SITE, "Camp site", "Place to go camping" , "accommodation/camping", "#90f080" },
  68         { NODE_TOURISM_CARAVAN_SITE, "Caravan site", "" , "accommodation/camping/caravan", "#90ff80" },
  69         { NODE_TOURISM_PICNIC_SITE, "Picnic", "Place to have a Picnic" , "recreation/picnic", "#a5f8e5" },
  70         { NODE_AMENITY_FOOD, "Fast food", "Places to eat or drink." , "food/fastfood", "#e5960d" },
  71         { NODE_AMENITY_RESTAURANT, "Restaurant", "Fancy places to eat or drink." , "food/restaurant", "#e5960d" },
  72         { NODE_AMENITY_PUB, "Pub", "Place to drink." , "food/pub", "#f5960d" },
  73         { NODE_AMENITY_NIGHTCLUB, "Disco, Club", "Place to drink, party and dance." , "food/bar", "#f59c1d" },
  74         { NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat." , "food/cafe", "#ff960d" },
  75         { NODE_AMENITY_CINEMA, "Cinema", "Place to see movies" , "recreation/cinema", "#9090a0" },
  76         { NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing" , "recreation/theater", "#9595a5" },
  77         { NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services." , "shopping", "#61ef1b" },
  78         { NODE_AMENITY_SHOP_ADULT, "Adult shop", "Shops providing adult items, such as sex toys,pornography and fetish clothing" , "", "#ff0000" },
  79         { NODE_AMENITY_POW, "Place of Worchip", "" , "religion/church", "#7c5b0b" },
  80         { NODE_TOURISM_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters." , "recreation/theme_park", "#8eef1b" },
  81         { NODE_AMENITY_COLLEGE, "College Campus/Building", "" , "education/college", "#813fdc" },
  82         { NODE_AMENITY_SCHOOL, "School", "" , "education/school", "#813fdc" },
  83         { NODE_AMENITY_LIBRARY, "Library", "Place to read and borrow books and magazines" , "shopping/rental/library", "#803090" },
  84         { NODE_AMENITY_TOWNHALL, "Townhall", "" , "", "#408090" },
  85         { NODE_AMENITY_WC, "WC/Toilets", "" , "public/toilets", "#e1d62c" },
  86         { NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone" , "public/telephone", "#208060" },
  87         { NODE_TOURISM_ATTRACTION, "Attraction", "Something interesting" , "misc", "#005000" },
  88
  89         { NODE_HISTORIC_MUSEUM, "Museum", "A place where objects of historical, artistic, or scientific interest are exhibited, preserved or studied." , "sightseeing/museum", "#202020" },
  90         { NODE_HISTORIC_CASTLE, "Castle", "Historical building or group of building used for defense by military forces, whose main structures are walls and towers." , "sightseeing/castle", "#404040" },
  91
  92         { NODE_SPORT_CENTER, "Sport Center", "" , "sports/centre", "#101080" },
  93         { NODE_SPORT_STADIUM, "Sport Stadium", "" , "sports/stadium", "#101080" },
  94         { NODE_SPORT_SKIING, "Skiing", "" , "sports/skiing", "#5050A0" },
  95         { NODE_SPORT_SWIMMING, "Swimming", "" , "sports/swimming", "#102080" },
  96         { NODE_SPORT_FOOTBALL, "Football", "" , "sports/football", "#102080" },
  97         { NODE_SPORT_SOCCER, "Soccer", "" , "sports/soccer", "#102080" },
  98         { NODE_SPORT_GOLF, "Golf", "" , "sports/golf", "#102080" },
  99         { NODE_SPORT_TENNIS, "Tennis", "" , "sports/tennis", "#101080" },
 100         { NODE_SPORT_BOWLING, "Bowling", "" , "sports", "#101080" },
 101         { NODE_SPORT_RUGBY, "Rugby", "" , "sports", "#101080" },
 102         { NODE_SPORT_CLIMBING, "Climbing", "" , "sports", "#101080" },
 103         { NODE_SPORT_CYCLING, "Cycling", "" , "sports/bicycle", "#101080" },
 104         { NODE_SPORT_MOTOR, "Motor sport", "" , "sports", "#101080" },
 105         { NODE_SPORT_HOCKEY, "Hockey", "" , "sports", "#5050A0" },
 106         { NODE_SPORT_SKATING, "Skating", "" , "sports", "#5050A0" },
 107         { NODE_SPORT_SKATEBOARD, "Skateboard", "" , "sports", "#101080" },
 108         { NODE_SPORT_HORSES, "Horses", "Horse riding or racing" , "sports/riding", "#101080" },
 109         { NODE_SPORT_DOG, "Dog racing", "" , "sports", "#101080" },
 110         { NODE_SPORT_BASKETBALL, "Basketball", "" , "sports", "#101080" },
 111         { NODE_SPORT_BASEBALL, "Baseball", "" , "sports", "#101080" },
 112         { NODE_SPORT_CANOE, "Canoe", "" , "", "#101080" },
 113         { NODE_SPORT_CROQUET, "Croquet", "" , "sports", "#101080" },
 114         { NODE_SPORT_CRICKET, "Cricket", "" , "sports", "#101080" },
 115         { NODE_SPORT_SHOOTING, "Shooting", "Shooting range" , "sports", "#101080" },
 116         { NODE_SPORT_PAINTBALL, "Paintball", "Run around and shoot people with paintballs" , "sports", "#101080" },
 117         { NODE_SPORT_TABLE_TENNIS, "Table tennis", "" , "sports", "#101080" },
 118         { NODE_SPORT_PELOTA, "Pelota", "" , "sports", "#101080" },
 119         { NODE_SPORT_RACQUET, "Racquet", "" , "sports/racquetball", "#101080" },
 120         { NODE_SPORT_BOWLS, "Lawn Bowls", "" , "sports", "#101080" },
 121         { NODE_SPORT_ATHLETICS, "Athletics", "" , "sports", "#101080" },
 122         { NODE_SPORT_OTHER, "Other Sports", "" , "sports/stadium", "#101077" },
 123
 124         { NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else." , "misc", "#002000" },
 125         { NODE_POI_END, NULL, NULL }
 126 };
 127
 128 static gboolean
 129 poi_populate_categories(sqlite3 *db)
 130 {
 131 sqlite3_stmt *sql_cat;
 132 guint i;
 133
 134 sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)"
 135                         " values (?, ?, ?, 1, ?, ?, ?)",
 136                         -1, &sql_cat, NULL);
 137
 138 for (i=0; default_poi_categories[i].name; i++) {
 139         sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
 140         sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name, -1, SQLITE_STATIC);
 141         sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc, -1, SQLITE_STATIC);
 142         sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
 143         sqlite3_bind_text(sql_cat, 5, default_poi_categories[i].icon, -1, SQLITE_STATIC);
 144         sqlite3_bind_text(sql_cat, 6, default_poi_categories[i].color, -1, SQLITE_STATIC);
 145         if (sqlite3_step(sql_cat)==SQLITE_OK)
 146                 g_printf("Failed to update category: %d [%s]\n", i, default_poi_categories[i].name);
 147         sqlite3_reset(sql_cat);
 148         sqlite3_clear_bindings(sql_cat);
 149 }
 150
 151 sqlite3_finalize(sql_cat);
 152
 153 return TRUE;
 154 }
 155
 156 const gchar *
 157 poi_get_icon_from_type(node_type_t t)
 158 {
 159 guint i;
 160
 161 for (i=0; default_poi_categories[i].name; i++) {
 162         if (t==default_poi_categories[i].type)
 163                 return default_poi_categories[i].icon;
 164 }
 165 return NULL;
 166 }
 167
 168 gboolean
 169 poi_db_create(sqlite3 *db)
 170 {
 171 gchar **pszResult;
 172 guint nRow, nColumn;
 173
 174 if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 175                                    &pszResult, &nRow, &nColumn, NULL)) {
 176         gchar *create_sql;
 177
 178         g_printerr("Creating initial POI tables\n");
 179
 180         create_sql = sqlite3_mprintf
 181             ("create table poi "
 182                 "(poi_id INTEGER PRIMARY KEY AUTOINCREMENT, "
 183                 "lat real, "
 184                 "lon real, "
 185                 "elev real, "
 186                 "label text, "
 187                 "desc text, "
 188                 "url text, "
 189                 "postal_code text,"
 190                 "public int default 1, "
 191                 "source int default 0, "
 192                 "cat_id int default 900,"
 193                 "osm_id int default 0, "
 194                 "isin_c int default 0, "
 195                 "isin_p int default 0, "
 196                 "priority int default 2, "
 197                 "addtime int);"
 198              "create table category (cat_id INTEGER PRIMARY KEY AUTOINCREMENT,"
 199                 "label text, "
 200                 "desc text, "
 201                 "icon text, "
 202                 "color char(7), "
 203                 "priority int default 2, "
 204                 "enabled int);");
 205
 206         if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
 207             && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 208                                   &pszResult, &nRow, &nColumn, NULL))) {
 209                 g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
 210                 sqlite3_close(db);
 211                 db = NULL;
 212                 return FALSE;
 213         }
 214 } else {
 215         sqlite3_free_table(pszResult);
 216 }
 217
 218 /* Make sure default categories exists */
 219 poi_populate_categories(db);
 220 return TRUE;
 221 }
 222
 223 typedef enum {
 224         PS_LAT=0,
 225         PS_LON,
 226         PS_ID,
 227         PS_LABEL,
 228         PS_DESC,
 229         PS_CAT_ID,
 230         PS_CAT_LABEL,
 231         PS_CAT_DESC,
 232         PS_CAT_ICON,
 233         PS_CAT_COLOR,
 234         PS_SOURCE,
 235         PS_PUBLIC,
 236         PS_URL,
 237         PS_POSTAL_CODE,
 238 } poi_sql_column;
 239
 240 #define POI_BASE_SQL_FIELDS "p.lat, p.lon, p.poi_id, p.label, p.desc, p.cat_id, c.label, c.desc, c.icon, c.color, p.source, p.public, p.url, p.postal_code"
 241 #define POI_MINI_SQL_FIELDS "p.lat, p.lon, p.poi_id, p.label, p.desc, p.cat_id, c.label, c.desc"
 242
 243 gboolean
 244 poi_db_prepare(sqlite3 *db)
 245 {
 246         /* Select POIs inside given minmax lat,lon */
 247         if (sqlite3_prepare_v2(db,
 248                         "select "
 249                         POI_BASE_SQL_FIELDS
 250                         " from poi p, category c "
 251                         " where p.lat between ? and ? "
 252                         " and p.lon between ? and ? "
 253                         " and c.enabled=1 and p.cat_id=c.cat_id order by c.priority limit 500",
 254                         -1, &poisql.select_poi, NULL)!=SQLITE_OK)
 255         return FALSE;
 256
 257         /* Get POI with given ID */
 258         if (sqlite3_prepare_v2(db,
 259                         "select "
 260                         POI_BASE_SQL_FIELDS
 261                         " from poi p, category c "
 262                         " where p.poi_id = ? "
 263                         " and p.cat_id=c.cat_id",
 264                         -1, &poisql.select_poi_by_id, NULL)!=SQLITE_OK)
 265         return FALSE;
 266
 267         /* Search POIs by label and any category */
 268         if (sqlite3_prepare_v2(db,
 269                         "select "
 270                         POI_BASE_SQL_FIELDS
 271                         " from poi p, category c "
 272                         " where p.lat between ? and ? "
 273                         " and p.lon between ? and ? "
 274                         " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) order by p.label, c.label",
 275                         -1, &poisql.select_poi_search, NULL)!=SQLITE_OK)
 276         return FALSE;
 277
 278         /* Search POI by label and category */
 279         if (sqlite3_prepare_v2(db,
 280                         "select "
 281                         POI_BASE_SQL_FIELDS
 282                         " from poi p, category c "
 283                         " where p.lat between ? and ? "
 284                         " and p.lon between ? and ? "
 285                         " and c.enabled=1 and p.cat_id = c.cat_id and (p.label like $NAME or p.postal_code like $NAME) and c.cat_id = ? order by p.label",
 286                         -1, &poisql.select_poi_search_cat, NULL)!=SQLITE_OK)
 287         return FALSE;
 288
 289         /* Search POIs by category */
 290         if (sqlite3_prepare_v2(db,
 291                         "select "
 292                         POI_BASE_SQL_FIELDS
 293                         " from poi p, category c "
 294                         " where p.lat between ? and ? "
 295                         " and p.lon between ? and ? "
 296                         " and c.enabled=1 and p.cat_id=c.cat_id and c.cat_id=? order by p.label",
 297                         -1, &poisql.select_poi_by_cat, NULL)!=SQLITE_OK)
 298         return FALSE;
 299
 300         /* Select any nearest POI */
 301         if (sqlite3_prepare_v2(db,
 302                         "select "
 303                         POI_MINI_SQL_FIELDS
 304                         " from poi p, category c "
 305                         " where c.enabled = 1 and p.cat_id = c.cat_id "
 306                         " and p.lat between $LAT-0.10 and $LAT+0.10 "
 307                         " and p.lon between $LON-0.10 and $LAT+0.10 "
 308                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 309                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 310                         -1, &poisql.select_nearest_poi, NULL)!=SQLITE_OK)
 311         return FALSE;
 312
 313         /* Insert POI */
 314         sqlite3_prepare_v2(db,  "insert into poi (lat, lon, label, desc, url, postal_code, cat_id, addtime, public, source)"
 315                                                         " values (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)", -1, &poisql.insert_poi, NULL);
 316         /* update poi */
 317         sqlite3_prepare_v2(db, "update poi set label=?, desc=?, cat_id=? where poi_id=?", -1, &poisql.update_poi, NULL);
 318         /* delete from poi */
 319         sqlite3_prepare_v2(db, "delete from poi where poi_id=?", -1, &poisql.delete_poi, NULL);
 320         /* delete from poi by cat_id */
 321         sqlite3_prepare_v2(db, "delete from poi where cat_id=?", -1, &poisql.delete_poi_by_catid, NULL);
 322
 323         /* select from category */
 324         sqlite3_prepare_v2(db,
 325                         "select c.label, c.desc, c.enabled"
 326                         " from category c where c.cat_id = ?",
 327                         -1, &poisql.select_cat, NULL);
 328         /* insert into category */
 329         sqlite3_prepare_v2(db,
 330                         "insert into category (label, desc, enabled)"
 331                         " values (?, ?, ?)", -1, &poisql.insert_cat, NULL);
 332         /* update category */
 333         sqlite3_prepare_v2(db,
 334                         "update category set label = ?, desc = ?,"
 335                         " enabled = ? where poi_id = ?",
 336                         -1, &poisql.update_cat, NULL);
 337         /* delete from category */
 338         sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
 339                         -1, &poisql.delete_cat, NULL);
 340         /* enable category */
 341         sqlite3_prepare_v2(db,
 342                         "update category set enabled = ?"
 343                         " where cat_id = ?", -1, &poisql.toggle_cat, NULL);
 344         /* select all category */
 345         sqlite3_prepare_v2(db,
 346                         "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
 347                         " count(p.poi_id)"
 348                         " from category c"
 349                         " left outer join poi p on c.cat_id = p.cat_id"
 350                         " group by c.cat_id, c.label, c.desc, c.enabled "
 351                         " order by c.priority,c.label", -1, &poisql.selall_cat, NULL);
 352
 353         /* Select quick categories */
 354         sqlite3_prepare_v2(db,
 355                         "select c.cat_id, c.label, c.icon, c.color"
 356                         " from category c where c.enabled=1 "
 357                         " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL);
 358
 359         return TRUE;
 360 }
 361
 362 void
 363 poi_icon_hash_clear(void)
 364 {
 365 #if (GLIB_CHECK_VERSION (2, 12, 0))
 366 g_hash_table_remove_all(poi_icon_hash);
 367 #else
 368 g_hash_table_foreach_remove(poi_icon_hash, gtk_true, NULL);
 369 #endif
 370 }
 371
 372 void
 373 poi_deinit(sqlite3 *db)
 374 {
 375 if (poi_icon_hash) {
 376         g_hash_table_destroy(poi_icon_hash);
 377         poi_icon_hash=NULL;
 378 }
 379
 380 sqlite3_finalize(poisql.select_quick_cat);
 381 sqlite3_finalize(poisql.selall_cat);
 382 sqlite3_finalize(poisql.toggle_cat);
 383 sqlite3_finalize(poisql.delete_cat);
 384 sqlite3_finalize(poisql.update_cat);
 385 sqlite3_finalize(poisql.insert_cat);
 386 sqlite3_finalize(poisql.select_cat);
 387 sqlite3_finalize(poisql.insert_poi);
 388 sqlite3_finalize(poisql.update_poi);
 389 sqlite3_finalize(poisql.delete_poi);
 390 sqlite3_finalize(poisql.delete_poi_by_catid);
 391 sqlite3_finalize(poisql.select_nearest_poi);
 392 sqlite3_finalize(poisql.select_poi);
 393 sqlite3_finalize(poisql.select_poi_search);
 394 sqlite3_finalize(poisql.select_poi_search_cat);
 395 }
 396
 397 gboolean
 398 poi_init(sqlite3 **db)
 399 {
 400 if (!poi_icon_hash)
 401         poi_icon_hash=g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
 402
 403 if (!db || !*db)
 404         return FALSE;
 405
 406 poidb=*db;
 407 poi_db_create(poidb);
 408 if (poi_db_prepare(poidb)==FALSE) {
 409         g_printerr("Failed to prepare POI SQL statements:");
 410         g_printf("SQLITE: %s\n", sqlite3_errmsg(poidb));
 411         return FALSE;
 412 }
 413 return TRUE;
 414 }
 415
 416 poi_info *
 417 poi_new(void)
 418 {
 419 poi_info *p;
 420
 421 p=g_slice_new0(poi_info);
 422 p->source=POI_SOURCE_USER;
 423 /* XXX: Set defaults ? */
 424 return p;
 425 }
 426
 427 void
 428 poi_free(poi_info *p)
 429 {
 430 if (p->label)
 431         g_free(p->label);
 432 if (p->desc)
 433         g_free(p->desc);
 434 if (p->url)
 435         g_free(p->url);
 436 if (p->postal_code)
 437         g_free(p->postal_code);
 438 if (p->cat_label)
 439         g_free(p->cat_label);
 440 if (p->cat_desc)
 441         g_free(p->cat_desc);
 442 g_slice_free(poi_info, p);
 443 }
 444
 445 static GtkListStore *
 446 poi_list_store_new(void) {
 447 return gtk_list_store_new(ITEM_NUM_COLUMNS,
 448                         G_TYPE_INT,     /* POI ID */
 449                         G_TYPE_INT,     /* Category ID */
 450                         G_TYPE_DOUBLE,  /* Latitude */
 451                         G_TYPE_DOUBLE,  /* Longitude */
 452                         G_TYPE_DOUBLE,  /* Dist */
 453                         G_TYPE_STRING,  /* Lat/Lon */
 454                         G_TYPE_STRING,  /* Label */
 455                         G_TYPE_STRING,  /* Desc. */
 456                         G_TYPE_STRING,  /* Category Label */
 457                         G_TYPE_STRING,  /* Icon */
 458                         G_TYPE_STRING); /* Color */
 459 }
 460
 461 /*************************************
 462  * POI Category functions
 463  *
 464  */
 465
 466 poi_category *
 467 poi_category_new(void)
 468 {
 469 return g_slice_new0(poi_category);
 470 }
 471
 472 void
 473 poi_category_free(poi_category *c)
 474 {
 475 if (c->label)
 476         g_free(c->label);
 477 if (c->desc)
 478         g_free(c->desc);
 479 g_slice_free(poi_category, c);
 480 }
 481
 482 gboolean
 483 poi_category_toggle(guint cat_id, gboolean cat_enabled)
 484 {
 485 g_return_val_if_fail(poisql.toggle_cat, FALSE);
 486
 487 if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
 488     SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
 489     SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
 490                 return FALSE;
 491         }
 492 return TRUE;
 493 }
 494
 495 gboolean
 496 poi_category_get(guint cat_id, poi_category **c)
 497 {
 498 poi_category *cc;
 499
 500 g_return_val_if_fail(poisql.select_cat, FALSE);
 501 if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id) || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
 502         sqlite3_reset(poisql.select_cat);
 503         return FALSE;
 504 }
 505
 506 cc=poi_category_new();
 507 cc->id=cat_id;
 508 cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
 509 cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
 510 cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
 511
 512 sqlite3_reset(poisql.select_cat);
 513 sqlite3_clear_bindings(poisql.select_cat);
 514 *c=cc;
 515 return TRUE;
 516 }
 517
 518 gboolean
 519 poi_category_update(guint cat_id, poi_category *c)
 520 {
 521 gboolean results=TRUE;
 522
 523 if (!_db)
 524         return FALSE;
 525
 526 g_return_val_if_fail(poisql.update_cat, FALSE);
 527 g_return_val_if_fail(poisql.insert_cat, FALSE);
 528
 529 if (cat_id > 0) {
 530 /* edit category */
 531                 if (SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, SQLITE_STATIC)
 532                     || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, SQLITE_STATIC)
 533                     || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
 534                     || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
 535                     || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
 536                         results = FALSE;
 537                 }
 538                 sqlite3_reset(poisql.update_cat);
 539                 sqlite3_clear_bindings(poisql.update_cat);
 540         } else {
 541                 /* add category */
 542                 if (SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, SQLITE_STATIC)
 543                     || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, SQLITE_STATIC)
 544                     || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
 545                     || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
 546                         results = FALSE;
 547                 }
 548                 sqlite3_reset(poisql.insert_cat);
 549                 sqlite3_clear_bindings(poisql.insert_cat);
 550         }
 551 return results;
 552 }
 553
 554 gboolean
 555 poi_category_delete(guint id)
 556 {
 557 if (!poidb)
 558         return FALSE;
 559
 560 g_return_val_if_fail(poisql.delete_poi_by_catid, FALSE);
 561 g_return_val_if_fail(poisql.delete_cat, FALSE);
 562
 563 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
 564         sqlite3_reset(poisql.delete_poi_by_catid);
 565         return FALSE;
 566 }
 567 sqlite3_reset(poisql.delete_poi_by_catid);
 568 sqlite3_clear_bindings(poisql.delete_poi_by_catid);
 569
 570 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
 571         sqlite3_reset(poisql.delete_cat);
 572         return FALSE;
 573 }
 574 sqlite3_reset(poisql.delete_cat);
 575 sqlite3_clear_bindings(poisql.delete_cat);
 576 return TRUE;
 577 }
 578
 579
 580 gboolean
 581 poi_delete(guint id)
 582 {
 583 if (!poidb)
 584         return FALSE;
 585
 586 g_return_val_if_fail(poisql.delete_poi, FALSE);
 587
 588 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
 589         sqlite3_reset(poisql.delete_poi);
 590         return FALSE;
 591 }
 592 sqlite3_reset(poisql.delete_poi);
 593 sqlite3_clear_bindings(poisql.delete_poi);
 594 return TRUE;
 595 }
 596
 597 gboolean
 598 poi_search(poi_search_type pst, gdouble lat, gdouble lon, gchar *text, guint cat, GtkListStore **store)
 599 {
 600 GtkTreeIter iter;
 601 sqlite3_stmt *sql=NULL;
 602 gchar *ltext=NULL;
 603 guint rows=0;
 604 gchar tmp1[16], tmp2[16];
 605 guint range=1;
 606
 607 if (!poidb)
 608         return FALSE;
 609
 610 g_return_val_if_fail(poisql.select_poi, FALSE);
 611
 612 g_printf("POI Search: [%s] around %.6f %.6f (%d %d)\n", text, lat, lon, cat, pst);
 613
 614 switch (pst) {
 615         case POI_SEARCH_NEAR:
 616                 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat-0.5) ||
 617                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon+0.5)) {
 618                                 g_printerr("Failed to bind values for poisql.select_poi\n");
 619                                 sqlite3_clear_bindings(poisql.select_poi);
 620                                 return FALSE;
 621                 }
 622                 sql=poisql.select_poi;
 623         break;
 624         case POI_SEARCH_TEXT:
 625                 ltext=g_strdup_printf("%s%%", text);
 626
 627                 if (SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search,   5, ltext, -1, SQLITE_TRANSIENT)) {
 628                                 g_printerr("Failed to bind values for poisql.select_poi_search\n");
 629                                 sqlite3_clear_bindings(poisql.select_poi_search);
 630                                 g_free(ltext);
 631                                 return FALSE;
 632                 }
 633                 g_free(ltext);
 634                 sql=poisql.select_poi_search;
 635         break;
 636         case POI_SEARCH_TEXT_CAT:
 637                 ltext=g_strdup_printf("%s%%", text);
 638
 639                 if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_search_cat, 6, cat) ||
 640                         SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search_cat, 5, ltext, -1, SQLITE_TRANSIENT)) {
 641                                 g_printerr("Failed to bind values for poisql.select_poi_search_cat\n");
 642                                 sqlite3_clear_bindings(poisql.select_poi_search_cat);
 643                                 g_free(ltext);
 644                                 return FALSE;
 645                 }
 646                 g_free(ltext);
 647                 sql=poisql.select_poi_search_cat;
 648         break;
 649         case POI_SEARCH_CAT:
 650                 if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_by_cat, 5, cat)) {
 651                                 g_printerr("Failed to bind values for poisql.select_poi_by_cat\n");
 652                                 sqlite3_clear_bindings(poisql.select_poi_by_cat);
 653                                 return FALSE;
 654                 }
 655                 sql=poisql.select_poi_by_cat;
 656         break;
 657         default:
 658                 g_assert_not_reached();
 659                 return FALSE;
 660         break;
 661 }
 662
 663 /* XXX: Use common bind for common variables */
 664 if (SQLITE_OK != sqlite3_bind_double(sql, 1, lat-range) ||
 665     SQLITE_OK != sqlite3_bind_double(sql, 2, lat+range) ||
 666     SQLITE_OK != sqlite3_bind_double(sql, 3, lon-range) ||
 667     SQLITE_OK != sqlite3_bind_double(sql, 4, lon+range)) {
 668         g_printerr("Failed to bind common variables for POI search\n");
 669         sqlite3_clear_bindings(sql);
 670         return FALSE;
 671 }
 672
 673 *store = poi_list_store_new();
 674
 675 while (SQLITE_ROW == sqlite3_step(sql)) {
 676         gdouble rlat, rlon, dist;
 677
 678         rlat=sqlite3_column_double(sql, 0);
 679         rlon=sqlite3_column_double(sql, 1);
 680         lat_format(_degformat, rlat, tmp1);
 681         lon_format(_degformat, rlon, tmp2);
 682         dist=calculate_distance(lat, lon, rlat, rlon) * UNITS_CONVERT[_units];
 683
 684         gtk_list_store_append(*store, &iter);
 685         gtk_list_store_set(*store, &iter,
 686                 ITEM_ID, sqlite3_column_int(sql, 2),
 687                 ITEM_CATID, sqlite3_column_int(sql, 5),
 688                 ITEM_LAT, rlat,
 689                 ITEM_LON, rlon,
 690                 ITEM_DIST, dist,
 691                 ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
 692                 ITEM_LABEL, sqlite3_column_text(sql, 3),
 693                 ITEM_DESC, sqlite3_column_text(sql, 4),
 694                 ITEM_CATLAB, sqlite3_column_text(sql, 6),
 695                 -1);
 696         rows++;
 697 }
 698
 699 g_printf("Found: %d items\n", rows);
 700
 701 sqlite3_reset(sql);
 702 sqlite3_clear_bindings(sql);
 703
 704 return TRUE;
 705 }
 706
 707 gboolean
 708 poi_get_list_inside(gdouble lat1, gdouble lon1, gdouble lat2, gdouble lon2, GtkListStore **store, guint *num_poi)
 709 {
 710 static active=FALSE;
 711 GtkTreeIter iter;
 712 gchar tmp1[16], tmp2[16];
 713
 714 num_poi=0;
 715
 716 if (!_db)
 717         return FALSE;
 718
 719 g_return_val_if_fail(poisql.select_poi, FALSE);
 720
 721 if (active)
 722         return FALSE;
 723
 724 active=TRUE;
 725 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
 726     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
 727     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
 728     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
 729         g_printerr("Failed to bind values for poisql.select_poi\n");
 730         return FALSE;
 731 }
 732
 733 *store = poi_list_store_new();
 734
 735 while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
 736         gdouble lat, lon, dist=0;
 737
 738         lat=sqlite3_column_double(poisql.select_poi, 0);
 739         lon=sqlite3_column_double(poisql.select_poi, 1);
 740         lat_format(_degformat, lat, tmp1);
 741         lon_format(_degformat, lon, tmp2);
 742
 743         gtk_list_store_append(*store, &iter);
 744         gtk_list_store_set(*store, &iter,
 745                 ITEM_ID, sqlite3_column_int(poisql.select_poi, 2),
 746                 ITEM_CATID, sqlite3_column_int(poisql.select_poi, 5),
 747                 ITEM_LAT, lat,
 748                 ITEM_LON, lon,
 749                 ITEM_DIST, dist,
 750                 ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
 751                 ITEM_LABEL, sqlite3_column_text(poisql.select_poi, 3),
 752                 ITEM_DESC, sqlite3_column_text(poisql.select_poi, 4),
 753                 ITEM_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
 754                 ITEM_ICON, sqlite3_column_text(poisql.select_poi, 8),
 755                 ITEM_COLOR, sqlite3_column_text(poisql.select_poi, 9),
 756                 -1);
 757         *num_poi++;
 758 }
 759 sqlite3_reset(poisql.select_poi);
 760 sqlite3_clear_bindings(poisql.select_poi);
 761 active=FALSE;
 762 return TRUE;
 763 }
 764
 765 gboolean
 766 poi_get_list_near_unit(guint unitx, guint unity, guint range, GtkListStore **store, guint *num_poi)
 767 {
 768 gdouble lat1, lon1, lat2, lon2;
 769 guint x, y;
 770
 771 x=unitx-pixel2unit(3*range);
 772 y=unity+pixel2unit(3*range);
 773 unit2latlon(x, y, lat1, lon1);
 774
 775 x=unitx+pixel2unit(3*range);
 776 y=unity-pixel2unit(3*range);
 777 unit2latlon(x, y, lat2, lon2);
 778
 779 return poi_get_list_inside(lat1, lon1, lat2, lon2, store, num_poi);
 780 }
 781
 782 poi_info *
 783 poi_get_by_id(guint id)
 784 {
 785 poi_info *p=NULL;
 786
 787 g_return_val_if_fail(poisql.select_poi_by_id, FALSE);
 788
 789 if (SQLITE_OK!=sqlite3_bind_double(poisql.select_poi_by_id, 1, id))
 790         return NULL;
 791
 792 if (SQLITE_ROW==sqlite3_step(poisql.select_poi_by_id)) {
 793         p=poi_new();
 794         p->poi_id=sqlite3_column_int(poisql.select_poi_by_id, PS_ID);
 795         p->lat=sqlite3_column_double(poisql.select_poi_by_id, PS_LAT);
 796         p->lon=sqlite3_column_double(poisql.select_poi_by_id, PS_LON);
 797         p->source=sqlite3_column_int(poisql.select_poi_by_id, PS_SOURCE);
 798         p->public=sqlite3_column_int(poisql.select_poi_by_id, PS_PUBLIC)==1 ? TRUE : FALSE;
 799         p->label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_LABEL));
 800         p->desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_DESC));
 801         p->url=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_URL));
 802         p->postal_code=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_POSTAL_CODE));
 803
 804         p->cat_id=sqlite3_column_int(poisql.select_poi_by_id, PS_CAT_ID);
 805         p->cat_label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_LABEL));
 806         p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_DESC));
 807 }
 808
 809 sqlite3_reset(poisql.select_poi_by_id);
 810 sqlite3_clear_bindings(poisql.select_poi_by_id);
 811
 812 return p;
 813 }
 814
 815 gboolean
 816 poi_update(poi_info *p)
 817 {
 818 if (!poidb)
 819         return FALSE;
 820
 821 g_return_val_if_fail(poisql.update_poi, FALSE);
 822
 823 if (p->poi_id==0)
 824         return FALSE;
 825
 826 if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, p->label, -1, SQLITE_STATIC)
 827    || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, p->desc, -1, SQLITE_STATIC)
 828    || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, p->cat_id)
 829    || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, p->poi_id)
 830    || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
 831                 return FALSE;
 832         }
 833 sqlite3_reset(poisql.update_poi);
 834 sqlite3_clear_bindings(poisql.update_poi);
 835 return TRUE;
 836 }
 837
 838 /* XXX: Add url and postal_code */
 839 gboolean
 840 poi_add(poi_info *p)
 841 {
 842 time_t t;
 843
 844 if (!poidb)
 845         return FALSE;
 846
 847 g_return_val_if_fail(poisql.insert_poi, FALSE);
 848
 849 if (p->poi_id!=0)
 850         return FALSE;
 851
 852 t=time(NULL);
 853
 854 if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, p->lat)
 855     || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, p->lon)
 856     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, p->label, -1, SQLITE_STATIC)
 857     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, p->desc, -1, SQLITE_STATIC)
 858     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 5, p->url, -1, SQLITE_STATIC)
 859     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 6, p->postal_code, -1, SQLITE_STATIC)
 860     || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 7, p->cat_id)
 861     || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 8, t)
 862     || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 9, p->source)
 863         || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
 864                 sqlite3_reset(poisql.insert_poi);
 865                 sqlite3_clear_bindings(poisql.insert_poi);
 866                 return FALSE;
 867         }
 868 sqlite3_reset(poisql.insert_poi);
 869 sqlite3_clear_bindings(poisql.insert_poi);
 870 return TRUE;
 871 }
 872
 873 poi_info *
 874 poi_find_nearest(gdouble lat, gdouble lon)
 875 {
 876 poi_info *p;
 877
 878 if (!poidb)
 879         return FALSE;
 880
 881 g_return_val_if_fail(poisql.select_nearest_poi, FALSE);
 882
 883 sqlite3_reset(poisql.select_nearest_poi);
 884 sqlite3_clear_bindings(poisql.select_nearest_poi);
 885
 886 if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
 887     && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
 888     && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
 889
 890         p=poi_new();
 891         p->lat=sqlite3_column_double(poisql.select_nearest_poi, 0);
 892         p->lon=sqlite3_column_double(poisql.select_nearest_poi, 1);
 893         p->poi_id=sqlite3_column_double(poisql.select_nearest_poi, 2);
 894         p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
 895         p->desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 4));
 896         p->cat_id=sqlite3_column_double(poisql.select_nearest_poi, 5);
 897         p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 6));
 898         sqlite3_reset(poisql.select_nearest_poi);
 899         sqlite3_clear_bindings(poisql.select_nearest_poi);
 900         return p;
 901 }
 902 return NULL;
 903 }
 904
 905 GdkPixbuf *
 906 poi_get_icon(gchar *icon, gboolean big)
 907 {
 908 static guint hit=0;
 909 static guint miss=0;
 910 gchar buffer[256];
 911 GdkPixbuf *pixbuf;
 912 GError *error = NULL;
 913
 914 if (icon==NULL)
 915         return NULL;
 916
 917 if (strlen(icon)==0)
 918         return NULL;
 919
 920 g_debug("POI icon cache: %d / %d", hit, miss);
 921
 922 g_snprintf(buffer, sizeof(buffer), "%s/%s.%s/%s.png",
 923         theme_base, theme, (big==TRUE) ? "big" : "small", icon);
 924
 925 pixbuf=g_hash_table_lookup(poi_icon_hash, buffer);
 926 if (pixbuf) {
 927         hit++;
 928         return pixbuf;
 929 }
 930 miss++;
 931
 932 g_debug("Loading POI icon: %s", buffer);
 933 pixbuf=gdk_pixbuf_new_from_file(buffer, &error);
 934
 935 if (error)
 936         return NULL;
 937
 938 g_hash_table_insert(poi_icon_hash, g_strdup(buffer), pixbuf);
 939 return pixbuf;
 940 }
 941
 942 GtkListStore *
 943 poi_category_generate_store(void)
 944 {
 945 GtkTreeIter iter;
 946 GtkListStore *store;
 947
 948 if (!poidb)
 949         return NULL;
 950
 951 g_return_val_if_fail(poisql.selall_cat, FALSE);
 952
 953 store=gtk_list_store_new(CAT_NUM_COLUMNS, /* pixbuf */
 954                                 G_TYPE_UINT,
 955                                 G_TYPE_BOOLEAN,
 956                                 G_TYPE_STRING,
 957                                 G_TYPE_STRING,
 958                                 G_TYPE_UINT,
 959                                 GDK_TYPE_PIXBUF);
 960
 961 while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
 962         gtk_list_store_append(store, &iter);
 963         gtk_list_store_set(store, &iter,
 964                 CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
 965                 CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
 966                 CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
 967                 CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
 968                 CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6),
 969                 CAT_ICON, poi_get_icon(sqlite3_column_text(poisql.selall_cat, 4),TRUE),
 970                 -1);
 971 }
 972
 973 sqlite3_reset(poisql.selall_cat);
 974 sqlite3_clear_bindings(poisql.selall_cat);
 975
 976 return store;
 977 }