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

Last change on this file since 17282 was 16554, checked in by skunk, 11 years ago

We are using costum osmosis-load-files

File size: 2.7 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;
6DROP INDEX idx_node_tags_node_id;
7DROP INDEX idx_nodes_geom;
8DROP INDEX idx_way_tags_way_id;
9DROP INDEX idx_way_nodes_node_id;
10DROP INDEX idx_relation_tags_relation_id;
11DROP INDEX idx_ways_bbox;
12
13SELECT DropGeometryColumn('ways', 'bbox');
14
15-- Import the table data from the data files using the fast COPY method.
16-- COPY nodes FROM E'C:\\tmp\\pgimport\\nodes.txt';
17-- COPY node_tags FROM E'C:\\tmp\\pgimport\\node_tags.txt';
18-- COPY ways FROM E'C:\\tmp\\pgimport\\ways.txt';
19-- COPY way_tags FROM E'C:\\tmp\\pgimport\\way_tags.txt';
20-- COPY way_nodes FROM E'C:\\tmp\\pgimport\\way_nodes.txt';
21-- COPY relations FROM E'C:\\tmp\\pgimport\\relations.txt';
22-- COPY relation_tags FROM E'C:\\tmp\\pgimport\\relation_tags.txt';
23-- COPY relation_members FROM E'C:\\tmp\\pgimport\\relation_members.txt';
24
25-- or do it this way
26\copy nodes FROM 'nodes.txt'
27\copy node_tags FROM 'node_tags.txt'
28\copy ways FROM 'ways.txt'
29\copy way_tags FROM 'way_tags.txt'
30\copy way_nodes FROM 'way_nodes.txt'
31\copy relations FROM 'relations.txt'
32\copy relation_tags FROM 'relation_tags.txt'
33\copy relation_members FROM 'relation_members.txt'
34
35-- Add the primary keys and indexes back again (except the way bbox index).
36ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
37ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
38ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
39ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
40CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
41-- CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
42CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
43CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
44CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
45
46-- Add a postgis bounding box column used for indexing the location of the way.
47-- This will contain a bounding box surrounding the extremities of the way.
48-- SELECT AddGeometryColumn('ways', 'bbox', 4326, 'GEOMETRY', 2);
49
50-- Update the bbox column of the way table.
51-- UPDATE ways SET bbox = (
52--      SELECT Envelope(Collect(geom))
53--      FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id
54--      WHERE way_nodes.way_id = ways.id
55-- );
56
57-- Index the way bounding box column.
58-- CREATE INDEX idx_ways_bbox ON ways USING gist (bbox);
59
60-- Perform database maintenance due to large database changes.
61VACUUM ANALYZE;
62
Note: See TracBrowser for help on using the repository browser.