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
  19 #include <libintl.h>
  20 #include <locale.h>
  21
  22 #include <sqlite3.h>
  23
  24 #include "utils.h"
  25 #include "poi.h"
  26 #include "gps.h"
  27 #include "map.h"
  28 #include "mapper-types.h"
  29 #include "settings.h"
  30 #include "db.h"
  31 #include "osm.h"
  32
  33 struct sql_poi_stmt {
  34         sqlite3_stmt *_stmt_select_poi;
  35         sqlite3_stmt *_stmt_select_nearest_poi;
  36         sqlite3_stmt *_stmt_insert_poi;
  37         sqlite3_stmt *_stmt_update_poi;
  38         sqlite3_stmt *_stmt_delete_poi;
  39         sqlite3_stmt *_stmt_delete_poi_by_catid;
  40         sqlite3_stmt *_stmt_nextlabel_poi;
  41         sqlite3_stmt *_stmt_select_cat;
  42         sqlite3_stmt *_stmt_insert_cat;
  43         sqlite3_stmt *_stmt_update_cat;
  44         sqlite3_stmt *_stmt_delete_cat;
  45         sqlite3_stmt *_stmt_toggle_cat;
  46         sqlite3_stmt *_stmt_selall_cat;
  47 };
  48 static struct sql_poi_stmt poisql;
  49
  50 sqlite3_stmt *_stmt_select_poi = NULL;
  51 sqlite3_stmt *_stmt_select_nearest_poi = NULL;
  52 sqlite3_stmt *_stmt_insert_poi = NULL;
  53 sqlite3_stmt *_stmt_update_poi = NULL;
  54 sqlite3_stmt *_stmt_delete_poi = NULL;
  55 sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
  56 sqlite3_stmt *_stmt_nextlabel_poi = NULL;
  57
  58 sqlite3_stmt *_stmt_select_cat = NULL;
  59 sqlite3_stmt *_stmt_insert_cat = NULL;
  60 sqlite3_stmt *_stmt_update_cat = NULL;
  61 sqlite3_stmt *_stmt_delete_cat = NULL;
  62 sqlite3_stmt *_stmt_toggle_cat = NULL;
  63 sqlite3_stmt *_stmt_selall_cat = NULL;
  64
  65 struct _poi_categories {
  66         node_type_t type;
  67         gchar *name, *desc;
  68 };
  69 static struct _poi_categories default_poi_categories[] = {
  70         {NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." },
  71         {NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." },
  72         {NODE_AMENITY_HOSPITAL, "Hospital", "" },
  73         {NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." },
  74         {NODE_AMENITY_POLICE, "Police", "Police station"},
  75         {NODE_AMENITY_HOTEL, "Hotel", "Places to stay temporarily or for the night."},
  76         {NODE_AMENITY_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap."},
  77         {NODE_AMENITY_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap."},
  78         {NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint."},
  79         {NODE_AMENITY_BANK, "Bank", "Place to take care of your money."},
  80         {NODE_AMENITY_POST, "Post office", "Place to handle mail."},
  81         {NODE_AMENITY_POST_BOX, "Post box", "Send letters."},
  82         {NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here."},
  83         {NODE_AMENITY_RAILWAY_STATION, "Railway station", "Transportation by train."},
  84         {NODE_AMENITY_RAILWAY_HALT, "Railway halt", ""},
  85         {NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus."},
  86         {NODE_AMENITY_BOAT, "Harbour", "Transportation by boat."},
  87         {NODE_AMENITY_AIRPORT, "Airport", "Transportation by air."},
  88         {NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink."},
  89         {NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party."},
  90         {NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat."},
  91         {NODE_AMENITY_CINEMA, "Cinema", "Place to see movies"},
  92         {NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing"},
  93         {NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services."},
  94         {NODE_AMENITY_POW, "Place of Worchip", ""},
  95         {NODE_AMENITY_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters."},
  96         {NODE_AMENITY_COLLEGE, "College Campus/Building", ""},
  97         {NODE_AMENITY_SCHOOL, "School", ""},
  98         {NODE_AMENITY_WC, "WC/Toilets", ""},
  99         {NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone"},
 100         {NODE_AMENITY_ATTRACTION, "Attraction", "Something interesting"},
 101         {NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else."},
 102         {NODE_POI_END, NULL, NULL }
 103 };
 104
 105 gboolean
 106 poi_cb_populate_categories(sqlite3 *db)
 107 {
 108 sqlite3_stmt *sql_cat;
 109 gint i;
 110
 111 g_printf("Checking default categories\n");
 112 sqlite3_prepare_v2(db,"insert into category (cat_id, label, desc, enabled)"
 113                         " values (?, ?, ?, 1)",
 114                         -1, &sql_cat, NULL);
 115
 116 for (i=0; default_poi_categories[i].name; i++) {
 117         sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
 118         sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name , -1, SQLITE_TRANSIENT);
 119         sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc , -1, SQLITE_TRANSIENT);
 120         sqlite3_step(sql_cat);
 121         sqlite3_reset(sql_cat);
 122         sqlite3_clear_bindings(sql_cat);
 123 }
 124
 125 sqlite3_finalize(sql_cat);
 126
 127 return TRUE;
 128 }
 129
 130 gboolean
 131 poi_db_create(sqlite3 *db)
 132 {
 133         gchar **pszResult;
 134         guint nRow, nColumn;
 135         if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 136                                            &pszResult, &nRow, &nColumn, NULL)) {
 137                 gchar *create_sql;
 138
 139                 g_printf("Creating initial tables\n");
 140
 141                 create_sql = sqlite3_mprintf
 142                     ("create table poi "
 143                         "(poi_id integer PRIMARY KEY, "
 144                         "lat real, "
 145                         "lon real, "
 146                         "elev real, "
 147                         "label text, "
 148                         "desc text, "
 149                         "public integer, "
 150                         "source integer default 0, "
 151                         "osm_id integer default 0, "
 152                         "priority integer default 2, "
 153                         "cat_id integer);"
 154                      "create table category (cat_id integer PRIMARY KEY,"
 155                         "label text, "
 156                         "desc text, "
 157                         "icon text, "
 158                         "color char(7), "
 159                         "enabled integer);");
 160
 161                 if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
 162                     && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
 163                                           &pszResult, &nRow, &nColumn, NULL))) {
 164                         g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
 165                         sqlite3_close(db);
 166                         db = NULL;
 167                         return FALSE;
 168                 }
 169         } else
 170                 sqlite3_free_table(pszResult);
 171
 172         /* Make sure default categories exists */
 173         poi_cb_populate_categories(db);
 174         return TRUE;
 175 }
 176
 177 gboolean
 178 poi_db_prepare(sqlite3 *db)
 179 {
 180         /* select from poi */
 181         sqlite3_prepare_v2(db,
 182                         "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
 183                         " p.cat_id, c.label, c.desc, c.icon, c.color"
 184                         " from poi p, category c "
 185                         " where p.lat between ? and ? "
 186                         " and p.lon between ? and ? "
 187                         " and c.enabled = 1 and p.cat_id = c.cat_id",
 188                         -1, &_stmt_select_poi, NULL);
 189
 190         /* select nearest pois */
 191         sqlite3_prepare_v2(db,
 192                         "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
 193                         " from poi p, category c "
 194                         " where c.enabled = 1 and p.cat_id = c.cat_id "
 195                         " and p.lat between $LAT-0.10 and $LAT+0.10 "
 196                         " and p.lon between $LON-0.10 and $LAT+0.10 "
 197                         " order by (($LAT - p.lat) * ($LAT - p.lat) "
 198                         "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
 199                         -1, &_stmt_select_nearest_poi, NULL);
 200
 201         /* insert poi */
 202         sqlite3_prepare_v2(db,
 203                         "insert into poi (lat, lon, label, desc, cat_id, public)"
 204                         " values (?, ?, ?, ?, ?, 1)", -1, &_stmt_insert_poi, NULL);
 205         /* update poi */
 206         sqlite3_prepare_v2(db, "update poi set label = ?, desc = ?, "
 207                         "cat_id = ? where poi_id = ?",
 208                         -1, &_stmt_update_poi, NULL);
 209         /* delete from poi */
 210         sqlite3_prepare_v2(db, "delete from poi where poi_id = ?",
 211                         -1, &_stmt_delete_poi, NULL);
 212         /* delete from poi by cat_id */
 213         sqlite3_prepare_v2(db, "delete from poi where cat_id = ?",
 214                         -1, &_stmt_delete_poi_by_catid, NULL);
 215         /* get next poilabel */
 216         sqlite3_prepare_v2(db, "select ifnull(max(poi_id) + 1,1) from poi",
 217                         -1, &_stmt_nextlabel_poi, NULL);
 218
 219         /* select from category */
 220         sqlite3_prepare_v2(db,
 221                         "select c.label, c.desc, c.enabled"
 222                         " from category c where c.cat_id = ?",
 223                         -1, &_stmt_select_cat, NULL);
 224         /* insert into category */
 225         sqlite3_prepare_v2(db,
 226                         "insert into category (label, desc, enabled)"
 227                         " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
 228         /* update category */
 229         sqlite3_prepare_v2(db,
 230                         "update category set label = ?, desc = ?,"
 231                         " enabled = ? where poi_id = ?",
 232                         -1, &_stmt_update_cat, NULL);
 233         /* delete from category */
 234         sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
 235                         -1, &_stmt_delete_cat, NULL);
 236         /* enable category */
 237         sqlite3_prepare_v2(db,
 238                         "update category set enabled = ?"
 239                         " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
 240         /* select all category */
 241         sqlite3_prepare_v2(db,
 242                         "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
 243                         " count(p.poi_id)"
 244                         " from category c"
 245                         " left outer join poi p on c.cat_id = p.cat_id"
 246                         " group by c.cat_id, c.label, c.desc, c.enabled "
 247                         " order by c.label", -1, &_stmt_selall_cat, NULL);
 248
 249         return TRUE;
 250 }
 251
 252 gboolean
 253 poi_db_disconnect(sqlite3 **db)
 254 {
 255 if (db && *db) {
 256         /* XXX: Handle prepared statements */
 257         sqlite3_close(*db);
 258         return TRUE;
 259 }
 260 return FALSE;
 261 }
 262
 263 gboolean
 264 poi_db_connect(sqlite3 **db, const gchar *poi_db)
 265 {
 266 if (db_connect(db, poi_db)) {
 267         poi_db_create(*db);
 268         if (poi_db_prepare(*db)==FALSE)
 269                 g_printerr("Failed to prepare POI SQL statements");
 270         if (osm_db_prepare(*db)==FALSE)
 271                 g_printerr("Failed to prepare OSM SQL statements");
 272         return TRUE;
 273 }
 274 return FALSE;
 275 }
 276
 277 PoiInfo *
 278 poi_new(void)
 279 {
 280 PoiInfo *p;
 281
 282 return g_slice_new0(PoiInfo);
 283 }
 284
 285 void
 286 poi_free(PoiInfo *p)
 287 {
 288 g_slice_free(PoiInfo, p);
 289 }
 290
 291 gboolean
 292 poi_category_update(gint cat_id, gchar *cat_label, gchar *cat_desc, gint cat_enabled)
 293 {
 294 gboolean results=TRUE;
 295
 296 if (!_db)
 297         return FALSE;
 298
 299 if (cat_id > 0) {
 300 /* edit category */
 301                 if (SQLITE_OK !=
 302                     sqlite3_bind_text(_stmt_update_cat, 1, cat_label, -1,
 303                                       g_free)
 304                     || SQLITE_OK != sqlite3_bind_text(_stmt_update_cat, 2,
 305                                                       cat_desc, -1, g_free)
 306                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 3,
 307                                                      cat_enabled)
 308                     || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 4,
 309                                                      cat_id)
 310                     || SQLITE_DONE != sqlite3_step(_stmt_update_cat)) {
 311                         results = FALSE;
 312                 }
 313                 sqlite3_reset(_stmt_update_cat);
 314                 sqlite3_clear_bindings(_stmt_update_cat);
 315         } else {
 316                 /* add category */
 317                 if (SQLITE_OK !=
 318                     sqlite3_bind_text(_stmt_insert_cat, 1, cat_label, -1,
 319                                       g_free)
 320                     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_cat, 2,
 321                                                       cat_desc, -1, g_free)
 322                     || SQLITE_OK != sqlite3_bind_int(_stmt_insert_cat, 3,
 323                                                      cat_enabled)
 324                     || SQLITE_DONE != sqlite3_step(_stmt_insert_cat)) {
 325                         results = FALSE;
 326                 }
 327                 sqlite3_reset(_stmt_insert_cat);
 328                 sqlite3_clear_bindings(_stmt_insert_cat);
 329         }
 330 return results;
 331 }
 332
 333 gboolean
 334 poi_category_delete(DeletePOI *dpoi)
 335 {
 336 if (!_db)
 337         return FALSE;
 338
 339 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi_by_catid, 1, dpoi->id)
 340             || SQLITE_DONE != sqlite3_step(_stmt_delete_poi_by_catid)) {
 341                 sqlite3_reset(_stmt_delete_poi_by_catid);
 342                 return FALSE;
 343 }
 344 sqlite3_reset(_stmt_delete_poi_by_catid);
 345 sqlite3_clear_bindings(_stmt_delete_poi_by_catid);
 346
 347 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_cat, 1, dpoi->id) ||
 348     SQLITE_DONE != sqlite3_step(_stmt_delete_cat)) {
 349         sqlite3_reset(_stmt_delete_cat);
 350         return FALSE;
 351 }
 352 sqlite3_reset(_stmt_delete_cat);
 353 sqlite3_clear_bindings(_stmt_delete_cat);
 354 return TRUE;
 355 }
 356
 357
 358 gboolean
 359 poi_delete(DeletePOI *dpoi)
 360 {
 361 if (!_db)
 362         return FALSE;
 363
 364 if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi, 1, dpoi->id) ||
 365     SQLITE_DONE != sqlite3_step(_stmt_delete_poi)) {
 366         sqlite3_reset(_stmt_delete_poi);
 367         return FALSE;
 368 } else {
 369         sqlite3_reset(_stmt_delete_poi);
 370         sqlite3_clear_bindings(_stmt_delete_poi);
 371 }
 372 return TRUE;
 373 }
 374
 375 gboolean
 376 poi_get_list(guint unitx, guint unity, GtkListStore **_store, guint *_num_cats)
 377 {
 378 guint x, y;
 379 gfloat lat1, lon1, lat2, lon2;
 380 GtkListStore *store;
 381 GtkTreeIter iter;
 382 gchar tmp1[16], tmp2[16];
 383 gint num_cats=0;
 384
 385 if (!_db)
 386         return FALSE;
 387
 388 store = gtk_list_store_new(POI_NUM_COLUMNS, G_TYPE_INT, /* POI ID */
 389                            G_TYPE_INT,  /* Category ID */
 390                            G_TYPE_FLOAT,        /* Latitude */
 391                            G_TYPE_FLOAT,        /* Longitude */
 392                            G_TYPE_STRING,       /* Lat/Lon */
 393                            G_TYPE_STRING,       /* POI Label */
 394                            G_TYPE_STRING,       /* POI Desc. */
 395                            G_TYPE_STRING);      /* Category Label */
 396
 397 x = unitx - pixel2unit(3 * _draw_width);
 398 y = unity + pixel2unit(3 * _draw_width);
 399 unit2latlon(x, y, lat1, lon1);
 400
 401 x = unitx + pixel2unit(3 * _draw_width);
 402 y = unity - pixel2unit(3 * _draw_width);
 403 unit2latlon(x, y, lat2, lon2);
 404
 405 if (SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 1, lat1) ||
 406     SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 2, lat2) ||
 407     SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 3, lon1) ||
 408     SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 4, lon2)) {
 409         g_printerr("Failed to bind values for _stmt_select_poi\n");
 410         return FALSE;
 411 }
 412
 413 while (SQLITE_ROW == sqlite3_step(_stmt_select_poi)) {
 414         gfloat lat, lon;
 415         lat = sqlite3_column_double(_stmt_select_poi, 0);
 416         lon = sqlite3_column_double(_stmt_select_poi, 1);
 417         g_printf("Found POI(%d): %f, %f, %s, %s, %s\n",
 418                 num_cats,
 419                 lat,
 420                 lon,
 421                 sqlite3_column_text(_stmt_select_poi, 3),
 422                 sqlite3_column_text(_stmt_select_poi, 4),
 423                 sqlite3_column_text(_stmt_select_poi, 6));
 424
 425         lat_format(lat, tmp1);
 426         lon_format(lon, tmp2);
 427         gtk_list_store_append(store, &iter);
 428         gtk_list_store_set(store, &iter,
 429                 POI_POIID, sqlite3_column_int(_stmt_select_poi, 2),
 430                 POI_CATID, sqlite3_column_int(_stmt_select_poi, 5),
 431                 POI_LAT, lat, POI_LON, lon, POI_LATLON,
 432                 g_strdup_printf("%s, %s", tmp1, tmp2),
 433                 POI_LABEL, sqlite3_column_text(_stmt_select_poi, 3),
 434                 POI_DESC, sqlite3_column_text(_stmt_select_poi, 4),
 435                 POI_CATLAB, sqlite3_column_text(_stmt_select_poi, 6),
 436                 -1);
 437         num_cats++;
 438 }
 439 g_printf("N: %d\n", num_cats);
 440 sqlite3_reset(_stmt_select_poi);
 441 sqlite3_clear_bindings(_stmt_select_poi);
 442 *_store=store;
 443 *_num_cats=num_cats;
 444 return TRUE;
 445 }
 446
 447 gboolean
 448 poi_update(gint poi_id, gint cat_id, gchar *poi_label, gchar *poi_desc)
 449 {
 450 if (!_db)
 451         return FALSE;
 452
 453 if (SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 1, poi_label, -1, SQLITE_STATIC)
 454    || SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 2, poi_desc, -1, g_free)
 455    || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 3, cat_id)
 456    || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 4, poi_id)
 457    || SQLITE_DONE != sqlite3_step(_stmt_update_poi)) {
 458                 return FALSE;
 459         }
 460 sqlite3_reset(_stmt_update_poi);
 461 sqlite3_clear_bindings(_stmt_update_poi);
 462 return TRUE;
 463 }
 464
 465 gboolean
 466 poi_add(gdouble lat, gdouble lon, gint cat_id, gchar *poi_label, gchar *poi_desc)
 467 {
 468 if (!_db)
 469         return FALSE;
 470
 471 if (SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 1, lat)
 472     || SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 2, lon)
 473     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 3, poi_label, -1, g_free)
 474     || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 4, poi_desc, -1, g_free)
 475     || SQLITE_OK != sqlite3_bind_int(_stmt_insert_poi, 5, cat_id)
 476         || SQLITE_DONE != sqlite3_step(_stmt_insert_poi)) {
 477                 return FALSE;
 478         }
 479 sqlite3_reset(_stmt_insert_poi);
 480 sqlite3_clear_bindings(_stmt_insert_poi);
 481 return TRUE;
 482 }
 483
 484 PoiInfo *
 485 poi_find_nearest(gdouble lat, gdouble lon)
 486 {
 487 const gchar *_poi_label, *_cat_label;
 488 PoiInfo *p;
 489
 490 if (!_db)
 491         return FALSE;
 492
 493 sqlite3_reset(_stmt_select_nearest_poi);
 494 sqlite3_clear_bindings(_stmt_select_nearest_poi);
 495
 496 if (SQLITE_OK == sqlite3_bind_double(_stmt_select_nearest_poi, 1, lat)
 497     && SQLITE_OK == sqlite3_bind_double(_stmt_select_nearest_poi, 2, lon)
 498     && SQLITE_ROW == sqlite3_step(_stmt_select_nearest_poi)) {
 499
 500         p=poi_new();
 501
 502         p->lat = sqlite3_column_double(_stmt_select_nearest_poi, 0);
 503         p->lon = sqlite3_column_double(_stmt_select_nearest_poi, 1);
 504         _poi_label = sqlite3_column_text(_stmt_select_nearest_poi, 2);
 505         _cat_label = sqlite3_column_text(_stmt_select_nearest_poi, 3);
 506         p->label=g_strdup(_poi_label);
 507         p->cat_label=g_strdup(_cat_label);
 508         g_printf("%f %f %s %s\n", p->lat, p->lon, p->label, p->cat_label);
 509         return p;
 510 }
 511 return NULL;
 512 }