Pull to refresh

Импортируем OpenStreetMap в MySQL

Reading time 25 min
Views 9.2K
Однажды мне потребовалось узнать номера улиц и домов Минска. Каково же было мое разочарование, когда я узнал, что полных данных нигде нет, и что делать если появляются новые улицы и дома. Вот тут и пришел на выход OpenStreetMap с открытым кодом и постоянными обновлениями. Беда в том, что карты представляют собой xml документ, объёмом аж целых 2 Гб и информация о домах представлена в таком виде:

<way id="25324320" >
  <nd ref="275904968"/>
  <nd ref="275904882"/>
  <nd ref="275904881"/>
  <nd ref="275904969"/>
  <nd ref="275904968"/>
  <tag k="addr:housenumber" v="17"/>
  <tag k="addr:postcode" v="220013"/>
  <tag k="addr:street" v="улица Якуба Коласа"/>
  <tag k="building" v="yes"/>
 </way>  

Обрабатывать 1.5 Гб данных проще, когда данные упорядочены а не представлены в виде строк. Так мной было принято решения конвертировать данные в БД. Сказано – сделано, в качестве рабочего инструмента выбраны: Eclipse(Java SE) и джентельменски набор денвера.

Немого теории

Как я уже говорил, файл представляет собой xml документ, в котором последовательно описываются объекты точка (node), линия (way) и отношение (relation). У каждого из объектов могут быть служебные атрибуты, которые описывают их свойства. Схематично это можно представить так.

Схема

Node – точка. Базовый элемент, хранит координаты объекта: широта, долгота (lat, lon). У каждой точки свой уникальный id, который допускает совпадения с id way или relation. В XML нотации, объект данного типа будет выглядеть так:

<node id="1877995696" lat="53.9216820" lon="27.5883786"/>

Way –линия. Базовый элемент, описывает совокупность точек, имеет только один параметр id.
Совокупность точек описывается тегом nd, с единственным атрибутом ref, где ref это ссылка на id элемента node.
В XML нотации, объект данного типа будет выглядеть так:

<way id="83643843">
<nd ref="1270318960"/>
 <nd ref="974055589"/>
 <nd ref="974055636"/>
 <nd ref="974055581"/>
 <nd ref="974055604"/>
</way>

Relation – отношения. Базовый элемент, описывает совокупность объектов, имеет только один параметр id. Совокупность объектов описывается тегом member. Тег member состоит из трех атрибутов: type – тип объекта, ref – ссылка на id объекта, role – параметры ролей, описывает связь объектов между собой.

Для описания объектов существует тег Tag, он состоит из двух атрибутов k – key(ключ), v- value(значение). В этом теге заключена вся информация об объекте. Подробнее можно посмотреть здесь.

Решение задачи я поделил на четыре части:

1. Визуализация программы
2. Импорт данных в SQL
3. Обработка данных
4. Парсинг XML файла.
Сам код можно посмотреть на github.com и не читать дальше!

Визуализация программы.

