When a DB query returns a binary string, this needs unescaping before use. The case that brought this to light for me was a relatively complex PostGIS query which generates a MapBox vector tile lines geometry imported from OpenStreeMap data:

  WITH bounds AS (
    SELECT ST_TileEnvelope(#{@zoom}, #{@x}, #{@y}) AS envelope
  ),
  tile_geom AS (
    SELECT ST_AsMVTGeom(ST_Transform(l.geom, 3857), bounds.envelope)
    FROM planet_osm_line
  )
  SELECT ST_AsMVT(*) FROM tile_geom;

While all my carrier has dealt with integer, string or even JSON data in the DB, this binary response was new to me. Outputting the result from a Rails controller for consumption by a MapBox GL instance was frustrating, as the tile data was received, but not displayed.

The output from ST_AsMVT() is a protobuf, so not easy to confirm correctness, but pasting the response body into a tool like protobuf-decoder can help verify if the data is a valid protobuf.

The solution was to return the respone unescaped by unescape_bytea():

ActiveRecord::Base.connection.unescape_bytea(response.rows.first&.first)

Well, I’ve been coding in Rails for around 15 years, and I’m still learning…