PostGIS geometry returned as binary in text/csv output when WKT would be expected

[bjornharrtell]

Environment

• PostgreSQL version: 12
• PostgREST version: postgrest/postgrest:v12.0.2
• Operating system:

Description of issue

PostGIS geometry is returned as some kind of binary string encoding when client has requested text/csv output. In standard json output it is GeoJSON which is logical and expected. In text/csv I would expect WKT representation (see https://postgis.net/docs/manual-3.4/ST_AsText.html).

[laurenceisla]

What's the exact request and response you're getting? An example using curl -i '<your-url>' would be great to better pinpoint the issue.

Perhaps this has to do with Media Type Handlers, in particular the example about overriding the text/csv handler?

[bjornharrtell]

@laurenceisla mabye I can use that feature to customize the output? But I'm not sure the binary string encoding is useful for anyone as the default. You can actually see example output of geometry in the example athttps://postgrest.org/en/latest/references/api/media_type_handlers.html#overriding-a-builtin-handler because it doesn't do any transformation of the geom field:

[wolfgangwalther]

What we would need here: Mediatype-specific domain representations.

Currently the output format for a single column can be changed with domain representations. But those always map to json - but here we are looking at csv. We don't want to replace the full csv parser, though - we just want the output format for a single column to change.

We don't have this, yet, but analogous to domain reps, we'd need a cast from the <postgis-type> to text/csv to be detected by postgrest.

I needed this in a similar case, too. Not postgis, but some other column type converted to text/csv.