Plans for a food waste transfer site have been scrapped amid staunch opposition by residents.