As environmentalists rejoice, US president says pipeline would't have made meaningful economic contribution.