13 #include <glib/gstdio.h>
16 #include <gconf/gconf-client.h>
17 #include <libxml/parser.h>
26 #include "mapper-types.h"
31 struct _poi_categories {
33 const gchar *name, *desc;
36 static struct _poi_categories default_poi_categories[] = {
37 {NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." },
38 {NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." },
39 {NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras." },
40 {NODE_AMENITY_HOSPITAL, "Hospital", "" },
41 {NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." },
42 {NODE_AMENITY_POLICE, "Police", "Police station"},
43 {NODE_AMENITY_HOTEL, "Hotel", "Places to stay temporarily or for the night."},
44 {NODE_AMENITY_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap."},
45 {NODE_AMENITY_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap."},
46 {NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint."},
47 {NODE_AMENITY_BANK, "Bank", "Place to take care of your money."},
48 {NODE_AMENITY_POST, "Post office", "Place to handle mail."},
49 {NODE_AMENITY_POST_BOX, "Post box", "Send letters."},
50 {NODE_AMENITY_TOURISM_INFO, "Tourism info", "A place for tourists to get information."},
51 {NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here."},
52 {NODE_AMENITY_RAILWAY_STATION, "Railway station", "Transportation by train."},
53 {NODE_AMENITY_RAILWAY_HALT, "Railway halt", ""},
54 {NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus."},
55 {NODE_AMENITY_BOAT, "Harbour", "Transportation by boat."},
56 {NODE_AMENITY_AIRPORT, "Airport", "Transportation by air."},
57 {NODE_AMENITY_CAMP_SITE, "Camp site", "Place to go camping"},
58 {NODE_AMENITY_CARAVAN_SITE, "Caravan site", ""},
59 {NODE_AMENITY_PICNIC_SITE, "Picnic", "Place to have a Picnic"},
60 {NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink."},
61 {NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party."},
62 {NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat."},
63 {NODE_AMENITY_CINEMA, "Cinema", "Place to see movies"},
64 {NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing"},
65 {NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services."},
66 {NODE_AMENITY_POW, "Place of Worchip", ""},
67 {NODE_AMENITY_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters."},
68 {NODE_AMENITY_COLLEGE, "College Campus/Building", ""},
69 {NODE_AMENITY_SCHOOL, "School", ""},
70 {NODE_AMENITY_WC, "WC/Toilets", ""},
71 {NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone"},
72 {NODE_AMENITY_ATTRACTION, "Attraction", "Something interesting"},
73 {NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else."},
74 {NODE_POI_END, NULL, NULL }
78 poi_populate_categories(sqlite3 *db)
80 sqlite3_stmt *sql_cat;
83 g_printf("Checking default categories\n");
84 sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority)"
85 " values (?, ?, ?, 1, ?)",
88 for (i=0; default_poi_categories[i].name; i++) {
89 sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
90 sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name , -1, SQLITE_TRANSIENT);
91 sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc , -1, SQLITE_TRANSIENT);
92 sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
93 sqlite3_step(sql_cat);
94 sqlite3_reset(sql_cat);
95 sqlite3_clear_bindings(sql_cat);
98 sqlite3_finalize(sql_cat);
104 poi_db_create(sqlite3 *db)
108 if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
109 &pszResult, &nRow, &nColumn, NULL)) {
112 g_printf("Creating initial tables\n");
114 create_sql = sqlite3_mprintf
116 "(poi_id integer PRIMARY KEY, "
123 "source integer default 0, "
124 "osm_id integer default 0, "
125 "priority integer default 2, "
127 "create table category (cat_id integer PRIMARY KEY,"
132 "priority integer default 2, "
133 "enabled integer);");
135 if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
136 && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
137 &pszResult, &nRow, &nColumn, NULL))) {
138 g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
144 sqlite3_free_table(pszResult);
146 /* Make sure default categories exists */
147 poi_populate_categories(db);
152 poi_db_prepare(sqlite3 *db)
154 /* select from poi */
155 sqlite3_prepare_v2(db,
156 "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
157 " p.cat_id, c.label, c.desc, c.icon, c.color"
158 " from poi p, category c "
159 " where p.lat between ? and ? "
160 " and p.lon between ? and ? "
161 " and c.enabled = 1 and p.cat_id = c.cat_id",
162 -1, &poisql.select_poi, NULL);
164 /* select nearest pois */
165 sqlite3_prepare_v2(db,
166 "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
167 " from poi p, category c "
168 " where c.enabled = 1 and p.cat_id = c.cat_id "
169 " and p.lat between $LAT-0.10 and $LAT+0.10 "
170 " and p.lon between $LON-0.10 and $LAT+0.10 "
171 " order by (($LAT - p.lat) * ($LAT - p.lat) "
172 "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
173 -1, &poisql.select_nearest_poi, NULL);
176 sqlite3_prepare_v2(db,
177 "insert into poi (lat, lon, label, desc, cat_id, public)"
178 " values (?, ?, ?, ?, ?, 1)", -1, &poisql.insert_poi, NULL);
180 sqlite3_prepare_v2(db, "update poi set label = ?, desc = ?, "
181 "cat_id = ? where poi_id = ?",
182 -1, &poisql.update_poi, NULL);
183 /* delete from poi */
184 sqlite3_prepare_v2(db, "delete from poi where poi_id = ?",
185 -1, &poisql.delete_poi, NULL);
186 /* delete from poi by cat_id */
187 sqlite3_prepare_v2(db, "delete from poi where cat_id = ?",
188 -1, &poisql.delete_poi_by_catid, NULL);
189 /* get next poilabel */
190 sqlite3_prepare_v2(db, "select ifnull(max(poi_id) + 1,1) from poi",
191 -1, &poisql.nextlabel_poi, NULL);
193 /* select from category */
194 sqlite3_prepare_v2(db,
195 "select c.label, c.desc, c.enabled"
196 " from category c where c.cat_id = ?",
197 -1, &poisql.select_cat, NULL);
198 /* insert into category */
199 sqlite3_prepare_v2(db,
200 "insert into category (label, desc, enabled)"
201 " values (?, ?, ?)", -1, &poisql.insert_cat, NULL);
202 /* update category */
203 sqlite3_prepare_v2(db,
204 "update category set label = ?, desc = ?,"
205 " enabled = ? where poi_id = ?",
206 -1, &poisql.update_cat, NULL);
207 /* delete from category */
208 sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
209 -1, &poisql.delete_cat, NULL);
210 /* enable category */
211 sqlite3_prepare_v2(db,
212 "update category set enabled = ?"
213 " where cat_id = ?", -1, &poisql.toggle_cat, NULL);
214 /* select all category */
215 sqlite3_prepare_v2(db,
216 "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
219 " left outer join poi p on c.cat_id = p.cat_id"
220 " group by c.cat_id, c.label, c.desc, c.enabled "
221 " order by c.priority,c.label", -1, &poisql.selall_cat, NULL);
223 /* Select quick categories */
224 sqlite3_prepare_v2(db,
225 "select c.cat_id, c.label, c.icon, c.color"
226 " from category c where c.enabled=1 "
227 " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL);
233 mapper_db_disconnect(sqlite3 **db)
236 /* XXX: Handle the prepared statements */
238 poi_db_unprepare(*db);
239 osm_db_unprepare(*db);
248 mapper_db_connect(sqlite3 **db, const gchar *data_db)
250 if (db_connect(db, data_db)) {
252 if (poi_db_prepare(*db)==FALSE)
253 g_printerr("Failed to prepare POI SQL statements");
254 if (osm_db_prepare(*db)==FALSE) {
255 g_printerr("Failed to prepare OSM SQL statements");
256 g_printf("SQLITE: %s\n", sqlite3_errmsg(*db));
266 return g_slice_new0(poi_info);
270 poi_free(poi_info *p)
272 g_slice_free(poi_info, p);
276 poi_category_new(void)
278 return g_slice_new0(poi_category);
282 poi_category_free(poi_category *c)
288 g_slice_free(poi_category, c);
292 poi_category_toggle(gint cat_id, gboolean cat_enabled)
294 if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
295 SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
296 SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
303 poi_category_get(gint cat_id, poi_category **c)
307 if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id)
308 || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
309 vprintf("%s(): return FALSE\n", __PRETTY_FUNCTION__);
310 sqlite3_reset(poisql.select_cat);
314 cc=poi_category_new();
316 cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
317 cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
318 cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
320 sqlite3_reset(poisql.select_cat);
321 sqlite3_clear_bindings(poisql.select_cat);
327 poi_category_update(gint cat_id, poi_category *c)
329 gboolean results=TRUE;
337 sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, g_free)
338 || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, g_free)
339 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
340 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
341 || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
344 sqlite3_reset(poisql.update_cat);
345 sqlite3_clear_bindings(poisql.update_cat);
349 sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, g_free)
350 || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, g_free)
351 || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
352 || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
355 sqlite3_reset(poisql.insert_cat);
356 sqlite3_clear_bindings(poisql.insert_cat);
362 poi_category_delete(delete_poi *dpoi)
367 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, dpoi->id)
368 || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
369 sqlite3_reset(poisql.delete_poi_by_catid);
372 sqlite3_reset(poisql.delete_poi_by_catid);
373 sqlite3_clear_bindings(poisql.delete_poi_by_catid);
375 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, dpoi->id) ||
376 SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
377 sqlite3_reset(poisql.delete_cat);
380 sqlite3_reset(poisql.delete_cat);
381 sqlite3_clear_bindings(poisql.delete_cat);
387 poi_delete(delete_poi *dpoi)
392 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, dpoi->id) ||
393 SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
394 sqlite3_reset(poisql.delete_poi);
397 sqlite3_reset(poisql.delete_poi);
398 sqlite3_clear_bindings(poisql.delete_poi);
404 poi_get_list(guint unitx, guint unity, GtkListStore **store, guint *_num_cats)
407 gdouble lat1, lon1, lat2, lon2;
409 gchar tmp1[16], tmp2[16];
415 *store = gtk_list_store_new(POI_NUM_COLUMNS, G_TYPE_INT, /* POI ID */
416 G_TYPE_INT, /* Category ID */
417 G_TYPE_DOUBLE, /* Latitude */
418 G_TYPE_DOUBLE, /* Longitude */
419 G_TYPE_STRING, /* Lat/Lon */
420 G_TYPE_STRING, /* POI Label */
421 G_TYPE_STRING, /* POI Desc. */
422 G_TYPE_STRING); /* Category Label */
424 x = unitx - pixel2unit(3 * _draw_width);
425 y = unity + pixel2unit(3 * _draw_width);
426 unit2latlon(x, y, lat1, lon1);
428 x = unitx + pixel2unit(3 * _draw_width);
429 y = unity - pixel2unit(3 * _draw_width);
430 unit2latlon(x, y, lat2, lon2);
432 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
433 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
434 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
435 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
436 g_printerr("Failed to bind values for poisql.select_poi\n");
440 while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
442 lat = sqlite3_column_double(poisql.select_poi, 0);
443 lon = sqlite3_column_double(poisql.select_poi, 1);
444 lat_format(lat, tmp1);
445 lon_format(lon, tmp2);
447 gtk_list_store_append(*store, &iter);
448 gtk_list_store_set(*store, &iter,
449 POI_POIID, sqlite3_column_int(poisql.select_poi, 2),
450 POI_CATID, sqlite3_column_int(poisql.select_poi, 5),
451 POI_LAT, lat, POI_LON, lon, POI_LATLON,
452 g_strdup_printf("%s, %s", tmp1, tmp2),
453 POI_LABEL, sqlite3_column_text(poisql.select_poi, 3),
454 POI_DESC, sqlite3_column_text(poisql.select_poi, 4),
455 POI_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
459 sqlite3_reset(poisql.select_poi);
460 sqlite3_clear_bindings(poisql.select_poi);
466 poi_update(gint poi_id, gint cat_id, gchar *poi_label, gchar *poi_desc)
471 if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, poi_label, -1, SQLITE_STATIC)
472 || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, poi_desc, -1, g_free)
473 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, cat_id)
474 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, poi_id)
475 || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
478 sqlite3_reset(poisql.update_poi);
479 sqlite3_clear_bindings(poisql.update_poi);
484 poi_add(gdouble lat, gdouble lon, gint cat_id, gchar *poi_label, gchar *poi_desc)
489 if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, lat)
490 || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, lon)
491 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, poi_label, -1, g_free)
492 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, poi_desc, -1, g_free)
493 || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 5, cat_id)
494 || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
497 sqlite3_reset(poisql.insert_poi);
498 sqlite3_clear_bindings(poisql.insert_poi);
503 poi_find_nearest(gdouble lat, gdouble lon)
510 sqlite3_reset(poisql.select_nearest_poi);
511 sqlite3_clear_bindings(poisql.select_nearest_poi);
513 if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
514 && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
515 && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
518 p->lat = sqlite3_column_double(poisql.select_nearest_poi, 0);
519 p->lon = sqlite3_column_double(poisql.select_nearest_poi, 1);
520 p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 2));
521 p->cat_label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
528 poi_category_generate_store()
532 printf("%s()\n", __PRETTY_FUNCTION__);
534 store = gtk_list_store_new(CAT_NUM_COLUMNS,
537 G_TYPE_STRING, G_TYPE_STRING, G_TYPE_UINT);
539 while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
540 gtk_list_store_append(store, &iter);
541 gtk_list_store_set(store, &iter,
542 CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
543 CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
544 CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
545 CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
546 CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6), -1);
549 sqlite3_reset(poisql.selall_cat);
550 sqlite3_clear_bindings(poisql.selall_cat);
552 vprintf("%s(): return %p\n", __PRETTY_FUNCTION__, store);