-- create tags for river 

select xid , sum(water_river_see) as river_see 
into table river_tmp
from (
SELECT     m.osm_id  as xid,
    sum(
        st_area(st_intersection(ST_Buffer(m.way, 150), ST_Buffer(q.way, 150)))       /
        st_area(ST_Buffer(m.way, 150))
    ) as water_river_see
FROM planet_osm_line AS m
INNER JOIN planet_osm_polygon AS q ON ST_DWithin(m.way, q.way, 120)
WHERE  m.highway is not null
and
 q.natural in ('water') and (q.water is null or q.water not in ('wastewater'))
GROUP BY m.osm_id
union
SELECT    m.osm_id as xid, 
    sum(
        st_area(st_intersection(ST_Buffer(m.way, 150), ST_Buffer(q.way, 150)))      /
        st_area(ST_Buffer(m.way, 150))
    ) as water_river_see
FROM planet_osm_line AS m
INNER JOIN planet_osm_line AS q ON ST_DWithin(m.way, q.way, 120)
WHERE m.highway is not null
and
q.waterway in ('river','canal') 
GROUP BY m.osm_id
) as abcd
GROUP BY xid
order by river_see desc;


SELECT     y.xid losmid, 
case
  when y.river_see < 0.1  then null
  when y.river_see < 0.4 then '1'
  when y.river_see < 0.6 then '2'
  when y.river_see < 0.8 then '3'
  when y.river_see < 1.3  then '4'
  when y.river_see < 1.8  then '5'
  else '6' 
end as river_class
into table river_tags
from river_tmp y where y.river_see > 0.25;