How to send PG binary data from the Rails controller?

Question

I have a Postgres database with PostGIS extension and some geo-data in it. I need to generate tiles with custom SQL queries (using ST_... functions), that returns binary data (bytea, like \x1a320a0474657374121d12020000180322150946ec3f1a14453b0a09280f091413121e09091e0f1a026331220228012880207802). And I need to send this data from my Rails controller to the requester, with the headers like 'Content-Type: x-application/protobuf'.

So, my question is:

1) How to get the binary data from the Database (Sequel, ActiveRecord::Base.connection.execute ?) 2) How to return this data in the controller action? (send_data ..., tyle: '' ?)

Thanks!

Do you mean, that you need to use one of postgres function that will generate a binary data as an output and then you need to send this data to the client? — AntonTkachov
– AntonTkachov, Commented Nov 4, 2017 at 12:56
@AntonTkachov exactly! ActiveRecord::Base.connection.execute returns PG::Result object, and I don't sure that i can get raw binary data from it. — Jack Owels
– Jack Owels, Commented Nov 4, 2017 at 13:41
1. Can you provide a sample of your query? 2. Does sql console return you a proper binary string, that you expect? — AntonTkachov
– AntonTkachov, Commented Nov 4, 2017 at 13:42
@AntonTkachov 1) SELECT ST_AsMVT(q, 'table', 512, 'geom') AS mvt FROM ( WITH a AS ( SELECT ST_AsMVTGeom( ST_Transform(public.table.wkb_geometry, 3857), TileBBox(13, 2411, 3078, 3857), 512, 256, true ) geom , columns FROM public.table WHERE ST_Intersects(TileBBox(13, 2411, 3078, 4326), public.table.wkb_geometry) ) SELECT * FROM a WHERE geom IS NOT NULL ) AS q , 2) yes, in form of Postgres (see example in my answer). How can I get raw data from PG::Result? — Jack Owels
– Jack Owels, Commented Nov 17, 2017 at 9:53

Wonyoung So · Accepted Answer · 2020-01-11 16:23:35Z

3

I just figured out the solution - hope it helps, although it's a long time ago..

records_array = ActiveRecord::Base.connection.execute(query)
result = records_array[0]['st_asmvt']
encoded = ActiveRecord::Base.connection.unescape_bytea(result)

ActiveRecord::Base.connection.unescape_bytea is the answer.

answered Jan 11, 2020 at 16:23

Wonyoung So

312 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to send PG binary data from the Rails controller?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related