Importing OpenStreetMap to MySQL

Once I needed to know the numbers of streets and houses in Minsk. What was my disappointment when I learned that there is no complete data anywhere, and what to do if new streets and houses appear. This is where OpenStreetMap came in with open source and constant updates. The trouble is that the cards are an xml document, as much as 2 GB and the information about houses is presented in this form:

<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> 

Handling 1.5 GB of data is easier when the data is ordered and not presented as strings. So I made a decision to convert the data into a database. No sooner said than done, Eclipse (Java SE) and gentlemen’s Denver set were chosen as the working tool.

Dumb theory

As I said before, the file is an xml document in which the objects ( node ), line ( way ) and relation ( relation ) are described successively. Each of the objects may have service attributes that describe their properties. Schematically it can be represented as follows.


')
Node - point. The base element stores the coordinates of the object: latitude, longitude ( lat , lon ). Each point has its own unique id , which allows matches with the id way or relation . In XML notation, an object of this type will look like this:

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

Way line. The base element, describes a set of points, has only one parameter id .
The set of points is described by the nd tag, with a single ref attribute, where ref is a link to the id of the node element.
In XML notation, an object of this type will look like this:

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

Relation - relationship. The base element, describing a collection of objects, has only one id parameter. The collection of objects is described by the member tag. The member tag consists of three attributes: type is the type of the object, ref is a link to the id of the object, role is the parameters of roles, describes the connection of objects with each other.

For the description of objects there is a tag 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 data into SQL
3. Data processing
4. Parsing XML file.
The code itself can be viewed on github.com and read on!

Visualization of the program.

For visualization, I used the Swing library. The main screen consists of input fields, labels, two buttons, a download bar and a message box.
DB URL is a special string that has the following format: jdbc: subprotocol: subname ,
where subprotocol is the name of the driver or the name of the connection mechanism ( mysql ),
subname is the string in which the host, port, database name is specified ( // localhost / ).
For our case: jdbc: mysql: // localhost /
User - the database user input field.
Password - database password entry field.
DB Name - the name of the database to be created or connected for writing.
FilePath - the name of the file from which we will take the data.

Connect - check connection to the database
Start - the beginning of the import.
The Start button is initially not activated, and is activated after successfully connecting to the database.

Appearance window.

Code

 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"); } } 

Database

I presented the database as follows.

 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 is a unique key.
relation : id unique key.
nd : id is the unique key (the counter is in the program ), id_way is the link to the id of the way table, id_node is the link to the id of the node table.
tag_key : id is a unique key, k is a text value ( key description )
tag_value : id is a unique key (the counter is in the program ), v is a text value ( key value ), id_tag_key is a link to id in the tag_key table.
node_tag : id is a unique key (the counter is in the program ), id_node is the link to the id of the node table, id_tag is the link to the id in the table tag_value .
way _ tag : id is a unique key (the counter is in the program ), id_way is the link to the id of the way table, id_tag is the link to the id in the table tag_value .
relation_tag : id is a unique key (the counter is in the program ), id_relation is a link to the id table of a relation , id_tag is a link to an id in the table tag_value .
role : id is the unique key (the counter is in the program ), v is the text value ( attribute value ).
member_node : id is a unique key (the counter is in the program common to all members ), id_node is the link to the id of the node table, id_relation is the link to the id of the relation table.
member_way : id is a unique key (the counter is in the program common to all members ), id_way is the link to the id of the way table, id_relation is the link to the id of the relation table.
member_ relation : id is a unique key (the counter is in the program common to all members ), id_rel is the link to the id of the relation table, id_relation is the link to the id of the relation table.

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<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(); } } } } 

Logics

 //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(); } } 

And the result