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 node_tags;
|
---|
8 | DROP TABLE IF EXISTS ways;
|
---|
9 | DROP TABLE IF EXISTS way_nodes;
|
---|
10 | DROP TABLE IF EXISTS way_tags;
|
---|
11 | DROP TABLE IF EXISTS relations;
|
---|
12 | DROP TABLE IF EXISTS relation_members;
|
---|
13 | DROP TABLE IF EXISTS relation_tags;
|
---|
14 | DROP TABLE IF EXISTS schema_info;
|
---|
15 |
|
---|
16 | -- Drop all stored procedures if they exist.
|
---|
17 | DROP FUNCTION IF EXISTS osmosisUpdate();
|
---|
18 |
|
---|
19 |
|
---|
20 | -- Create a table which will contain a single row defining the current schema version.
|
---|
21 | CREATE TABLE schema_info (
|
---|
22 | version integer NOT NULL
|
---|
23 | );
|
---|
24 |
|
---|
25 |
|
---|
26 | -- Create a table for users.
|
---|
27 | CREATE TABLE users (
|
---|
28 | id int NOT NULL,
|
---|
29 | name text NOT NULL
|
---|
30 | );
|
---|
31 |
|
---|
32 |
|
---|
33 | -- Create a table for nodes.
|
---|
34 | CREATE TABLE nodes (
|
---|
35 | id bigint NOT NULL,
|
---|
36 | version int NOT NULL,
|
---|
37 | user_id int NOT NULL,
|
---|
38 | tstamp timestamp without time zone NOT NULL,
|
---|
39 | changeset_id bigint NOT NULL
|
---|
40 | );
|
---|
41 | -- Add a postgis point column holding the location of the node.
|
---|
42 | SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);
|
---|
43 |
|
---|
44 |
|
---|
45 | -- Create a table for node tags.
|
---|
46 | CREATE TABLE node_tags (
|
---|
47 | node_id bigint NOT NULL,
|
---|
48 | k text NOT NULL,
|
---|
49 | v text NOT NULL
|
---|
50 | );
|
---|
51 |
|
---|
52 |
|
---|
53 | -- Create a table for ways.
|
---|
54 | CREATE TABLE ways (
|
---|
55 | id bigint NOT NULL,
|
---|
56 | version int NOT NULL,
|
---|
57 | user_id int NOT NULL,
|
---|
58 | tstamp timestamp without time zone NOT NULL,
|
---|
59 | changeset_id bigint NOT NULL
|
---|
60 | );
|
---|
61 |
|
---|
62 |
|
---|
63 | -- Create a table for representing way to node relationships.
|
---|
64 | CREATE TABLE way_nodes (
|
---|
65 | way_id bigint NOT NULL,
|
---|
66 | node_id bigint NOT NULL,
|
---|
67 | sequence_id int NOT NULL
|
---|
68 | );
|
---|
69 |
|
---|
70 |
|
---|
71 | -- Create a table for way tags.
|
---|
72 | CREATE TABLE way_tags (
|
---|
73 | way_id bigint NOT NULL,
|
---|
74 | k text NOT NULL,
|
---|
75 | v text
|
---|
76 | );
|
---|
77 |
|
---|
78 |
|
---|
79 | -- Create a table for relations.
|
---|
80 | CREATE TABLE relations (
|
---|
81 | id bigint NOT NULL,
|
---|
82 | version int NOT NULL,
|
---|
83 | user_id int NOT NULL,
|
---|
84 | tstamp timestamp without time zone NOT NULL,
|
---|
85 | changeset_id bigint NOT NULL
|
---|
86 | );
|
---|
87 |
|
---|
88 | -- Create a table for representing relation member relationships.
|
---|
89 | CREATE TABLE relation_members (
|
---|
90 | relation_id bigint NOT NULL,
|
---|
91 | member_id bigint NOT NULL,
|
---|
92 | member_type character(1) NOT NULL,
|
---|
93 | member_role text NOT NULL,
|
---|
94 | sequence_id int NOT NULL
|
---|
95 | );
|
---|
96 |
|
---|
97 |
|
---|
98 | -- Create a table for relation tags.
|
---|
99 | CREATE TABLE relation_tags (
|
---|
100 | relation_id bigint NOT NULL,
|
---|
101 | k text NOT NULL,
|
---|
102 | v text NOT NULL
|
---|
103 | );
|
---|
104 |
|
---|
105 |
|
---|
106 | -- Configure the schema version.
|
---|
107 | INSERT INTO schema_info (version) VALUES (5);
|
---|
108 |
|
---|
109 |
|
---|
110 | -- Add primary keys to tables.
|
---|
111 | ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
|
---|
112 |
|
---|
113 | ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id);
|
---|
114 |
|
---|
115 | ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
|
---|
116 |
|
---|
117 | ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
|
---|
118 |
|
---|
119 | ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
|
---|
120 |
|
---|
121 | ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
|
---|
122 |
|
---|
123 | ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);
|
---|
124 |
|
---|
125 |
|
---|
126 | -- Add indexes to tables.
|
---|
127 | CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
|
---|
128 | CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
|
---|
129 |
|
---|
130 | CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
|
---|
131 | CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
|
---|
132 |
|
---|
133 | CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
|
---|
134 |
|
---|
135 |
|
---|
136 | -- Create stored procedures.
|
---|
137 | CREATE FUNCTION osmosisUpdate() RETURNS void AS $$
|
---|
138 | DECLARE
|
---|
139 | BEGIN
|
---|
140 | END;
|
---|
141 | $$ LANGUAGE plpgsql;
|
---|