1 | -- Drop all primary keys and indexes to improve load speed.
|
---|
2 | ALTER TABLE nodes DROP CONSTRAINT pk_nodes;
|
---|
3 | ALTER TABLE ways DROP CONSTRAINT pk_ways;
|
---|
4 | ALTER TABLE way_nodes DROP CONSTRAINT pk_way_nodes;
|
---|
5 | ALTER TABLE relations DROP CONSTRAINT pk_relations;
|
---|
6 | ALTER TABLE relation_members DROP CONSTRAINT pk_relation_members;
|
---|
7 | DROP INDEX idx_nodes_geom;
|
---|
8 | DROP INDEX idx_way_nodes_node_id;
|
---|
9 | DROP INDEX idx_relation_members_member_id_and_type;
|
---|
10 | DROP INDEX idx_ways_bbox;
|
---|
11 | DROP INDEX idx_ways_linestring;
|
---|
12 |
|
---|
13 | -- Comment these out if the COPY files include bbox or linestring column values.
|
---|
14 | SELECT DropGeometryColumn('ways', 'bbox');
|
---|
15 | SELECT DropGeometryColumn('ways', 'linestring');
|
---|
16 |
|
---|
17 | -- Import the table data from the data files using the fast COPY method.
|
---|
18 | \copy users FROM 'users.txt'
|
---|
19 | \copy nodes FROM 'nodes.txt'
|
---|
20 | \copy ways FROM 'ways.txt'
|
---|
21 | \copy way_nodes FROM 'way_nodes.txt'
|
---|
22 | \copy relations FROM 'relations.txt'
|
---|
23 | \copy relation_members FROM 'relation_members.txt'
|
---|
24 |
|
---|
25 | -- Add the primary keys and indexes back again (except the way bbox index).
|
---|
26 | ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
|
---|
27 | ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
|
---|
28 | ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
|
---|
29 | ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
|
---|
30 | ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);
|
---|
31 | CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
|
---|
32 | CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
|
---|
33 | CREATE INDEX idx_relation_members_member_id_and_type ON relation_members USING btree (member_id, member_type);
|
---|
34 |
|
---|
35 | -- Comment these out if the COPY files include bbox or linestring column values.
|
---|
36 | SELECT AddGeometryColumn('ways', 'bbox', 4326, 'GEOMETRY', 2);
|
---|
37 | SELECT AddGeometryColumn('ways', 'linestring', 4326, 'GEOMETRY', 2);
|
---|
38 |
|
---|
39 | -- Comment these out if the COPY files include bbox or linestring column values.
|
---|
40 | -- Update the bbox column of the way table.
|
---|
41 | UPDATE ways SET bbox = (
|
---|
42 | SELECT Envelope(Collect(geom))
|
---|
43 | FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id
|
---|
44 | WHERE way_nodes.way_id = ways.id
|
---|
45 | );
|
---|
46 | -- Update the linestring column of the way table.
|
---|
47 | UPDATE ways w SET linestring = (
|
---|
48 | SELECT ST_MakeLine(c.geom) AS way_line FROM (
|
---|
49 | SELECT n.geom AS geom
|
---|
50 | FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id
|
---|
51 | WHERE (wn.way_id = w.id) ORDER BY wn.sequence_id
|
---|
52 | ) c
|
---|
53 | );
|
---|
54 |
|
---|
55 | -- Index the way bounding box column.
|
---|
56 | CREATE INDEX idx_ways_bbox ON ways USING gist (bbox);
|
---|
57 | CREATE INDEX idx_ways_linestring ON ways USING gist (linestring);
|
---|
58 |
|
---|
59 | -- Update all clustered tables because it doesn't happen implicitly.
|
---|
60 | CLUSTER nodes USING idx_nodes_geom;
|
---|
61 | CLUSTER ways USING idx_ways_linestring;
|
---|
62 |
|
---|
63 | -- Perform database maintenance due to large database changes.
|
---|
64 | VACUUM ANALYZE;
|
---|