-- create views for grosm and grenosm
BEGIN;
-- ************** enosm
DROP VIEW IF EXISTS planet_enosm_line;
DROP VIEW IF EXISTS planet_enosm_point;
DROP VIEW IF EXISTS planet_enosm_polygon;
DROP VIEW IF EXISTS planet_enosm_roads;
CREATE VIEW planet_enosm_line AS
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en') then tags->'name:en' else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_line;
CREATE VIEW planet_enosm_point AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  capital,
  construction,
  cutting,
  disused,
  ele,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en') then tags->'name:en' else name end AS name,
  "natural",
  oneway,
  "operator",
  poi,
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way
  FROM planet_osm_point;
CREATE VIEW planet_enosm_polygon AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en') then tags->'name:en' else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_polygon;
CREATE VIEW planet_enosm_roads AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en') then tags->'name:en' else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_roads;
-- add geometry. I assue spherical mercator only exists when already added
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_enosm_point', 'way', 2, 900913, 'POINT' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_enosm_point');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_enosm_line', 'way', 2, 900913, 'LINESTRING' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_enosm_line');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_enosm_polygon', 'way', 2, 900913, 'POLYGON' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_enosm_polygon');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_enosm_roads', 'way', 2, 900913, 'LINESTRING' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_enosm_roads');
-- set permissions to osm
ALTER TABLE planet_enosm_point OWNER TO osm;
ALTER TABLE planet_enosm_line OWNER TO osm;
ALTER TABLE planet_enosm_polygon OWNER TO osm;
ALTER TABLE planet_enosm_roads OWNER TO osm;

-- ************** grenosm
-- create a mixed view of name (name:en) where name:en exists. Fallback is name
DROP VIEW IF EXISTS planet_grenosm_line;
DROP VIEW IF EXISTS planet_grenosm_point;
DROP VIEW IF EXISTS planet_grenosm_polygon;
DROP VIEW IF EXISTS planet_grenosm_roads;
CREATE VIEW planet_grenosm_line AS
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en' AND name != (tags->'name:en')) then name || ' ' || (tags->'name:en') else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_line;
CREATE VIEW planet_grenosm_point AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  capital,
  construction,
  cutting,
  disused,
  ele,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en' AND name != (tags->'name:en')) then name || E'\n' || (tags->'name:en') else name end AS name,
  "natural",
  oneway,
  "operator",
  poi,
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way
  FROM planet_osm_point;
CREATE VIEW planet_grenosm_polygon AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en' AND name != (tags->'name:en')) then name || E'\n' || (tags->'name:en') else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_polygon;
CREATE VIEW planet_grenosm_roads AS 
	SELECT osm_id ,
  "access",
  "addr:flats",
  "addr:housenumber",
  "addr:interpolation",
  admin_level,
  aerialway,
  aeroway,
  amenity,
  area,
  barrier,
  bicycle,
  bridge,
  boundary,
  building,
  construction,
  cutting,
  disused,
  embankment,
  foot,
  highway,
  historic,
  horse,
  junction,
  landuse,
  layer,
  learning,
  leisure,
  "lock",
  man_made,
  military,
  motorcar,
  case when (tags ? 'name:en' AND name != (tags->'name:en')) then name || ' ' || (tags->'name:en') else name end AS name,
  "natural",
  oneway,
  "operator",
  power,
  power_source,
  place,
  railway,
  ref,
  religion,
  residence,
  route,
  service,
  shop,
  sport,
  tourism,
  tracktype,
  tunnel,
  waterway,
  width,
  wood,
  z_order,
  way_area,
  way
	FROM planet_osm_roads;
-- add geometry. I assue spherical mercator only exists when already added
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_grenosm_point', 'way', 2, 900913, 'POINT' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_grenosm_point');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_grenosm_line', 'way', 2, 900913, 'LINESTRING' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_grenosm_line');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_grenosm_polygon', 'way', 2, 900913, 'POLYGON' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_grenosm_polygon');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
  SELECT '', 'public', 'planet_grenosm_roads', 'way', 2, 900913, 'LINESTRING' 
WHERE NOT EXISTS (SELECT 1 FROM geometry_columns WHERE srid = 900913 AND f_table_name='planet_grenosm_roads');
-- set permissions to osm
ALTER TABLE planet_grenosm_point OWNER TO osm;
ALTER TABLE planet_grenosm_line OWNER TO osm;
ALTER TABLE planet_grenosm_polygon OWNER TO osm;
ALTER TABLE planet_grenosm_roads OWNER TO osm;
COMMIT;
