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

   1 #include <config.h>
   2
   3 #define _GNU_SOURCE
   4
   5 #include <unistd.h>
   6 #include <stdlib.h>
   7 #include <string.h>
   8 #include <strings.h>
   9 #include <stddef.h>
  10 #include <locale.h>
  11 #include <math.h>
  12 #include <errno.h>
  13 #include <glib/gstdio.h>
  14 #include <gtk/gtk.h>
  15 #include <fcntl.h>
  16 #include <gconf/gconf-client.h>
  17 #include <libxml/parser.h>
  18 #include <libintl.h>
  19 #include <locale.h>
  20 #include <sqlite3.h>
  21
  22 #include "utils.h"
  23 #include "poi.h"
  24 #include "gps.h"
  25 #include "map.h"
  26 #include "mapper-types.h"
  27 #include "settings.h"
  28 #include "db.h"
  29 #include "osm.h"
  30 #include "osm-db.h"
  31
  32 static sqlite3 *poidb;
  33
  34 struct _poi_categories {
  35         node_type_t type;
  36         const gchar *name, *desc, *icon, *color;
  37 };
  38
  39 /* The default POI categories */
  40 static struct _poi_categories default_poi_categories[] = {
  41         { NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles."  , "vehicle/parking", "#2020ff" },
  42         { NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles."  , "vehicle/fuel_station", "#4040f0" },
  43         { NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras."  , "vehicle/restrictions/speed_trap", "#ff0000" },
  44         { NODE_AMENITY_HOSPITAL, "Hospital", ""  , "health/hospital", "#ff4040" },
  45         { NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs."  , "health/pharmacy", "#40f040" },
  46         { NODE_AMENITY_POLICE, "Police", "Police station" , "public/police", "#8570ff" },
  47         { NODE_TOURISM_HOTEL, "Hotel", "Places to stay temporarily or for the night." , "accommodation/hotel", "#ba20ba" },
  48         { NODE_TOURISM_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap." , "accommodation/hostel", "#ba30ba" },
  49         { NODE_TOURISM_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap." , "accommodation/motel", "#ba40ba" },
  50         { NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint." , "money/atm", "#40a040" },
  51         { NODE_AMENITY_BANK, "Bank", "Place to take care of your money." , "money/bank", "#50b550" },
  52         { NODE_AMENITY_POST, "Post office", "Place to handle mail." , "public/post_office", "#ff6868" },
  53         { NODE_AMENITY_POST_BOX, "Post box", "Send letters." , "public/post_box", "#ff6060" },
  54         { NODE_TOURISM_INFO, "Tourism info", "A place for tourists to get information." , "misc/information", "#4af04a" },
  55         { NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here." , "", "#50ffff" },
  56         { NODE_RAILWAY_STATION, "Railway station", "Transportation by train." , "transport/railway", "#fa7070" },
  57         { NODE_RAILWAY_HALT, "Railway halt", "" , "transport/railway_small", "#f27777" },
  58         { NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus." , "transport/bus", "#f07070" },
  59         { NODE_AMENITY_BOAT, "Harbour", "Transportation by boat." , "transport/ferry", "#9090ee" },
  60         { NODE_AIRPORT_TERMINAL, "Airport", "Transportation by air." , "transport/airport", "#909099" },
  61         { NODE_TOURISM_CAMP_SITE, "Camp site", "Place to go camping" , "accommodation/camping", "#90f080" },
  62         { NODE_TOURISM_CARAVAN_SITE, "Caravan site", "" , "accommodation/camping/caravan", "#90ff80" },
  63         { NODE_TOURISM_PICNIC_SITE, "Picnic", "Place to have a Picnic" , "recreation/picnic", "#a5f8e5" },
  64         { NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink." , "food/fastfood", "#e5960d" },
  65         { NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party." , "food/pub", "#f5960d" },
  66         { NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat." , "food/cafe", "#ff960d" },
  67         { NODE_AMENITY_CINEMA, "Cinema", "Place to see movies" , "recreation/cinema", "#9090a0" },
  68         { NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing" , "recreation/theater", "#9595a5" },
  69         { NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services." , "shopping", "#61ef1b" },
  70         { NODE_AMENITY_POW, "Place of Worchip", "" , "religion/church", "#7c5b0b" },
  71         { NODE_TOURISM_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters." , "recreation/theme_park", "#8eef1b" },
  72         { NODE_AMENITY_COLLEGE, "College Campus/Building", "" , "education/college", "#813fdc" },
  73         { NODE_AMENITY_SCHOOL, "School", "" , "education/school", "#813fdc" },
  74         { NODE_AMENITY_LIBRARY, "Library", "Place to read and borrow books and magazines" , "shopping/rental/library", "#803090" },
  75         { NODE_AMENITY_TOWNHALL, "Townhall", "" , "", "#408090" },
  76         { NODE_AMENITY_WC, "WC/Toilets", "" , "public/toilets", "#e1d62c" },
  77         { NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone" , "public/telephone", "#208060" },
  78         { NODE_TOURISM_ATTRACTION, "Attraction", "Something interesting" , "generic", "#005000" },
  79
  80         { NODE_HISTORIC_MUSEUM, "Museum", "A place where objects of historical, artistic, or scientific interest are exhibited, preserved or studied." , "sightseeing/museum", "#202020" },
  81         { 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" },
  82
  83         { NODE_SPORT_CENTER, "Sport Center", "" , "sports/centre", "#101080" },
  84         { NODE_SPORT_STADIUM, "Sport Stadium", "" , "sports/stadium", "#101080" },
  85         { NODE_SPORT_SKIING, "Skiing", "" , "sports/skiing", "#5050A0" },
  86         { NODE_SPORT_SWIMMING, "Swimming", "" , "sports/swimming", "#102080" },
  87         { NODE_SPORT_FOOTBALL, "Football", "" , "sports/football", "#102080" },
  88         { NODE_SPORT_SOCCER, "Soccer", "" , "sports/soccer", "#102080" },
  89         { NODE_SPORT_GOLF, "Golf", "" , "sports/golf", "#102080" },
  90         { NODE_SPORT_TENNIS, "Tennis", "" , "sports/tennis", "#101080" },
  91         { NODE_SPORT_BOWLING, "Bowling", "" , "", "#101080" },
  92         { NODE_SPORT_RUGBY, "Rugby", "" , "", "#101080" },
  93         { NODE_SPORT_CLIMBING, "Climbing", "" , "", "#101080" },
  94         { NODE_SPORT_CYCLING, "Cycling", "" , "sports/bicycle", "#101080" },
  95         { NODE_SPORT_MOTOR, "Motor sport", "" , "", "#101080" },
  96         { NODE_SPORT_HOCKEY, "Hockey", "" , "", "#5050A0" },
  97         { NODE_SPORT_SKATING, "Skating", "" , "", "#5050A0" },
  98         { NODE_SPORT_SKATEBOARD, "Skateboard", "" , "", "#101080" },
  99         { NODE_SPORT_HORSES, "Horses", "Horse riding or racing" , "sports/riding", "#101080" },
 100         { NODE_SPORT_DOG, "Dog racing", "" , "", "#101080" },
 101         { NODE_SPORT_BASKETBALL, "Basketball", "" , "", "#101080" },
 102         { NODE_SPORT_BASEBALL, "Baseball", "" , "", "#101080" },
 103         { NODE_SPORT_CANOE, "Canoe", "" , "", "#101080" },
 104         { NODE_SPORT_CROQUET, "Croquet", "" , "", "#101080" },
 105         { NODE_SPORT_CRICKET, "Cricket", "" , "", "#101080" },
 106         { NODE_SPORT_SHOOTING, "Shooting", "Shooting range" , "", "#101080" },
 107         { NODE_SPORT_PAINTBALL, "Paintball", "Run around and shoot people with paintballs" , "", "#101080" },
 108         { NODE_SPORT_TABLE_TENNIS, "Table tennis", "" , "", "#101080" },
 109         { NODE_SPORT_PELOTA, "Pelota", "" , "", "#101080" },
 110         { NODE_SPORT_RACQUET, "Racquet", "" , "sports/racquetball", "#101080" },
 111         { NODE_SPORT_BOWLS, "Lawn Bowls", "" , "", "#101080" },
 112         { NODE_SPORT_ATHLETICS, "Athletics", "" , "", "#101080" },
 113         { NODE_SPORT_OTHER, "Other Sports", "" , "", "#101077" },
 114
 115         { NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else." , "generic", "#002000" },
 116         { NODE_POI_END, NULL, NULL }
 117 };
 118
 119 static gboolean
 120 poi_populate_categories(sqlite3 *db)
 121 {
 122 sqlite3_stmt *sql_cat;
 123 gint i;
 124
 125 sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)"
 126                         " values (?, ?, ?, 1, ?, ?, ?)",
 127                         -1, &sql_cat, NULL);
 128
 129 for (i=0; default_poi_categories[i].name; i++) {
 130         sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
 131         sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name, -1, SQLITE_STATIC);
 132         sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc, -1, SQLITE_STATIC);
 133         sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
 134         sqlite3_bind_text(sql_cat, 5, default_poi_categories[i].icon, -1, SQLITE_STATIC);
 135         sqlite3_bind_text(sql_cat, 6, default_poi_categories[i].color, -1, SQLITE_STATIC);
 136         if (sqlite3_step(sql_cat)==SQLITE_OK)
 137                 g_printf("Failed to update category: %d [%s]\n", i, default_poi_categories[i].name);
 138         sqlite3_reset(sql_cat);
 139         sqlite3_clear_bindings(sql_cat);
 140 }
 141
 142 sqlite3_finalize(sql_cat);
 143
 144 return TRUE;
 145 }
 146
 147 gboolean
 148 poi_db_create(sqlite3 *db)
 149 {
 150 gchar **pszResult;
 151 guint nRow, nColumn;
 152
 153 if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 154                                    &pszResult, &nRow, &nColumn, NULL)) {
 155         gchar *create_sql;
 156
 157         g_printerr("Creating initial POI tables\n");
 158
 159         create_sql = sqlite3_mprintf
 160             ("create table poi "
 161                 "(poi_id INTEGER PRIMARY KEY AUTOINCREMENT, "
 162                 "lat real, "
 163                 "lon real, "
 164                 "elev real, "
 165                 "label text, "
 166                 "desc text, "
 167                 "public int, "
 168                 "source int default 0, "
 169                 "osm_id int default 0, "
 170                 "priority int default 2, "
 171                 "cat_id int);"
 172              "create table category (cat_id INTEGER PRIMARY KEY AUTOINCREMENT,"
 173                 "label text, "
 174                 "desc text, "
 175                 "icon text, "
 176                 "color char(7), "
 177                 "priority int default 2, "
 178                 "enabled int);");
 179
 180         if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
 181             && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 182                                   &pszResult, &nRow, &nColumn, NULL))) {
 183                 g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
 184                 sqlite3_close(db);
 185                 db = NULL;
 186                 return FALSE;
 187         }
 188 } else {
 189         sqlite3_free_table(pszResult);
 190 }
 191
 192 /* Make sure default categories exists */
 193 poi_populate_categories(db);
 194 return TRUE;
 195 }
 196
 197 gboolean
 198 poi_db_prepare(sqlite3 *db)
 199 {
 200         /* select from poi */
 201         if (sqlite3_prepare_v2(db,
 202                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 203                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 204                         " from poi p, category c "
 205                         " where p.lat between ? and ? "
 206                         " and p.lon between ? and ? "
 207                         " and c.enabled = 1 and p.cat_id = c.cat_id",
 208                         -1, &poisql.select_poi, NULL)!=SQLITE_OK)
 209         return FALSE;
 210
 211         /* Search POI label */
 212         if (sqlite3_prepare_v2(db,
 213                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 214                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 215                         " from poi p, category c "
 216                         " where p.lat between ? and ? "
 217                         " and p.lon between ? and ? "
 218                         " and c.enabled = 1 and p.cat_id = c.cat_id and p.label like ? order by p.label",
 219                         -1, &poisql.select_poi_search, NULL)!=SQLITE_OK)
 220         return FALSE;
 221
 222         /* Search POI label && category */
 223         if (sqlite3_prepare_v2(db,
 224                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 225                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 226                         " from poi p, category c "
 227                         " where p.lat between ? and ? "
 228                         " and p.lon between ? and ? "
 229                         " and c.enabled = 1 and p.cat_id = c.cat_id and p.label like ? and c.cat_id = ? order by p.label",
 230                         -1, &poisql.select_poi_search_cat, NULL)!=SQLITE_OK)
 231         return FALSE;
 232
 233         /* select nearest pois */
 234         if (sqlite3_prepare_v2(db,
 235                         "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
 236                         " from poi p, category c "
 237                         " where c.enabled = 1 and p.cat_id = c.cat_id "
 238                         " and p.lat between $LAT-0.10 and $LAT+0.10 "
 239                         " and p.lon between $LON-0.10 and $LAT+0.10 "
 240                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 241                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 242                         -1, &poisql.select_nearest_poi, NULL)!=SQLITE_OK)
 243         return FALSE;
 244
 245         /* insert poi */
 246         sqlite3_prepare_v2(db,
 247                         "insert into poi (lat, lon, label, desc, cat_id, public)"
 248                         " values (?, ?, ?, ?, ?, 1)", -1, &poisql.insert_poi, NULL);
 249         /* update poi */
 250         sqlite3_prepare_v2(db, "update poi set label = ?, desc = ?, cat_id = ? where poi_id = ?", -1, &poisql.update_poi, NULL);
 251         /* delete from poi */
 252         sqlite3_prepare_v2(db, "delete from poi where poi_id = ?", -1, &poisql.delete_poi, NULL);
 253         /* delete from poi by cat_id */
 254         sqlite3_prepare_v2(db, "delete from poi where cat_id = ?", -1, &poisql.delete_poi_by_catid, NULL);
 255         /* get next poilabel */
 256         sqlite3_prepare_v2(db, "select ifnull(max(poi_id) + 1,1) from poi", -1, &poisql.nextlabel_poi, NULL);
 257
 258         /* select from category */
 259         sqlite3_prepare_v2(db,
 260                         "select c.label, c.desc, c.enabled"
 261                         " from category c where c.cat_id = ?",
 262                         -1, &poisql.select_cat, NULL);
 263         /* insert into category */
 264         sqlite3_prepare_v2(db,
 265                         "insert into category (label, desc, enabled)"
 266                         " values (?, ?, ?)", -1, &poisql.insert_cat, NULL);
 267         /* update category */
 268         sqlite3_prepare_v2(db,
 269                         "update category set label = ?, desc = ?,"
 270                         " enabled = ? where poi_id = ?",
 271                         -1, &poisql.update_cat, NULL);
 272         /* delete from category */
 273         sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
 274                         -1, &poisql.delete_cat, NULL);
 275         /* enable category */
 276         sqlite3_prepare_v2(db,
 277                         "update category set enabled = ?"
 278                         " where cat_id = ?", -1, &poisql.toggle_cat, NULL);
 279         /* select all category */
 280         sqlite3_prepare_v2(db,
 281                         "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
 282                         " count(p.poi_id)"
 283                         " from category c"
 284                         " left outer join poi p on c.cat_id = p.cat_id"
 285                         " group by c.cat_id, c.label, c.desc, c.enabled "
 286                         " order by c.priority,c.label", -1, &poisql.selall_cat, NULL);
 287
 288         /* Select quick categories */
 289         sqlite3_prepare_v2(db,
 290                         "select c.cat_id, c.label, c.icon, c.color"
 291                         " from category c where c.enabled=1 "
 292                         " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL);
 293
 294         return TRUE;
 295 }
 296
 297 gboolean
 298 poi_init(sqlite3 **db)
 299 {
 300 if (!db || !*db)
 301         return FALSE;
 302
 303 poidb=*db;
 304 poi_db_create(poidb);
 305 if (poi_db_prepare(poidb)==FALSE) {
 306         g_printerr("Failed to prepare POI SQL statements:");
 307         g_printf("SQLITE: %s\n", sqlite3_errmsg(poidb));
 308         return FALSE;
 309 }
 310 return TRUE;
 311 }
 312
 313 poi_info *
 314 poi_new(void)
 315 {
 316 return g_slice_new0(poi_info);
 317 }
 318
 319 void
 320 poi_free(poi_info *p)
 321 {
 322 g_slice_free(poi_info, p);
 323 }
 324
 325 /*************************************
 326  * POI Category functions
 327  *
 328  */
 329
 330 poi_category *
 331 poi_category_new(void)
 332 {
 333 return g_slice_new0(poi_category);
 334 }
 335
 336 void
 337 poi_category_free(poi_category *c)
 338 {
 339 if (c->label)
 340         g_free(c->label);
 341 if (c->desc)
 342         g_free(c->desc);
 343 g_slice_free(poi_category, c);
 344 }
 345
 346 gboolean
 347 poi_category_toggle(guint cat_id, gboolean cat_enabled)
 348 {
 349 if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
 350     SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
 351     SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
 352                 return FALSE;
 353         }
 354 return TRUE;
 355 }
 356
 357 gboolean
 358 poi_category_get(guint cat_id, poi_category **c)
 359 {
 360 poi_category *cc;
 361
 362 if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id) || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
 363         sqlite3_reset(poisql.select_cat);
 364         return FALSE;
 365 }
 366
 367 cc=poi_category_new();
 368 cc->id=cat_id;
 369 cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
 370 cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
 371 cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
 372
 373 sqlite3_reset(poisql.select_cat);
 374 sqlite3_clear_bindings(poisql.select_cat);
 375 *c=cc;
 376 return TRUE;
 377 }
 378
 379 gboolean
 380 poi_category_update(guint cat_id, poi_category *c)
 381 {
 382 gboolean results=TRUE;
 383
 384 if (!_db)
 385         return FALSE;
 386
 387 if (cat_id > 0) {
 388 /* edit category */
 389                 if (SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, SQLITE_STATIC)
 390                     || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, SQLITE_STATIC)
 391                     || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
 392                     || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
 393                     || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
 394                         results = FALSE;
 395                 }
 396                 sqlite3_reset(poisql.update_cat);
 397                 sqlite3_clear_bindings(poisql.update_cat);
 398         } else {
 399                 /* add category */
 400                 if (SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, SQLITE_STATIC)
 401                     || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, SQLITE_STATIC)
 402                     || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
 403                     || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
 404                         results = FALSE;
 405                 }
 406                 sqlite3_reset(poisql.insert_cat);
 407                 sqlite3_clear_bindings(poisql.insert_cat);
 408         }
 409 return results;
 410 }
 411
 412 gboolean
 413 poi_category_delete(guint id)
 414 {
 415 if (!_db)
 416         return FALSE;
 417
 418 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
 419         sqlite3_reset(poisql.delete_poi_by_catid);
 420         return FALSE;
 421 }
 422 sqlite3_reset(poisql.delete_poi_by_catid);
 423 sqlite3_clear_bindings(poisql.delete_poi_by_catid);
 424
 425 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
 426         sqlite3_reset(poisql.delete_cat);
 427         return FALSE;
 428 }
 429 sqlite3_reset(poisql.delete_cat);
 430 sqlite3_clear_bindings(poisql.delete_cat);
 431 return TRUE;
 432 }
 433
 434
 435 gboolean
 436 poi_delete(guint id)
 437 {
 438 if (!_db)
 439         return FALSE;
 440
 441 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
 442         sqlite3_reset(poisql.delete_poi);
 443         return FALSE;
 444 }
 445 sqlite3_reset(poisql.delete_poi);
 446 sqlite3_clear_bindings(poisql.delete_poi);
 447 return TRUE;
 448 }
 449
 450 gboolean
 451 poi_search(poi_search_type pst, gdouble lat, gdouble lon, gchar *text, guint cat, GtkListStore **store)
 452 {
 453 GtkTreeIter iter;
 454 sqlite3_stmt *sql=NULL;
 455 gchar *ltext=NULL;
 456 guint rows=0;
 457 gchar tmp1[16], tmp2[16];
 458
 459 if (!_db)
 460         return FALSE;
 461
 462 g_printf("POI Search: [%s] around %.6f %.6f (%d)\n", text, lat, lon, cat);
 463
 464 switch (pst) {
 465         case POI_SEARCH_NEAR:
 466                 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat-0.5) ||
 467                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat+0.5) ||
 468                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon-0.5) ||
 469                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon+0.5)) {
 470                                 g_printerr("Failed to bind values for poisql.select_poi\n");
 471                                 sqlite3_clear_bindings(sql);
 472                                 return FALSE;
 473                 }
 474                 sql=poisql.select_poi;
 475         break;
 476         case POI_SEARCH_TEXT:
 477                 ltext=g_strdup_printf("%s%%", text);
 478
 479                 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search, 1, lat-1) ||
 480                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search, 2, lat+1) ||
 481                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search, 3, lon-1) ||
 482                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search, 4, lon+1) ||
 483                         SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search,   5, ltext, -1, SQLITE_TRANSIENT)) {
 484                                 g_printerr("Failed to bind values for poisql.select_poi_search\n");
 485                                 sqlite3_clear_bindings(sql);
 486                                 g_free(ltext);
 487                                 return FALSE;
 488                 }
 489                 sql=poisql.select_poi_search;
 490         break;
 491         case POI_SEARCH_TEXT_CAT:
 492                 ltext=g_strdup_printf("%s%%", text);
 493
 494                 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search_cat, 1, lat-1) ||
 495                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search_cat, 2, lat+1) ||
 496                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search_cat, 3, lon-1) ||
 497                     SQLITE_OK != sqlite3_bind_double(poisql.select_poi_search_cat, 4, lon+1) ||
 498                     SQLITE_OK != sqlite3_bind_int(poisql.select_poi_search_cat,    6, cat) ||
 499                         SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search_cat,   5, ltext, -1, SQLITE_TRANSIENT)) {
 500                                 g_printerr("Failed to bind values for poisql.select_poi_search_cat\n");
 501                                 sqlite3_clear_bindings(sql);
 502                                 g_free(ltext);
 503                                 return FALSE;
 504                 }
 505                 sql=poisql.select_poi_search_cat;
 506         break;
 507         default:
 508                 g_assert_not_reached();
 509                 return FALSE;
 510         break;
 511 }
 512
 513 *store = gtk_list_store_new(POI_NUM_COLUMNS,
 514                                 G_TYPE_INT,     /* POI ID */
 515                                 G_TYPE_INT,     /* Category ID */
 516                                 G_TYPE_DOUBLE,  /* Latitude */
 517                                 G_TYPE_DOUBLE,  /* Longitude */
 518                                 G_TYPE_STRING,  /* Lat/Lon */
 519                                 G_TYPE_STRING,  /* POI Label */
 520                                 G_TYPE_STRING,  /* POI Desc. */
 521                                 G_TYPE_STRING); /* Category Label */
 522
 523 while (SQLITE_ROW == sqlite3_step(sql)) {
 524         gdouble rlat, rlon;
 525
 526         rlat=sqlite3_column_double(sql, 0);
 527         rlon=sqlite3_column_double(sql, 1);
 528         lat_format(rlat, tmp1);
 529         lon_format(rlon, tmp2);
 530
 531         gtk_list_store_append(*store, &iter);
 532         gtk_list_store_set(*store, &iter,
 533                 POI_POIID, sqlite3_column_int(sql, 2),
 534                 POI_CATID, sqlite3_column_int(sql, 5),
 535                 POI_LAT, rlat,
 536                 POI_LON, rlon,
 537                 POI_LATLON,     g_strdup_printf("%s, %s", tmp1, tmp2),
 538                 POI_LABEL, sqlite3_column_text(sql, 3),
 539                 POI_DESC, sqlite3_column_text(sql, 4),
 540                 POI_CATLAB, sqlite3_column_text(sql, 6),
 541                 -1);
 542         rows++;
 543 }
 544
 545 g_printf("Found: %d items\n", rows);
 546
 547 sqlite3_reset(sql);
 548 sqlite3_clear_bindings(sql);
 549
 550 if (ltext)
 551         g_free(ltext);
 552
 553 return TRUE;
 554 }
 555
 556 gboolean
 557 poi_get_list_near(guint unitx, guint unity, GtkListStore **store, guint *_num_cats)
 558 {
 559 guint x, y;
 560 gdouble lat1, lon1, lat2, lon2;
 561 GtkTreeIter iter;
 562 gchar tmp1[16], tmp2[16];
 563 gint num_cats=0;
 564
 565 if (!_db)
 566         return FALSE;
 567
 568 x = unitx - pixel2unit(3 * _draw_width);
 569 y = unity + pixel2unit(3 * _draw_width);
 570 unit2latlon(x, y, lat1, lon1);
 571
 572 x = unitx + pixel2unit(3 * _draw_width);
 573 y = unity - pixel2unit(3 * _draw_width);
 574 unit2latlon(x, y, lat2, lon2);
 575
 576 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
 577     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
 578     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
 579     SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
 580         g_printerr("Failed to bind values for poisql.select_poi\n");
 581         return FALSE;
 582 }
 583
 584 *store = gtk_list_store_new(POI_NUM_COLUMNS,
 585                                 G_TYPE_INT,     /* POI ID */
 586                                 G_TYPE_INT,     /* Category ID */
 587                                 G_TYPE_DOUBLE,  /* Latitude */
 588                                 G_TYPE_DOUBLE,  /* Longitude */
 589                                 G_TYPE_STRING,  /* Lat/Lon */
 590                                 G_TYPE_STRING,  /* POI Label */
 591                                 G_TYPE_STRING,  /* POI Desc. */
 592                                 G_TYPE_STRING); /* Category Label */
 593
 594 while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
 595         gdouble lat, lon;
 596
 597         lat = sqlite3_column_double(poisql.select_poi, 0);
 598         lon = sqlite3_column_double(poisql.select_poi, 1);
 599         lat_format(lat, tmp1);
 600         lon_format(lon, tmp2);
 601
 602         gtk_list_store_append(*store, &iter);
 603         gtk_list_store_set(*store, &iter,
 604                 POI_POIID, sqlite3_column_int(poisql.select_poi, 2),
 605                 POI_CATID, sqlite3_column_int(poisql.select_poi, 5),
 606                 POI_LAT, lat,
 607                 POI_LON, lon,
 608                 POI_LATLON,     g_strdup_printf("%s, %s", tmp1, tmp2),
 609                 POI_LABEL, sqlite3_column_text(poisql.select_poi, 3),
 610                 POI_DESC, sqlite3_column_text(poisql.select_poi, 4),
 611                 POI_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
 612                 -1);
 613         num_cats++;
 614 }
 615 sqlite3_reset(poisql.select_poi);
 616 sqlite3_clear_bindings(poisql.select_poi);
 617 *_num_cats=num_cats;
 618 return TRUE;
 619 }
 620
 621 gboolean
 622 poi_update(guint poi_id, guint cat_id, gchar *poi_label, gchar *poi_desc)
 623 {
 624 if (!_db)
 625         return FALSE;
 626
 627 if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, poi_label, -1, SQLITE_STATIC)
 628    || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, poi_desc, -1, g_free)
 629    || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, cat_id)
 630    || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, poi_id)
 631    || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
 632                 return FALSE;
 633         }
 634 sqlite3_reset(poisql.update_poi);
 635 sqlite3_clear_bindings(poisql.update_poi);
 636 return TRUE;
 637 }
 638
 639 gboolean
 640 poi_add(gdouble lat, gdouble lon, guint cat_id, gchar *poi_label, gchar *poi_desc)
 641 {
 642 if (!_db)
 643         return FALSE;
 644
 645 if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, lat)
 646     || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, lon)
 647     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, poi_label, -1, g_free)
 648     || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, poi_desc, -1, g_free)
 649     || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 5, cat_id)
 650         || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
 651                 return FALSE;
 652         }
 653 sqlite3_reset(poisql.insert_poi);
 654 sqlite3_clear_bindings(poisql.insert_poi);
 655 return TRUE;
 656 }
 657
 658 poi_info *
 659 poi_find_nearest(gdouble lat, gdouble lon)
 660 {
 661 poi_info *p;
 662
 663 if (!_db)
 664         return FALSE;
 665
 666 sqlite3_reset(poisql.select_nearest_poi);
 667 sqlite3_clear_bindings(poisql.select_nearest_poi);
 668
 669 if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
 670     && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
 671     && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
 672
 673         p=poi_new();
 674         p->lat = sqlite3_column_double(poisql.select_nearest_poi, 0);
 675         p->lon = sqlite3_column_double(poisql.select_nearest_poi, 1);
 676         p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 2));
 677         p->cat_label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
 678         return p;
 679 }
 680 return NULL;
 681 }
 682
 683 GtkListStore *
 684 poi_category_generate_store(void)
 685 {
 686 GtkTreeIter iter;
 687 GtkListStore *store;
 688
 689 if (!_db)
 690         return NULL;
 691
 692 store = gtk_list_store_new(CAT_NUM_COLUMNS,
 693                            G_TYPE_UINT,
 694                            G_TYPE_BOOLEAN,
 695                            G_TYPE_STRING, G_TYPE_STRING, G_TYPE_UINT);
 696
 697 while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
 698         gtk_list_store_append(store, &iter);
 699         gtk_list_store_set(store, &iter,
 700                         CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
 701                         CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
 702                         CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
 703                         CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
 704                         CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6), -1);
 705 }
 706
 707 sqlite3_reset(poisql.selall_cat);
 708 sqlite3_clear_bindings(poisql.selall_cat);
 709
 710 return store;
 711 }