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