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 carreer 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…
