11 #include <glib/gstdio.h>
14 #include <gconf/gconf-client.h>
15 #include <libxml/parser.h>
24 #include "mapper-types.h"
31 static sqlite3 *poidb;
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";
37 /* Hash table for caching POI icons */
38 static GHashTable *poi_icon_hash = NULL;
40 struct _poi_categories {
42 const gchar *name, *desc, *icon, *color;
45 /* The default POI categories */
46 static struct _poi_categories default_poi_categories[] = {
47 { NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." , "vehicle/parking", "#2020ff" },
48 { NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." , "vehicle/fuel_station", "#4040f0" },
49 { NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras." , "vehicle/restrictions/speed_trap", "#ff0000" },
50 { NODE_AMENITY_HOSPITAL, "Hospital", "Get medical help" , "health/hospital", "#ff4040" },
51 { NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." , "health/pharmacy", "#40f040" },
52 { NODE_AMENITY_POLICE, "Police", "Police station" , "public/police", "#8570ff" },
53 { NODE_TOURISM_HOTEL, "Hotel", "Places to stay temporarily or for the night." , "accommodation/hotel", "#ba20ba" },
54 { NODE_TOURISM_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap." , "accommodation/hostel", "#ba30ba" },
55 { NODE_TOURISM_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap." , "accommodation/motel", "#ba40ba" },
56 { NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint." , "money/atm", "#40a040" },
57 { NODE_AMENITY_BANK, "Bank", "Place to take care of your money." , "money/bank", "#50b550" },
58 { NODE_AMENITY_POST, "Post office", "Place to handle mail." , "public/post_office", "#ff6868" },
59 { NODE_AMENITY_POST_BOX, "Post box", "Send letters." , "public/post_box", "#ff6060" },
60 { NODE_TOURISM_INFO, "Tourism info", "A place for tourists to get information." , "misc/information", "#4af04a" },
61 { NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here." , "transport/taxi", "#50ffff" },
62 { NODE_RAILWAY_STATION, "Railway station", "Transportation by train." , "transport/railway", "#fa7070" },
63 { NODE_RAILWAY_HALT, "Railway halt", "" , "transport/railway_small", "#f27777" },
64 { NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus." , "transport/bus", "#f07070" },
65 { NODE_AMENITY_BOAT, "Harbour", "Transportation by boat." , "transport/ferry", "#9090ee" },
66 { NODE_AIRPORT_TERMINAL, "Airport", "Transportation by air." , "transport/airport", "#909099" },
67 { NODE_TOURISM_CAMP_SITE, "Camp site", "Place to go camping" , "accommodation/camping", "#90f080" },
68 { NODE_TOURISM_CARAVAN_SITE, "Caravan site", "" , "accommodation/camping/caravan", "#90ff80" },
69 { NODE_TOURISM_PICNIC_SITE, "Picnic", "Place to have a Picnic" , "recreation/picnic", "#a5f8e5" },
70 { NODE_AMENITY_FOOD, "Fast food", "Places to eat or drink." , "food/fastfood", "#e5960d" },
71 { NODE_AMENITY_RESTAURANT, "Restaurant", "Fancy places to eat or drink." , "food/restaurant", "#e5960d" },
72 { NODE_AMENITY_PUB, "Pub", "Place to drink." , "food/pub", "#f5960d" },
73 { NODE_AMENITY_NIGHTCLUB, "Disco, Club", "Place to drink, party and dance." , "food/bar", "#f59c1d" },
74 { NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat." , "food/cafe", "#ff960d" },
75 { NODE_AMENITY_CINEMA, "Cinema", "Place to see movies" , "recreation/cinema", "#9090a0" },
76 { NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing" , "recreation/theater", "#9595a5" },
77 { NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services." , "shopping", "#61ef1b" },
78 { NODE_AMENITY_SHOP_ADULT, "Adult shop", "Shops providing adult items, such as sex toys,pornography and fetish clothing" , "", "#ff0000" },
79 { NODE_AMENITY_POW, "Place of Worchip", "" , "religion/church", "#7c5b0b" },
80 { NODE_TOURISM_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters." , "recreation/theme_park", "#8eef1b" },
81 { NODE_AMENITY_COLLEGE, "College Campus/Building", "" , "education/college", "#813fdc" },
82 { NODE_AMENITY_SCHOOL, "School", "" , "education/school", "#813fdc" },
83 { NODE_AMENITY_LIBRARY, "Library", "Place to read and borrow books and magazines" , "shopping/rental/library", "#803090" },
84 { NODE_AMENITY_TOWNHALL, "Townhall", "" , "", "#408090" },
85 { NODE_AMENITY_WC, "WC/Toilets", "" , "public/toilets", "#e1d62c" },
86 { NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone" , "public/telephone", "#208060" },
87 { NODE_TOURISM_ATTRACTION, "Attraction", "Something interesting" , "misc", "#005000" },
89 { NODE_HISTORIC_MUSEUM, "Museum", "A place where objects of historical, artistic, or scientific interest are exhibited, preserved or studied." , "sightseeing/museum", "#202020" },
90 { 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" },
92 { NODE_SPORT_CENTER, "Sport Center", "" , "sports/centre", "#101080" },
93 { NODE_SPORT_STADIUM, "Sport Stadium", "" , "sports/stadium", "#101080" },
94 { NODE_SPORT_SKIING, "Skiing", "" , "sports/skiing", "#5050A0" },
95 { NODE_SPORT_SWIMMING, "Swimming", "" , "sports/swimming", "#102080" },
96 { NODE_SPORT_FOOTBALL, "Football", "" , "sports/football", "#102080" },
97 { NODE_SPORT_SOCCER, "Soccer", "" , "sports/soccer", "#102080" },
98 { NODE_SPORT_GOLF, "Golf", "" , "sports/golf", "#102080" },
99 { NODE_SPORT_TENNIS, "Tennis", "" , "sports/tennis", "#101080" },
100 { NODE_SPORT_BOWLING, "Bowling", "" , "sports", "#101080" },
101 { NODE_SPORT_RUGBY, "Rugby", "" , "sports", "#101080" },
102 { NODE_SPORT_CLIMBING, "Climbing", "" , "sports", "#101080" },
103 { NODE_SPORT_CYCLING, "Cycling", "" , "sports/bicycle", "#101080" },
104 { NODE_SPORT_MOTOR, "Motor sport", "" , "sports", "#101080" },
105 { NODE_SPORT_HOCKEY, "Hockey", "" , "sports", "#5050A0" },
106 { NODE_SPORT_SKATING, "Skating", "" , "sports", "#5050A0" },
107 { NODE_SPORT_SKATEBOARD, "Skateboard", "" , "sports", "#101080" },
108 { NODE_SPORT_HORSES, "Horses", "Horse riding or racing" , "sports/riding", "#101080" },
109 { NODE_SPORT_DOG, "Dog racing", "" , "sports", "#101080" },
110 { NODE_SPORT_BASKETBALL, "Basketball", "" , "sports", "#101080" },
111 { NODE_SPORT_BASEBALL, "Baseball", "" , "sports", "#101080" },
112 { NODE_SPORT_CANOE, "Canoe", "" , "", "#101080" },
113 { NODE_SPORT_CROQUET, "Croquet", "" , "sports", "#101080" },
114 { NODE_SPORT_CRICKET, "Cricket", "" , "sports", "#101080" },
115 { NODE_SPORT_SHOOTING, "Shooting", "Shooting range" , "sports", "#101080" },
116 { NODE_SPORT_PAINTBALL, "Paintball", "Run around and shoot people with paintballs" , "sports", "#101080" },
117 { NODE_SPORT_TABLE_TENNIS, "Table tennis", "" , "sports", "#101080" },
118 { NODE_SPORT_PELOTA, "Pelota", "" , "sports", "#101080" },
119 { NODE_SPORT_RACQUET, "Racquet", "" , "sports/racquetball", "#101080" },
120 { NODE_SPORT_BOWLS, "Lawn Bowls", "" , "sports", "#101080" },
121 { NODE_SPORT_ATHLETICS, "Athletics", "" , "sports", "#101080" },
122 { NODE_SPORT_OTHER, "Other Sports", "" , "sports/stadium", "#101077" },
124 { NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else." , "misc", "#002000" },
125 { NODE_POI_END, NULL, NULL }
129 poi_populate_categories(sqlite3 *db)
131 sqlite3_stmt *sql_cat;
134 sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)"
135 " values (?, ?, ?, 1, ?, ?, ?)",
138 for (i=0; default_poi_categories[i].name; i++) {
139 sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
140 sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name, -1, SQLITE_STATIC);
141 sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc, -1, SQLITE_STATIC);
142 sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
143 sqlite3_bind_text(sql_cat, 5, default_poi_categories[i].icon, -1, SQLITE_STATIC);
144 sqlite3_bind_text(sql_cat, 6, default_poi_categories[i].color, -1, SQLITE_STATIC);
145 if (sqlite3_step(sql_cat)==SQLITE_OK)
146 g_printf("Failed to update category: %d [%s]\n", i, default_poi_categories[i].name);
147 sqlite3_reset(sql_cat);
148 sqlite3_clear_bindings(sql_cat);
151 sqlite3_finalize(sql_cat);
157 poi_get_icon_from_type(node_type_t t)
161 for (i=0; default_poi_categories[i].name; i++) {
162 if (t==default_poi_categories[i].type)
163 return default_poi_categories[i].icon;
169 poi_db_create(sqlite3 *db)
174 if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
175 &pszResult, &nRow, &nColumn, NULL)) {
178 g_printerr("Creating initial POI tables\n");
180 create_sql = sqlite3_mprintf
182 "(poi_id INTEGER PRIMARY KEY AUTOINCREMENT, "
190 "public int default 1, "
191 "source int default 0, "
192 "cat_id int default 900,"
193 "osm_id int default 0, "
194 "isin_c int default 0, "
195 "isin_p int default 0, "
196 "priority int default 2, "
198 "create table category (cat_id INTEGER PRIMARY KEY AUTOINCREMENT,"
203 "priority int default 2, "
206 if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
207 && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
208 &pszResult, &nRow, &nColumn, NULL))) {
209 g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
215 sqlite3_free_table(pszResult);
218 /* Make sure default categories exists */
219 poi_populate_categories(db);
240 #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"
241 #define POI_MINI_SQL_FIELDS "p.lat, p.lon, p.poi_id, p.label, p.desc, p.cat_id, c.label, c.desc"
244 poi_db_prepare(sqlite3 *db)
246 /* Select POIs inside given minmax lat,lon */
247 if (sqlite3_prepare_v2(db,
250 " from poi p, category c "
251 " where p.lat between ? and ? "
252 " and p.lon between ? and ? "
253 " and c.enabled=1 and p.cat_id=c.cat_id order by c.priority limit 500",
254 -1, &poisql.select_poi, NULL)!=SQLITE_OK)
257 /* Get POI with given ID */
258 if (sqlite3_prepare_v2(db,
261 " from poi p, category c "
262 " where p.poi_id = ? "
263 " and p.cat_id=c.cat_id",
264 -1, &poisql.select_poi_by_id, NULL)!=SQLITE_OK)
267 /* Search POIs by label and any category */
268 if (sqlite3_prepare_v2(db,
271 " from poi p, category c "
272 " where p.lat between ? and ? "
273 " and p.lon between ? and ? "
274 " 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",
275 -1, &poisql.select_poi_search, NULL)!=SQLITE_OK)
278 /* Search POI by label and category */
279 if (sqlite3_prepare_v2(db,
282 " from poi p, category c "
283 " where p.lat between ? and ? "
284 " and p.lon between ? and ? "
285 " 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",
286 -1, &poisql.select_poi_search_cat, NULL)!=SQLITE_OK)
289 /* Search POIs by category */
290 if (sqlite3_prepare_v2(db,
293 " from poi p, category c "
294 " where p.lat between ? and ? "
295 " and p.lon between ? and ? "
296 " and c.enabled=1 and p.cat_id=c.cat_id and c.cat_id=? order by p.label",
297 -1, &poisql.select_poi_by_cat, NULL)!=SQLITE_OK)
300 /* Select any nearest POI */
301 if (sqlite3_prepare_v2(db,
304 " from poi p, category c "
305 " where c.enabled = 1 and p.cat_id = c.cat_id "
306 " and p.lat between $LAT-0.10 and $LAT+0.10 "
307 " and p.lon between $LON-0.10 and $LAT+0.10 "
308 " order by (($LAT - p.lat) * ($LAT - p.lat) "
309 "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
310 -1, &poisql.select_nearest_poi, NULL)!=SQLITE_OK)
314 sqlite3_prepare_v2(db, "insert into poi (lat, lon, label, desc, url, postal_code, cat_id, addtime, public, source)"
315 " values (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)", -1, &poisql.insert_poi, NULL);
317 sqlite3_prepare_v2(db, "update poi set label=?, desc=?, cat_id=? where poi_id=?", -1, &poisql.update_poi, NULL);
318 /* delete from poi */
319 sqlite3_prepare_v2(db, "delete from poi where poi_id=?", -1, &poisql.delete_poi, NULL);
320 /* delete from poi by cat_id */
321 sqlite3_prepare_v2(db, "delete from poi where cat_id=?", -1, &poisql.delete_poi_by_catid, NULL);
323 /* select from category */
324 sqlite3_prepare_v2(db,
325 "select c.label, c.desc, c.enabled"
326 " from category c where c.cat_id = ?",
327 -1, &poisql.select_cat, NULL);
328 /* insert into category */
329 sqlite3_prepare_v2(db,
330 "insert into category (label, desc, enabled)"
331 " values (?, ?, ?)", -1, &poisql.insert_cat, NULL);
332 /* update category */
333 sqlite3_prepare_v2(db,
334 "update category set label = ?, desc = ?,"
335 " enabled = ? where poi_id = ?",
336 -1, &poisql.update_cat, NULL);
337 /* delete from category */
338 sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
339 -1, &poisql.delete_cat, NULL);
340 /* enable category */
341 sqlite3_prepare_v2(db,
342 "update category set enabled = ?"
343 " where cat_id = ?", -1, &poisql.toggle_cat, NULL);
344 /* select all category */
345 sqlite3_prepare_v2(db,
346 "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
349 " left outer join poi p on c.cat_id = p.cat_id"
350 " group by c.cat_id, c.label, c.desc, c.enabled "
351 " order by c.priority,c.label", -1, &poisql.selall_cat, NULL);
353 /* Select quick categories */
354 sqlite3_prepare_v2(db,
355 "select c.cat_id, c.label, c.icon, c.color"
356 " from category c where c.enabled=1 "
357 " order by c.priority,c.label limit 9", -1, &poisql.select_quick_cat, NULL);
363 poi_icon_hash_clear(void)
365 #if (GLIB_CHECK_VERSION (2, 12, 0))
366 g_hash_table_remove_all(poi_icon_hash);
368 g_hash_table_foreach_remove(poi_icon_hash, gtk_true, NULL);
373 poi_deinit(sqlite3 *db)
376 g_hash_table_destroy(poi_icon_hash);
380 sqlite3_finalize(poisql.select_quick_cat);
381 sqlite3_finalize(poisql.selall_cat);
382 sqlite3_finalize(poisql.toggle_cat);
383 sqlite3_finalize(poisql.delete_cat);
384 sqlite3_finalize(poisql.update_cat);
385 sqlite3_finalize(poisql.insert_cat);
386 sqlite3_finalize(poisql.select_cat);
387 sqlite3_finalize(poisql.insert_poi);
388 sqlite3_finalize(poisql.update_poi);
389 sqlite3_finalize(poisql.delete_poi);
390 sqlite3_finalize(poisql.delete_poi_by_catid);
391 sqlite3_finalize(poisql.select_nearest_poi);
392 sqlite3_finalize(poisql.select_poi);
393 sqlite3_finalize(poisql.select_poi_search);
394 sqlite3_finalize(poisql.select_poi_search_cat);
398 poi_init(sqlite3 **db)
401 poi_icon_hash=g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
407 poi_db_create(poidb);
408 if (poi_db_prepare(poidb)==FALSE) {
409 g_printerr("Failed to prepare POI SQL statements:");
410 g_printf("SQLITE: %s\n", sqlite3_errmsg(poidb));
421 p=g_slice_new0(poi_info);
422 p->source=POI_SOURCE_USER;
423 /* XXX: Set defaults ? */
428 poi_free(poi_info *p)
437 g_free(p->postal_code);
439 g_free(p->cat_label);
442 g_slice_free(poi_info, p);
445 static GtkListStore *
446 poi_list_store_new(void) {
447 return gtk_list_store_new(ITEM_NUM_COLUMNS,
448 G_TYPE_INT, /* POI ID */
449 G_TYPE_INT, /* Category ID */
450 G_TYPE_DOUBLE, /* Latitude */
451 G_TYPE_DOUBLE, /* Longitude */
452 G_TYPE_DOUBLE, /* Dist */
453 G_TYPE_STRING, /* Lat/Lon */
454 G_TYPE_STRING, /* Label */
455 G_TYPE_STRING, /* Desc. */
456 G_TYPE_STRING, /* Category Label */
457 G_TYPE_STRING, /* Icon */
458 G_TYPE_STRING); /* Color */
461 /*************************************
462 * POI Category functions
467 poi_category_new(void)
469 return g_slice_new0(poi_category);
473 poi_category_free(poi_category *c)
479 g_slice_free(poi_category, c);
483 poi_category_toggle(guint cat_id, gboolean cat_enabled)
485 g_return_val_if_fail(poisql.toggle_cat, FALSE);
487 if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
488 SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
489 SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
496 poi_category_get(guint cat_id, poi_category **c)
500 g_return_val_if_fail(poisql.select_cat, FALSE);
501 if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id) || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
502 sqlite3_reset(poisql.select_cat);
506 cc=poi_category_new();
508 cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
509 cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
510 cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
512 sqlite3_reset(poisql.select_cat);
513 sqlite3_clear_bindings(poisql.select_cat);
519 poi_category_update(guint cat_id, poi_category *c)
521 gboolean results=TRUE;
526 g_return_val_if_fail(poisql.update_cat, FALSE);
527 g_return_val_if_fail(poisql.insert_cat, FALSE);
531 if (SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, SQLITE_STATIC)
532 || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, SQLITE_STATIC)
533 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
534 || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
535 || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
538 sqlite3_reset(poisql.update_cat);
539 sqlite3_clear_bindings(poisql.update_cat);
542 if (SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, SQLITE_STATIC)
543 || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, SQLITE_STATIC)
544 || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
545 || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
548 sqlite3_reset(poisql.insert_cat);
549 sqlite3_clear_bindings(poisql.insert_cat);
555 poi_category_delete(guint id)
560 g_return_val_if_fail(poisql.delete_poi_by_catid, FALSE);
561 g_return_val_if_fail(poisql.delete_cat, FALSE);
563 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
564 sqlite3_reset(poisql.delete_poi_by_catid);
567 sqlite3_reset(poisql.delete_poi_by_catid);
568 sqlite3_clear_bindings(poisql.delete_poi_by_catid);
570 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
571 sqlite3_reset(poisql.delete_cat);
574 sqlite3_reset(poisql.delete_cat);
575 sqlite3_clear_bindings(poisql.delete_cat);
586 g_return_val_if_fail(poisql.delete_poi, FALSE);
588 if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
589 sqlite3_reset(poisql.delete_poi);
592 sqlite3_reset(poisql.delete_poi);
593 sqlite3_clear_bindings(poisql.delete_poi);
598 poi_search(poi_search_type pst, gdouble lat, gdouble lon, gchar *text, guint cat, GtkListStore **store)
601 sqlite3_stmt *sql=NULL;
604 gchar tmp1[16], tmp2[16];
610 g_return_val_if_fail(poisql.select_poi, FALSE);
612 g_printf("POI Search: [%s] around %.6f %.6f (%d %d)\n", text, lat, lon, cat, pst);
615 case POI_SEARCH_NEAR:
616 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat-0.5) ||
617 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon+0.5)) {
618 g_printerr("Failed to bind values for poisql.select_poi\n");
619 sqlite3_clear_bindings(poisql.select_poi);
622 sql=poisql.select_poi;
624 case POI_SEARCH_TEXT:
625 ltext=g_strdup_printf("%s%%", text);
627 if (SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search, 5, ltext, -1, SQLITE_TRANSIENT)) {
628 g_printerr("Failed to bind values for poisql.select_poi_search\n");
629 sqlite3_clear_bindings(poisql.select_poi_search);
634 sql=poisql.select_poi_search;
636 case POI_SEARCH_TEXT_CAT:
637 ltext=g_strdup_printf("%s%%", text);
639 if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_search_cat, 6, cat) ||
640 SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search_cat, 5, ltext, -1, SQLITE_TRANSIENT)) {
641 g_printerr("Failed to bind values for poisql.select_poi_search_cat\n");
642 sqlite3_clear_bindings(poisql.select_poi_search_cat);
647 sql=poisql.select_poi_search_cat;
650 if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_by_cat, 5, cat)) {
651 g_printerr("Failed to bind values for poisql.select_poi_by_cat\n");
652 sqlite3_clear_bindings(poisql.select_poi_by_cat);
655 sql=poisql.select_poi_by_cat;
658 g_assert_not_reached();
663 /* XXX: Use common bind for common variables */
664 if (SQLITE_OK != sqlite3_bind_double(sql, 1, lat-range) ||
665 SQLITE_OK != sqlite3_bind_double(sql, 2, lat+range) ||
666 SQLITE_OK != sqlite3_bind_double(sql, 3, lon-range) ||
667 SQLITE_OK != sqlite3_bind_double(sql, 4, lon+range)) {
668 g_printerr("Failed to bind common variables for POI search\n");
669 sqlite3_clear_bindings(sql);
673 *store = poi_list_store_new();
675 while (SQLITE_ROW == sqlite3_step(sql)) {
676 gdouble rlat, rlon, dist;
678 rlat=sqlite3_column_double(sql, 0);
679 rlon=sqlite3_column_double(sql, 1);
680 lat_format(_degformat, rlat, tmp1);
681 lon_format(_degformat, rlon, tmp2);
682 dist=calculate_distance(lat, lon, rlat, rlon) * UNITS_CONVERT[_units];
684 gtk_list_store_append(*store, &iter);
685 gtk_list_store_set(*store, &iter,
686 ITEM_ID, sqlite3_column_int(sql, 2),
687 ITEM_CATID, sqlite3_column_int(sql, 5),
691 ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
692 ITEM_LABEL, sqlite3_column_text(sql, 3),
693 ITEM_DESC, sqlite3_column_text(sql, 4),
694 ITEM_CATLAB, sqlite3_column_text(sql, 6),
699 g_printf("Found: %d items\n", rows);
702 sqlite3_clear_bindings(sql);
708 poi_get_list_inside(gdouble lat1, gdouble lon1, gdouble lat2, gdouble lon2, GtkListStore **store, guint *num_poi)
712 gchar tmp1[16], tmp2[16];
719 g_return_val_if_fail(poisql.select_poi, FALSE);
725 if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
726 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
727 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
728 SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
729 g_printerr("Failed to bind values for poisql.select_poi\n");
733 *store = poi_list_store_new();
735 while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
736 gdouble lat, lon, dist=0;
738 lat=sqlite3_column_double(poisql.select_poi, 0);
739 lon=sqlite3_column_double(poisql.select_poi, 1);
740 lat_format(_degformat, lat, tmp1);
741 lon_format(_degformat, lon, tmp2);
743 gtk_list_store_append(*store, &iter);
744 gtk_list_store_set(*store, &iter,
745 ITEM_ID, sqlite3_column_int(poisql.select_poi, 2),
746 ITEM_CATID, sqlite3_column_int(poisql.select_poi, 5),
750 ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
751 ITEM_LABEL, sqlite3_column_text(poisql.select_poi, 3),
752 ITEM_DESC, sqlite3_column_text(poisql.select_poi, 4),
753 ITEM_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
754 ITEM_ICON, sqlite3_column_text(poisql.select_poi, 8),
755 ITEM_COLOR, sqlite3_column_text(poisql.select_poi, 9),
759 sqlite3_reset(poisql.select_poi);
760 sqlite3_clear_bindings(poisql.select_poi);
766 poi_get_list_near_unit(guint unitx, guint unity, guint range, GtkListStore **store, guint *num_poi)
768 gdouble lat1, lon1, lat2, lon2;
771 x=unitx-pixel2unit(3*range);
772 y=unity+pixel2unit(3*range);
773 unit2latlon(x, y, lat1, lon1);
775 x=unitx+pixel2unit(3*range);
776 y=unity-pixel2unit(3*range);
777 unit2latlon(x, y, lat2, lon2);
779 return poi_get_list_inside(lat1, lon1, lat2, lon2, store, num_poi);
783 poi_get_by_id(guint id)
787 g_return_val_if_fail(poisql.select_poi_by_id, FALSE);
789 if (SQLITE_OK!=sqlite3_bind_double(poisql.select_poi_by_id, 1, id))
792 if (SQLITE_ROW==sqlite3_step(poisql.select_poi_by_id)) {
794 p->poi_id=sqlite3_column_int(poisql.select_poi_by_id, PS_ID);
795 p->lat=sqlite3_column_double(poisql.select_poi_by_id, PS_LAT);
796 p->lon=sqlite3_column_double(poisql.select_poi_by_id, PS_LON);
797 p->source=sqlite3_column_int(poisql.select_poi_by_id, PS_SOURCE);
798 p->public=sqlite3_column_int(poisql.select_poi_by_id, PS_PUBLIC)==1 ? TRUE : FALSE;
799 p->label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_LABEL));
800 p->desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_DESC));
801 p->url=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_URL));
802 p->postal_code=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_POSTAL_CODE));
804 p->cat_id=sqlite3_column_int(poisql.select_poi_by_id, PS_CAT_ID);
805 p->cat_label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_LABEL));
806 p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_DESC));
809 sqlite3_reset(poisql.select_poi_by_id);
810 sqlite3_clear_bindings(poisql.select_poi_by_id);
816 poi_update(poi_info *p)
821 g_return_val_if_fail(poisql.update_poi, FALSE);
826 if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, p->label, -1, SQLITE_STATIC)
827 || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, p->desc, -1, SQLITE_STATIC)
828 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, p->cat_id)
829 || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, p->poi_id)
830 || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
833 sqlite3_reset(poisql.update_poi);
834 sqlite3_clear_bindings(poisql.update_poi);
838 /* XXX: Add url and postal_code */
847 g_return_val_if_fail(poisql.insert_poi, FALSE);
854 if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, p->lat)
855 || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, p->lon)
856 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, p->label, -1, SQLITE_STATIC)
857 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, p->desc, -1, SQLITE_STATIC)
858 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 5, p->url, -1, SQLITE_STATIC)
859 || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 6, p->postal_code, -1, SQLITE_STATIC)
860 || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 7, p->cat_id)
861 || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 8, t)
862 || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 9, p->source)
863 || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
864 sqlite3_reset(poisql.insert_poi);
865 sqlite3_clear_bindings(poisql.insert_poi);
868 sqlite3_reset(poisql.insert_poi);
869 sqlite3_clear_bindings(poisql.insert_poi);
874 poi_find_nearest(gdouble lat, gdouble lon)
881 g_return_val_if_fail(poisql.select_nearest_poi, FALSE);
883 sqlite3_reset(poisql.select_nearest_poi);
884 sqlite3_clear_bindings(poisql.select_nearest_poi);
886 if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
887 && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
888 && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
891 p->lat=sqlite3_column_double(poisql.select_nearest_poi, 0);
892 p->lon=sqlite3_column_double(poisql.select_nearest_poi, 1);
893 p->poi_id=sqlite3_column_double(poisql.select_nearest_poi, 2);
894 p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
895 p->desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 4));
896 p->cat_id=sqlite3_column_double(poisql.select_nearest_poi, 5);
897 p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 6));
898 sqlite3_reset(poisql.select_nearest_poi);
899 sqlite3_clear_bindings(poisql.select_nearest_poi);
906 poi_get_icon(gchar *icon, gboolean big)
912 GError *error = NULL;
920 g_snprintf(buffer, sizeof(buffer), "%s/%s.%s/%s.png",
921 theme_base, theme, (big==TRUE) ? "big" : "small", icon);
923 pixbuf=g_hash_table_lookup(poi_icon_hash, buffer);
930 pixbuf=gdk_pixbuf_new_from_file(buffer, &error);
935 g_hash_table_insert(poi_icon_hash, g_strdup(buffer), pixbuf);
940 poi_category_generate_store(void)
948 g_return_val_if_fail(poisql.selall_cat, FALSE);
950 store=gtk_list_store_new(CAT_NUM_COLUMNS, /* pixbuf */
958 while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
959 gtk_list_store_append(store, &iter);
960 gtk_list_store_set(store, &iter,
961 CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
962 CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
963 CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
964 CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
965 CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6),
966 CAT_ICON, poi_get_icon(sqlite3_column_text(poisql.selall_cat, 4),TRUE),
970 sqlite3_reset(poisql.selall_cat);
971 sqlite3_clear_bindings(poisql.selall_cat);