source: subversion/sites/www.openstreetbrowser.org/src/pgsql_simple_schema.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: 3.2 KB
Line 
1-- Database creation script for the simple PostgreSQL schema.
2
3-- Drop all tables if they exist.
4DROP TABLE IF EXISTS nodes;
5DROP TABLE IF EXISTS node_tags;
6DROP TABLE IF EXISTS ways;
7DROP TABLE IF EXISTS way_nodes;
8DROP TABLE IF EXISTS way_tags;
9DROP TABLE IF EXISTS relations;
10DROP TABLE IF EXISTS relation_members;
11DROP TABLE IF EXISTS relation_tags;
12DROP TABLE IF EXISTS schema_info;
13
14
15-- Create a table which will contain a single row defining the current schema version.
16CREATE TABLE schema_info (
17    version integer NOT NULL
18);
19
20
21-- Create a table for nodes.
22CREATE TABLE nodes (
23    id bigint NOT NULL,
24    user_name text NOT NULL,
25    tstamp timestamp without time zone NOT NULL
26);
27-- Add a postgis point column holding the location of the node.
28-- SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);
29
30-- Create a table for node tags.
31CREATE TABLE node_tags (
32    node_id bigint NOT NULL,
33    k text NOT NULL,
34    v text NOT NULL
35);
36
37
38-- Create a table for ways.
39CREATE TABLE ways (
40    id bigint NOT NULL,
41    user_name text NOT NULL,
42    tstamp timestamp without time zone NOT NULL
43);
44-- Add a postgis bounding box column used for indexing the location of the way.
45-- This will contain a bounding box surrounding the extremities of the way.
46-- SELECT AddGeometryColumn('ways', 'bbox', 4326, 'GEOMETRY', 2);
47
48
49-- Create a table for representing way to node relationships.
50CREATE TABLE way_nodes (
51    way_id bigint NOT NULL,
52    node_id bigint NOT NULL,
53    sequence_id smallint NOT NULL
54);
55
56
57-- Create a table for way tags.
58CREATE TABLE way_tags (
59    way_id bigint NOT NULL,
60    k text NOT NULL,
61    v text
62);
63
64
65-- Create a table for relations.
66CREATE TABLE relations (
67    id bigint NOT NULL,
68    user_name text NOT NULL,
69    tstamp timestamp without time zone NOT NULL
70);
71
72-- Create a table for representing relation member relationships.
73CREATE TABLE relation_members (
74    relation_id bigint NOT NULL,
75    member_id bigint NOT NULL,
76    member_role text NOT NULL,
77    member_type smallint NOT NULL
78);
79
80
81-- Create a table for relation tags.
82CREATE TABLE relation_tags (
83    relation_id bigint NOT NULL,
84    k text NOT NULL,
85    v text NOT NULL
86);
87
88
89-- Configure the schema version.
90INSERT INTO schema_info (version) VALUES (1);
91
92
93-- Add primary keys to tables.
94
95ALTER TABLE ONLY schema_info ADD CONSTRAINT pk_schema_info PRIMARY KEY (version);
96
97
98ALTER TABLE ONLY nodes ADD CONSTRAINT pk_nodes PRIMARY KEY (id);
99
100
101ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
102
103
104ALTER TABLE ONLY way_nodes ADD CONSTRAINT pk_way_nodes PRIMARY KEY (way_id, sequence_id);
105
106
107ALTER TABLE ONLY relations ADD CONSTRAINT pk_relations PRIMARY KEY (id);
108
109
110-- Add indexes to tables.
111
112CREATE INDEX idx_node_tags_node_id ON node_tags USING btree (node_id);
113-- CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);
114
115
116CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id);
117-- CREATE INDEX idx_ways_bbox ON ways USING gist (bbox);
118CREATE INDEX idx_way_nodes_node_id ON way_nodes USING btree (node_id);
119
120
121CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id);
Note: See TracBrowser for help on using the repository browser.