13 #include <glib/gstdio.h>
16 #include <gconf/gconf-client.h>
17 #include <libxml/parser.h>
26 #include "mapper-types.h"
32 struct _poi_categories {
34 const gchar *name, *desc;
37 static struct _poi_categories default_poi_categories[] = {
38 {NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." },
39 {NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." },
40 {NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras." },
41 {NODE_AMENITY_HOSPITAL, "Hospital", "" },
42 {NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." },
43 {NODE_AMENITY_POLICE, "Police", "Police station"},
44 {NODE_AMENITY_HOTEL, "Hotel", "Places to stay temporarily or for the night."},
45 {NODE_AMENITY_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap."},
46 {NODE_AMENITY_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap."},
47 {NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint."},
48 {NODE_AMENITY_BANK, "Bank", "Place to take care of your money."},
49 {NODE_AMENITY_POST, "Post office", "Place to handle mail."},
50 {NODE_AMENITY_POST_BOX, "Post box", "Send letters."},
51 {NODE_AMENITY_TOURISM_INFO, "Tourism info", "A place for tourists to get information."},
52 {NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here."},
53 {NODE_AMENITY_RAILWAY_STATION, "Railway station", "Transportation by train."},
54 {NODE_AMENITY_RAILWAY_HALT, "Railway halt", ""},
55 {NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus."},
56 {NODE_AMENITY_BOAT, "Harbour", "Transportation by boat."},
57 {NODE_AMENITY_AIRPORT, "Airport", "Transportation by air."},
58 {NODE_AMENITY_CAMP_SITE, "Camp site", "Place to go camping"},
59 {NODE_AMENITY_CARAVAN_SITE, "Caravan site", ""},
60 {NODE_AMENITY_PICNIC_SITE, "Picnic", "Place to have a Picnic"},
61 {NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink."},
62 {NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party."},
63 {NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat."},
64 {NODE_AMENITY_CINEMA, "Cinema", "Place to see movies"},
65 {NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing"},
66 {NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services."},
67 {NODE_AMENITY_POW, "Place of Worchip", ""},
68 {NODE_AMENITY_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters."},
69 {NODE_AMENITY_COLLEGE, "College Campus/Building", ""},
70 {NODE_AMENITY_SCHOOL, "School", ""},
71 {NODE_AMENITY_WC, "WC/Toilets", ""},
72 {NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone"},
73 {NODE_AMENITY_ATTRACTION, "Attraction", "Something interesting"},
74 {NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else."},
75 {NODE_POI_END, NULL, NULL }
79 poi_populate_categories(sqlite3 *db)
81 sqlite3_stmt *sql_cat;
84 g_printf("Checking default categories\n");
85 sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority)"
86 " values (?, ?, ?, 1, ?)",
89 for (i=0; default_poi_categories[i].name; i++) {
90 sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
91 sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name , -1, SQLITE_TRANSIENT);
92 sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc , -1, SQLITE_TRANSIENT);
93 sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
94 sqlite3_step(sql_cat);
95 sqlite3_reset(sql_cat);
96 sqlite3_clear_bindings(sql_cat);
99 sqlite3_finalize(sql_cat);
105 poi_db_create(sqlite3 *db)
109 if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
110 &pszResult, &nRow, &nColumn, NULL)) {
113 g_printf("Creating initial tables\n");
115 create_sql = sqlite3_mprintf
117 "(poi_id integer PRIMARY KEY, "
124 "source integer default 0, "
125 "osm_id integer default 0, "
126 "priority integer default 2, "
128 "create table category (cat_id integer PRIMARY KEY,"
133 "priority integer default 2, "
134 "enabled integer);");
136 if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
137 && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
138 &pszResult, &nRow, &nColumn, NULL))) {
139 g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
145 sqlite3_free_table(pszResult);
147 /* Make sure default categories exists */
148 poi_populate_categories(db);
153 poi_db_prepare(sqlite3 *db)
155 /* select from poi */
156 sqlite3_prepare_v2(db,
157 "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
158 " p.cat_id, c.label, c.desc, c.icon, c.color"
159 " from poi p, category c "
160 " where p.lat between ? and ? "
161 " and p.lon between ? and ? "
162 " and c.enabled = 1 and p.cat_id = c.cat_id",
163 -1, &poisql.select_poi, NULL);
165 /* select nearest pois */
166 sqlite3_prepare_v2(db,
167 "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
168 " from poi p, category c "
169 " where c.enabled = 1 and p.cat_id = c.cat_id "
170 " and p.lat between $LAT-0.10 and $LAT+0.10 "
171 " and p.lon between $LON-0.10 and $LAT+0.10 "
172 " order by (($LAT - p.lat) * ($LAT - p.lat) "
173 "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
174 -1, &poisql.select_nearest_poi, NULL);
177 sqlite3_prepare_v2(db,
178 "insert into poi (lat, lon, label, desc, cat_id, public)"
179 " values (?, ?, ?, ?, ?, 1)", -1, &poisql.insert_poi, NULL);
181 sqlite3_prepare_v2(db, "update poi set label = ?, desc = ?, "
182 "cat_id = ? where poi_id = ?",
183 -1, &poisql.update_poi, NULL);
184 /* delete from poi */
185 sqlite3_prepare_v2(db, "delete from poi where poi_id = ?",
186 -1, &poisql.delete_poi, NULL);
187 /* delete from poi by cat_id */
188 sqlite3_prepare_v2(db, "delete from poi where cat_id = ?",
189 -1, &poisql.delete_poi_by_catid, NULL);
190 /* get next poilabel */
191 sqlite3_prepare_v2(db, "select ifnull(max(poi_id) + 1,1) from poi",
192 -1, &poisql.nextlabel_poi, NULL);
194 /* select from category */
195 sqlite3_prepare_v2(db,
196 "select c.label, c.desc, c.enabled"
197 " from category c where c.cat_id = ?",
198 -1, &poisql.select_cat, NULL);
199 /* insert into category */
200 sqlite3_prepare_v2(db,
201 "insert into category (label, desc, enabled)"
202 " values (?, ?, ?)", -1, &poisql.insert_cat, NULL);
203 /* update category */
204 sqlite3_prepare_v2(db,
205 "update category set label = ?, desc = ?,"
206 " enabled = ? where poi_id = ?",
207 -1, &poisql.update_cat, NULL);
208 /* delete from category */
209 sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
210 -1, &poisql.delete_cat, NULL);
211 /* enable category */
212 sqlite3_prepare_v2(db,
213 "update category set enabled = ?"
214 " where cat_id = ?", -1, &poisql.toggle_cat, NULL);
215 /* select all category */
216 sqlite3_prepare_v2(db,
217 "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
220 " left outer join poi p on c.cat_id = p.cat_id"
221 " group by c.cat_id, c.label, c.desc, c.enabled "
222 " order by c.priority,c.label", -1, &poisql.selall_cat, NULL);
224 /* Select quick categories */
225 sqlite3_prepare_v2(db,
226 "select c.cat_id, c.label, c.icon, c.color"
227 " from category c where c.enabled=1 "
228 " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL);
234 mapper_db_disconnect(sqlite3 **db)
237 /* XXX: Handle the prepared statements */
239 poi_db_unprepare(*db);
240 osm_db_unprepare(*db);
249 mapper_db_connect(sqlite3 **db, const gchar *data_db)
251 if (db_connect(db, data_db)) {
253 if (poi_db_prepare(*db)==FALSE)
254 g_printerr("Failed to prepare POI SQL statements");
255 if (osm_db_prepare(*db)==FALSE) {
256 g_printerr("Failed to prepare OSM SQL statements");
257 g_printf("SQLITE: %s\n", sqlite3_errmsg(*db));
267 return g_slice_new0(poi_info);
271 poi_free(poi_info *p)
273 g_slice_free(poi_info, p);
277 poi_category_new(void)
279 return g_slice_new0(poi_category);
283 poi_category_free(poi_category *c)
289 g_slice_free(poi_category, c);
293 poi_category_toggle(gint cat_id, gboolean cat_enabled)
295 if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
296 SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
297 SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
304 poi_category_get(gint cat_id, poi_category **c)
308 if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id)
309 || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
310 vprintf("%s(): return FALSE\n", __PRETTY_FUNCTION__);
311 sqlite3_reset(poisql.select_cat);
315 cc=poi_category_new();
317 cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
318 cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
319 cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
321 sqlite3_reset(poisql.select_cat);
322 sqlite3_clear_bindings(poisql.select_cat);
328 poi_category_update(gint cat_id, poi_category *c)
330 gboolean results=TRUE;
338 sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, g_free)
339 || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, g_free)
340 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
341 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
342 || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
345 sqlite3_reset(poisql.update_cat);
346 sqlite3_clear_bindings(poisql.update_cat);
350 sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, g_free)
351 || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, g_free)
352 || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
353 || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
356 sqlite3_reset(poisql.insert_cat);
357 sqlite3_clear_bindings(poisql.insert_cat);
363 poi_category_delete(delete_poi *dpoi)
368 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, dpoi->id)
369 || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
370 sqlite3_reset(poisql.delete_poi_by_catid);
373 sqlite3_reset(poisql.delete_poi_by_catid);
374 sqlite3_clear_bindings(poisql.delete_poi_by_catid);
376 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, dpoi->id) ||
377 SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
378 sqlite3_reset(poisql.delete_cat);
381 sqlite3_reset(poisql.delete_cat);
382 sqlite3_clear_bindings(poisql.delete_cat);
388 poi_delete(delete_poi *dpoi)
393 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, dpoi->id) ||
394 SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
395 sqlite3_reset(poisql.delete_poi);
398 sqlite3_reset(poisql.delete_poi);
399 sqlite3_clear_bindings(poisql.delete_poi);
405 poi_get_list(guint unitx, guint unity, GtkListStore **store, guint *_num_cats)
408 gdouble lat1, lon1, lat2, lon2;
410 gchar tmp1[16], tmp2[16];
416 *store = gtk_list_store_new(POI_NUM_COLUMNS, G_TYPE_INT, /* POI ID */
417 G_TYPE_INT, /* Category ID */
418 G_TYPE_DOUBLE, /* Latitude */
419 G_TYPE_DOUBLE, /* Longitude */
420 G_TYPE_STRING, /* Lat/Lon */
421 G_TYPE_STRING, /* POI Label */
422 G_TYPE_STRING, /* POI Desc. */
423 G_TYPE_STRING); /* Category Label */
425 x = unitx - pixel2unit(3 * _draw_width);
426 y = unity + pixel2unit(3 * _draw_width);
427 unit2latlon(x, y, lat1, lon1);
429 x = unitx + pixel2unit(3 * _draw_width);
430 y = unity - pixel2unit(3 * _draw_width);
431 unit2latlon(x, y, lat2, lon2);
433 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
434 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
435 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
436 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
437 g_printerr("Failed to bind values for poisql.select_poi\n");
441 while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
443 lat = sqlite3_column_double(poisql.select_poi, 0);
444 lon = sqlite3_column_double(poisql.select_poi, 1);
445 lat_format(lat, tmp1);
446 lon_format(lon, tmp2);
448 gtk_list_store_append(*store, &iter);
449 gtk_list_store_set(*store, &iter,
450 POI_POIID, sqlite3_column_int(poisql.select_poi, 2),
451 POI_CATID, sqlite3_column_int(poisql.select_poi, 5),
452 POI_LAT, lat, POI_LON, lon, POI_LATLON,
453 g_strdup_printf("%s, %s", tmp1, tmp2),
454 POI_LABEL, sqlite3_column_text(poisql.select_poi, 3),
455 POI_DESC, sqlite3_column_text(poisql.select_poi, 4),
456 POI_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
460 sqlite3_reset(poisql.select_poi);
461 sqlite3_clear_bindings(poisql.select_poi);
467 poi_update(gint poi_id, gint cat_id, gchar *poi_label, gchar *poi_desc)
472 if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, poi_label, -1, SQLITE_STATIC)
473 || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, poi_desc, -1, g_free)
474 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, cat_id)
475 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, poi_id)
476 || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
479 sqlite3_reset(poisql.update_poi);
480 sqlite3_clear_bindings(poisql.update_poi);
485 poi_add(gdouble lat, gdouble lon, gint cat_id, gchar *poi_label, gchar *poi_desc)
490 if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, lat)
491 || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, lon)
492 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, poi_label, -1, g_free)
493 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, poi_desc, -1, g_free)
494 || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 5, cat_id)
495 || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
498 sqlite3_reset(poisql.insert_poi);
499 sqlite3_clear_bindings(poisql.insert_poi);
504 poi_find_nearest(gdouble lat, gdouble lon)
511 sqlite3_reset(poisql.select_nearest_poi);
512 sqlite3_clear_bindings(poisql.select_nearest_poi);
514 if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
515 && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
516 && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
519 p->lat = sqlite3_column_double(poisql.select_nearest_poi, 0);
520 p->lon = sqlite3_column_double(poisql.select_nearest_poi, 1);
521 p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 2));
522 p->cat_label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
529 poi_category_generate_store()
533 printf("%s()\n", __PRETTY_FUNCTION__);
535 store = gtk_list_store_new(CAT_NUM_COLUMNS,
538 G_TYPE_STRING, G_TYPE_STRING, G_TYPE_UINT);
540 while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
541 gtk_list_store_append(store, &iter);
542 gtk_list_store_set(store, &iter,
543 CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
544 CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
545 CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
546 CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
547 CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6), -1);
550 sqlite3_reset(poisql.selall_cat);
551 sqlite3_clear_bindings(poisql.selall_cat);
553 vprintf("%s(): return %p\n", __PRETTY_FUNCTION__, store);