Once I needed to find out the street numbers and houses of Minsk. Imagine my disappointment when I found out that there is no complete data anywhere, and what to do if new streets and houses appear. This is where OpenStreetMap came up with open source and constant updates. The trouble is that the cards are an xml document with a volume of as much as 2 GB and information about the houses is presented in this form:
Processing 1.5 GB of data is easier when the data is ordered and not presented as strings. So I decided to convert the data to the database. It is said - done, as a working tool selected: Eclipse (Java SE) and a gentleman's set of denver.
Silent theory
As I already said, the file is an xml document in which the objects point ( node ), line ( way ) and relation ( relation ) are sequentially described . Each of the objects can have utility attributes that describe their properties. Schematically, this can be represented as follows.
Node is the point. The basic element that stores the coordinates of the object: latitude, longitude ( lat , lon ). Each point has its own unique id , which allows matching with the id way or relation . In XML notation, an object of this type will look like this:
Way is the line. The basic element, describes a collection of points, has only one id parameter .
A collection of points is described by the nd tag , with a single ref attribute , where ref is a reference to the id of a node element .
In XML notation, an object of this type will look like this:
Relation is a relationship. The basic element, describes a collection of objects, has only one id parameter . A collection of objects is described by the member tag . The member tag consists of three attributes: type - the type of the object, ref - a link to the id of the object, role - parameters of the roles, describes the relationship between the objects.
To describe objects, there is a Tag , it consists of two attributes k - key ( key ), v-value ( value ). This tag contains all the information about the object. More details can be found here..
I divided the solution of the problem into four parts:
1. Visualization of the program 2. Import of data into SQL 3. Data processing 4. Parsing of the XML file.
The code itself can be viewed on github.com and not read further!
Visualization of the program.
Для визуализации я использовал библиотеку Swing. Главный экран состоит из полей ввода, меток, двух кнопок, полосы загрузки и окна сообщений. DB URL — это специальная строка, имеющая следующий формат: jdbc:subprotocol:subname,
где subprotocol — имя драйвера или имя механизма подключения (mysql), subname — это строка, в которой указывается хост, порт, имя базы данных(//localhost/).
Для нашего случая: jdbc:mysql://localhost/ User — поле ввода пользователя базы данных. Password – поле ввода пароля базы данных. DB Name- the name of the database to be created or mounted for writing. FilePath - the name of the file from which we will take data.
Connect - check connection to the Start database - start import.
The Start button is not initially activated, and is activated after a successful connection to the database.
CREATE TABLE IF NOT EXISTS node (
id INT (10) UNSIGNED NOT NULL,
lat FLOAT (10,7) NOT NULL,
lon FLOAT (10,7) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS way (
id INT (10) UNSIGNED NOT NULL
,PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS relation (
id INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS nd (
id INT (10) UNSIGNED NOT NULL
,id_way INT (10) UNSIGNED NOT NULL,
id_node INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_way) REFERENCES way(id),
FOREIGN KEY (id_node) REFERENCES node(id)
);
CREATE TABLE IF NOT EXISTS tag_key (
id INT (10) UNSIGNED NOT NULL,
k VARCHAR(25) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS tag_value (
id INT (10) UNSIGNED NOT NULL,
v VARCHAR(255) NOT NULL,
id_tag_key INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_tag_key) REFERENCES tag_key(id)
);
CREATE TABLE IF NOT EXISTS node_tag (
id INT (10) UNSIGNED NOT NULL,
id_node INT (10) UNSIGNED NOT NULL,
id_tag INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_node) REFERENCES node(id),
FOREIGN KEY (id_tag) REFERENCES tag_value(id)
);
CREATE TABLE IF NOT EXISTS way_tag (
id INT (10) UNSIGNED NOT NULL,
id_way INT (10) UNSIGNED NOT NULL,
id_tag INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_way) REFERENCES way(id),
FOREIGN KEY (id_tag) REFERENCES tag_value(id)
);
CREATE TABLE IF NOT EXISTS relation_tag (
id INT (10) UNSIGNED NOT NULL,
id_relation INT (10) UNSIGNED NOT NULL,
id_tag INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_relation) REFERENCES relation(id),
FOREIGN KEY (id_tag) REFERENCES tag_value(id)
);
CREATE TABLE IF NOT EXISTS role (
id INT (10) UNSIGNED NOT NULL,
v VARCHAR(25) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS member_node (
id INT (10) UNSIGNED NOT NULL,
id_node INT (10) UNSIGNED NOT NULL,
id_relation INT (10) UNSIGNED NOT NULL,
id_role INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_relation) REFERENCES relation(id),
FOREIGN KEY (id_role) REFERENCES role(id)
);
CREATE TABLE IF NOT EXISTS member_way (
id INT (10) UNSIGNED NOT NULL,
id_way INT (10) UNSIGNED NOT NULL,
id_relation INT (10) UNSIGNED NOT NULL,
id_role INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),FOREIGN KEY (id_relation) REFERENCES relation(id),
FOREIGN KEY (id_role) REFERENCES role(id)
);
CREATE TABLE IF NOT EXISTS member_relation (
id INT (10) UNSIGNED NOT NULL,
id_rel INT (10) UNSIGNED NOT NULL,
id_relation INT (10) UNSIGNED NOT NULL,
id_role INT (10) UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (id_relation) REFERENCES relation(id),
FOREIGN KEY (id_role) REFERENCES role(id)
);
INSERT INTO `tag_key` (`id`,`k`)
VALUES
('1', 'aerialway'),('2', 'aeroway'),('3', 'amenity'),('4', 'barrier'),('5', 'boundary'),('6', 'building'),('7', 'craft'),('8', 'emergency'),('9', 'geological'),('10', 'highway'),('11', 'historic'),('12', 'landuse'),('13', 'leisure'),('14', 'man_made'),('15', 'military'),('16', 'natural'),('17', 'office'),('18', 'place'),('19','cycleway'),('20','bridge'),('21', 'power'),('22', 'public_transport'),('23', 'railway'),('24', 'route'),('25', 'shop'),('26', 'sport'),('27', 'tourism'),('28', 'waterway'),('29','tunnel'),('30','type'),('31','admin_level'),('100', 'addr:housenumber'),('101', 'addr:housename'),('102', 'addr:street'),('103', 'addr:place'),('104', 'addr:postcode'),('105', 'addr:city'),('106', 'addr:country'),('107', 'addr:province'),('108', 'addr:state'),('109', 'addr:interpolation'),('110', 'attribution'),('111', 'description'),('112', 'email'),('113', 'fax'),('114', 'phone'),('115', 'name'),('116', 'official_name');
Description of the created tables:
node : id unique key, lat, lon - coordinates. way : id unique key. relation : id unique key. nd : id is a unique key (the counter is in the program ), id_way is a link to the id of the way table , id_node is a link to the id of the node table . tag_key : id unique key, k - text value ( key description ) tag_value :id уникальный ключ(счетчик идет в программе), v — текстовое значение (значение ключа), id_tag_key – ссылка на id в таблице tag_key. node_tag: id уникальный ключ(счетчик идет в программе), id_node – ссылка на id таблицы node, id_tag – ссылка на id в таблице tag_value. way _ tag: id уникальный ключ(счетчик идет в программе), id_way – ссылка на id таблицы way, id_tag – ссылка на id в таблице tag_value. relation_tag: id уникальный ключ(счетчик идет в программе), id_relation – ссылка на id таблицы relation, id_tag – ссылка на id в таблице tag_value. role: id уникальный ключ(счетчик идет в программе), v — текстовое значение (значение атрибута). member_node : id уникальный ключ(счетчик идет в программе общий для всех member), id_node – ссылка на id таблицы node, id_relation – ссылка на id таблицы relation. member_way : id уникальный ключ(счетчик идет в программе общий для всех member), id_way – ссылка на id таблицы way, id_relation – ссылка на id таблицы relation. member_ relation: id уникальный ключ(the counter is in the program common for all member ), id_rel - link to the id of the relation table , id_relation - link to the id of the relation table .
The code
public final class SqlDriver {
private long iTagKey;
private long iTagUK;
private long iTagValue;
private long iTagUValue;
private long iNd;
private long iTagNode;
private long iTagWay;
private long iTagRelation;
private long iMember;
private long iRole;
private Statement statement;
private Connection connection;
private Window window;
private Element e;
public SqlDriver(Window w) {
this.window = w;
this.iRole = 1;
this.iNd = 1;
this.iMember = 1;
this.iTagNode = 1;
this.iTagWay = 1;
this.iTagRelation = 1;
this.iTagUK = 1;
this.iTagUValue = 1;
this.iTagValue = 1;
this.e = new Element("node", 0);
}
//Поиск объектов в базе данных, если найден хотя бы один, вернет true и удалит все атрибуты для данного объекта
private boolean initStart() {
boolean result = false;
if (update("USE " + window.getDbNameValue().getText()) >= 0) {
try {
ResultSet rs = execute("SELECT * FROM `relation` ORDER BY `id` DESC LIMIT 1");
if (rs != null) {
if (rs.next()) {
long id = rs.getLong("id");
update("DELETE FROM `member_node` WHERE `id_relation` = "
+ id);
update("DELETE FROM `member_way` WHERE `id_relation` = "
+ id);
update("DELETE FROM `member_relation` WHERE `id_relation` = "
+ id);
update("DELETE FROM `relation_tag` WHERE `id_relation` = "
+ id);
this.e = new Element("relation", id);
rs.close();
rs = null;
return true;
}
}
rs = execute("SELECT * FROM `way` ORDER BY `id` DESC LIMIT 1");
if (rs != null) {
if (rs.next()) {
long id = rs.getLong("id");
update("DELETE FROM `way_tag` WHERE `id_way` = " + id);
update("DELETE FROM `nd` WHERE `id_way` = " + id);
this.e = new Element("way", id);
rs.close();
rs = null;
return true;
}
}
rs = execute("SELECT * FROM `node` ORDER BY `id` DESC LIMIT 1");
if (rs != null) {
if (rs.next()) {
long id = rs.getLong("id");
update("DELETE FROM `node_tag` WHERE `id_node` = " + id);
this.e = new Element("node", id);
rs.close();
rs = null;
return true;
}
}
} catch (SQLException e) {
System.out.println("Ошибка поиска последнего элемента");
}
}
return result;
}
//Установка начальных индексов(счетчиков) для атрибутов
private void setIndex() {
try {
ResultSet rs = execute("SELECT `id` FROM `member_node` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iMember = rs.getLong("id");
System.out.println("iMemberNode: " + iMember);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `member_relation` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iMember = iMember > rs.getLong("id") ? iMember : rs
.getLong("id");
System.out.println("iMemberRelation: " + iMember);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `member_way` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iMember = iMember > rs.getLong("id") ? iMember : rs
.getLong("id");
System.out.println("iMemberWay: " + iMember);
}
rs.close();
rs = null;
iMember++;
rs = execute("SELECT `id` FROM `nd` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iNd = rs.getLong("id") + 1;
System.out.println("iNd: " + iNd);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `node_tag` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iTagNode = rs.getLong("id") + 1;
System.out.println("iTagNode: " + iTagNode);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `relation_tag` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iTagRelation = rs.getLong("id") + 1;
System.out.println("iTagRelation: " + iTagRelation);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `role` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iRole = rs.getLong("id") + 1;
System.out.println("iRole: " + iRole);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `tag_value` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iTagValue = rs.getLong("id") + 1;
System.out.println("iTagValue: " + iTagValue);
}
rs.close();
rs = null;
rs = execute("SELECT `id` FROM `way_tag` ORDER BY `id` DESC LIMIT 1");
if (rs.next()) {
iTagWay = rs.getLong("id") + 1;
System.out.println("iTagWay: " + iTagWay);
}
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
//Загрузка схемы если БД не создана или продолжение загрузки в существующую
public void loadSchema() {
if (initStart()) {
window.addLog("Таблица уже создана");
setIndex();
} else {
window.addLog("Загружаем схему");
update("CREATE DATABASE IF NOT EXISTS "
+ window.getDbNameValue().getText());
update("USE " + window.getDbNameValue().getText());
getShema("shema.sh");
}
}
//Установка соединения с БД
public boolean getConnection() {
String url = window.getUrlValue().getText();
String user = window.getUserValue().getText();
String pass = window.getPassValue().getText();
window.addLog("Connected to: " + url);
boolean result = false;
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
connection = DriverManager.getConnection(url, user, pass);
if (connection != null) {
window.addLog("Connection Successful !\n");
result = true;
}
if (connection == null) {
window.addLog("Connection Error !\n");
result = false;
}
statement = connection.createStatement();
} catch (SQLException e) {
window.addLog(e.toString());
result = false;
}
return result;
}
public int update(String sql) {
int rs = -1;
try {
rs = statement.executeUpdate(sql);
} catch (SQLException e) {
}
System.out.println("sql [" + rs + "]-> " + sql);
return rs;
}
public ResultSet execute(String sql) {
ResultSet rs = null;
try {
rs = this.statement.executeQuery(sql);
} catch (SQLException e) {
System.out.println("sql [ ]<- " + sql);
}
return rs;
}
//Создание списка ключей
public ArrayList getTagKey() {
ArrayList tagKey = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_key`");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("k");
Element e = new Element(name, id);
tagKey.add(e);
}
rs.close();
rs = null;
return tagKey;
} catch (SQLException e) {
e.printStackTrace();
}
return tagKey;
}
//Создание списка значений для ключей
public ArrayList getHouseNumber() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 100");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 100);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
public ArrayList getCity() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 105");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 105);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
public ArrayList getStreet() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 102");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 102);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
public ArrayList getPostCode() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 104");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 104);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
public ArrayList getName() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 115");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 115);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
public ArrayList getCountry() {
ArrayList tag = new ArrayList();
ResultSet rs = execute("SELECT * FROM `tag_value` WHERE `id_tag_key` = 106");
try {
while (rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("v");
TagElement e = new TagElement(id, name, 32);
tag.add(e);
}
rs.close();
rs = null;
return tag;
} catch (SQLException e) {
e.printStackTrace();
}
return tag;
}
//импорт данных в таблицы, через подготовленный запрос.
public boolean insertNode(long id, float lat, float lon) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `node`(`id`, `lat`, `lon`) VALUES (?,?,?)");
ps.setLong(1, id);
ps.setFloat(2, lat);
ps.setFloat(3, lon);
ps.executeUpdate();
ps.close();
ps = null;
result = true;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `node`(`id`, `lat`, `lon`) VALUES ("
+ id + ", " + lat + ", " + lon + ")");
}
return result;
}
public boolean insertWay(long id) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `way`(`id`) VALUES (?)");
ps.setLong(1, id);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
} catch (SQLException e) {
System.out.println("Ошибка! INSERT INTO `way`(`id`) VALUES (" + id
+ ")");
}
return result;
}
public boolean insertRelation(long id) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `relation`(`id`) VALUES (?)");
ps.setLong(1, id);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
} catch (SQLException e) {
System.out.println("Ошибка! INSERT INTO `relation`(`id`) VALUES ("
+ id + ")");
}
return result;
}
public boolean insertNd(long idWay, long idNode) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `nd`(`id`,`id_way`,`id_node`) VALUES (?,?,?)");
ps.setLong(1, this.iNd);
ps.setLong(2, idWay);
ps.setLong(3, idNode);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
this.iNd++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `nd`(`id`,`id_way`,`id_node`) VALUES ("
+ this.iNd + ", " + idWay + ", " + idNode + ")");
}
return result;
}
public boolean insertTagKey(String k) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `tag_key`(`id`,`k`) VALUES (?,?)");
ps.setLong(1, iTagKey);
ps.setString(2, k);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagKey++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `tag_key`(`id`,`k`) VALUES ("
+ iTagKey + ", " + k + ")");
}
return result;
}
public boolean insertUcertainKey(String k) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `uncertain_key`(`id`,`k`) VALUES (?,?)");
ps.setLong(1, iTagUK);
ps.setString(2, k);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagUK++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `uncertain_key`(`id`,`k`) VALUES ("
+ iTagUK + ", " + k + ")");
}
return result;
}
public boolean insertTagValue(String v, Long id) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `tag_value`(`id`,`v`,`id_tag_key`) VALUES (?,?,?)");
ps.setLong(1, iTagValue);
ps.setString(2, v);
ps.setLong(3, id);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagValue++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `tag_value`(`id`,`v`,,`id_tag_key) VALUES ("
+ iTagValue + ", " + v + "," + id + ")");
}
return result;
}
public boolean insertUcertainValue(String v, int idKey) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `uncertain_value`(`id`,`v`,`id_tag_key`) VALUES (?,?,?)");
ps.setLong(1, iTagUValue);
ps.setString(2, v);
ps.setInt(3, idKey);
ps.executeUpdate();
ps.close();
ps = null;
result = true;
iTagUValue++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `uncertain_value`(`id`,`v`,`id_tag_key) VALUES ("
+ iTagUValue + ", " + v + "," + idKey + ")");
}
return result;
}
public boolean insertNodeTag(long idNode, long idTag) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `node_tag`(`id`,`id_node`,`id_tag`) VALUES (?,?,?)");
ps.setLong(1, iTagNode);
ps.setLong(2, idNode);
ps.setLong(3, idTag);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagNode++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `node_tag`(`id`,`id_node`,`id_tag) VALUES ("
+ iTagNode + ", " + idNode + "," + idTag + ")");
}
return result;
}
public boolean insertWayTag(long idWay, long l) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `way_tag`(`id`,`id_way`,`id_tag`) VALUES (?,?,?)");
ps.setLong(1, iTagWay);
ps.setLong(2, idWay);
ps.setLong(3, l);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagWay++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `way_tag`(`id`,`id_way`,`id_tag) VALUES ("
+ iTagWay + ", " + idWay + "," + l + ")");
}
return result;
}
public boolean insertRelationTag(long idRelation, long idValue) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `relation_tag`(`id`,`id_relation`,`id_tag`) VALUES (?,?,?)");
ps.setLong(1, iTagRelation);
ps.setLong(2, idRelation);
ps.setLong(3, idValue);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iTagRelation++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `relation_tag`(`id`,`id_relation`,`id_tag) VALUES ("
+ iTagRelation
+ ", "
+ idRelation
+ ","
+ idValue
+ ")");
}
return result;
}
public boolean insertMemberNode(long idNode, long idRelation, long idRole) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `member_node` (`id`,`id_node`, `id_relation`, `id_role` ) VALUES (?,?,?,?)");
ps.setLong(1, iMember);
ps.setLong(2, idNode);
ps.setLong(3, idRelation);
ps.setLong(4, idRole);
ps.executeUpdate();
result = true;
ps.close();
ps = null;
iMember++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `member_node`(`id`,`id_node`,`id_relation`, `id_role`) VALUES ("
+ iMember
+ ", "
+ idNode
+ ","
+ idRelation
+ ","
+ idRole + ")");
}
return result;
}
public boolean insertMemberWay(long idWay, long idRelation, long idRole) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `member_way` (`id`,`id_way`, `id_relation`, `id_role` ) VALUES (?,?,?,?)");
ps.setLong(1, iMember);
ps.setLong(2, idWay);
ps.setLong(3, idRelation);
ps.setLong(4, idRole);
ps.executeUpdate();
ps.close();
ps = null;
result = true;
iMember++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `member_way`(`id`,`id_way`,`id_relation`, `id_role`) VALUES ("
+ iMember
+ ", "
+ idWay
+ ","
+ idRelation
+ ","
+ idRole + ")");
}
return result;
}
public boolean insertMemberRelation(long idRel, long idRelation, long idRole) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `member_relation` (`id`,`id_rel`, `id_relation`, `id_role` ) VALUES (?,?,?,?)");
ps.setLong(1, iMember);
ps.setLong(2, idRel);
ps.setLong(3, idRelation);
ps.setLong(4, idRole);
ps.executeUpdate();
ps.close();
ps = null;
result = true;
iMember++;
} catch (SQLException e) {
System.out
.println("Ошибка! INSERT INTO `member_relation`(`id`,`id_way`,`id_relation`, `id_role`) VALUES ("
+ iMember
+ ", "
+ idRel
+ ","
+ idRelation
+ ","
+ idRole + ")");
}
return result;
}
public boolean insertRole(String v) {
boolean result = false;
try {
PreparedStatement ps = connection
.prepareStatement("INSERT INTO `role` (`id`,`v`) VALUES (?,?)");
ps.setLong(1, iRole);
ps.setString(2, v);
ps.executeUpdate();
ps.close();
ps = null;
result = true;
iRole++;
} catch (SQLException e) {
System.out.println("Ошибка" + e.getMessage()
+ "! INSERT INTO `role`(`id`,`v`) VALUES (" + iRole + ", "
+ v + ")");
}
return result;
}
//Загрузка файла схемы
private void getShema(String file) {
BufferedReader shema = null;
try {
shema = new BufferedReader(new FileReader(file));
String line;
line = shema.readLine();
while (line != null) {
update(line);
line = shema.readLine();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
shema.close();
shema = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
Logics
//ver 1.0
public class LogicOSM {
private Element eParent;
private SqlDriver sql;
private ArrayList role;
private ArrayList tagKey;
private ArrayList tagValue;
private ArrayList houseNumber;
private ArrayList postCode;
private ArrayList street;
private ArrayList name;
private ArrayList country;
public LogicOSM(SqlDriver sql) {
this.sql = sql;
//Загрузка списка элементов
this.tagKey = sql.getTagKey();
this.tagValue = sql.getTagValue();
this.houseNumber = sql.getHouseNumber();
this.postCode = sql.getPostCode();
this.street = sql.getStreet();
this.postCode = sql.getPostCode();
this.name = sql.getName();
this.country = sql.getCountry();
this.role = new ArrayList();
}
//Возвращаем id ключа по имени
public long getTagKeyId(String key) {
long id = -1;
for (Element e : tagKey) {
if (e.getName().equals(key)) {
id = e.getId();
return id;
}
}
return id;
}
//Возвращаем элемент Tag, если найден и добавляем в таблицу если новый
public TagElement getTag(Long id, String value) {
TagElement tagElement;
if (id < 100) {
for (TagElement tE : this.tagValue) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.tagValue.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else if (id == 100) {
for (TagElement tE : this.houseNumber) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.houseNumber.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else if (id == 102) {
for (TagElement tE : this.street) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.street.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else if (id == 104) {
for (TagElement tE : this.postCode) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.postCode.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else if (id == 105) {
for (TagElement tE : this.city) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.city.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
}
else if (id == 106) {
for (TagElement tE : this.country) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.country.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else if (id == 115) {
for (TagElement tE : this.name) {
if ((tE.getName().equals(value)) && (tE.getKeyId() == id)) {
tagElement = new TagElement(tE.getId(), value, id);
return tagElement;
}
}
tagElement = new TagElement(sql.getiTagValue(), value, id);
this.name.add(tagElement);
sql.insertTagValue(value, id);
return tagElement;
} else {
tagElement = new TagElement(sql.getiTagValue(), value, id);
sql.insertTagValue(value, id);
return tagElement;
}
}
//Возвращаем индекс role
public long getRoleIndex(String r) {
long index = 1;
for (Element e : this.role) {
if (e.getName().equals(r)) {
index = e.getId();
return index;
}
}
sql.insertRole(r);
index = sql.getiRole();
Element e = new Element(r, index);
role.add(e);
return index;
}
//Метод вызывается из парсера, на входе имя элемента и его атрибуты
//Далее определяем объект, создаем его и записываем данные в таблицу
public void newElement(String eName, Attributes attr) {
switch (eName) {
case "node":
Node node = new Node(attr);
eParent = null;
eParent = new Element("node", node.getId());
sql.insertNode(node.getId(), node.getLat(), node.getLon());
node = null;
break;
case "way":
Way way = new Way(attr);
eParent = null;
eParent = new Element("way", way.getId());
sql.insertWay(way.getId());
way = null;
break;
case "relation":
Relation relation = new Relation(attr);
eParent = null;
eParent = new Element("relation", relation.getId());
sql.insertRelation(relation.getId());
relation = null;
break;
case "nd":
Nd nd = new Nd(attr);
sql.insertNd(eParent.getId(), nd.getRef());
nd = null;
break;
case "member":
Member member = new Member(attr);
long idRole = this.getRoleIndex(member.getRole());
if (member.getType().equals("node")) {
sql.insertMemberNode(member.getRef(), eParent.getId(), idRole);
} else if (member.getType().equals("way")) {
sql.insertMemberWay(member.getRef(), eParent.getId(), idRole);
} else if (member.getType().equals("relation")) {
sql.insertMemberRelation(member.getRef(), eParent.getId(),
idRole);
} else {
// error
}
member = null;
break;
case "tag":
Tag tag = new Tag(attr);
long keyId = getTagKeyId(tag.getK());
if (keyId > 0) {
TagElement tagElement = this.getTag(keyId, tag.getV());
if (eParent.getName().equals("node")) {
sql.insertNodeTag(eParent.getId(), tagElement.getId());
} else if (eParent.getName().equals("way")) {
sql.insertWayTag(eParent.getId(), tagElement.getId());
} else if (eParent.getName().equals("relation")) {
sql.insertRelationTag(eParent.getId(), tagElement.getId());
} else {
// error
}
}
tag = null;
break;
}
}
}
SAX XML parser
Since I used the Progress Bar in the window, the file was read twice, in the first we count the number of lines, in the second we write to the database.
