1 | -- Database creation script for the simple PostgreSQL schema.
|
---|
2 |
|
---|
3 | -- Drop all tables if they exist.
|
---|
4 | DROP TABLE IF EXISTS actions;
|
---|
5 | DROP TABLE IF EXISTS users;
|
---|
6 | DROP TABLE IF EXISTS nodes;
|
---|
7 | DROP TABLE IF EXISTS ways;
|
---|
8 | DROP TABLE IF EXISTS way_nodes;
|
---|
9 | DROP TABLE IF EXISTS relations;
|
---|
10 | DROP TABLE IF EXISTS relation_members;
|
---|
11 | DROP TABLE IF EXISTS schema_info;
|
---|
12 |
|
---|
13 | -- Drop all stored procedures if they exist.
|
---|
14 | DROP FUNCTION IF EXISTS osmosisUpdate();
|
---|
15 |
|
---|
16 |
|
---|
17 | -- Create a table which will contain a single row defining the current schema version.
|
---|
18 | CREATE TABLE schema_info (
|
---|
19 | version integer NOT NULL
|
---|
20 | );
|
---|
21 |
|
---|
22 |
|
---|
23 | -- Create a table for users.
|
---|
24 | CREATE TABLE users (
|
---|
25 | id int NOT NULL,
|
---|
26 | name text NOT NULL
|
---|
27 | );
|
---|
28 |
|
---|
29 |
|
---|
30 | -- Create a table for nodes.
|
---|
31 | CREATE TABLE nodes (
|
---|
32 | id bigint NOT NULL,
|
---|
33 | version int NOT NULL,
|
---|
34 | user_id int NOT NULL,
|
---|
35 | tstamp timestamp without time zone NOT NULL,
|
---|
36 | changeset_id bigint NOT NULL,
|
---|
37 | tags hstore
|
---|
38 | );
|
---|
39 | -- Add a postgis point column holding the location of the node.
|
---|
40 | SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);
|
---|
41 |
|
---|
42 |
|
---|
43 | -- Create a table for ways.
|
---|
44 | CREATE TABLE ways (
|
---|
45 | id bigint NOT NULL,
|
---|
46 | version int NOT NULL,
|
---|
47 | user_id int NOT NULL,
|
---|
48 | tstamp timestamp without time zone NOT NULL,
|
---|
49 | changeset_id bigint NOT NULL,
|
---|
50 | tags hstore,
|
---|
51 | nodes bigint[]
|
---|
52 | );
|
---|
53 |
|
---|
54 |
|
---|
55 | -- Create a table for representing way to node relationships.
|
---|
56 | CREATE TABLE way_nodes (
|
---|
57 | way_id bigint NOT NULL,
|
---|
58 | node_id bigint NOT NULL,
|
---|
59 | sequence_id int NOT NULL
|
---|
60 | );
|
---|
61 |
|
---|
62 |
|
---|
63 | -- Create a table for relations.
|
---|
64 | CREATE TABLE relations (
|
---|
65 | id bigint NOT NULL,
|
---|
66 | version int NOT NULL,
|
---|
67 | user_id int NOT NULL,
|
---|
68 | tstamp timestamp without time zone NOT NULL,
|
---|
69 | changeset_id bigint NOT NULL,
|
---|
70 | tags hstore
|
---|
71 | );
|
---|
72 |
|
---|
73 | -- Create a table for representing relation member relationships.
|
---|
74 | CREATE TABLE relation_members (
|
---|
75 | relation_id bigint NOT NULL,
|
---|
76 | member_id bigint NOT NULL,
|
---|
77 | member_type character(1) NOT NULL,
|
---|
78 | member_role text NOT NULL,
|
---|
79 | sequence_id int NOT NULL
|
---|
80 | );
|
---|
81 |
|
---|
82 |
|
---|
83 | -- Configure the schema version.
|
---|
84 | INSERT INTO schema_info (version) VALUES (6);
|
---|
85 |
|
---|
86 |
|
---|
87 | -- Add primary keys to tables.
|
---|
88 | ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
|
---|
89 |
|
---|
90 | ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id);
|
---|
91 |
|
---|
92 | ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
|
---|
93 |
|
---|
94 | ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
|
---|
95 |
|
---|
96 | ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
|
---|
97 |
|
---|
98 | ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
|
---|
99 |
|
---|
100 | ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);
|
---|
101 |
|
---|
102 |
|
---|
103 | -- Add indexes to tables.
|
---|
104 | CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
|
---|
105 |
|
---|
106 | CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
|
---|
107 |
|
---|
108 | CREATE INDEX idx_relation_members_member_id_and_type ON relation_members USING btree (member_id, member_type);
|
---|
109 |
|
---|
110 |
|
---|
111 | -- Cluster tables by geographical location.
|
---|
112 | CLUSTER nodes USING idx_nodes_geom;
|
---|
113 |
|
---|
114 |
|
---|
115 | -- Create the function that provides "unnest" functionality while remaining compatible with 8.3.
|
---|
116 | CREATE OR REPLACE FUNCTION unnest_bbox_way_nodes() RETURNS void AS $$
|
---|
117 | DECLARE
|
---|
118 | previousId ways.id%TYPE;
|
---|
119 | currentId ways.id%TYPE;
|
---|
120 | result bigint[];
|
---|
121 | wayNodeRow way_nodes%ROWTYPE;
|
---|
122 | wayNodes ways.nodes%TYPE;
|
---|
123 | BEGIN
|
---|
124 | FOR wayNodes IN SELECT bw.nodes FROM bbox_ways bw LOOP
|
---|
125 | FOR i IN 1 .. array_upper(wayNodes, 1) LOOP
|
---|
126 | INSERT INTO bbox_way_nodes (id) VALUES (wayNodes[i]);
|
---|
127 | END LOOP;
|
---|
128 | END LOOP;
|
---|
129 | END;
|
---|
130 | $$ LANGUAGE plpgsql;
|
---|
131 |
|
---|
132 |
|
---|
133 | -- Create customisable hook function that is called within the replication update transaction.
|
---|
134 | CREATE FUNCTION osmosisUpdate() RETURNS void AS $$
|
---|
135 | DECLARE
|
---|
136 | BEGIN
|
---|
137 | END;
|
---|
138 | $$ LANGUAGE plpgsql;
|
---|