Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
OpenStreetMap database schema for api 0.6 for Sqlite
--
-- OpenStreetMap database schema for api 0.6
--
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE acls(
`id` INTEGER NOT NULL,
`address` INTEGER NOT NULL,
`netmask` INTEGER NOT NULL,
`k` TEXT NOT NULL,
`v` TEXT default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE changeset_tags(
`changeset_id` INTEGER NOT NULL,
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default ''
);
CREATE TABLE changesets(
`id` INTEGER NOT NULL,
`user_id` INTEGER NOT NULL,
`created_at` INTEGER NOT NULL,
`min_lat` INTEGER default NULL,
`max_lat` INTEGER default NULL,
`min_lon` INTEGER default NULL,
`max_lon` INTEGER default NULL,
`closed_at` INTEGER NOT NULL,
`num_changes` INTEGER NOT NULL default '0',
PRIMARY KEY (`id`)
);
CREATE TABLE countries(
`id` INTEGER NOT NULL,
`code` TEXT NOT NULL,
`english_name` TEXT NOT NULL,
`native_name` TEXT NOT NULL
PRIMARY KEY (`id`, `code`)
);
CREATE TABLE current_node_tags(
`node_id` INTEGER NOT NULL,
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default '',
PRIMARY KEY (`node_id`, `k`)
);
CREATE TABLE current_nodes(
`id` INTEGER NOT NULL,
`latitude` INTEGER NOT NULL,
`longitude` INTEGER NOT NULL,
`changeset_id` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL,
`timestamp` INTEGER NOT NULL,
`tile` int(10) default NULL,
`version` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE current_relation_members(
`relation_id` INTEGER NOT NULL,
`member_type` text NOT NULL default 'Node',
`member_id` bigINTEGER NOT NULL,
`member_role` TEXT NOT NULL default '',
`sequence_id` INTEGER NOT NULL default '0',
PRIMARY KEY (`relation_id`,`member_type`,`member_id`,`member_role`,`sequence_id`)
);
CREATE TABLE current_relation_tags(
`id` INTEGER NOT NULL,
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default '',
PRIMARY KEY (`id`,`k`)
);
CREATE TABLE current_relations(
`relation_id` INTEGER NOT NULL ,
`changeset_id` INTEGER NOT NULL,
`timestamp` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL,
`version` INTEGER NOT NULL,
PRIMARY KEY (`relation_id`)
);
CREATE TABLE current_way_nodes(
`id` INTEGER NOT NULL,
`node_id` INTEGER NOT NULL,
`sequence_id` bigINTEGER NOT NULL,
PRIMARY KEY (`id`,`sequence_id`)
);
CREATE TABLE current_way_tags(
`way_id` INTEGER NOT NULL,
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default '',
PRIMARY KEY (`way_id`,`k`)
);
CREATE TABLE current_ways(
`id` INTEGER NOT NULL ,
`changeset_id` INTEGER NOT NULL,
`timestamp` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL,
`version` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE diary_comments(
`id` INTEGER NOT NULL ,
`diary_entry_id` INTEGER NOT NULL,
`user_id` INTEGER NOT NULL,
`body` text NOT NULL,
`created_at` INTEGER NOT NULL,
`updated_at` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE diary_entries(
`id` INTEGER NOT NULL ,
`user_id` INTEGER NOT NULL,
`title` TEXT NOT NULL,
`body` text NOT NULL,
`created_at` INTEGER NOT NULL,
`updated_at` INTEGER NOT NULL,
`latitude` REAL default NULL,
`longitude` REAL default NULL,
`language` varchar(3) default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE friends(
`id` INTEGER NOT NULL ,
`user_id` INTEGER NOT NULL,
`friend_user_id` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE gps_points(
`altitude` float default NULL,
`trackid` INTEGER NOT NULL,
`latitude` INTEGER NOT NULL,
`longitude` INTEGER NOT NULL,
`gpx_id` INTEGER NOT NULL,
`timestamp` INTEGER default NULL,
`tile` int(10) default NULL
);
CREATE TABLE gpx_file_tags(
`gpx_id` INTEGER NOT NULL default '0',
`tag` TEXT NOT NULL,
`id` INTEGER NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE gpx_files(
`id` INTEGER NOT NULL ,
`user_id` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL default '1',
`name` TEXT NOT NULL default '',
`size` INTEGER default NULL,
`latitude` REAL default NULL,
`longitude` REAL default NULL,
`timestamp` INTEGER NOT NULL,
`public` BOOLEAN NOT NULL default '1',
`description` TEXT NOT NULL default '',
`inserted` BOOLEAN NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE languages(
`code` TEXT NOT NULL,
`english_name` TEXT NOT NULL,
`native_name` TEXT NOT NULL
PRIMARY KEY (`code`)
);
CREATE TABLE messages(
`id` INTEGER NOT NULL ,
`from_user_id` INTEGER NOT NULL,
`title` TEXT NOT NULL,
`body` text NOT NULL,
`sent_on` INTEGER NOT NULL,
`message_read` BOOLEAN NOT NULL default '0',
`to_user_id` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE node_tags(
`node_id` INTEGER NOT NULL,
`version` INTEGER NOT NULL,
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default '',
PRIMARY KEY (`node_id`,`version`,`k`)
);
CREATE TABLE nodes(
`node_id` INTEGER NOT NULL,
`latitude` INTEGER NOT NULL,
`longitude` INTEGER NOT NULL,
`changeset_id` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL,
`timestamp` INTEGER NOT NULL,
`tile` int(10) default NULL,
`version` INTEGER NOT NULL,
PRIMARY KEY (`node_id`,`version`)
);
CREATE TABLE relation_members(
`relation_id` INTEGER NOT NULL default '0',
`member_type` text NOT NULL default 'Node',
`member_id` bigINTEGER NOT NULL,
`member_role` TEXT NOT NULL default '',
`version` INTEGER NOT NULL default '0',
`sequence_id` INTEGER NOT NULL default '0',
PRIMARY KEY (`relation_id`,`version`,`member_type`,`member_id`,`member_role`,`sequence_id`)
);
CREATE TABLE relation_tags(
`relation_id` INTEGER NOT NULL default '0',
`k` TEXT NOT NULL default '',
`v` TEXT NOT NULL default '',
`version` INTEGER NOT NULL,
PRIMARY KEY (`relation_id`,`version`,`k`)
);
CREATE TABLE relations(
`relation_id` INTEGER NOT NULL default '0',
`changeset_id` INTEGER NOT NULL,
`timestamp` INTEGER NOT NULL,
`version` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL default '1',
PRIMARY KEY (`relation_id`,`version`)
);
CREATE TABLE schema_migrations(
`version` TEXT UNIQUE NOT NULL
);
CREATE TABLE sessions(
`id` INTEGER NOT NULL ,
`session_id` TEXT default NULL,
`data` text,
`created_at` INTEGER default NULL,
`updated_at` INTEGER default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE user_preferences(
`user_id` INTEGER NOT NULL,
`k` TEXT NOT NULL,
`v` TEXT NOT NULL,
PRIMARY KEY (`user_id`,`k`)
);
CREATE TABLE user_tokens(
`id` INTEGER NOT NULL ,
`user_id` INTEGER NOT NULL,
`token` TEXT NOT NULL,
`expiry` INTEGER NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE users(
`email` TEXT NOT NULL,
`id` INTEGER NOT NULL ,
`active` INTEGER NOT NULL default '0',
`pass_crypt` TEXT NOT NULL,
`creation_time` INTEGER NOT NULL,
`display_name` TEXT NOT NULL default '',
`data_public` BOOLEAN NOT NULL default '0',
`description` text NOT NULL,
`home_lat` REAL default NULL,
`home_lon` REAL default NULL,
`home_zoom` smallint(6) default '3',
`nearby` INTEGER default '50',
`pass_salt` TEXT default NULL,
`image` text,
`administrator` BOOLEAN NOT NULL default '0',
`email_valid` BOOLEAN NOT NULL default '0',
`new_email` TEXT default NULL,
`visible` BOOLEAN NOT NULL default '1',
`creation_ip` TEXT default NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE way_nodes(
`way_id` INTEGER NOT NULL,
`node_id` INTEGER NOT NULL,
`version` INTEGER NOT NULL,
`sequence_id` bigINTEGER NOT NULL,
PRIMARY KEY (`way_id`,`version`,`sequence_id`)
);
CREATE TABLE way_tags(
`way_id` INTEGER NOT NULL default '0',
`k` TEXT NOT NULL,
`v` TEXT NOT NULL,
`version` INTEGER NOT NULL,
PRIMARY KEY (`way_id`,`version`,`k`)
);
CREATE TABLE ways(
`way_id` INTEGER NOT NULL default '0',
`changeset_id` INTEGER NOT NULL,
`timestamp` INTEGER NOT NULL,
`version` INTEGER NOT NULL,
`visible` BOOLEAN NOT NULL default '1',
PRIMARY KEY (`way_id`,`version`)
);
CREATE INDEX "acls_k_idx" ON "acls" (`k`);
CREATE INDEX "changeset_tags_id_idx" ON "changeset_tags" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`node_id`);
CREATE INDEX "current_nodes_timestamp_idx" ON "current_nodes" (`timestamp`);
CREATE INDEX "current_nodes_tile_idx" ON "current_nodes" (`tile`);
CREATE INDEX "changeset_id" ON "current_nodes" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`changeset_id`);
CREATE INDEX "current_relation_members_member_idx" ON "current_relation_members" (`member_type`,`member_id`);
-- CREATE INDEX "_" ON "" (`relation_id`);
-- CREATE INDEX "_" ON "" (`id`);
CREATE INDEX "current_relations_timestamp_idx" ON "current_relations" (`timestamp`);
CREATE INDEX "changeset_id" ON "current_relations" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`changeset_id`);
CREATE INDEX "current_way_nodes_node_idx" ON "current_way_nodes" (`node_id`);
-- CREATE INDEX "_" ON "" (`node_id`);
-- CREATE INDEX "_" ON "" (`id`);
-- CREATE INDEX "_" ON "" (`way_id`);
CREATE INDEX "current_ways_timestamp_idx" ON "current_ways" (`timestamp`);
CREATE INDEX "changeset_id" ON "current_ways" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`diary_entry_id`,`id`);
CREATE INDEX "user_id_idx" ON "friends" (`friend_user_id`);
CREATE INDEX "friends_user_id_idx" ON "friends" (`user_id`);
CREATE INDEX "points_gpxid_idx" ON "gps_points" (`gpx_id`);
CREATE INDEX "points_tile_idx" ON "gps_points" (`tile`);
CREATE INDEX "gpx_file_tags_gpxid_idx" ON "gpx_file_tags" (`gpx_id`);
CREATE INDEX "gpx_file_tags_tag_idx" ON "gpx_file_tags" (`tag`);
CREATE INDEX "gpx_files_timestamp_idx" ON "gpx_files" (`timestamp`);
CREATE INDEX "gpx_files_visible_public_idx" ON "gpx_files" (`visible`,`public`);
CREATE INDEX "gpx_files_user_id_idx" ON "gpx_files" (`user_id`);
CREATE INDEX "messages_to_user_id_idx" ON "messages" (`to_user_id`);
-- CREATE INDEX "_" ON "" (`node_id`, `version`);
CREATE INDEX "nodes_timestamp_idx" ON "nodes" (`timestamp`);
CREATE INDEX "nodes_tile_idx" ON "nodes" (`tile`);
CREATE INDEX "changeset_id" ON "nodes" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`changeset_id`);
CREATE INDEX "relation_members_member_idx" ON "relation_members" (`member_type`,`member_id`);
-- CREATE INDEX "_" ON "" (`relation_id`, `version`);
-- CREATE INDEX "_" ON "" (`relation_id`, `version`);
CREATE INDEX "relations_timestamp_idx" ON "relations" (`timestamp`);
CREATE INDEX "changeset_id" ON "relations" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`changeset_id`);
-- CREATE INDEX "_" ON "" (`version`);
CREATE UNIQUE INDEX "sessions_session_id_idx" ON "sessions" (`session_id`);
CREATE UNIQUE INDEX "user_tokens_token_idx" ON "user_tokens" (`token`);
CREATE INDEX "user_tokens_user_id_idx" ON "user_tokens" (`user_id`);
CREATE UNIQUE INDEX "users_email_idx" ON "users" (`email`);
CREATE UNIQUE INDEX "users_display_name_idx" ON "users" (`display_name`);
CREATE INDEX "way_nodes_node_idx" ON "way_nodes" (`node_id`);
-- CONTRAIN -- CREATE INDEX "_" ON "" (`way_id`, `version`);
-- PRIMARY -- CREATE INDEX "_" ON "" (`way_id`, `version`);
CREATE INDEX "ways_timestamp_idx" ON "ways" (`timestamp`);
CREATE INDEX "changeset_id" ON "ways" (`changeset_id`);
-- CONSTRAIN -- CREATE INDEX "_" ON "" (`changeset_id`);
INSERT INTO `schema_migrations` VALUES
('1'), ('2'), ('3'), ('4'), ('5'),
('6'), ('7'), ('8'), ('9'), ('10'),
('11'), ('12'), ('13'), ('14'), ('15'),
('16'), ('17'), ('18'), ('19'), ('20'),
('21'), ('22'), ('23'), ('24'), ('25');
END TRANSACTION;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment