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 (example)

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?


You are getting that error because you are getting the_geom twice. The first inside the *, and the second, as the new field you are creating on the fly. If you change the alias of this last field and use any other name but the_geom such as the_geom_as_text, the query would work. Here you have the working query.

  • Thanks, but it works fine when I query it as JSON. It's only when I add the &format=csv parameter to the URL that it throws an error. Could that point to another issue? – Tobias Fünke Feb 7 '17 at 15:35
  • 1
    It could. The error is expected (it fails also with other formats). json most likely works because of a wrapped query where the second the_geom shadows the first one. But this is a not expected behaviour. – ramiroaznar Feb 8 '17 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.