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

Last change on this file since 29773 was 19834, checked in by skunk, 10 years ago

Change for Osmosis 0.32

File size: 3.5 KB
Line 
1-- Database creation script for the simple PostgreSQL schema.
2
3-- Drop all tables if they exist.
4DROP TABLE IF EXISTS actions;
5DROP TABLE IF EXISTS users;
6DROP TABLE IF EXISTS nodes;
7DROP TABLE IF EXISTS node_tags;
8DROP TABLE IF EXISTS ways;
9DROP TABLE IF EXISTS way_nodes;
10DROP TABLE IF EXISTS way_tags;
11DROP TABLE IF EXISTS relations;
12DROP TABLE IF EXISTS relation_members;
13DROP TABLE IF EXISTS relation_tags;
14DROP TABLE IF EXISTS schema_info;
15
16-- Drop all stored procedures if they exist.
17DROP FUNCTION IF EXISTS osmosisUpdate();
18
19
20-- Create a table which will contain a single row defining the current schema version.
21CREATE TABLE schema_info (
22    version integer NOT NULL
23);
24
25
26-- Create a table for users.
27CREATE TABLE users (
28    id int NOT NULL,
29    name text NOT NULL
30);
31
32
33-- Create a table for nodes.
34CREATE 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.
46CREATE 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.
54CREATE 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.
64CREATE 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.
72CREATE 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.
80CREATE 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.
89CREATE 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.
99CREATE 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.
107INSERT INTO schema_info (version) VALUES (4);
108
109
110-- Add primary keys to tables.
111ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
112
113ALTER TABLE ONLY users ADD CONSTRAINT pk_users PRIMARY KEY (id);
114
115ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
116
117ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
118
119ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
120
121ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
122
123
124-- Add indexes to tables.
125CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
126-- CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
127-- this would take too much time
128
129CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
130CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
131
132CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
133
134
135-- Create stored procedures.
136CREATE FUNCTION osmosisUpdate() RETURNS void AS $$
137DECLARE
138BEGIN
139END;
140$$ LANGUAGE plpgsql;
Note: See TracBrowser for help on using the repository browser.