Hi all,
One of the last things I wanted to add was 'native' update support for
database to database transactions. Currently known as OSMChangeset; the
attachment is what I import into the database and the following file
generates it.
http://repo.or.cz/w/handlerosm.git?a=blob;f=osmsucker-ywk.c;hb=HEAD
For Monet it is extremely fast less than 15mins to push it through. I
realise that the pgsql people need some horizontal merging, but the tool
might inspire some more people. What is also pretty unique, is that the
tool actually takes care of references that cannot exists anymore when a
changeset contains deletes.
As can be seen in the example I first delete the actual deletes from the
way_nds and relation_members tables. After that the temporary table with
delete ids is added with the modified ids. They all get deleted, and
reinserted like a normal planet import.
There are still come catches, but this makes my life much easier and
might other toolmakers to take a different approach in updating.
Stefan
START TRANSACTION;
CREATE TEMPORARY TABLE delete_relations (id integer);
CREATE TEMPORARY TABLE delete_ways (id integer);
CREATE TEMPORARY TABLE delete_nodes (id integer);
COPY 8 RECORDS INTO delete_relations FROM
'/export/data1/konink/test/delete_relations.csv' USING DELIMITERS ',', '\n',
'''';
COPY 2679 RECORDS INTO delete_ways FROM
'/export/data1/konink/test/delete_ways.csv' USING DELIMITERS ',', '\n', '''';
COPY 56271 RECORDS INTO delete_nodes FROM
'/export/data1/konink/test/delete_nodes.csv' USING DELIMITERS ',', '\n', '''';
DELETE FROM relation_members_relation WHERE to_relation IN (SELECT id FROM
delete_relations);
DELETE FROM relation_members_way WHERE to_way IN (SELECT id FROM delete_ways);
DELETE FROM relation_members_node WHERE to_node IN (SELECT id FROM
delete_nodes);
DELETE FROM way_nds WHERE to_node IN (SELECT id FROM delete_nodes);
COPY 575 RECORDS INTO delete_relations FROM
'/export/data1/konink/test/modify_relations.csv' USING DELIMITERS ',', '\n',
'''';
COPY 33086 RECORDS INTO delete_ways FROM
'/export/data1/konink/test/modify_ways.csv' USING DELIMITERS ',', '\n', '''';
COPY 263133 RECORDS INTO delete_nodes FROM
'/export/data1/konink/test/modify_nodes.csv' USING DELIMITERS ',', '\n', '''';
DELETE FROM relation_members_relation WHERE relation IN (SELECT id FROM
delete_relations);
DELETE FROM relation_members_way WHERE relation IN (SELECT id FROM
delete_relations);
DELETE FROM relation_members_node WHERE relation IN (SELECT id FROM
delete_relations);
DELETE FROM relation_tags WHERE relation IN (SELECT id FROM delete_relations);
DELETE FROM relations WHERE id IN (SELECT id FROM delete_relations);
DROP TABLE delete_relations;
DELETE FROM way_nds WHERE way IN (SELECT id FROM delete_ways);
DELETE FROM way_tags WHERE way IN (SELECT id FROM delete_ways);
DELETE FROM ways WHERE id IN (SELECT id FROM delete_ways);
DROP TABLE delete_ways;
DELETE FROM node_tags WHERE node IN (SELECT id FROM delete_nodes);
DELETE FROM nodes_legacy WHERE id IN (SELECT id FROM delete_nodes);
DROP TABLE delete_nodes;
COPY 518515 RECORDS INTO nodes_legacy from
'/export/data1/konink/test/nodes.csv' USING DELIMITERS ',', '\n', '''';
COPY 188654 RECORDS INTO node_tags from
'/export/data1/konink/test/node_tags.csv' USING DELIMITERS ',', '\n', '''';
COPY 59460 RECORDS INTO ways from '/export/data1/konink/test/ways.csv' USING
DELIMITERS ',', '\n', '''';
COPY 281077 RECORDS INTO way_tags from '/export/data1/konink/test/way_tags.csv'
USING DELIMITERS ',', '\n', '''';
COPY 963761 RECORDS INTO way_nds from '/export/data1/konink/test/way_nds.csv'
USING DELIMITERS ',', '\n', '''';
COPY 934 RECORDS INTO relations from '/export/data1/konink/test/relations.csv'
USING DELIMITERS ',', '\n', '''';
COPY 4302 RECORDS INTO relation_tags from
'/export/data1/konink/test/relation_tags.csv' USING DELIMITERS ',', '\n', '''';
COPY 2727 RECORDS INTO relation_members_node from
'/export/data1/konink/test/relation_member_node.csv' USING DELIMITERS ',',
'\n', '''';
COPY 269 RECORDS INTO relation_members_relation from
'/export/data1/konink/test/relation_member_relation.csv' USING DELIMITERS ',',
'\n', '''';
COPY 78306 RECORDS INTO relation_members_way from
'/export/data1/konink/test/relation_member_way.csv' USING DELIMITERS ',', '\n',
'''';
COMMIT;
_______________________________________________
dev mailing list
dev@openstreetmap.org
http://lists.openstreetmap.org/listinfo/dev
