From 68cd7001da0b0e93d0ccaee2114af49e5789bf1b Mon Sep 17 00:00:00 2001 From: Kaj-Michael Lang Date: Thu, 1 Nov 2007 02:43:51 +0200 Subject: [PATCH] Many changes to importer: - Store "postal_code" - Use two isin fields, one for country and one for place (city/village) - Store midle way node lat,lon so we can calculate a distance to way quickly - Store note and postal_code for POIs - Always import all places even if outside bounding box - Misc other changes --- src/osm.c | 182 ++++++++++++++++++++++++++++++++++++++++++------------ 1 file changed, 143 insertions(+), 39 deletions(-) diff --git a/src/osm.c b/src/osm.c index 79c349c..4589603 100644 --- a/src/osm.c +++ b/src/osm.c @@ -83,7 +83,11 @@ static XML_Parser xp; typedef struct _node_data node_data; struct _node_data { gchar *name; - guint32 isin; + gchar *desc; + gchar *url; + gchar *postal_code; + guint32 isin_c; + guint32 isin_p; }; /* Node type */ @@ -103,7 +107,9 @@ struct _way_data { GHashTable *names; gchar *ref; gchar *int_ref; - guint32 isin; + gchar *postal_code; + guint32 isin_c; /* Country */ + guint32 isin_p; /* Primary (city, village) place */ guint speed; gint8 layer; }; @@ -363,15 +369,16 @@ struct sql_stmt { sqlite3_stmt *insert_way_data; sqlite3_stmt *insert_way_ref; + sqlite3_stmt *insert_way_pc; sqlite3_stmt *insert_way_name; sqlite3_stmt *insert_way_names_nls; sqlite3_stmt *insert_way_n2n; sqlite3_stmt *delete_way; sqlite3_stmt *delete_way_n2n; - sqlite3_stmt *delete_way_seg_wsid; sqlite3_stmt *delete_way_name; sqlite3_stmt *delete_way_names_nls; sqlite3_stmt *delete_way_ref; + sqlite3_stmt *delete_way_pc; sqlite3_stmt *insert_place; sqlite3_stmt *delete_place; @@ -392,7 +399,7 @@ void print_way(way *w); void db_prepare(void); gboolean db_insert_node(node *n); -guint32 osm_find_way_place(way *w); +guint32 osm_find_way_place(way *w, node_type_t nt); /****************************************************/ /* Functions */ @@ -421,6 +428,9 @@ sqlite3_finalize(sql.insert_way_name); sqlite3_finalize(sql.delete_way_n2n); sqlite3_finalize(sql.insert_way_n2n); +sqlite3_finalize(sql.delete_way_pc); +sqlite3_finalize(sql.insert_way_pc); + sqlite3_finalize(sql.delete_way_names_nls); sqlite3_finalize(sql.insert_way_names_nls); } @@ -429,7 +439,7 @@ void db_prepare(void) { /* Way nodes */ -sqlite3_prepare_v2(db, "insert or replace into nodes (nid,lat,lon,l,f) values (?,?,?,0,?)", -1, &sql.insert_node, NULL); +sqlite3_prepare_v2(db, "insert or replace into nodes (nid,ilat,ilon,rlat,rlon,l,f) values (?,?,?,?,?,0,?)", -1, &sql.insert_node, NULL); sqlite3_prepare_v2(db, "select lat,lon,l from nodes where nid=?", -1, &sql.select_node, NULL); sqlite3_prepare_v2(db, "delete from nodes", -1, &sql.delete_nodes, NULL); sqlite3_prepare_v2(db, "update nodes set l=l+1 where nid=?", -1, &sql.update_node, NULL); @@ -439,22 +449,27 @@ sqlite3_prepare_v2(db, "insert or replace into places (nid,type,name,isin) value sqlite3_prepare_v2(db, "delete from places", -1, &sql.delete_place, NULL); /* POI nodes */ -sqlite3_prepare_v2(db, "insert or replace into poi (osm_id, lat, lon, label, cat_id, public, source, priority, isin, desc) values (?, ?, ?, ?, ?, 1, 1, ?, ?, ?)", - -1, &sql.insert_poi, NULL); -sqlite3_prepare_v2(db, "delete from poi where osm_id>0", -1, &sql.delete_osm_poi, NULL); +sqlite3_prepare_v2(db, "insert or replace into poi (osm_id, lat, lon, label, cat_id, public, source, priority, isin_c, isin_p, desc, url, postal_code) " + " values (?, ?, ?, ?, ?, 1, 1, ?, ?, ?, ?, ?, ?)", -1, &sql.insert_poi, NULL); + +sqlite3_prepare_v2(db, "delete from poi where osm_id>0 and source=1", -1, &sql.delete_osm_poi, NULL); /* Ways */ -sqlite3_prepare_v2(db, "insert or replace into way (wid,nodes,type,flags,speed,isin) values (?, ?, ?, ?, ?, ?)", -1, &sql.insert_way_data, NULL); +sqlite3_prepare_v2(db, "insert or replace into way (wid,nodes,type,flags,speed,isin_c,isin_p,lat,lon) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", -1, &sql.insert_way_data, NULL); sqlite3_prepare_v2(db, "delete from way", -1, &sql.delete_way, NULL); /* Way nodes */ -sqlite3_prepare_v2(db, "insert into way_n2n (wsid,f,t) values (?,?,?)", -1, &sql.insert_way_n2n, NULL); -sqlite3_prepare_v2(db, "delete from way_n2n where wsid=?", -1, &sql.delete_way_n2n, NULL); +sqlite3_prepare_v2(db, "insert into way_n2n (wid,f,t) values (?,?,?)", -1, &sql.insert_way_n2n, NULL); +sqlite3_prepare_v2(db, "delete from way_n2n where wid=?", -1, &sql.delete_way_n2n, NULL); /* Way names */ sqlite3_prepare_v2(db, "insert or replace into way_names (wid,name) values (?, ?)", -1, &sql.insert_way_name, NULL); sqlite3_prepare_v2(db, "delete from way_names", -1, &sql.delete_way_name, NULL); +/* Way postal codes */ +sqlite3_prepare_v2(db, "insert or replace into way_pc (wid,pc) values (?, ?)", -1, &sql.insert_way_pc, NULL); +sqlite3_prepare_v2(db, "delete from way_pc", -1, &sql.delete_way_pc, NULL); + /* Other language names for ways */ sqlite3_prepare_v2(db, "insert into way_names_nls (wid,lang,name) values (?, ?, ?)", -1, &sql.insert_way_names_nls, NULL); sqlite3_prepare_v2(db, "delete from way_names_nls where wid=?", -1, &sql.delete_way_names_nls, NULL); @@ -534,7 +549,8 @@ if (!n->data->name) sqlite3_bind_int(sql.insert_place, 1, n->id); sqlite3_bind_int(sql.insert_place, 2, n->type); sqlite3_bind_text(sql.insert_place, 3, n->data->name, -1, SQLITE_TRANSIENT); -sqlite3_bind_int(sql.insert_place, 4, n->data->isin); +sqlite3_bind_int(sql.insert_place, 4, n->data->isin_p); +sqlite3_bind_int(sql.insert_place, 5, n->data->isin_c); return db_exec(sql.insert_place); } @@ -551,14 +567,15 @@ else sqlite3_bind_text(sql.insert_poi, 4, "", -1, SQLITE_TRANSIENT); sqlite3_bind_int(sql.insert_poi, 5, n->type); sqlite3_bind_int(sql.insert_poi, 6, n->type/100); -sqlite3_bind_int(sql.insert_poi, 7, n->data->isin); +sqlite3_bind_int(sql.insert_poi, 7, n->data->isin_c); +sqlite3_bind_int(sql.insert_poi, 8, n->data->isin_p); -#if 0 if (n->data->desc) - sqlite3_bind_text(sql.insert_poi, 8, n->data->desc, -1, SQLITE_TRANSIENT); + sqlite3_bind_text(sql.insert_poi, 9, n->data->desc, -1, SQLITE_TRANSIENT); if (n->data->url) - sqlite3_bind_text(sql.insert_poi, 9, n->data->url, -1, SQLITE_TRANSIENT); -#endif + sqlite3_bind_text(sql.insert_poi, 10, n->data->url, -1, SQLITE_TRANSIENT); +if (n->data->postal_code) + sqlite3_bind_text(sql.insert_poi, 11, n->data->postal_code, -1, SQLITE_TRANSIENT); return db_exec(sql.insert_poi); } @@ -655,10 +672,28 @@ static void db_delete_way_names_nls(way *w) { sqlite3_bind_int(sql.delete_way_names_nls, 1, w->id); +db_exec(sql.delete_way_names_nls); +} + +static void +db_insert_way_pc(way *w) +{ +if (!w->data) + return; +if (!w->data->postal_code) + return; + +sqlite3_bind_int(sql.insert_way_pc, 1, w->id); +sqlite3_bind_text(sql.insert_way_pc, 2, w->data->postal_code, -1, SQLITE_TRANSIENT); + +db_exec(sql.insert_way_pc); +} -sqlite3_step(sql.delete_way_names_nls); -sqlite3_reset(sql.delete_way_names_nls); -sqlite3_clear_bindings(sql.delete_way_names_nls); +static void +db_delete_way_pc(way *w) +{ +sqlite3_bind_int(sql.delete_way_pc, 1, w->id); +db_exec(sql.delete_way_pc); } static void @@ -695,6 +730,8 @@ static gboolean db_insert_way(way *w) { GSList *iter; +guint ncnt; +node *wmn; if (!w) return FALSE; @@ -713,18 +750,31 @@ for (iter=w->nodes; iter!=NULL; iter=iter->next) { if (w->id==0) return FALSE; -if (w->data) - w->data->isin=osm_find_way_place(w); +if (w->data) { + w->data->isin_p=osm_find_way_place(w, NODE_PLACE_CITY); + w->data->isin_c=osm_find_way_place(w, NODE_PLACE_COUNTRY); +} print_way(w); +/* Get middle node, use it as way location */ +ncnt=g_slist_length(w->nodes); +wmn=g_slist_nth_data(w->nodes, ncnt/2); + sqlite3_bind_int(sql.insert_way_data, 1, w->id); sqlite3_bind_int(sql.insert_way_data, 2, w->ncnt); sqlite3_bind_int(sql.insert_way_data, 3, w->type); sqlite3_bind_int(sql.insert_way_data, 4, w->flags); if (w->data) { sqlite3_bind_int(sql.insert_way_data, 5, w->data->speed); - sqlite3_bind_int(sql.insert_way_data, 6, w->data->isin); + sqlite3_bind_int(sql.insert_way_data, 6, w->data->isin_c); + sqlite3_bind_int(sql.insert_way_data, 7, w->data->isin_p); +} +if (wmn) { + sqlite3_bind_double(sql.insert_way_data, 8, wmn->lat); + sqlite3_bind_double(sql.insert_way_data, 9, wmn->lon); +} else { + g_printerr("Failed to get way location node!\n"); } db_exec(sql.insert_way_data); @@ -732,6 +782,7 @@ db_exec(sql.insert_way_data); db_insert_way_ref(w); db_insert_way_name(w); db_insert_way_names_nls(w); +db_insert_way_pc(w); osm_free_way_data(w); return TRUE; @@ -844,6 +895,9 @@ if (n==NULL) return; if (n->data!=NULL) return; n->data=g_slice_new(node_data); n->data->name=NULL; +n->data->url=NULL; +n->data->desc=NULL; +n->data->postal_code=NULL; n->type=NODE_PLAIN; noded_cnt++; } @@ -855,6 +909,12 @@ g_assert(n); g_assert(n->data); if (n->data->name) g_free(n->data->name); +if (n->data->url) + g_free(n->data->url); +if (n->data->desc) + g_free(n->data->desc); +if (n->data->postal_code) + g_free(n->data->postal_code); g_slice_free(node_data, n->data); n->data=NULL; noded_cnt--; @@ -871,10 +931,16 @@ n->id=id; n->lat=lat; n->lon=lon; n->data=(node_data *)NULL; -g_hash_table_insert(osm_nodes, GINT_TO_POINTER(id), n); return n; } +static void +osm_free_node(node *n) +{ +g_assert(n); +g_slice_free(node, n); +} + static node * osm_find_node(guint32 nid) { @@ -898,6 +964,7 @@ w->data->name=NULL; w->data->names=NULL; w->data->ref=NULL; w->data->int_ref=NULL; +w->data->postal_code=NULL; w->data->layer=0; w->data->speed=0; } @@ -1009,7 +1076,7 @@ return 0; } guint32 -osm_find_way_place(way *w) +osm_find_way_place(way *w, node_type_t nt) { gchar **isin; gchar **place; @@ -1028,10 +1095,24 @@ while (*place!=NULL) { #ifdef VERBOSE g_printf("Checking (%d) in [%s]\n",w->id, ps); #endif - - t=g_hash_table_lookup(osm_place_city, ps); - if (t) - return t->id; +switch (nt) { + case NODE_PLACE_CITY: + case NODE_PLACE_TOWN: + case NODE_PLACE_VILLAGE: + case NODE_PLACE_HAMLET: + t=g_hash_table_lookup(osm_place_city, ps); + if (t) + return t->id; + break; + case NODE_PLACE_COUNTRY: + t=g_hash_table_lookup(osm_place_country, ps); + if (t) + return t->id; + break; + default: + g_assert_not_reached(); + break; + } place++; } @@ -1068,7 +1149,8 @@ if (!n->data) { return FALSE; } -n->data->isin=osm_find_node_place(n); +n->data->isin_p=osm_find_node_place(n); +n->data->isin_c=0; if (n->type>NODE_POI_START && n->typelat, cnode->lon)==FALSE) && + (cnode->typeid), cnode); + if (cnode->type!=NODE_PLAIN) { cnode->data->name=NULL; v=g_hash_table_lookup(osm_node_tags, "name"); if (v) cnode->data->name=g_strstrip(g_strdup(v)); + v=g_hash_table_lookup(osm_node_tags, "note"); + if (v) + cnode->data->desc=g_strstrip(g_strdup(v)); + v=g_hash_table_lookup(osm_node_tags, "postal_code"); + if (v) + cnode->data->postal_code=g_strstrip(g_strdup(v)); } - cnode->data->isin=0; + cnode->data->isin_c=0; + cnode->data->isin_p=0; v=g_hash_table_lookup(osm_node_tags, "is_in"); if (v) { gchar **isin; @@ -1414,6 +1514,10 @@ switch (t) { if (v) cway->data->int_ref=g_strdup(v); + v=g_hash_table_lookup(osm_way_tags, "postal_code"); + if (v) + cway->data->postal_code=g_strdup(v); + v=g_hash_table_lookup(osm_way_tags, "oneway"); if (v) cway->flags|=W_ONEWAY; -- 2.39.5