I would like to use the data in a Collect database for purposes outside of Open Foris. I have set up the survey and am using PostgreSQL as my backend. I have found the collect.ofc_record_data table which seems to correspond to the place where the data in the records from the survey are held. The values appear to be held in the 'data' field of this table, however, the datatype is 'bytea', which is not human readable. I would like to convert that into human readable text in PostgreSQL, can someone please help me do this?

asked 02 Oct, 18:46

thayer_young's gravatar image

thayer_young
111
accept rate: 0%


Dear Thayer,
You are right, the data is stored in the collect.ofc_record_data table, but it's in a binary format and you can read it only through Collect (this is because the structure of the data is dynamic and it depends on the related survey).
If you need to view the data using a proper relational database, you can try to generate the Saiku database (go to the Saiku link on the sidebar and click on Generate).
The relational database will be generated inside C:\Users\YOUR_USER\OpenForis\Collect\data\rdb and it will be named as your survey, with a suffix for every record phase (entry, cleansing, analysis).
That database is a SQLite database that you can query with whatever software you want and there will be one table for each entity in your survey.
I hope it can be useful.
Many thanks,
Open Foris Team

permanent link

answered 03 Oct, 17:27

OF%20Collect's gravatar image

OF Collect ♦♦
2.0k5
accept rate: 19%

Thanks for your answer. I appreciate the need to keep things dynamic and I am sure that the binary also reduces storage space and improves data transfer time. I have some additional questions though. I was excited to see that I could use PostgreSQL for the backend, because I would like to dynamically query the database and to display results on a public webpage, e.g. with PHP or JSP. Another use case would be for me to be able to query the live database with an SQL editor like PGAdmin. I was also happy to see that Open Foris is Java based, as I have experience with Java. From your answer it sounds like I will need to manually make a dump every time the database changes, and then work out a way to upload that to a database that would be queried for the webpage. While this is not completely unreasonable it would be very inconvenient. Instead, I am wondering if you could please suggest another alternative for this use case, or point to where in the Java code the binary is converted back to human readable text? I can dig through the code base myself but as it is quite voluminous it would help to have an idea where to look. From there I can adapt the code to my purpose. Thank you for your additional attention to this matter.

permanent link

answered 03 Oct, 20:43

thayer_young's gravatar image

thayer_young
111
accept rate: 0%

Dear Thayer,
You can try to have a look to the collect-rdb module: this module takes care of the conversion of the data into a relational schema.
It should be easily understandable for who knows a bit of Java.
Many thanks,
Open Foris Team

permanent link

answered 04 Oct, 09:21

OF%20Collect's gravatar image

OF Collect ♦♦
2.0k5
accept rate: 19%

Thanks for the direction to collect-rdb. I am working my way through the code and will try loading it into Eclipse and try running some of the test classes. If you leave the thread open I can post further questions if they come up. PS sorry for using the answer field and not comment. I wasn't paying attention.

(04 Oct, 17:58) thayer_young thayer_young's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×272

question asked: 02 Oct, 18:46

question was seen: 46 times

last updated: 04 Oct, 17:58