out of to the xml file posted below, iam creating 2 tables, called Node and Tag. the Node table :
xml file:
<node id="25454461" lat="49.1628359" lon="8.3868881"/>
<node id="25454468" lat="49.2520203" lon="8.3989846"/>
<node id="25454480" lat="49.1562799" lon="8.393818"/>
<node id="25454484" lat="49.2249985" lon="8.496858"/>
<tag k="railway:maxspeed:diverging" v="200"/>
<tag k="railway:maxspeed:straight" v="280"/>
Node table is structured as follows:
//both (lat,lng) are primary keys
nodeId lat lng
25454461 49.1628359 8.3868881
.... ..... .....
Tag table is structured as follows:
tagTyp maxspeed
railway:maxspeed:diverging 200
railway:maxspeed:straight 280
and the relation betwwe the 2 tables is M:M so I created three tables as follwos
private final String sqlTableNode = "CREATE TABLE "+this.TABLE_NODE+
" ( "+this.NODE_TABLE_ID_COL+" INTEGER AUTOINCREMENT, " +
this.NODE_TABLE_NODE_ID_COL+" TEXT NOT NULL, " +
this.NODE_TABLE_LAT_COL+" TEXT NOT NULL, " +
this.NODE_TABLE_LNG_COL+" TEXT NOT NULL, " +
"PRIMARY KEY ("+this.NODE_TABLE_LAT_COL+", "+this.NODE_TABLE_LNG_COL+") );";
private final String TABLE_TAG = SysConsts.TABLE_TAG_NAME;
private final String sqlTableTag = "CREATE TABLE "+this.TABLE_TAG+ //if not exists
" ( "+this.TAG_TABLE_PK_COL+" INTEGER PRIMARY KEY AUTOINCREMENT, " +
this.TAG_TABLE_TYPE_COL+" TEXT NOT NULL, " +
this.TAG_TABLE_MAX_SPEED_COL+" TEXT NOT NULL);";
private final String TABLE_NODETAG = SysConsts.TABLE_TAG_PER_NODE_NAME;
private final String sqlTableNodeTag = "CREATE TABLE "+this.TABLE_NODETAG+ //if not exists
" ( "+this.NODETAG_TABLE_PK_COL+" INTEGER PRIMARY KEY AUTOINCREMENT, " +
this.NODETAG_TABL_LAT_COL+" INTEGER NOT NULL, " +
this.NODETAG_TABLE_LNG_COL+" INTEGER NOT NULL, " +
this.NODETAG_TABLE_TAG_PK_COL+" INTEGER NOT NULL);";
and what i want to do after the creation of the tables is, to do a query from the "sqlTableNodeTag" to retrieve all columns in the "sqlTableTag" table based on a given (lat,lng). something like,
select * from sqlTableNodeTag where this.NODETAG_TABLE_LAT_COL = latValue And this.NODETAG_TABLE_LNG_COL = lngValue
my question is, the table "sqlTableNodeTag" should contain just the "this.NODETAG_TABLE_TAG_PK_COL" or i have to remove it and add "this.TAG_TABLE_TYPE_COL" and "this.TAG_TABLE_MAX_SPEED_COL"
Aucun commentaire:
Enregistrer un commentaire