Для визуализации я использовал библиотеку Swing. Главный экран состоит из полей ввода, меток, двух кнопок, полосы загрузки и окна сообщений.
DB URL — это специальная строка, имеющая следующий формат: jdbc:subprotocol:subname,
где subprotocol — имя драйвера или имя механизма подключения (mysql),
subname — это строка, в которой указывается хост, порт, имя базы данных(//localhost/).
Для нашего случая: jdbc:mysql://localhost/
User — поле ввода пользователя базы данных.
Password – поле ввода пароля базы данных.
DB Name — имя базы данных, которая будет создана или подключены для записи.
FilePath – название файла, из которого будем брать данные.

Connect – проверка подключения к БД
Start – начало импорта.
Кнопка Start изначально не активирована, и активируется после успешного подключения к БД.

Внешний вид окна.
image
Код

public class Window extends Thread {

	private JFrame window;
	private JTextField userValue;
	private JTextField passValue;
	private JTextField dbNameValue;
	private TextArea textArea;
	private JButton btnConnected;
	private JButton btnExport;
	private JTextField filePathValue;
	private JTextField urlValue;
	private JProgressBar progressBar;

	public Window() {
		initialize();
	}

	@Override
	public void run() {

	}

	private void initialize() {
		window = new JFrame();
		window.setTitle("OSMtoMySQL");
		window.setResizable(false);
		window.setBounds(100, 100, 420, 450);
		window.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		window.getContentPane().setLayout(null);

		JLabel dbUrl = new JLabel("DB URL");
		dbUrl.setBounds(10, 29, 100, 14);
		window.getContentPane().add(dbUrl);

		urlValue = new JTextField();
		urlValue.setText("jdbc:mysql://localhost/");
		urlValue.setBounds(120, 26, 203, 20);
		window.getContentPane().add(urlValue);
		urlValue.setColumns(10);

		JLabel user = new JLabel("User");
		user.setBounds(10, 54, 100, 14);
		window.getContentPane().add(user);

		userValue = new JTextField();
		userValue.setText("root");
		userValue.setBounds(120, 51, 203, 20);
		window.getContentPane().add(userValue);
		userValue.setColumns(10);

		JLabel pass = new JLabel("Password");
		pass.setBounds(10, 79, 100, 14);
		window.getContentPane().add(pass);

		passValue = new JTextField();
		passValue.setBounds(120, 76, 203, 20);
		window.getContentPane().add(passValue);
		passValue.setColumns(10);

		JLabel dbName = new JLabel("DB Name");
		dbName.setBounds(10, 104, 100, 14);
		window.getContentPane().add(dbName);

		dbNameValue = new JTextField();
		dbNameValue.setText("Belarus");

		dbNameValue.setBounds(120, 101, 203, 20);
		window.getContentPane().add(dbNameValue);
		dbNameValue.setColumns(10);

		btnConnected = new JButton("Connect");

		btnConnected.setBounds(120, 159, 89, 23);
		window.getContentPane().add(btnConnected);

		btnExport = new JButton("Start");
		btnExport.setBounds(234, 159, 89, 23);
		btnExport.setEnabled(false);
		window.getContentPane().add(btnExport);

		textArea = new TextArea();
		textArea.setEditable(false);
		textArea.setBounds(10, 237, 394, 175);
		window.getContentPane().add(textArea);

		JLabel filePath = new JLabel("FilePath");
		filePath.setBounds(10, 129, 46, 14);
		window.getContentPane().add(filePath);

		filePathValue = new JTextField();
		filePathValue.setText("BY.osm");
		filePathValue.setColumns(10);
		filePathValue.setBounds(120, 126, 203, 20);
		window.getContentPane().add(filePathValue);

		progressBar = new JProgressBar();
		progressBar.setMaximum(1000);
		progressBar.setBounds(10, 202, 394, 20);
		progressBar.setStringPainted(true);
		window.getContentPane().add(progressBar);

	}

	public void addLog(String str) {
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
		this.textArea.append(sdf.format(cal.getTime()) + " > " + str + "\n");
	}

	
}



База данных

Базу данных я представил в следующем виде.
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');



Описание созданных таблиц:

node : id уникальный ключ, lat, lon – координаты.
way: id уникальный ключ.
relation : id уникальный ключ.
nd : id уникальный ключ(счетчик идет в программе), id_way – ссылка на id таблицы way, id_node – ссылка на id таблицы node.
tag_key: id уникальный ключ, k – текстовое значение (описание ключа)
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 уникальный ключ(счетчик идет в программе общий для всех member), id_rel – ссылка на id таблицы relation, id_relation – ссылка на id таблицы relation.

Код


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<Element> getTagKey() {
		ArrayList<Element> tagKey = new ArrayList<Element>();
		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<TagElement> getHouseNumber() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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<TagElement> getCity() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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<TagElement> getStreet() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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<TagElement> getPostCode() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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<TagElement> getName() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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<TagElement> getCountry() {
		ArrayList<TagElement> tag = new ArrayList<TagElement>();
		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();
			}
		}
	}
}

Логика


//ver 1.0
public class LogicOSM {
	private Element eParent;
	private SqlDriver sql;
	private ArrayList<Element> role;
	private ArrayList<Element> tagKey;
	private ArrayList<TagElement> tagValue;
	private ArrayList<TagElement> houseNumber;
	private ArrayList<TagElement> postCode;
	private ArrayList<TagElement> street;
	private ArrayList<TagElement> name;
	private ArrayList<TagElement> 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<Element>();
	}
       //Возвращаем 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;
		}

	}
}

<source lang="java">


SAX парсер XML

Так как в окне я использовал Progress Bar чтение файла производилось два раза, в первом считаем количество строк, во втором производим запись в БД.
Конструктор XML

public class XML extends Thread {
	private Window window;
	private SqlDriver sql;
	public XML(SqlDriver sql, Window window )
	{
		this.window = window;
		this.sql = sql;
	}
	@Override
	public void run()
	{
		 SAXParserFactory factory = SAXParserFactory.newInstance();
	        factory.setValidating(false);
	        factory.setNamespaceAware(false);
	        SAXParser parser;
	        InputStream xmlData = null;
	        try
	        {
	          xmlData = new FileInputStream(window.getFilePathValue().getText());
	          parser = factory.newSAXParser();
	          
	          XMLReader reader = new XMLReader();
	          window.addLog("Приступили к чтению файла");
	          parser.parse(xmlData, reader);
	          window.addLog("Количествво строк: " + Long.toString(reader.getLine()));
	          window.addLog("node: " + Long.toString(reader.getNode()));
	          window.addLog("way: " + Long.toString(reader.getWay()));
	          window.addLog("relation: " + Long.toString(reader.getRelation()));
	          window.addLog("Производим запись в MySQL");
	          xmlData.close();
	          xmlData = new FileInputStream(window.getFilePathValue().getText());
	          XMLParser xml =new XMLParser(sql, window, reader.getLine());
	          parser.parse(xmlData, xml);
	        } catch (FileNotFoundException e)
	        {
	            e.printStackTrace();
	            // обработки ошибки, файл не найден
	        } catch (ParserConfigurationException e)
	        {
	            e.printStackTrace();
	            // обработка ошибки Parser
	        } catch (SAXException e)
	        {
	            e.printStackTrace();
	            // обработка ошибки SAX
	        } catch (IOException e)
	        {
	            e.printStackTrace();
	            // обработка ошибок ввода
	        } 
	}	 
	}

