source: subversion/sites/www.openstreetbrowser.org/src/pgsql_simple_load.sql @ 29773

Last change on this file since 29773 was 19108, checked in by skunk, 11 years ago

Changes to comply with osmosis-0.31

  • new schema/load scripts
File size: 3.6 KB
Line 
1-- Drop all primary keys and indexes to improve load speed.
2ALTER TABLE nodes DROP CONSTRAINT pk_nodes;
3ALTER TABLE ways DROP CONSTRAINT pk_ways;
4ALTER TABLE way_nodes DROP CONSTRAINT pk_way_nodes;
5ALTER TABLE relations DROP CONSTRAINT pk_relations;
6-- DROP INDEX idx_nodes_action;
7DROP INDEX idx_node_tags_node_id;
8-- DROP INDEX idx_nodes_geom;
9-- DROP INDEX idx_ways_action;
10DROP INDEX idx_way_tags_way_id;
11DROP INDEX idx_way_nodes_node_id;
12-- DROP INDEX idx_relations_action;
13DROP INDEX idx_relation_tags_relation_id;
14-- DROP INDEX idx_ways_bbox;
15-- DROP INDEX idx_ways_linestring;
16
17-- Comment these out if the COPY files include bbox or linestring column values.
18-- SELECT DropGeometryColumn('ways', 'bbox');
19-- SELECT DropGeometryColumn('ways', 'linestring');
20
21-- Import the table data from the data files using the fast COPY method.
22-- COPY nodes FROM E'C:\\tmp\\pgimport\\nodes.txt';
23-- COPY node_tags FROM E'C:\\tmp\\pgimport\\node_tags.txt';
24-- COPY ways FROM E'C:\\tmp\\pgimport\\ways.txt';
25-- COPY way_tags FROM E'C:\\tmp\\pgimport\\way_tags.txt';
26-- COPY way_nodes FROM E'C:\\tmp\\pgimport\\way_nodes.txt';
27-- COPY relations FROM E'C:\\tmp\\pgimport\\relations.txt';
28-- COPY relation_tags FROM E'C:\\tmp\\pgimport\\relation_tags.txt';
29-- COPY relation_members FROM E'C:\\tmp\\pgimport\\relation_members.txt';
30
31-- or do it this way
32\copy nodes FROM 'nodes.txt'
33\copy node_tags FROM 'node_tags.txt'
34\copy ways FROM 'ways.txt'
35\copy way_tags FROM 'way_tags.txt'
36\copy way_nodes FROM 'way_nodes.txt'
37\copy relations FROM 'relations.txt'
38\copy relation_tags FROM 'relation_tags.txt'
39\copy relation_members FROM 'relation_members.txt'
40
41-- Add the primary keys and indexes back again (except the way bbox index).
42ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
43ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
44ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
45ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
46-- CREATE INDEX idx_nodes_action ON nodes USING btree (action);
47CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
48-- CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
49-- this would take too much time
50-- CREATE INDEX idx_ways_action ON ways USING btree (action);
51CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
52CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
53-- CREATE INDEX idx_relations_action ON relations USING btree (action);
54CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
55
56-- Comment these out if the COPY files include bbox or linestring column values.
57-- SELECT AddGeometryColumn('ways', 'bbox', 4326, 'GEOMETRY', 2);
58-- SELECT AddGeometryColumn('ways', 'linestring', 4326, 'GEOMETRY', 2);
59
60-- Comment these out if the COPY files include bbox or linestring column values.
61-- Update the bbox column of the way table.
62-- UPDATE ways SET bbox = (
63--      SELECT Envelope(Collect(geom))
64--      FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id
65--      WHERE way_nodes.way_id = ways.id
66-- );
67-- Update the linestring column of the way table.
68-- UPDATE ways w SET linestring = (
69--      SELECT MakeLine(c.geom) AS way_line FROM (
70--              SELECT n.geom AS geom
71--              FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id
72--              WHERE (wn.way_id = w.id) ORDER BY wn.sequence_id
73--      ) c
74-- )
75
76-- Index the way bounding box column.
77-- CREATE INDEX idx_ways_bbox ON ways USING gist (bbox);
78-- CREATE INDEX idx_ways_linestring ON ways USING gist (linestring);
79
80-- Perform database maintenance due to large database changes.
81VACUUM ANALYZE;
Note: See TracBrowser for help on using the repository browser.