[OSM-dev] Slow Osmosis

[Stefan de Konink]

Andreas Kalsch wrote:
> I decompress the data before putting them into Osmosis, but it's still 
> slow.
> So back to my question -- ;)
> (The best would be raw dump files for MySQL's "LOAD DATA INFILE" - I can 
> imagine that it would be pretty quick)

If you want to try your luck I can provide you a program that is capable 
to exactly produce the CSV files that you would require. But I don't 
follow the MySQL schema myself.


If you think you can convert the following in something that would be 
what your MySQL database requires the output tables can probably be 
useful for you.


Stefan

START TRANSACTION;
CREATE TABLE nodes_legacy (id serial, long double, lat double, username 
varchar(255), timestamp timestamptz);
CREATE TABLE node_tags (node integer, k varchar(255), v varchar(1024), 
primary key (node, k), foreign key(node) references nodes_legacy);
CREATE TABLE ways (id serial,username varchar(255), timestamp timestamptz);
CREATE TABLE way_tags (way integer, k varchar(255), v varchar(1024), 
primary key (way, k), foreign key(way) references ways);
CREATE TABLE way_nds (way integer, idx integer, to_node integer, foreign 
key(way) references ways, foreign key(to_node) references nodes_legacy, 
primary key(way, idx));
CREATE TABLE relations(id serial, username varchar(255), timestamp 
timestamptz);
CREATE TABLE relation_members_node (relation integer, idx integer, 
to_node integer, role varchar(255), foreign key(relation) references 
relations, foreign key(to_node) references nodes_legacy, primary 
key(relation, idx));
CREATE TABLE relation_members_relation (relation integer, idx integer, 
to_relation integer, role varchar(255), foreign key(relation) references 
relations, foreign key(to_relation) references relations, primary 
key(relation, idx));
CREATE TABLE relation_members_way (relation integer, idx integer, to_way 
integer, role varchar(255), foreign key(relation) references relations, 
foreign key(to_way) references ways, primary key(relation, idx));
CREATE TABLE relation_tags (relation integer, k varchar(255), v 
varchar(1024), foreign key(relation) references relations, primary 
key(relation, k));
COPY 279956849 RECORDS INTO nodes_legacy from '/mnt/data2/csv/nodes.csv' 
USING DELIMITERS ',', '\n', '''';
COPY 773410980 RECORDS INTO node_tags from 
'/mnt/data2/csv/node_tags.csv' USING DELIMITERS ',', '\n', '''';
COPY 22859478 RECORDS INTO ways from '/mnt/data2/csv/ways.csv' USING 
DELIMITERS ',', '\n', '''';
COPY 171779781 RECORDS INTO way_tags from '/mnt/data2/csv/way_tags.csv' 
USING DELIMITERS ',', '\n', '''';
COPY 311898699 RECORDS INTO way_nds from '/mnt/data2/csv/way_nds.csv' 
USING DELIMITERS ',', '\n', '''';
COPY 43411 RECORDS INTO relations from '/mnt/data2/csv/relations.csv' 
USING DELIMITERS ',', '\n', '''';
COPY 153421 RECORDS INTO relation_tags from 
'/mnt/data2/csv/relation_tags.csv' USING DELIMITERS ',', '\n', '''';
COPY 23533 RECORDS INTO relation_members_node from 
'/mnt/data2/csv/relation_member_node.csv' USING DELIMITERS ',', '\n', '''';
COPY 1222 RECORDS INTO relation_members_relation from 
'/mnt/data2/csv/relation_member_relation.csv' USING DELIMITERS ',', 
'\n', '''';
COPY 435327 RECORDS INTO relation_members_way from 
'/mnt/data2/csv/relation_member_way.csv' USING DELIMITERS ',', '\n', '''';
COMMIT;