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

asked 06 Sep '17, 15:06

Rajkumar's gravatar image

Rajkumar
10422353
accept rate: 3%

edited 06 Sep '17, 15:16


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%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.6k19
accept rate: 20%

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's gravatar image

Rajkumar
10422353
accept rate: 3%

edited 06 Sep '17, 18:22

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's gravatar image

Fehrmann
7731532
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%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.6k19
accept rate: 20%

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's gravatar image

Fehrmann
7731532
accept rate: 0%

edited 14 Mar '18, 09:27

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's gravatar image

roks
112
accept rate: 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%20%28OF%29's gravatar image

Stefano (OF) ♦♦
4.6k19
accept rate: 20%

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:

×493

question asked: 06 Sep '17, 15:06

question was seen: 5,125 times

last updated: 17 May '20, 23:07