We're developing a reverse proxy sitting in front of Carto's API. When a user of our reverse proxy requests data, we'd like the geometry values to be returned as Well-Known Text. And our reverse proxy sits in front of an arbitrary number of tables/datasets, so we don't know the field names ahead of time (and I'd like to avoid a second HTTP request to find them out if possible, just for speed).
To accomplish this with the JSON format, we've been using:
SELECT *, ST_AsText(the_geom) AS the_geom
This works fine - it only outputs a single
the_geom field. I'm not sure if this is a feature of Postgres or simply a "feature" of JSON (you can't have more than one property with the same name).
The problem is that it doesn't work when making the same query with
&format=csv (example). It throws the error:
ERROR 1: ERROR: column reference \"the_geom\" is ambiguous"
Any advice on getting around this?