What you write sounds like heavenly music. Something tells me, however, that it is strange that no one has used it yet. For example, Firaxis in some expansion pack.
However, I have something from Platyping in my mod. The minimod is originally called Grand Canal, I think. It makes a canal from the city one to three squares and when I do a water improvement on that water, it seems to hang in the air. After restarting the game, the tile aligns to the normal water level, but somewhere in this mechanism there is a bug that would allow us to do what you are writing about. It would just be necessary to somehow arrange for the bug to remain after restarting the game.
However, the canyon on the water would then not be accessible to ground units.