#include <config.h>
-#define _GNU_SOURCE
-
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include <fcntl.h>
#include <gconf/gconf-client.h>
#include <libxml/parser.h>
-
#include <libintl.h>
#include <locale.h>
-
#include <sqlite3.h>
#include "utils.h"
#include "settings.h"
#include "db.h"
#include "osm.h"
+#include "osm-db.h"
+#include "latlon.h"
+#include "image-cache.h"
-struct sql_poi_stmt {
- sqlite3_stmt *_stmt_select_poi;
- sqlite3_stmt *_stmt_select_nearest_poi;
- sqlite3_stmt *_stmt_insert_poi;
- sqlite3_stmt *_stmt_update_poi;
- sqlite3_stmt *_stmt_delete_poi;
- sqlite3_stmt *_stmt_delete_poi_by_catid;
- sqlite3_stmt *_stmt_nextlabel_poi;
- sqlite3_stmt *_stmt_select_cat;
- sqlite3_stmt *_stmt_insert_cat;
- sqlite3_stmt *_stmt_update_cat;
- sqlite3_stmt *_stmt_delete_cat;
- sqlite3_stmt *_stmt_toggle_cat;
- sqlite3_stmt *_stmt_selall_cat;
-};
-static struct sql_poi_stmt poisql;
-
-sqlite3_stmt *_stmt_select_poi = NULL;
-sqlite3_stmt *_stmt_select_nearest_poi = NULL;
-sqlite3_stmt *_stmt_insert_poi = NULL;
-sqlite3_stmt *_stmt_update_poi = NULL;
-sqlite3_stmt *_stmt_delete_poi = NULL;
-sqlite3_stmt *_stmt_delete_poi_by_catid = NULL;
-sqlite3_stmt *_stmt_nextlabel_poi = NULL;
-
-sqlite3_stmt *_stmt_select_cat = NULL;
-sqlite3_stmt *_stmt_insert_cat = NULL;
-sqlite3_stmt *_stmt_update_cat = NULL;
-sqlite3_stmt *_stmt_delete_cat = NULL;
-sqlite3_stmt *_stmt_toggle_cat = NULL;
-sqlite3_stmt *_stmt_selall_cat = NULL;
+static sqlite3 *poidb;
+
+/* POI Icon theme. "classic" or "square". Should be made into a configuration option */
+static gchar *theme="square";
+static gchar *theme_base=DATADIR "/icons/map-icons";
+
+static ImageCache *poi_ic=NULL;
struct _poi_categories {
node_type_t type;
- gchar *name, *desc;
+ const gchar *name, *desc, *icon, *color;
};
+
+/* The default POI categories */
static struct _poi_categories default_poi_categories[] = {
- {NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." },
- {NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." },
- {NODE_AMENITY_HOSPITAL, "Hospital", "" },
- {NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." },
- {NODE_AMENITY_POLICE, "Police", "Police station"},
- {NODE_AMENITY_HOTEL, "Hotel", "Places to stay temporarily or for the night."},
- {NODE_AMENITY_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap."},
- {NODE_AMENITY_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap."},
- {NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint."},
- {NODE_AMENITY_BANK, "Bank", "Place to take care of your money."},
- {NODE_AMENITY_POST, "Post office", "Place to handle mail."},
- {NODE_AMENITY_POST_BOX, "Post box", "Send letters."},
- {NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here."},
- {NODE_AMENITY_RAILWAY_STATION, "Railway station", "Transportation by train."},
- {NODE_AMENITY_RAILWAY_HALT, "Railway halt", ""},
- {NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus."},
- {NODE_AMENITY_BOAT, "Harbour", "Transportation by boat."},
- {NODE_AMENITY_AIRPORT, "Airport", "Transportation by air."},
- {NODE_AMENITY_FOOD, "Restaurant, Fast food", "Places to eat or drink."},
- {NODE_AMENITY_PUB, "Pub, Disco, Club", "Place to drink and party."},
- {NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat."},
- {NODE_AMENITY_CINEMA, "Cinema", "Place to see movies"},
- {NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing"},
- {NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services."},
- {NODE_AMENITY_POW, "Place of Worchip", ""},
- {NODE_AMENITY_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters."},
- {NODE_AMENITY_COLLEGE, "College Campus/Building", ""},
- {NODE_AMENITY_SCHOOL, "School", ""},
- {NODE_AMENITY_WC, "WC/Toilets", ""},
- {NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone"},
- {NODE_AMENITY_ATTRACTION, "Attraction", "Something interesting"},
- {NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else."},
- {NODE_POI_END, NULL, NULL }
+ { NODE_AMENITY_PARKING, "Parking", "Parking place for vehicles." , "vehicle/parking", "#2020ff" },
+ { NODE_AMENITY_FUEL, "Fuel", "Stations for purchasing fuel for vehicles." , "vehicle/fuel_station", "#4040f0" },
+ { NODE_AMENITY_SPEEDCAM, "Speed Cam", "Automatic speed cameras." , "vehicle/restrictions/speed_trap", "#ff0000" },
+ { NODE_AMENITY_HOSPITAL, "Hospital", "Get medical help" , "health/hospital", "#ff4040" },
+ { NODE_AMENITY_PHARMACY, "Pharmacy", "Place to get drugs." , "health/pharmacy", "#40f040" },
+ { NODE_AMENITY_POLICE, "Police", "Police station" , "public/police", "#8570ff" },
+ { NODE_TOURISM_HOTEL, "Hotel", "Places to stay temporarily or for the night." , "accommodation/hotel", "#ba20ba" },
+ { NODE_TOURISM_HOSTEL, "Hostel", "Places to stay temporarily or for the night. Cheap." , "accommodation/hostel", "#ba30ba" },
+ { NODE_TOURISM_MOTEL, "Motel", "Places to stay temporarily or for the night. Cheap." , "accommodation/motel", "#ba40ba" },
+ { NODE_AMENITY_ATM, "ATM", "Automatic Teller Machine/Cashpoint." , "money/atm", "#40a040" },
+ { NODE_AMENITY_BANK, "Bank", "Place to take care of your money." , "money/bank", "#50b550" },
+ { NODE_AMENITY_POST, "Post office", "Place to handle mail." , "public/post_office", "#ff6868" },
+ { NODE_AMENITY_POST_BOX, "Post box", "Send letters." , "public/post_box", "#ff6060" },
+ { NODE_TOURISM_INFO, "Tourism info", "A place for tourists to get information." , "misc/information", "#4af04a" },
+ { NODE_AMENITY_TAXI, "Taxi station", "Get a Taxi here." , "transport/taxi", "#50ffff" },
+ { NODE_RAILWAY_STATION, "Railway station", "Transportation by train." , "transport/railway", "#fa7070" },
+ { NODE_RAILWAY_HALT, "Railway halt", "" , "transport/railway_small", "#f27777" },
+ { NODE_AMENITY_BUS_STATION, "Bus station", "Transportation by bus." , "transport/bus", "#f07070" },
+ { NODE_AMENITY_BOAT, "Harbour", "Transportation by boat." , "transport/ferry", "#9090ee" },
+ { NODE_AIRPORT_TERMINAL, "Airport", "Transportation by air." , "transport/airport", "#909099" },
+ { NODE_TOURISM_CAMP_SITE, "Camp site", "Place to go camping" , "accommodation/camping", "#90f080" },
+ { NODE_TOURISM_CARAVAN_SITE, "Caravan site", "" , "accommodation/camping/caravan", "#90ff80" },
+ { NODE_TOURISM_PICNIC_SITE, "Picnic", "Place to have a Picnic" , "recreation/picnic", "#a5f8e5" },
+ { NODE_AMENITY_FOOD, "Fast food", "Places to eat or drink." , "food/fastfood", "#e5960d" },
+ { NODE_AMENITY_RESTAURANT, "Restaurant", "Fancy places to eat or drink." , "food/restaurant", "#e5960d" },
+ { NODE_AMENITY_PUB, "Pub", "Place to drink." , "food/pub", "#f5960d" },
+ { NODE_AMENITY_NIGHTCLUB, "Disco, Club", "Place to drink, party and dance." , "food/bar", "#f59c1d" },
+ { NODE_AMENITY_CAFE, "Cafe", "Place to drink coffe or tee and eat." , "food/cafe", "#ff960d" },
+ { NODE_AMENITY_CINEMA, "Cinema", "Place to see movies" , "recreation/cinema", "#9090a0" },
+ { NODE_AMENITY_THEATRE, "Theatre", "Place to see people performing" , "recreation/theater", "#9595a5" },
+ { NODE_AMENITY_SHOP, "Shopping", "Places to shop or acquire services." , "shopping", "#61ef1b" },
+ { NODE_AMENITY_SHOP_ADULT, "Adult shop", "Shops providing adult items, such as sex toys,pornography and fetish clothing" , "", "#ff0000" },
+ { NODE_AMENITY_POW, "Place of Worchip", "" , "religion/church", "#7c5b0b" },
+ { NODE_TOURISM_THEME_PARK, "Theme Park", "Place to have fun and ride for example rollercoasters." , "recreation/theme_park", "#8eef1b" },
+ { NODE_AMENITY_COLLEGE, "College Campus/Building", "" , "education/college", "#813fdc" },
+ { NODE_AMENITY_SCHOOL, "School", "" , "education/school", "#813fdc" },
+ { NODE_AMENITY_LIBRARY, "Library", "Place to read and borrow books and magazines" , "shopping/rental/library", "#803090" },
+ { NODE_AMENITY_TOWNHALL, "Townhall", "" , "", "#408090" },
+ { NODE_AMENITY_WC, "WC/Toilets", "" , "public/toilets", "#e1d62c" },
+ { NODE_AMENITY_TELEPHONE, "Telephone", "Public telephone" , "public/telephone", "#208060" },
+ { NODE_TOURISM_ATTRACTION, "Attraction", "Something interesting" , "misc", "#005000" },
+
+ { NODE_HISTORIC_MUSEUM, "Museum", "A place where objects of historical, artistic, or scientific interest are exhibited, preserved or studied." , "sightseeing/museum", "#202020" },
+ { 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" },
+
+ { NODE_SPORT_CENTER, "Sport Center", "" , "sports/centre", "#101080" },
+ { NODE_SPORT_STADIUM, "Sport Stadium", "" , "sports/stadium", "#101080" },
+ { NODE_SPORT_SKIING, "Skiing", "" , "sports/skiing", "#5050A0" },
+ { NODE_SPORT_SWIMMING, "Swimming", "" , "sports/swimming", "#102080" },
+ { NODE_SPORT_FOOTBALL, "Football", "" , "sports/football", "#102080" },
+ { NODE_SPORT_SOCCER, "Soccer", "" , "sports/soccer", "#102080" },
+ { NODE_SPORT_GOLF, "Golf", "" , "sports/golf", "#102080" },
+ { NODE_SPORT_TENNIS, "Tennis", "" , "sports/tennis", "#101080" },
+ { NODE_SPORT_BOWLING, "Bowling", "" , "sports", "#101080" },
+ { NODE_SPORT_RUGBY, "Rugby", "" , "sports", "#101080" },
+ { NODE_SPORT_CLIMBING, "Climbing", "" , "sports", "#101080" },
+ { NODE_SPORT_CYCLING, "Cycling", "" , "sports/bicycle", "#101080" },
+ { NODE_SPORT_MOTOR, "Motor sport", "" , "sports", "#101080" },
+ { NODE_SPORT_HOCKEY, "Hockey", "" , "sports", "#5050A0" },
+ { NODE_SPORT_SKATING, "Skating", "" , "sports", "#5050A0" },
+ { NODE_SPORT_SKATEBOARD, "Skateboard", "" , "sports", "#101080" },
+ { NODE_SPORT_HORSES, "Horses", "Horse riding or racing" , "sports/riding", "#101080" },
+ { NODE_SPORT_DOG, "Dog racing", "" , "sports", "#101080" },
+ { NODE_SPORT_BASKETBALL, "Basketball", "" , "sports", "#101080" },
+ { NODE_SPORT_BASEBALL, "Baseball", "" , "sports", "#101080" },
+ { NODE_SPORT_CANOE, "Canoe", "" , "", "#101080" },
+ { NODE_SPORT_CROQUET, "Croquet", "" , "sports", "#101080" },
+ { NODE_SPORT_CRICKET, "Cricket", "" , "sports", "#101080" },
+ { NODE_SPORT_SHOOTING, "Shooting", "Shooting range" , "sports", "#101080" },
+ { NODE_SPORT_PAINTBALL, "Paintball", "Run around and shoot people with paintballs" , "sports", "#101080" },
+ { NODE_SPORT_TABLE_TENNIS, "Table tennis", "" , "sports", "#101080" },
+ { NODE_SPORT_PELOTA, "Pelota", "" , "sports", "#101080" },
+ { NODE_SPORT_RACQUET, "Racquet", "" , "sports/racquetball", "#101080" },
+ { NODE_SPORT_BOWLS, "Lawn Bowls", "" , "sports", "#101080" },
+ { NODE_SPORT_ATHLETICS, "Athletics", "" , "sports", "#101080" },
+ { NODE_SPORT_OTHER, "Other Sports", "" , "sports/stadium", "#101077" },
+
+ { NODE_AMENITY_GENERIC, "Other", "Miscellaneous category for everything else." , "misc", "#002000" },
+ { NODE_POI_END, NULL, NULL }
};
-gboolean
-poi_cb_populate_categories(sqlite3 *db)
+static gboolean
+poi_populate_categories(sqlite3 *db)
{
sqlite3_stmt *sql_cat;
-gint i;
+guint i;
-g_printf("Checking default categories\n");
-sqlite3_prepare_v2(db,"insert or replace into category (cat_id, label, desc, enabled, priority)"
- " values (?, ?, ?, 1, ?)",
- -1, &sql_cat, NULL);
+DB_PREP(db,"insert or replace into category (cat_id, label, desc, enabled, priority, icon, color)"
+ " values (?, ?, ?, 1, ?, ?, ?)", sql_cat);
for (i=0; default_poi_categories[i].name; i++) {
sqlite3_bind_int(sql_cat, 1, default_poi_categories[i].type);
- sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name , -1, SQLITE_TRANSIENT);
- sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc , -1, SQLITE_TRANSIENT);
+ sqlite3_bind_text(sql_cat, 2, default_poi_categories[i].name, -1, SQLITE_STATIC);
+ sqlite3_bind_text(sql_cat, 3, default_poi_categories[i].desc, -1, SQLITE_STATIC);
sqlite3_bind_int(sql_cat, 4, default_poi_categories[i].type/100);
- sqlite3_step(sql_cat);
+ sqlite3_bind_text(sql_cat, 5, default_poi_categories[i].icon, -1, SQLITE_STATIC);
+ sqlite3_bind_text(sql_cat, 6, default_poi_categories[i].color, -1, SQLITE_STATIC);
+ if (sqlite3_step(sql_cat)==SQLITE_OK)
+ g_printf("Failed to update category: %d [%s]\n", i, default_poi_categories[i].name);
sqlite3_reset(sql_cat);
sqlite3_clear_bindings(sql_cat);
}
return TRUE;
}
+const gchar *
+poi_get_icon_from_type(node_type_t t)
+{
+guint i;
+
+for (i=0; default_poi_categories[i].name; i++) {
+ if (t==default_poi_categories[i].type)
+ return default_poi_categories[i].icon;
+}
+return NULL;
+}
+
gboolean
poi_db_create(sqlite3 *db)
{
- gchar **pszResult;
- guint nRow, nColumn;
- if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
- &pszResult, &nRow, &nColumn, NULL)) {
- gchar *create_sql;
-
- g_printf("Creating initial tables\n");
-
- create_sql = sqlite3_mprintf
- ("create table poi "
- "(poi_id integer PRIMARY KEY, "
- "lat real, "
- "lon real, "
- "elev real, "
- "label text, "
- "desc text, "
- "public integer, "
- "source integer default 0, "
- "osm_id integer default 0, "
- "priority integer default 2, "
- "cat_id integer);"
- "create table category (cat_id integer PRIMARY KEY,"
- "label text, "
- "desc text, "
- "icon text, "
- "color char(7), "
- "priority integer default 2, "
- "enabled integer);");
-
- if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
- && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
- &pszResult, &nRow, &nColumn, NULL))) {
- g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
- sqlite3_close(db);
- db = NULL;
- return FALSE;
- }
- } else
- sqlite3_free_table(pszResult);
+gchar **pszResult;
+guint nRow, nColumn;
+
+if (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1", &pszResult, &nRow, &nColumn, NULL)) {
+ gchar *create_sql;
+
+ g_printerr("Creating initial POI tables\n");
+
+ create_sql = sqlite3_mprintf
+ ("create table poi "
+ "(poi_id INTEGER PRIMARY KEY AUTOINCREMENT, "
+ "lat real, "
+ "lon real, "
+ "elev real, "
+ "label text, "
+ "desc text, "
+ "url text, "
+ "postal_code text,"
+ "public int default 1, "
+ "source int default 0, "
+ "cat_id int default 900,"
+ "osm_id int default 0, "
+ "isin_c int default 0, "
+ "isin_p int default 0, "
+ "priority int default 2, "
+ "addtime int);"
+ "create table category (cat_id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "label text, "
+ "desc text, "
+ "icon text, "
+ "color char(7), "
+ "priority int default 2, "
+ "enabled int);");
+
+ if (SQLITE_OK != sqlite3_exec(db, create_sql, NULL, NULL, NULL)
+ && (SQLITE_OK != sqlite3_get_table(db, "select label from poi limit 1",
+ &pszResult, &nRow, &nColumn, NULL))) {
+ g_printf("%s:\n%s",_("Failed to open or create database"),sqlite3_errmsg(db));
+ sqlite3_close(db);
+ db = NULL;
+ return FALSE;
+ }
+} else {
+ sqlite3_free_table(pszResult);
+}
- /* Make sure default categories exists */
- poi_cb_populate_categories(db);
- return TRUE;
+/* Make sure default categories exists */
+poi_populate_categories(db);
+return TRUE;
}
+typedef enum {
+ PS_LAT=0,
+ PS_LON,
+ PS_ID,
+ PS_LABEL,
+ PS_DESC,
+ PS_CAT_ID,
+ PS_CAT_LABEL,
+ PS_CAT_DESC,
+ PS_CAT_ICON,
+ PS_CAT_COLOR,
+ PS_SOURCE,
+ PS_PUBLIC,
+ PS_URL,
+ PS_POSTAL_CODE,
+} poi_sql_column;
+
+#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"
+#define POI_MINI_SQL_FIELDS "p.lat, p.lon, p.poi_id, p.label, p.desc, p.cat_id, c.label, c.desc"
+
gboolean
poi_db_prepare(sqlite3 *db)
{
- /* select from poi */
- sqlite3_prepare_v2(db,
- "select p.lat, p.lon, p.poi_id, p.label, p.desc,"
- " p.cat_id, c.label, c.desc, c.icon, c.color"
- " from poi p, category c "
- " where p.lat between ? and ? "
- " and p.lon between ? and ? "
- " and c.enabled = 1 and p.cat_id = c.cat_id",
- -1, &_stmt_select_poi, NULL);
-
- /* select nearest pois */
- sqlite3_prepare_v2(db,
- "select p.lat, p.lon, p.label, c.label, c.icon, c.color"
- " from poi p, category c "
- " where c.enabled = 1 and p.cat_id = c.cat_id "
- " and p.lat between $LAT-0.10 and $LAT+0.10 "
- " and p.lon between $LON-0.10 and $LAT+0.10 "
- " order by (($LAT - p.lat) * ($LAT - p.lat) "
- "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
- -1, &_stmt_select_nearest_poi, NULL);
-
- /* insert poi */
- sqlite3_prepare_v2(db,
- "insert into poi (lat, lon, label, desc, cat_id, public)"
- " values (?, ?, ?, ?, ?, 1)", -1, &_stmt_insert_poi, NULL);
- /* update poi */
- sqlite3_prepare_v2(db, "update poi set label = ?, desc = ?, "
- "cat_id = ? where poi_id = ?",
- -1, &_stmt_update_poi, NULL);
- /* delete from poi */
- sqlite3_prepare_v2(db, "delete from poi where poi_id = ?",
- -1, &_stmt_delete_poi, NULL);
- /* delete from poi by cat_id */
- sqlite3_prepare_v2(db, "delete from poi where cat_id = ?",
- -1, &_stmt_delete_poi_by_catid, NULL);
- /* get next poilabel */
- sqlite3_prepare_v2(db, "select ifnull(max(poi_id) + 1,1) from poi",
- -1, &_stmt_nextlabel_poi, NULL);
-
- /* select from category */
- sqlite3_prepare_v2(db,
- "select c.label, c.desc, c.enabled"
- " from category c where c.cat_id = ?",
- -1, &_stmt_select_cat, NULL);
- /* insert into category */
- sqlite3_prepare_v2(db,
- "insert into category (label, desc, enabled)"
- " values (?, ?, ?)", -1, &_stmt_insert_cat, NULL);
- /* update category */
- sqlite3_prepare_v2(db,
- "update category set label = ?, desc = ?,"
- " enabled = ? where poi_id = ?",
- -1, &_stmt_update_cat, NULL);
- /* delete from category */
- sqlite3_prepare_v2(db,"delete from category where cat_id = ?",
- -1, &_stmt_delete_cat, NULL);
- /* enable category */
- sqlite3_prepare_v2(db,
- "update category set enabled = ?"
- " where cat_id = ?", -1, &_stmt_toggle_cat, NULL);
- /* select all category */
- sqlite3_prepare_v2(db,
- "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
- " count(p.poi_id)"
- " from category c"
- " left outer join poi p on c.cat_id = p.cat_id"
- " group by c.cat_id, c.label, c.desc, c.enabled "
- " order by c.priority,c.label", -1, &_stmt_selall_cat, NULL);
-
- return TRUE;
+/* Select POIs inside given minmax lat,lon */
+DB_PREP(db, "select "
+ POI_BASE_SQL_FIELDS
+ " from poi p, category c "
+ " where p.lat between ? and ? "
+ " and p.lon between ? and ? "
+ " and c.enabled=1 and p.cat_id=c.cat_id order by c.priority limit 500",
+ poisql.select_poi);
+
+/* Get POI with given ID */
+DB_PREP(db, "select "
+ POI_BASE_SQL_FIELDS
+ " from poi p, category c "
+ " where p.poi_id = ? "
+ " and p.cat_id=c.cat_id",
+ poisql.select_poi_by_id);
+
+/* Search POIs by label and any category */
+DB_PREP(db, "select "
+ POI_BASE_SQL_FIELDS
+ " from poi p, category c "
+ " where p.lat between ? and ? "
+ " and p.lon between ? and ? "
+ " 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",
+ poisql.select_poi_search);
+
+/* Search POI by label and category */
+DB_PREP(db, "select "
+ POI_BASE_SQL_FIELDS
+ " from poi p, category c "
+ " where p.lat between ? and ? "
+ " and p.lon between ? and ? "
+ " 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",
+ poisql.select_poi_search_cat);
+
+/* Search POIs by category */
+DB_PREP(db, "select "
+ POI_BASE_SQL_FIELDS
+ " from poi p, category c "
+ " where p.lat between ? and ? "
+ " and p.lon between ? and ? "
+ " and c.enabled=1 and p.cat_id=c.cat_id and c.cat_id=? order by p.label",
+ poisql.select_poi_by_cat);
+
+/* Select any nearest POI */
+DB_PREP(db, "select "
+ POI_MINI_SQL_FIELDS
+ " from poi p, category c "
+ " where c.enabled = 1 and p.cat_id = c.cat_id "
+ " and p.lat between $LAT-0.10 and $LAT+0.10 "
+ " and p.lon between $LON-0.10 and $LAT+0.10 "
+ " order by (($LAT - p.lat) * ($LAT - p.lat) "
+ "+ ($LON - p.lon) * ($LON - p.lon)) limit 1",
+ poisql.select_nearest_poi);
+
+/* Insert POI */
+DB_PREP(db, "insert into poi (lat, lon, label, desc, url, postal_code, cat_id, addtime, public, source)"
+ " values (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)", poisql.insert_poi);
+/* update poi */
+DB_PREP(db, "update poi set label=?, desc=?, cat_id=? where poi_id=?", poisql.update_poi);
+/* delete from poi */
+DB_PREP(db, "delete from poi where poi_id=?", poisql.delete_poi);
+/* delete from poi by cat_id */
+DB_PREP(db, "delete from poi where cat_id=?", poisql.delete_poi_by_catid);
+
+/* select from category */
+DB_PREP(db, "select c.label, c.desc, c.enabled from category c where c.cat_id = ?", poisql.select_cat);
+/* insert into category */
+DB_PREP(db, "insert into category (label, desc, enabled) values (?, ?, ?)", poisql.insert_cat);
+/* update category */
+DB_PREP(db, "update category set label = ?, desc = ?, enabled = ? where cat_id = ?", poisql.update_cat);
+/* delete from category */
+DB_PREP(db,"delete from category where cat_id = ?", poisql.delete_cat);
+/* enable category */
+DB_PREP(db, "update category set enabled = ? where cat_id = ?", poisql.toggle_cat);
+/* select all category */
+DB_PREP(db, "select c.cat_id, c.label, c.desc, c.enabled, c.icon, c.color,"
+ " count(p.poi_id)"
+ " from category c"
+ " left outer join poi p on c.cat_id = p.cat_id"
+ " group by c.cat_id, c.label, c.desc, c.enabled "
+ " order by c.priority,c.label",
+ poisql.selall_cat);
+/* Select quick categories */
+DB_PREP(db, "select c.cat_id, c.label, c.icon, c.color"
+ " from category c where c.enabled=1 "
+ " order by c.priority,c.label limit 9",
+ poisql.select_quick_cat);
+return TRUE;
}
-gboolean
-mapper_db_disconnect(sqlite3 **db)
+void
+poi_icon_hash_clear(void)
{
-if (db && *db) {
- /* XXX: Handle the prepared statements */
-#if 0
- poi_db_unprepare(*db);
- osm_db_unprepare(*db);
-#endif
- sqlite3_close(*db);
- return TRUE;
+image_cache_clear(poi_ic);
}
-return FALSE;
+
+void
+poi_deinit(sqlite3 *db)
+{
+if (poi_ic) {
+ image_cache_free(poi_ic);
+ poi_ic=NULL;
+}
+
+sqlite3_finalize(poisql.select_quick_cat);
+sqlite3_finalize(poisql.selall_cat);
+sqlite3_finalize(poisql.toggle_cat);
+sqlite3_finalize(poisql.delete_cat);
+sqlite3_finalize(poisql.update_cat);
+sqlite3_finalize(poisql.insert_cat);
+sqlite3_finalize(poisql.select_cat);
+sqlite3_finalize(poisql.insert_poi);
+sqlite3_finalize(poisql.update_poi);
+sqlite3_finalize(poisql.delete_poi);
+sqlite3_finalize(poisql.delete_poi_by_catid);
+sqlite3_finalize(poisql.select_nearest_poi);
+sqlite3_finalize(poisql.select_poi);
+sqlite3_finalize(poisql.select_poi_search);
+sqlite3_finalize(poisql.select_poi_search_cat);
}
gboolean
-mapper_db_connect(sqlite3 **db, const gchar *data_db)
+poi_init(sqlite3 **db)
{
-if (db_connect(db, data_db)) {
- poi_db_create(*db);
- if (poi_db_prepare(*db)==FALSE)
- g_printerr("Failed to prepare POI SQL statements");
- if (osm_db_prepare(*db)==FALSE) {
- g_printerr("Failed to prepare OSM SQL statements");
- g_printf("SQLITE: %s\n", sqlite3_errmsg(*db));
- }
- return TRUE;
+if (!db || !*db)
+ return FALSE;
+
+if (!poi_ic)
+ poi_ic=image_cache_new(128);
+
+poidb=*db;
+poi_db_create(poidb);
+if (poi_db_prepare(poidb)==FALSE) {
+ g_printerr("Failed to prepare POI SQL statements:");
+ g_printf("SQLITE: %s\n", sqlite3_errmsg(poidb));
+ return FALSE;
}
-return FALSE;
+return TRUE;
}
-PoiInfo *
+poi_info *
poi_new(void)
{
-PoiInfo *p;
+poi_info *p;
+
+p=g_slice_new0(poi_info);
+p->source=POI_SOURCE_USER;
+/* XXX: Set defaults ? */
+return p;
+}
+
+void
+poi_free(poi_info *p)
+{
+if (p->label)
+ g_free(p->label);
+if (p->desc)
+ g_free(p->desc);
+if (p->url)
+ g_free(p->url);
+if (p->postal_code)
+ g_free(p->postal_code);
+if (p->cat_label)
+ g_free(p->cat_label);
+if (p->cat_desc)
+ g_free(p->cat_desc);
+g_slice_free(poi_info, p);
+}
+
+GtkListStore *
+poi_list_store_new(void) {
+return gtk_list_store_new(ITEM_NUM_COLUMNS,
+ G_TYPE_INT, /* POI ID */
+ G_TYPE_INT, /* Category ID */
+ G_TYPE_DOUBLE, /* Latitude */
+ G_TYPE_DOUBLE, /* Longitude */
+ G_TYPE_DOUBLE, /* Dist */
+ G_TYPE_STRING, /* Lat/Lon */
+ G_TYPE_STRING, /* Label */
+ G_TYPE_STRING, /* Desc. */
+ G_TYPE_STRING, /* Category Label */
+ G_TYPE_STRING, /* Icon */
+ G_TYPE_STRING); /* Color */
+}
+
+/*************************************
+ * POI Category functions
+ *
+ */
-return g_slice_new0(PoiInfo);
+poi_category *
+poi_category_new(void)
+{
+return g_slice_new0(poi_category);
}
void
-poi_free(PoiInfo *p)
+poi_category_free(poi_category *c)
+{
+if (c->label)
+ g_free(c->label);
+if (c->desc)
+ g_free(c->desc);
+g_slice_free(poi_category, c);
+}
+
+gboolean
+poi_category_toggle(guint cat_id, gboolean cat_enabled)
{
-g_slice_free(PoiInfo, p);
+g_return_val_if_fail(poisql.toggle_cat, FALSE);
+
+if (SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 1, cat_enabled) ||
+ SQLITE_OK != sqlite3_bind_int(poisql.toggle_cat, 2, cat_id) ||
+ SQLITE_DONE != sqlite3_step(poisql.toggle_cat)) {
+ return FALSE;
+ }
+return TRUE;
+}
+
+gboolean
+poi_category_get(guint cat_id, poi_category **c)
+{
+poi_category *cc;
+
+g_return_val_if_fail(poisql.select_cat, FALSE);
+if (SQLITE_OK != sqlite3_bind_int(poisql.select_cat, 1, cat_id) || SQLITE_ROW != sqlite3_step(poisql.select_cat)) {
+ sqlite3_reset(poisql.select_cat);
+ return FALSE;
+}
+
+cc=poi_category_new();
+cc->id=cat_id;
+cc->label = g_strdup(sqlite3_column_text(poisql.select_cat, 0));
+cc->desc = g_strdup(sqlite3_column_text(poisql.select_cat, 1));
+cc->enabled = sqlite3_column_int(poisql.select_cat, 2);
+
+sqlite3_reset(poisql.select_cat);
+sqlite3_clear_bindings(poisql.select_cat);
+*c=cc;
+return TRUE;
}
gboolean
-poi_category_update(gint cat_id, gchar *cat_label, gchar *cat_desc, gint cat_enabled)
+poi_category_update(guint cat_id, poi_category *c)
{
gboolean results=TRUE;
if (!_db)
return FALSE;
+g_return_val_if_fail(poisql.update_cat, FALSE);
+g_return_val_if_fail(poisql.insert_cat, FALSE);
+
if (cat_id > 0) {
/* edit category */
- if (SQLITE_OK !=
- sqlite3_bind_text(_stmt_update_cat, 1, cat_label, -1,
- g_free)
- || SQLITE_OK != sqlite3_bind_text(_stmt_update_cat, 2,
- cat_desc, -1, g_free)
- || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 3,
- cat_enabled)
- || SQLITE_OK != sqlite3_bind_int(_stmt_update_cat, 4,
- cat_id)
- || SQLITE_DONE != sqlite3_step(_stmt_update_cat)) {
+ if (SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 1, c->label, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.update_cat, 2, c->desc, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 3, c->enabled)
+ || SQLITE_OK != sqlite3_bind_int(poisql.update_cat, 4, c->id)
+ || SQLITE_DONE != sqlite3_step(poisql.update_cat)) {
results = FALSE;
}
- sqlite3_reset(_stmt_update_cat);
- sqlite3_clear_bindings(_stmt_update_cat);
+ sqlite3_reset(poisql.update_cat);
+ sqlite3_clear_bindings(poisql.update_cat);
} else {
/* add category */
- if (SQLITE_OK !=
- sqlite3_bind_text(_stmt_insert_cat, 1, cat_label, -1,
- g_free)
- || SQLITE_OK != sqlite3_bind_text(_stmt_insert_cat, 2,
- cat_desc, -1, g_free)
- || SQLITE_OK != sqlite3_bind_int(_stmt_insert_cat, 3,
- cat_enabled)
- || SQLITE_DONE != sqlite3_step(_stmt_insert_cat)) {
+ if (SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 1, c->label, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.insert_cat, 2, c->desc, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_int(poisql.insert_cat, 3, c->enabled)
+ || SQLITE_DONE != sqlite3_step(poisql.insert_cat)) {
results = FALSE;
}
- sqlite3_reset(_stmt_insert_cat);
- sqlite3_clear_bindings(_stmt_insert_cat);
+ sqlite3_reset(poisql.insert_cat);
+ sqlite3_clear_bindings(poisql.insert_cat);
}
return results;
}
gboolean
-poi_category_delete(DeletePOI *dpoi)
+poi_category_delete(guint id)
{
-if (!_db)
+if (!poidb)
return FALSE;
-if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi_by_catid, 1, dpoi->id)
- || SQLITE_DONE != sqlite3_step(_stmt_delete_poi_by_catid)) {
- sqlite3_reset(_stmt_delete_poi_by_catid);
- return FALSE;
+g_return_val_if_fail(poisql.delete_poi_by_catid, FALSE);
+g_return_val_if_fail(poisql.delete_cat, FALSE);
+
+if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi_by_catid, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi_by_catid)) {
+ sqlite3_reset(poisql.delete_poi_by_catid);
+ return FALSE;
}
-sqlite3_reset(_stmt_delete_poi_by_catid);
-sqlite3_clear_bindings(_stmt_delete_poi_by_catid);
+sqlite3_reset(poisql.delete_poi_by_catid);
+sqlite3_clear_bindings(poisql.delete_poi_by_catid);
-if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_cat, 1, dpoi->id) ||
- SQLITE_DONE != sqlite3_step(_stmt_delete_cat)) {
- sqlite3_reset(_stmt_delete_cat);
+if (SQLITE_OK != sqlite3_bind_int(poisql.delete_cat, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_cat)) {
+ sqlite3_reset(poisql.delete_cat);
return FALSE;
}
-sqlite3_reset(_stmt_delete_cat);
-sqlite3_clear_bindings(_stmt_delete_cat);
+sqlite3_reset(poisql.delete_cat);
+sqlite3_clear_bindings(poisql.delete_cat);
return TRUE;
}
gboolean
-poi_delete(DeletePOI *dpoi)
+poi_delete(guint id)
{
-if (!_db)
+if (!poidb)
return FALSE;
-if (SQLITE_OK != sqlite3_bind_int(_stmt_delete_poi, 1, dpoi->id) ||
- SQLITE_DONE != sqlite3_step(_stmt_delete_poi)) {
- sqlite3_reset(_stmt_delete_poi);
+g_return_val_if_fail(poisql.delete_poi, FALSE);
+
+if (SQLITE_OK != sqlite3_bind_int(poisql.delete_poi, 1, id) || SQLITE_DONE != sqlite3_step(poisql.delete_poi)) {
+ sqlite3_reset(poisql.delete_poi);
return FALSE;
-} else {
- sqlite3_reset(_stmt_delete_poi);
- sqlite3_clear_bindings(_stmt_delete_poi);
}
+sqlite3_reset(poisql.delete_poi);
+sqlite3_clear_bindings(poisql.delete_poi);
return TRUE;
}
gboolean
-poi_get_list(guint unitx, guint unity, GtkListStore **_store, guint *_num_cats)
+poi_search(poi_search_type pst, gdouble lat, gdouble lon, gchar *text, guint cat, GtkListStore *store)
{
-guint x, y;
-gfloat lat1, lon1, lat2, lon2;
-GtkListStore *store;
GtkTreeIter iter;
+sqlite3_stmt *sql=NULL;
+gchar *ltext=NULL;
+guint rows=0;
gchar tmp1[16], tmp2[16];
-gint num_cats=0;
+guint range=1;
-if (!_db)
+if (!poidb)
return FALSE;
-store = gtk_list_store_new(POI_NUM_COLUMNS, G_TYPE_INT, /* POI ID */
- G_TYPE_INT, /* Category ID */
- G_TYPE_FLOAT, /* Latitude */
- G_TYPE_FLOAT, /* Longitude */
- G_TYPE_STRING, /* Lat/Lon */
- G_TYPE_STRING, /* POI Label */
- G_TYPE_STRING, /* POI Desc. */
- G_TYPE_STRING); /* Category Label */
-
-x = unitx - pixel2unit(3 * _draw_width);
-y = unity + pixel2unit(3 * _draw_width);
-unit2latlon(x, y, lat1, lon1);
-
-x = unitx + pixel2unit(3 * _draw_width);
-y = unity - pixel2unit(3 * _draw_width);
-unit2latlon(x, y, lat2, lon2);
-
-if (SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 1, lat1) ||
- SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 2, lat2) ||
- SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 3, lon1) ||
- SQLITE_OK != sqlite3_bind_double(_stmt_select_poi, 4, lon2)) {
- g_printerr("Failed to bind values for _stmt_select_poi\n");
+g_return_val_if_fail(poisql.select_poi, FALSE);
+g_return_val_if_fail(store, FALSE);
+
+g_printf("POI Search: [%s] around %.6f %.6f (%d %d)\n", text, lat, lon, cat, pst);
+
+switch (pst) {
+ case POI_SEARCH_NEAR:
+ if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat-0.5) ||
+ SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon+0.5)) {
+ g_printerr("Failed to bind values for poisql.select_poi\n");
+ sqlite3_clear_bindings(poisql.select_poi);
+ return FALSE;
+ }
+ sql=poisql.select_poi;
+ break;
+ case POI_SEARCH_TEXT:
+ ltext=g_strdup_printf("%s%%", text);
+
+ if (SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search, 5, ltext, -1, SQLITE_TRANSIENT)) {
+ g_printerr("Failed to bind values for poisql.select_poi_search\n");
+ sqlite3_clear_bindings(poisql.select_poi_search);
+ g_free(ltext);
+ return FALSE;
+ }
+ g_free(ltext);
+ sql=poisql.select_poi_search;
+ break;
+ case POI_SEARCH_TEXT_CAT:
+ ltext=g_strdup_printf("%s%%", text);
+
+ if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_search_cat, 6, cat) ||
+ SQLITE_OK != sqlite3_bind_text(poisql.select_poi_search_cat, 5, ltext, -1, SQLITE_TRANSIENT)) {
+ g_printerr("Failed to bind values for poisql.select_poi_search_cat\n");
+ sqlite3_clear_bindings(poisql.select_poi_search_cat);
+ g_free(ltext);
+ return FALSE;
+ }
+ g_free(ltext);
+ sql=poisql.select_poi_search_cat;
+ break;
+ case POI_SEARCH_CAT:
+ if (SQLITE_OK != sqlite3_bind_int(poisql.select_poi_by_cat, 5, cat)) {
+ g_printerr("Failed to bind values for poisql.select_poi_by_cat\n");
+ sqlite3_clear_bindings(poisql.select_poi_by_cat);
+ return FALSE;
+ }
+ sql=poisql.select_poi_by_cat;
+ break;
+ default:
+ g_assert_not_reached();
+ return FALSE;
+ break;
+}
+
+/* XXX: Use common bind for common variables */
+if (SQLITE_OK != sqlite3_bind_double(sql, 1, lat-range) ||
+ SQLITE_OK != sqlite3_bind_double(sql, 2, lat+range) ||
+ SQLITE_OK != sqlite3_bind_double(sql, 3, lon-range) ||
+ SQLITE_OK != sqlite3_bind_double(sql, 4, lon+range)) {
+ g_printerr("Failed to bind common variables for POI search\n");
+ sqlite3_clear_bindings(sql);
return FALSE;
}
-while (SQLITE_ROW == sqlite3_step(_stmt_select_poi)) {
- gfloat lat, lon;
- lat = sqlite3_column_double(_stmt_select_poi, 0);
- lon = sqlite3_column_double(_stmt_select_poi, 1);
- g_printf("Found POI(%d): %f, %f, %s, %s, %s\n",
- num_cats,
- lat,
- lon,
- sqlite3_column_text(_stmt_select_poi, 3),
- sqlite3_column_text(_stmt_select_poi, 4),
- sqlite3_column_text(_stmt_select_poi, 6));
-
- lat_format(lat, tmp1);
- lon_format(lon, tmp2);
+while (SQLITE_ROW == sqlite3_step(sql)) {
+ gdouble rlat, rlon, dist;
+
+ rlat=sqlite3_column_double(sql, 0);
+ rlon=sqlite3_column_double(sql, 1);
+ lat_format(_degformat, rlat, tmp1);
+ lon_format(_degformat, rlon, tmp2);
+ dist=calculate_distance(lat, lon, rlat, rlon) * UNITS_CONVERT[_units];
+
gtk_list_store_append(store, &iter);
gtk_list_store_set(store, &iter,
- POI_POIID, sqlite3_column_int(_stmt_select_poi, 2),
- POI_CATID, sqlite3_column_int(_stmt_select_poi, 5),
- POI_LAT, lat, POI_LON, lon, POI_LATLON,
- g_strdup_printf("%s, %s", tmp1, tmp2),
- POI_LABEL, sqlite3_column_text(_stmt_select_poi, 3),
- POI_DESC, sqlite3_column_text(_stmt_select_poi, 4),
- POI_CATLAB, sqlite3_column_text(_stmt_select_poi, 6),
+ ITEM_ID, sqlite3_column_int(sql, 2),
+ ITEM_CATID, sqlite3_column_int(sql, 5),
+ ITEM_LAT, rlat,
+ ITEM_LON, rlon,
+ ITEM_DIST, dist,
+ ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
+ ITEM_LABEL, sqlite3_column_text(sql, 3),
+ ITEM_DESC, sqlite3_column_text(sql, 4),
+ ITEM_CATLAB, sqlite3_column_text(sql, 6),
-1);
- num_cats++;
+ rows++;
}
-g_printf("N: %d\n", num_cats);
-sqlite3_reset(_stmt_select_poi);
-sqlite3_clear_bindings(_stmt_select_poi);
-*_store=store;
-*_num_cats=num_cats;
+
+g_printf("Found: %d items\n", rows);
+
+sqlite3_reset(sql);
+sqlite3_clear_bindings(sql);
+
return TRUE;
}
gboolean
-poi_update(gint poi_id, gint cat_id, gchar *poi_label, gchar *poi_desc)
+poi_get_list_inside(gdouble lat1, gdouble lon1, gdouble lat2, gdouble lon2, GtkListStore *store, guint *num_poi)
{
+static gboolean active=FALSE;
+GtkTreeIter iter;
+gchar tmp1[16], tmp2[16];
+
+num_poi=0;
+
if (!_db)
return FALSE;
-if (SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 1, poi_label, -1, SQLITE_STATIC)
- || SQLITE_OK != sqlite3_bind_text(_stmt_update_poi, 2, poi_desc, -1, g_free)
- || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 3, cat_id)
- || SQLITE_OK != sqlite3_bind_int(_stmt_update_poi, 4, poi_id)
- || SQLITE_DONE != sqlite3_step(_stmt_update_poi)) {
+g_return_val_if_fail(poisql.select_poi, FALSE);
+g_return_val_if_fail(store, FALSE);
+
+if (active)
+ return FALSE;
+
+active=TRUE;
+if (SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 1, lat1) ||
+ SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 2, lat2) ||
+ SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 3, lon1) ||
+ SQLITE_OK != sqlite3_bind_double(poisql.select_poi, 4, lon2)) {
+ g_printerr("Failed to bind values for poisql.select_poi\n");
+ return FALSE;
+}
+
+while (SQLITE_ROW == sqlite3_step(poisql.select_poi)) {
+ gdouble lat, lon, dist=0;
+
+ lat=sqlite3_column_double(poisql.select_poi, 0);
+ lon=sqlite3_column_double(poisql.select_poi, 1);
+ lat_format(_degformat, lat, tmp1);
+ lon_format(_degformat, lon, tmp2);
+
+ gtk_list_store_append(store, &iter);
+ gtk_list_store_set(store, &iter,
+ ITEM_ID, sqlite3_column_int(poisql.select_poi, 2),
+ ITEM_CATID, sqlite3_column_int(poisql.select_poi, 5),
+ ITEM_LAT, lat,
+ ITEM_LON, lon,
+ ITEM_DIST, dist,
+ ITEM_LATLON, g_strdup_printf("%s, %s", tmp1, tmp2),
+ ITEM_LABEL, sqlite3_column_text(poisql.select_poi, 3),
+ ITEM_DESC, sqlite3_column_text(poisql.select_poi, 4),
+ ITEM_CATLAB, sqlite3_column_text(poisql.select_poi, 6),
+ ITEM_ICON, sqlite3_column_text(poisql.select_poi, 8),
+ ITEM_COLOR, sqlite3_column_text(poisql.select_poi, 9),
+ -1);
+ *num_poi++;
+}
+sqlite3_reset(poisql.select_poi);
+sqlite3_clear_bindings(poisql.select_poi);
+active=FALSE;
+return TRUE;
+}
+
+gboolean
+poi_get_list_near(gdouble lat, gdouble lon, gfloat range, GtkListStore *store, guint *num_poi)
+{
+gdouble lat1, lon1, lat2, lon2;
+
+lat1=lat-range;
+lon1=lon-range;
+lat2=lat+range;
+lon2=lon+range;
+
+return poi_get_list_inside(lat1, lon1, lat2, lon2, store, num_poi);
+}
+
+poi_info *
+poi_get_by_id(guint id)
+{
+poi_info *p=NULL;
+
+g_return_val_if_fail(poisql.select_poi_by_id, FALSE);
+
+if (SQLITE_OK!=sqlite3_bind_double(poisql.select_poi_by_id, 1, id))
+ return NULL;
+
+if (SQLITE_ROW==sqlite3_step(poisql.select_poi_by_id)) {
+ p=poi_new();
+ p->poi_id=sqlite3_column_int(poisql.select_poi_by_id, PS_ID);
+ p->lat=sqlite3_column_double(poisql.select_poi_by_id, PS_LAT);
+ p->lon=sqlite3_column_double(poisql.select_poi_by_id, PS_LON);
+ p->source=sqlite3_column_int(poisql.select_poi_by_id, PS_SOURCE);
+ p->public=sqlite3_column_int(poisql.select_poi_by_id, PS_PUBLIC)==1 ? TRUE : FALSE;
+ p->label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_LABEL));
+ p->desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_DESC));
+ p->url=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_URL));
+ p->postal_code=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_POSTAL_CODE));
+
+ p->cat_id=sqlite3_column_int(poisql.select_poi_by_id, PS_CAT_ID);
+ p->cat_label=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_LABEL));
+ p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_poi_by_id, PS_CAT_DESC));
+}
+
+sqlite3_reset(poisql.select_poi_by_id);
+sqlite3_clear_bindings(poisql.select_poi_by_id);
+
+return p;
+}
+
+gboolean
+poi_update(poi_info *p)
+{
+if (!poidb)
+ return FALSE;
+
+g_return_val_if_fail(poisql.update_poi, FALSE);
+
+if (p->poi_id==0)
+ return FALSE;
+
+if (SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 1, p->label, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.update_poi, 2, p->desc, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 3, p->cat_id)
+ || SQLITE_OK != sqlite3_bind_int(poisql.update_poi, 4, p->poi_id)
+ || SQLITE_DONE != sqlite3_step(poisql.update_poi)) {
return FALSE;
}
-sqlite3_reset(_stmt_update_poi);
-sqlite3_clear_bindings(_stmt_update_poi);
+sqlite3_reset(poisql.update_poi);
+sqlite3_clear_bindings(poisql.update_poi);
return TRUE;
}
+/* XXX: Add url and postal_code */
gboolean
-poi_add(gdouble lat, gdouble lon, gint cat_id, gchar *poi_label, gchar *poi_desc)
+poi_add(poi_info *p)
{
-if (!_db)
+time_t t;
+
+if (!poidb)
+ return FALSE;
+
+g_return_val_if_fail(poisql.insert_poi, FALSE);
+
+if (p->poi_id!=0)
return FALSE;
-if (SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 1, lat)
- || SQLITE_OK != sqlite3_bind_double(_stmt_insert_poi, 2, lon)
- || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 3, poi_label, -1, g_free)
- || SQLITE_OK != sqlite3_bind_text(_stmt_insert_poi, 4, poi_desc, -1, g_free)
- || SQLITE_OK != sqlite3_bind_int(_stmt_insert_poi, 5, cat_id)
- || SQLITE_DONE != sqlite3_step(_stmt_insert_poi)) {
+t=time(NULL);
+
+if (SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 1, p->lat)
+ || SQLITE_OK != sqlite3_bind_double(poisql.insert_poi, 2, p->lon)
+ || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 3, p->label, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 4, p->desc, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 5, p->url, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_text(poisql.insert_poi, 6, p->postal_code, -1, SQLITE_STATIC)
+ || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 7, p->cat_id)
+ || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 8, t)
+ || SQLITE_OK != sqlite3_bind_int(poisql.insert_poi, 9, p->source)
+ || SQLITE_DONE != sqlite3_step(poisql.insert_poi)) {
+ sqlite3_reset(poisql.insert_poi);
+ sqlite3_clear_bindings(poisql.insert_poi);
return FALSE;
}
-sqlite3_reset(_stmt_insert_poi);
-sqlite3_clear_bindings(_stmt_insert_poi);
+sqlite3_reset(poisql.insert_poi);
+sqlite3_clear_bindings(poisql.insert_poi);
return TRUE;
}
-PoiInfo *
+poi_info *
poi_find_nearest(gdouble lat, gdouble lon)
{
-const gchar *_poi_label, *_cat_label;
-PoiInfo *p;
+poi_info *p;
-if (!_db)
+if (!poidb)
return FALSE;
-sqlite3_reset(_stmt_select_nearest_poi);
-sqlite3_clear_bindings(_stmt_select_nearest_poi);
+g_return_val_if_fail(poisql.select_nearest_poi, FALSE);
-if (SQLITE_OK == sqlite3_bind_double(_stmt_select_nearest_poi, 1, lat)
- && SQLITE_OK == sqlite3_bind_double(_stmt_select_nearest_poi, 2, lon)
- && SQLITE_ROW == sqlite3_step(_stmt_select_nearest_poi)) {
+sqlite3_reset(poisql.select_nearest_poi);
+sqlite3_clear_bindings(poisql.select_nearest_poi);
- p=poi_new();
+if (SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 1, lat)
+ && SQLITE_OK == sqlite3_bind_double(poisql.select_nearest_poi, 2, lon)
+ && SQLITE_ROW == sqlite3_step(poisql.select_nearest_poi)) {
- p->lat = sqlite3_column_double(_stmt_select_nearest_poi, 0);
- p->lon = sqlite3_column_double(_stmt_select_nearest_poi, 1);
- _poi_label = sqlite3_column_text(_stmt_select_nearest_poi, 2);
- _cat_label = sqlite3_column_text(_stmt_select_nearest_poi, 3);
- p->label=g_strdup(_poi_label);
- p->cat_label=g_strdup(_cat_label);
- g_printf("%f %f %s %s\n", p->lat, p->lon, p->label, p->cat_label);
+ p=poi_new();
+ p->lat=sqlite3_column_double(poisql.select_nearest_poi, 0);
+ p->lon=sqlite3_column_double(poisql.select_nearest_poi, 1);
+ p->poi_id=sqlite3_column_double(poisql.select_nearest_poi, 2);
+ p->label=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 3));
+ p->desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 4));
+ p->cat_id=sqlite3_column_double(poisql.select_nearest_poi, 5);
+ p->cat_desc=g_strdup(sqlite3_column_text(poisql.select_nearest_poi, 6));
+ sqlite3_reset(poisql.select_nearest_poi);
+ sqlite3_clear_bindings(poisql.select_nearest_poi);
return p;
}
return NULL;
}
+
+GdkPixbuf *
+poi_get_icon(const gchar *icon, gboolean big)
+{
+gchar buffer[128];
+gchar key[32];
+
+if (icon==NULL)
+ return NULL;
+
+if (strlen(icon)==0)
+ return NULL;
+
+g_snprintf(buffer, sizeof(buffer), "%s/%s.%s/%s.png", theme_base, theme, (big==TRUE) ? "big" : "small", icon);
+g_snprintf(key, sizeof(key), "%s:%s:%s", theme, (big==TRUE) ? "big" : "small", icon);
+
+return image_cache_get(poi_ic, key, buffer);
+}
+
+GtkListStore *
+poi_category_generate_store(void)
+{
+GtkTreeIter iter;
+GtkListStore *store;
+
+if (!poidb)
+ return NULL;
+
+g_return_val_if_fail(poisql.selall_cat, FALSE);
+
+store=gtk_list_store_new(CAT_NUM_COLUMNS, /* pixbuf */
+ G_TYPE_UINT,
+ G_TYPE_BOOLEAN,
+ G_TYPE_STRING,
+ G_TYPE_STRING,
+ G_TYPE_UINT,
+ GDK_TYPE_PIXBUF);
+
+while (SQLITE_ROW == sqlite3_step(poisql.selall_cat)) {
+ gtk_list_store_append(store, &iter);
+ gtk_list_store_set(store, &iter,
+ CAT_ID, sqlite3_column_int(poisql.selall_cat, 0),
+ CAT_ENABLED, sqlite3_column_int(poisql.selall_cat, 3),
+ CAT_LABEL, sqlite3_column_text(poisql.selall_cat, 1),
+ CAT_DESC, sqlite3_column_text(poisql.selall_cat, 2),
+ CAT_POI_CNT, sqlite3_column_int(poisql.selall_cat, 6),
+ CAT_ICON, poi_get_icon(sqlite3_column_text(poisql.selall_cat, 4),TRUE),
+ -1);
+}
+
+sqlite3_reset(poisql.selall_cat);
+sqlite3_clear_bindings(poisql.selall_cat);
+
+return store;
+}