XML Reader

public class XMLReader extends DefaultHandler {
	private long line;
	private long node;
	private long way;
	private long relation;

	public XMLReader() {
		this.line = 0;
		this.node = 0;
		this.way = 0;
		this.relation = 0;
	}
	@Override
	public void startElement(String uri, String name, String eName,
			Attributes atts) {
		this.line++;
		if (eName.equals("way"))
			this.way++;
		if (eName.equals("node"))
			this.node++;
		if (eName.equals("relation"))
			this.relation++;
               }

	@Override
	public void endElement(String uri, String name, String eName) {

	}

	@Override
	public void startDocument() throws SAXException {
		super.startDocument();

	}

	@Override
	public void endDocument() throws SAXException {
		super.endDocument();

	}
}

XML Parser

public class XMLParser extends DefaultHandler {
	private int ipmplement;
	private long line;
	private LogicOSM logic;
	private Window widnow;
	private long onePercent;
	private long nextPercent;
	private boolean extension;
	private String elemName;
	private Long idStart;

	public XMLParser(SqlDriver sql, Window window, long maxLine) {
		this.line = 1;
		this.widnow = window;
		this.logic = new LogicOSM(sql);
		this.onePercent = (long) (maxLine / 1000);
		this.nextPercent = onePercent;
		if (sql.getE().getId() != 0) {
			this.extension = true;
			this.elemName = sql.getE().getName();
			this.idStart = sql.getE().getId();
			this.ipmplement = 0;
		} else
			this.extension = false;
	}

	@Override
	public void startElement(String uri, String name, String eName,
			Attributes atts) {
		if (ipmplement == 0) {
			// root element
		} else if (!extension) {
			logic.newElement(eName, atts);
		} else {
			if (eName.equals(this.elemName)) {
				Long id = Long.valueOf(atts.getValue("", "id"));
				if (id.equals(this.idStart)) {
					extension = false;
					this.widnow.addLog("Продолжает разбор");
					logic.newElement(eName, atts);
				}
			}
		}
		ipmplement++;
		this.line++;
		if (this.line > this.nextPercent) {
			this.nextPercent += this.onePercent;
			int curVal = this.widnow.getProgressBar().getValue();
			int newVal = curVal + 1;
			this.widnow.getProgressBar().setValue(newVal);
			this.widnow.getProgressBar().setString(
					String.valueOf(((double) newVal) / 10) + "%");
		}
	}

	@Override
	public void endElement(String uri, String name, String eName) {
		ipmplement--;
	}

	@Override
	public void startDocument() throws SAXException {

		this.widnow.addLog("Начало разбора документа!");
		if (extension) {
			this.widnow.addLog("Парсинг уже был запущен");
			this.widnow.addLog("Ищем элемент: " + this.elemName + " id="
					+ this.idStart);
		}
		super.startDocument();
	}

	@Override
	public void endDocument() throws SAXException {
		super.endDocument();
		this.widnow.addLog("Разбор документа окончен!");
		this.widnow.addLog("Количество строк: " + this.line);
	}
}


Ну и собственно контроллер

public class Controler{
	private final Window window;
	private final SqlDriver sql;
	
public Controler()
{
	this.window = new Window();
	window.start();
	this.sql = new SqlDriver(window);
}
public void init()
{
	System.out.println("Метод run из Controller");
	try {
		window.getFrame().setVisible(true);
		window.addLog("Hello");
		window.getConnected().addActionListener(new ActionListener() {
			 @Override 
	            public void actionPerformed(ActionEvent e) { 
				if(sql.getConnection()) sql.loadSchema();
				window.getConnected().setEnabled(false);
				window.getExport().setEnabled(true);
				}
		});
		window.getExport().addActionListener(new ActionListener() {
			 @Override 
	            public void actionPerformed(ActionEvent e) { 
				 window.addLog("Export");
				 window.getExport().setEnabled(false);
				 XML xml = new XML(sql, window);
				 xml.start();
			 }
		});
		
	} catch (Exception e) {
		e.printStackTrace();
	}
}

И результат

image
Tags:
Hubs:
+2
Comments 6
Comments Comments 6

Articles