Central2pg : PostgreSQL set of functions to get data from Central

[mrrodge]

@mathieubossaert

But if your problem is "only" to get pictures from central, I'm sure a small python wrapper using pyODK could serve you the photos directly from Central instead of building all my database centric workflow.
But I will be glad to help if

Yup - that, at the moment, is my only problem! Any pointers for where to start with a wrapper like you suggest? I've no experience with python either.

Many thanks for your efforts - it's greatly appreciated.

Looking at the get_file_from_central function, it suggests that files are saved to the filesystem but I'm guessing I would need to host them somehow to get them into my reports.

[mathieubossaert]

@mrrodge

Looking at the get_file_from_central function, it suggests that files are saved to the filesystem but I'm guessing I would need to host them somehow to get them into my reports.

Yes, in our case, the folder is used and visible by both Apache and PostgreSQL (user postgres) but it could be anywhere else your reporting tool can access

[mrrodge]

OK thanks - getting the idea now and am almost there. How do you use the get_file_from_central command? It looks like that will only download one image, where I want to download all the images for the form submissions I got with odk_central_to_pg.

Also what does it do with the file names? What's prise_image?

Thanks again!

[mathieubossaert]

Let's continue the discussion with direct messages or over Github

[mathieubossaert]

We achieved to get it work. Function were not ready to accept spaces chars in the form_id.
https://forum.cen-occitanie.org/t/suivi-bourreau-des-arbres/963/6?u=mathieu
I will also improve the example given on github's README page.
Thanks @mrrodge, maybe we can clean the showcase and drop the messages above ?

[mathieubossaert]

People who used central2pg may take a look at pl-pyodk.
It allows the use of filters to pull data from central.

Pl-pyodk : use of pyODK with PL/Python PostgreSQL functions to pull data from Central into your own database

Two years ago, I spent time during the third COVID lock-down working on PostgreSQL functions to automatically pull data from Central into a PostgreSQL database. Central2PG is the result of that research. And we've been able to maintain the workflows we built on Aggregate since 2015 on Central Every time we publish a new form, we simply set up a scheduled task that uses Central2PG to automatically pull the data into a PostgreSQL database. Central2PG works very well and we never …

[mathieubossaert]

With Central 2023.4 we can now filter data from repeat table using a top level (Submissions) filter as the submission date.
central2pg can make use of it right now and allows you to get only data that were submitted since a given timestamp or date
For example, data from our main form are pulled every hour to get fresh data, without filtering, we needed to pull all the data created since the form was published (90000) !

Now we can filter subtables on submission date and pull only the 37 observations created since last known submission date

As a consequence we can increase a lot the frequency of pulling data from Central and be very close to a real time field sync between our databases or desktop tools and the field.

Thanks a lot to the ODK Team !