mrx db

CREATE TABLE game (id SERIAL primary key);
CREATE TABLE photo_point ( id SERIAL PRIMARY KEY, lat double precision, lng double precision, img_uri text );
CREATE TABLE photo_answer ( id INTEGER primary key references photo_point (id), answer text[]);
CREATE TYPE task_status AS enum ('open', 'done', 'closed');
CREATE TABLE task (id SERIAL PRIMARY KEY, game_id INTEGER REFERENCES game, point_id INTEGER REFERENCES photo_point, center_lat double precision, center_lng double precision, radius double precision, status task_status);
CREATE TABLE mrx_pos( id SERIAL PRIMARY KEY, TIME TIMESTAMP, lat double precision, lng double precision, exposed BOOLEAN, game_id INTEGER REFERENCES game;);

CREATE INDEX game_idx ON task (game_id);
CREATE INDEX pos_game_idx ON mrx_pos (game_id);

INSERT INTO mrx.photo_point VALUES (DEFAULT, 55.761993, 37.610164, 'https://upload.wikimedia.org/wikipedia/ru/1/11/MemorialToJuriDolgoruky.jpeg');
INSERT INTO mrx.photo_point VALUES (DEFAULT, 55.761914, 37.623006, 'http://samsebeturist.net/img/posolstvo-moldovy.jpg');
INSERT INTO mrx.photo_point VALUES (DEFAULT, 55.759222, 37.619036, 'http://img-fotki.yandex.ru/get/6101/129976944.f/0_72cc4_499fc6fe_XXL');
INSERT INTO mrx.photo_point VALUES (DEFAULT, 55.756191, 37.604199, 'http://cs619221.vk.me/v619221898/5b8/3EK0qKOhOg8.jpg');
INSERT INTO mrx.photo_point VALUES (DEFAULT, 55.717294, 37.611571, 'http://m.io.ua/img_aa/medium/1931/09/19310990.jpg');
INSERT INTO mrx.game VALUES (DEFAULT);
INSERT INTO mrx.task VALUES (DEFAULT, 1, 1, 55.761993, 37.610164, 50, "open");
INSERT INTO mrx.task VALUES (DEFAULT, 1, 1, 55.761993, 37.610164, 50, 'open');
INSERT INTO mrx.task VALUES (DEFAULT, 1, 2, 55.761914, 37.623006, 70, 'open');
INSERT INTO mrx.task VALUES (DEFAULT, 1, 3, 55.759222, 37.619036, 50, 'open');
INSERT INTO mrx.task VALUES (DEFAULT, 1, 4, 55.756191, 37.604199, 60, 'open');
INSERT INTO mrx.task VALUES (DEFAULT, 1, 5, 55.717294, 37.611571, 100, 'open');