BEGIN;


-- vrstva klubu
CREATE TABLE b11.kluby AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, way FROM
osm.czech_point WHERE amenity='nightclub';
ALTER TABLE b11.kluby ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.kluby'::regclass);

-- vrstva kin
CREATE TABLE b11.kina AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, way FROM
osm.czech_point WHERE amenity='cinema';
ALTER TABLE b11.kina ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.kina'::regclass);

-- vrstva musea
CREATE TABLE b11.musea AS SELECT osm_id, name, amenity, tourism,
"addr:city", "addr:street", "addr:housenumber", website, phone, way FROM
osm.czech_point WHERE tourism='museum';
UPDATE b11.musea SET amenity=tourism; -- převedení pro zjednodušení práce
ALTER TABLE b11.musea DROP tourism;
ALTER TABLE b11.musea ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.musea'::regclass);

-- vstva divadla
CREATE TABLE b11.divadla AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, "theatre:genre", way
FROM osm.czech_point WHERE amenity='theatre';
ALTER TABLE b11.divadla ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.divadla'::regclass);

-- vrstva bary
CREATE TABLE b11.bary AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, way FROM
osm.czech_point WHERE amenity='bar';
ALTER TABLE b11.bary ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.bary'::regclass);

-- vrstva pub
CREATE TABLE b11.pub AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, way FROM
osm.czech_point WHERE amenity='pub';
ALTER TABLE b11.pub ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.pub'::regclass);

-- vrstva obsahujici vsechny vyse uvedene
CREATE TABLE b11.vse AS SELECT osm_id, name, amenity, "addr:city",
"addr:street", "addr:housenumber", website, phone, "theatre:genre",
tourism, way FROM osm.czech_point WHERE amenity IN
('nightclub','cinema','theatre','pub','bar') OR tourism='museum';
UPDATE b11.vse SET amenity=tourism WHERE tourism='museum';
ALTER TABLE b11.vse DROP tourism;
ALTER TABLE b11.vse ADD PRIMARY KEY (osm_id);
SELECT Populate_Geometry_Columns('b11.vse'::regclass);

COMMIT;
