connection of collect with postgreSQL

Hi every one, I want to connect my collect desktop with postgresql database. I followed the following procedure described here https://openforis.support/questions/1228/how-to-set-up-postgresql-server-for-new-collect I could not get success. how can I connect with it? Is there special process. what should I do in postgres database eg. the name of database, schema, username etc please guide me. thank you very much. The error msg was as this alt text

collect

asked 06 Sep '17, 15:06

Rajkumar
104●2●28●57
accept rate: 3%

edited 06 Sep '17, 15:16

7 Answers:

active answers oldest answers newest answers popular answers

Hi Raj kumar,
Good that you find yourself the way to setup Collect with PostgreSQL database.
The data you record is stored into the table ofc_record. Every record is stored into a binary column of that table (the columns data1 or data2 depending on the record's phase, entry or cleansing/analysis).
The reason why the data is stored in that format is because the structure of the data it's dynamic and depends on the survey schema structure. Anyway, we are planning to move from this way of storing the data into a more "open" way where the user can actually see the values stored for each record, but you will see the new structure only by the beginning of next year.
Many thanks,
Open Foris Team

permanent link

answered 06 Sep '17, 22:17

Stefano (OF) ♦♦
4.8k●1●1●9
accept rate: 19%

Hi all, I am successful to connect with postgres database. I changed username and password in collect.porperties file in Data folder. The password was that which I put when installing the postgres database. Now I can enter the data and save through collect desktop application.Now my new question is that in which table the data is stored that I entered? I have created a database named collect and schema also collect.

permanent link

answered 06 Sep '17, 17:59

Rajkumar
104●2●28●57
accept rate: 3%

edited 06 Sep '17, 18:22

0	Dear developers, is there any news about this issue? I also set up Collect with PostgreSQL and it would be a huge step forward if we would be able to access the data from there. Regards, Lutz permanent link answered 07 Mar '18, 21:41 Fehrmann 77●3●19●35 accept rate: 0%

Dear Lutz,
We are still working on this, it will take time to change the way we store the records into the database.
Meanwhile, if you need to access the data using a relational database, you can consider accessing the relational database generated when using Saiku: this database is a SQLite database and is stored under c:\Users\YOUR_USERNAME\OpenForis\Collect\data\rdb.
For every survey there will be 3 different files (one per record "step").
Will it be sufficient for you?
Let us know, many thanks.
Open Foris Team

permanent link

answered 13 Mar '18, 16:39

Stefano (OF) ♦♦
4.8k●1●1●9
accept rate: 19%

Thank you, this is a solution I have not thought about so far. I tested it and it works nicely. I can now connect the sqlite .db directly to RStudio and can access all my tables and data there. Thank you for this hint, very useful!

For others who are looking for that, the respective R code to access the SQLite.db would be

library(RSQLite)

filename <- "C:/Users/YOUR USER ACCOUNT/OpenForis/Collect/data/rdb/YOUR.db"

sqlite.driver <- dbDriver("SQLite")

db <- dbConnect(sqlite.driver, dbname = filename)

dbListTables(db)

plots <- dbReadTable(db,"plot")

Lutz

permanent link

answered 13 Mar '18, 17:53

Fehrmann
77●3●19●35
accept rate: 0%

edited 14 Mar '18, 09:27

0	Hello, is there any news regarding saving data to a PostgreSQL database as normal entries which can be accessed from outside Open Foris (directly with SQL queries)? permanent link answered 13 May '20, 09:27 roks 11●2 accept rate: 0%

0	Dear Rok, Unfortunately there are no new from this side. We will let you know when such a feature will be available. Thanks, Open Foris Team permanent link answered 17 May '20, 23:07 Stefano (OF) ♦♦ 4.8k●1●1●9 accept rate: 19%

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

learn more about Markdown

Question tags:

collect ×494

question asked: 06 Sep '17, 15:06

question was seen: 5,627 times

last updated: 17 May '20, 23:07

connection of collect with postgreSQL

Follow this question

Related questions