-- create tags for forest 

SELECT
    l.osm_id, l.highway,
    sum(
        st_area(st_intersection(ST_Buffer(l.way, 50), ST_Buffer(p.way, 50)))
        /
        st_area(ST_Buffer(l.way, 50))
    ) as green_factor
into table forest_tmp
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_DWithin(l.way, p.way, 70)
WHERE l.highway is not null 
and
(p.landuse in ('forest','allotments','flowerbed','orchard','vineyard','recreation_ground','village_green')  
     or p.leisure in ( 'park','nature_reserve'))
GROUP BY l.osm_id, l.highway
order by green_factor desc;


SELECT  y.osm_id losmid, 
case
  when y.green_factor < 0.1   then null
  when y.green_factor < 0.4  then '1'
  when y.green_factor < 0.65  then '2'
  when y.green_factor < 0.95  then '3'
  when y.green_factor < 1     then '4'
  when y.green_factor < 1.2   then '5'
  else '6' 
end as forest_class
into table forest_tags
from forest_tmp y where y.green_factor  > 0.25;