‘Eyesore’ waste site hated by locals to remain as operator flags 20-year transformation into public park