SQL first try to get Central data into internal PostGIS database

[mathieubossaert]

Some discussion over the forum or with users in France about "how to integrate Central's data to my own database' encourage me to share my recent tries.
Central is a big step forward in projects/users/groups and user rights management. Using it will save a lot of time and gymnastics. Its standard API will help a lot of users to create data analysis, dashboards... I plan to try creating dashboards for specific forms (with all data produced by Collect and stored in Central) using Central's API.
But, in order to transfer Central's data to our main database, I would like to continue as I do quite simply with Aggregate and not to have to rebuild too much "working like a charm" procedures.

So last week I started to explore SQL access to Central's data as we do with Aggregate (which will continue to manage approx 10 existing forms).

I think the method I'll expose here is not the encouraged way to access data (as it was not for Aggregate), but in our context, it is an easy way to use PostgreSQL's functionalities as our information system is already build over PostgreSQL.

PostgreSQL come with some practical capabilities, to connect to a foreign server and process xml data (xpath/xml functions, Foreign Data Wrappers, triggers...).
Some colleagues do that data transfer from Central's database to their main one with an ETL. The kettle example on the forum is a good one : Automating Data Delivery using the OData Endpoint in ODK Central
But I wanted to try to keep the data work-flow as "simple" as possible such as two databases talking together.

We will work through a Foreign Data Wrapper over ODK Central's database with an unprivileged user (SELECT only on Central's tables).
The form we use for this test is the one described here
The form contains 2 nested repeats (the first for locations (localite), the nested one for observations)

CREATE EXTENSION postgres_fdw;

CREATE SERVER odk_central
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'aaa.bbb.ccc.ddd', port '1234', dbname 'odk');

CREATE USER MAPPING FOR local_user SERVER odk_central
    OPTIONS ("user" 'odk_reader', password 'odk_pwd');
/* odk_user exists on Central's server, with only connect to the database, usage to the schema and select rights on odk tables */
	
CREATE SCHEMA odk_central
    AUTHORIZATION dba;
	
IMPORT FOREIGN SCHEMA public LIMIT TO (forms, form_defs, form_fields, submission_defs, submission, submission_attachments)
FROM SERVER odk_central INTO odk_central;
	
SELECT * from odk_central.forms /* to try ;-) */

Now we can query the listed tables.

Let's investigate how Centr'al stores our submission data :

Forms structure is very well described in the database :

SELECT path, form_fields.name, type, "binary", "order"
FROM public.forms JOIN public.form_defs ON "formId" = forms.id
JOIN public.form_fields ON "formDefId" = form_defs.id
WHERE forms.name = 'Sicen2020' AND version = '14'
ORDER BY "order"

Will return each field of the form, numbered from 0 to the last one appearing in the form.

Repeats are typed as 'repeat' (type column) and groups are typed as 'structure'.

Every other form element is typed (string, decimal, integer, datetime, geopoint, geotrace, geoshape...)

And its path from the beginning of the xml entity is mentioned in the path column. So we can now try to generate tables from xml, as in aggregate, the root one (xxxx_CORE) and its child, sometimes nested.

We will use the xmltable() function, available since PostgreSQL 10.

CREATE VIEW odk_central.obs_sicen2020 AS
 WITH xmldata AS (
         SELECT submission_defs.xml::xml AS xml
           FROM odk_central.submission_defs
        ), core AS (
         SELECT "xmltable".phonenumber,
            "xmltable".custom_setting,
            "xmltable".astuce,
            "xmltable".email_utilisateur,
            "xmltable".username,
            "xmltable".nom_observateur,
            "xmltable".mail_observateur,
            "xmltable".now,
            "xmltable".date_time,
            "xmltable".id_etude,
            "xmltable".id_protocole,
            "xmltable".emplacement,
            "xmltable".obs,
            "xmltable".ajout_acompagnateur1,
            "xmltable".acompagnateur1,
            "xmltable".ajout_acompagnateur2,
            "xmltable".acompagnateur2,
            "xmltable".ajout_acompagnateur3,
            "xmltable".acompagnateur3,
            "xmltable".ajout_acompagnateur4,
            "xmltable".acompagnateur4,
            "xmltable".ajout_acompagnateur5,
            "xmltable".acompagnateur5,
            "xmltable".ajout_acompagnateur6,
            "xmltable".acompagnateur6,
            "xmltable".ajout_acompagnateur7,
            "xmltable".acompagnateur7,
            "xmltable".ajout_acompagnateur8,
            "xmltable".acompagnateur8,
            "xmltable".ajout_acompagnateur9,
            "xmltable".acompagnateur9,
            "xmltable".ajout_acompagnateur10,
            "xmltable".acompagnateur10,
            "xmltable".instanceid,
            "xmltable".instancename
           FROM xmldata,
            LATERAL XMLTABLE(('//data'::text) PASSING (xmldata.xml) COLUMNS phonenumber text PATH ('phonenumber'::text), custom_setting text PATH ('custom_setting'::text), astuce text PATH ('utilisateur/astuce'::text), email_utilisateur text PATH ('utilisateur/email_utilisateur'::text), username text PATH ('utilisateur/username'::text), nom_observateur text PATH ('utilisateur/nom_observateur'::text), mail_observateur text PATH ('utilisateur/mail_observateur'::text), now text PATH ('utilisateur/now'::text), date_time date PATH ('utilisateur/date_time'::text), id_etude text PATH ('protocole_etude/id_etude'::text), id_protocole text PATH ('protocole_etude/id_protocole'::text), emplacement xml PATH ('emplacement'::text), obs xml PATH ('emplacement/observation'::text), ajout_acompagnateur1 text PATH ('accompagnateurs/ajout_acompagnateur1'::text), acompagnateur1 text PATH ('accompagnateurs/acompagnateur1'::text), ajout_acompagnateur2 text PATH ('accompagnateurs/ajout_acompagnateur2'::text), acompagnateur2 text PATH ('accompagnateurs/acompagnateur2'::text), ajout_acompagnateur3 text PATH ('accompagnateurs/ajout_acompagnateur3'::text), acompagnateur3 text PATH ('accompagnateurs/acompagnateur3'::text), ajout_acompagnateur4 text PATH ('accompagnateurs/ajout_acompagnateur4'::text), acompagnateur4 text PATH ('accompagnateurs/acompagnateur4'::text), ajout_acompagnateur5 text PATH ('accompagnateurs/ajout_acompagnateur5'::text), acompagnateur5 text PATH ('accompagnateurs/acompagnateur5'::text), ajout_acompagnateur6 text PATH ('accompagnateurs/ajout_acompagnateur6'::text), acompagnateur6 text PATH ('accompagnateurs/acompagnateur6'::text), ajout_acompagnateur7 text PATH ('accompagnateurs/ajout_acompagnateur7'::text), acompagnateur7 text PATH ('accompagnateurs/acompagnateur7'::text), ajout_acompagnateur8 text PATH ('accompagnateurs/ajout_acompagnateur8'::text), acompagnateur8 text PATH ('accompagnateurs/acompagnateur8'::text), ajout_acompagnateur9 text PATH ('accompagnateurs/ajout_acompagnateur9'::text), acompagnateur9 text PATH ('accompagnateurs/acompagnateur9'::text), ajout_acompagnateur10 text PATH ('accompagnateurs/ajout_acompagnateur10'::text), acompagnateur10 text PATH ('accompagnateurs/acompagnateur10'::text), instanceid text PATH ('meta/instanceID'::text), instancename text PATH ('meta/instanceName'::text))
        ), emplacements AS (
         SELECT core_1.instanceid,
            concat('<data>', core_1.emplacement, '</data>')::xml AS localite,
            core_1.obs
           FROM core core_1
        ), localite AS (
         SELECT emplacements.instanceid,
            "xmltable".localite_id,
            "xmltable".methode_geo,
            "xmltable".longitude,
            "xmltable".latitude,
            "xmltable".point,
            "xmltable".trace,
            "xmltable".lenght_trace,
            "xmltable".rounded_lenght_trace,
            "xmltable".shape,
            "xmltable".shape_area,
            "xmltable".rounded_shape_area,
            "xmltable".observations
           FROM emplacements,
            LATERAL XMLTABLE(('//data/emplacement'::text) PASSING (emplacements.localite) COLUMNS localite_id FOR ORDINALITY, methode_geo text PATH ('localisation/methode_geo'::text), longitude numeric PATH ('localisation/longitude'::text), latitude numeric PATH ('localisation/latitude'::text), point text PATH ('localisation/point'::text), trace text PATH ('localisation/trace'::text), lenght_trace text PATH ('localisation/lenght_trace'::text), rounded_lenght_trace text PATH ('localisation/rounded_lenght_trace'::text), shape text PATH ('localisation/shape'::text), shape_area text PATH ('localisation/shape_area'::text), rounded_shape_area text PATH ('localisation/rounded_shape_area'::text), observations xml PATH ('observation'::text))
        ), observations AS (
         SELECT localite_1.instanceid,
            localite_1.localite_id,
            concat('<data>', localite_1.observations, '</data>')::xml AS obs
           FROM localite localite_1
        ), obs AS (
         SELECT observations.instanceid,
            observations.localite_id,
            "xmltable".obs_id,
            "xmltable".useregne,
            "xmltable".calcul_regne,
            "xmltable".type_observation,
            "xmltable".nom_pression,
            "xmltable".intensite,
            "xmltable".atteinte,
            "xmltable".menace,
            "xmltable".nom_observation_generale,
            "xmltable".searchtext_habitat,
            "xmltable".search_nom_habitat,
            "xmltable".recherche_espece_animale,
            "xmltable".lb_nom_animalia,
            "xmltable".cd_nom_animalia,
            "xmltable".recherche_espece_plante,
            "xmltable".lb_nom_plantae,
            "xmltable".cd_nom_plantae,
            "xmltable".recherche_espece_champi,
            "xmltable".lb_nom_fungi,
            "xmltable".cd_nom_fungi,
            "xmltable".groupe,
            "xmltable".adulte_sexe,
            "xmltable".adulte_male,
            "xmltable".adulte_femelle,
            "xmltable".adulte_sexe_indet,
            "xmltable".juvenile_sexe,
            "xmltable".juvenile_male,
            "xmltable".juvenile_femelle,
            "xmltable".juvenile_sexe_indet,
            "xmltable".effectif_textuel,
            "xmltable".adulte,
            "xmltable".juvenile,
            "xmltable".plantule,
            "xmltable".graine,
            "xmltable".larve,
            "xmltable".oeuf,
            "xmltable".age_indet_sexe,
            "xmltable".age_indet_male,
            "xmltable".age_indet_femelle,
            "xmltable".age_indet_sexe_indet,
            "xmltable".no_count,
            "xmltable".total_individu,
            "xmltable".note_indiv,
            "xmltable".determination,
            "xmltable".comportement,
            "xmltable".eff_habitat,
            "xmltable".etat_conservation,
            "xmltable".gestion,
            "xmltable".code_phyto,
            "xmltable".remarque,
            "xmltable".surface_estimee,
            "xmltable".lineaire_estime,
            "xmltable".prendre_image
           FROM observations,
            LATERAL XMLTABLE(('//data/observation'::text) PASSING (observations.obs) COLUMNS obs_id FOR ORDINALITY, useregne text PATH ('type_obs/useregne'::text), calcul_regne text PATH ('type_obs/calcul_regne'::text), type_observation text PATH ('type_obs/type_observation'::text), nom_pression text PATH ('obs/pression/nom_pression'::text), intensite text PATH ('obs/pression/intensite'::text), atteinte text PATH ('obs/pression/atteinte'::text), menace text PATH ('obs/pression/menace'::text), nom_observation_generale text PATH ('obs/observation_generale/nom_observation_generale'::text), searchtext_habitat text PATH ('obs/habitat/searchtext_habitat'::text), search_nom_habitat text PATH ('obs/habitat/search_nom_habitat'::text), recherche_espece_animale text PATH ('obs/animalia/recherche_espece_animale'::text), lb_nom_animalia text PATH ('obs/animalia/lb_nom_animalia'::text), cd_nom_animalia text PATH ('obs/animalia/cd_nom_animalia'::text), recherche_espece_plante text PATH ('obs/plantae/recherche_espece_plante'::text), lb_nom_plantae text PATH ('obs/plantae/lb_nom_plantae'::text), cd_nom_plantae text PATH ('obs/plantae/cd_nom_plantae'::text), recherche_espece_champi text PATH ('obs/fungi/recherche_espece_champi'::text), lb_nom_fungi text PATH ('obs/fungi/lb_nom_fungi'::text), cd_nom_fungi text PATH ('obs/fungi/cd_nom_fungi'::text), groupe text PATH ('obs/groupe'::text), adulte_sexe text PATH ('detail/adulte_sexe'::text), adulte_male integer PATH ('detail/adulte_male'::text), adulte_femelle integer PATH ('detail/adulte_femelle'::text), adulte_sexe_indet integer PATH ('detail/adulte_sexe_indet'::text), juvenile_sexe text PATH ('detail/juvenile_sexe'::text), juvenile_male integer PATH ('detail/juvenile_male'::text), juvenile_femelle integer PATH ('detail/juvenile_femelle'::text), juvenile_sexe_indet integer PATH ('detail/juvenile_sexe_indet'::text), effectif_textuel text PATH ('detail/effectif_textuel'::text), adulte text PATH ('detail/adulte'::text), juvenile text PATH ('detail/juvenile'::text), plantule text PATH ('detail/plantule'::text), graine text PATH ('detail/graine'::text), larve text PATH ('detail/larve'::text), oeuf text PATH ('detail/Oeuf'::text), age_indet_sexe text PATH ('detail/age_indet_sexe'::text), age_indet_male integer PATH ('detail/age_indet_male'::text), age_indet_femelle integer PATH ('detail/age_indet_femelle'::text), age_indet_sexe_indet integer PATH ('detail/age_indet_sexe_indet'::text), no_count text PATH ('detail/no_count'::text), total_individu text PATH ('detail/total_individu'::text), note_indiv text PATH ('detail/note_indiv'::text), determination text PATH ('detail_optionnel/determination'::text), comportement text PATH ('detail_optionnel/comportement'::text), eff_habitat integer PATH ('detail_optionnel/eff_habitat'::text), etat_conservation text PATH ('detail_optionnel/etat_conservation'::text), gestion text PATH ('detail_optionnel/gestion'::text), code_phyto text PATH ('detail_optionnel/code_phyto'::text), remarque text PATH ('detail_optionnel/remarque'::text), surface_estimee text PATH ('detail_optionnel/surface_estimee'::text), lineaire_estime text PATH ('detail_optionnel/lineaire_estime'::text), prendre_image text PATH ('detail_optionnel/prendre_image'::text))
        )
 SELECT row_number() OVER(ORDER BY core.instanceid, localite.localite_id, obs.obs_id) AS gid, core.phonenumber,
    core.custom_setting,
    core.astuce,
    core.email_utilisateur,
    core.username,
    core.nom_observateur,
    core.mail_observateur,
    core.now,
    core.date_time,
    core.id_etude,
    core.id_protocole,
    core.ajout_acompagnateur1,
    core.acompagnateur1,
    core.ajout_acompagnateur2,
    core.acompagnateur2,
    core.ajout_acompagnateur3,
    core.acompagnateur3,
    core.ajout_acompagnateur4,
    core.acompagnateur4,
    core.ajout_acompagnateur5,
    core.acompagnateur5,
    core.ajout_acompagnateur6,
    core.acompagnateur6,
    core.ajout_acompagnateur7,
    core.acompagnateur7,
    core.ajout_acompagnateur8,
    core.acompagnateur8,
    core.ajout_acompagnateur9,
    core.acompagnateur9,
    core.ajout_acompagnateur10,
    core.acompagnateur10,
    core.instanceid,
    core.instancename,
    localite.localite_id,
    localite.methode_geo,
    localite.longitude,
    localite.latitude,
    localite.point,
    localite.trace,
    localite.lenght_trace,
    localite.rounded_lenght_trace,
    localite.shape,
    localite.shape_area,
    localite.rounded_shape_area,
    obs.obs_id,
    obs.useregne,
    obs.calcul_regne,
    obs.type_observation,
    obs.nom_pression,
    obs.intensite,
    obs.atteinte,
    obs.menace,
    obs.nom_observation_generale,
    obs.searchtext_habitat,
    obs.search_nom_habitat,
    obs.recherche_espece_animale,
    obs.lb_nom_animalia,
    obs.cd_nom_animalia,
    obs.recherche_espece_plante,
    obs.lb_nom_plantae,
    obs.cd_nom_plantae,
    obs.recherche_espece_champi,
    obs.lb_nom_fungi,
    obs.cd_nom_fungi,
    obs.groupe,
    obs.adulte_sexe,
    obs.adulte_male,
    obs.adulte_femelle,
    obs.adulte_sexe_indet,
    obs.juvenile_sexe,
    obs.juvenile_male,
    obs.juvenile_femelle,
    obs.juvenile_sexe_indet,
    obs.effectif_textuel,
    obs.adulte,
    obs.juvenile,
    obs.plantule,
    obs.graine,
    obs.larve,
    obs.oeuf,
    obs.age_indet_sexe,
    obs.age_indet_male,
    obs.age_indet_femelle,
    obs.age_indet_sexe_indet,
    obs.no_count,
    obs.total_individu,
    obs.note_indiv,
    obs.determination,
    obs.comportement,
    obs.eff_habitat,
    obs.etat_conservation,
    obs.gestion,
    obs.code_phyto,
    obs.remarque,
    obs.surface_estimee,
    obs.lineaire_estime,
    obs.prendre_image,
	st_transform(odk.geom_from_odk_to_postgis(COALESCE(longitude, split_part(point,' ',2)::numeric), COALESCE(latitude, split_part(point,' ',1)::numeric), trace, shape),2154)
   FROM core
     JOIN localite ON core.instanceid = localite.instanceid
     JOIN obs ON obs.instanceid = localite.instanceid AND obs.localite_id = obs.localite_id;

I can now open my table with QGIS and map my observation, or "move" it to a consolidate observations table

We will have to complete this with binary data and media creation, filter form's name and version... The geom_from_odk_to_postgis() function needs to be improve to use directly the geopoint data.
This is a very first try (no materialization, no indexes, no trigger...), and I maybe missed some subtilities, but it convince myself that Central will be as accessible than Aggregate, with a lot of bonus features.

[seadowg]

Out of interest is there any data you accessed as part of this transfer that you couldn't of as part of the OData API?

As much as this might be possible (grabbing data using SQL from Central's DB) I think it's probably not a great idea to rely on. My understanding is that the database might change without warning as it is not seen as a public interface to Central's data. Someone please correct me if I'm wrong though!

[mathieubossaert]

I think you're right, it is not the method to promote.
I did it as a try by I know that the stable interface to connect to central is its API. I also want to try it with kettle or another ETL and also PostgreSQL FDW like 'www_fdw'.
I'll come back here to complete my tries

[mathieubossaert]

Promising second try, within PostgreSQL with pgsql_http extension.
We query directly Central's Odata service and get a json response :

SELECT content::json FROM http((
'GET', 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions/',
ARRAY[http_header('Authorization','Basic bWF0aGlldS[...]OOGphYi9BQzIw')], NULL, NULL
)::http_request)

PostgreSQL offers a lot of json processing functions and in this scenario, we connect our database to a standardized stable interface !

To be continued

[mathieubossaert]

Happy to close the week on that

Calls to Odata API works great.
I have to investigate a more generic way to create views but it works "like a charm too"

For the moment, I created 3 views like it was (tables) in Aggregate.
So one core view based on an api call to Submissions :

SELECT json_array_elements(http.content::json -> 'value'::text) AS data
FROM http((
    'GET', 
    'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions/',
    ARRAY[http_header('Authorization','Basic bWF0aGlldS[...]OOGphYi9BQzIw')], 
    NULL, 
    NULL
)::http_request)

A second one for the first repeat of the form (emplacements) calling this URL

https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/**Submissions.emplacement/?$wkt=true**

And third one for the second repeat "observation" (nested into emplacement) calling this URL :

https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/**Submissions.emplacement.observation**

Here is the whole SQL (with fake connection parameters) code using json capabilities.
I think it could be more generic, I will try to work on that, but even if it took 2 hours, it will save a lot of time as soon as data will arrive in Central's database.

CREATE OR REPLACE VIEW public.sicen2020_core
 AS
 WITH submissions AS (
         SELECT json_array_elements(http.content::json -> 'value'::text) AS data
           FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
        )
 SELECT submissions.data ->> '__id'::text AS id,
    submissions.data ->> '__Submissions-id'::text AS "__Submissions-id",
        CASE
            WHEN (submissions.data #> '{phonenumber}'::text[]) IS NOT NULL THEN submissions.data -> 'phonenumber'::text
            ELSE NULL::json
        END AS phonenumber,
        CASE
            WHEN (submissions.data #> '{custom_setting}'::text[]) IS NOT NULL THEN submissions.data -> 'custom_setting'::text
            ELSE NULL::json
        END AS custom_setting,
        CASE
            WHEN (submissions.data #> '{utilisateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateur'::text
            ELSE NULL::json
        END AS utilisateur,
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submissionDate'::text
            ELSE NULL::text
        END AS "submissionDate",
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submitterId'::text
            ELSE NULL::text
        END AS "submitterId",
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submitterName'::text
            ELSE NULL::text
        END AS "submitterName",
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'attachmentsPresent'::text
            ELSE NULL::text
        END AS "attachmentsPresent",
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'attachmentsExpected'::text
            ELSE NULL::text
        END AS "attachmentsExpected",
        CASE
            WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'status'::text
            ELSE NULL::text
        END AS status,
        CASE
            WHEN (submissions.data #> '{utilisateurastuce}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurastuce'::text
            ELSE NULL::json
        END AS utilisateurastuce,
        CASE
            WHEN (submissions.data #> '{utilisateuremail_utilisateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateuremail_utilisateur'::text
            ELSE NULL::json
        END AS utilisateuremail_utilisateur,
        CASE
            WHEN (submissions.data #> '{utilisateurusername}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurusername'::text
            ELSE NULL::json
        END AS utilisateurusername,
        CASE
            WHEN (submissions.data #> '{utilisateurnom_observateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurnom_observateur'::text
            ELSE NULL::json
        END AS utilisateurnom_observateur,
        CASE
            WHEN (submissions.data #> '{utilisateurmail_observateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurmail_observateur'::text
            ELSE NULL::json
        END AS utilisateurmail_observateur,
        CASE
            WHEN (submissions.data #> '{utilisateurnow}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurnow'::text
            ELSE NULL::json
        END AS utilisateurnow,
        CASE
            WHEN (submissions.data #> '{utilisateurdate_time}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurdate_time'::text
            ELSE NULL::json
        END AS utilisateurdate_time,
        CASE
            WHEN (submissions.data #> '{protocole_etude}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etude'::text
            ELSE NULL::json
        END AS protocole_etude,
        CASE
            WHEN (submissions.data #> '{protocole_etudeid_etude}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etudeid_etude'::text
            ELSE NULL::json
        END AS protocole_etudeid_etude,
        CASE
            WHEN (submissions.data #> '{protocole_etudeid_protocole}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etudeid_protocole'::text
            ELSE NULL::json
        END AS protocole_etudeid_protocole
   FROM submissions;
   

CREATE OR REPLACE VIEW public.localisations
 AS
 WITH emplacements AS (
         SELECT json_array_elements(http.content::json -> 'value'::text) AS data
           FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions.emplacement/?$wkt=true'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
        )
 SELECT emplacements.data ->> '__id'::text AS id,
    emplacements.data ->> '__Submissions-id'::text AS "__Submissions-id",
        CASE
            WHEN (emplacements.data #> '{heure}'::text[]) IS NOT NULL THEN emplacements.data -> 'heure'::text
            ELSE NULL::json
        END AS heure,
        CASE
            WHEN (emplacements.data #> '{localisation,methode_geo}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'methode_geo'::text
            ELSE NULL::text
        END AS methode_geo,
        CASE
            WHEN (emplacements.data #> '{localisation,longitude}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'longitude'::text
            ELSE NULL::text
        END AS longitude,
        CASE
            WHEN (emplacements.data #> '{localisation,latitude}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'latitude'::text
            ELSE NULL::text
        END AS latitude,
        CASE
            WHEN (emplacements.data #> '{localisation,point}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'point'::text
            ELSE NULL::text
        END AS point,
        CASE
            WHEN (emplacements.data #> '{localisation,trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'trace'::text
            ELSE NULL::text
        END AS trace,
        CASE
            WHEN (emplacements.data #> '{localisation,lenght_trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'lenght_trace'::text
            ELSE NULL::text
        END AS lenght_trace,
        CASE
            WHEN (emplacements.data #> '{localisation,rounded_lenght_trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'rounded_lenght_trace'::text
            ELSE NULL::text
        END AS rounded_lenght_trace,
        CASE
            WHEN (emplacements.data #> '{localisation,shape}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'shape'::text
            ELSE NULL::text
        END AS shape,
        CASE
            WHEN (emplacements.data #> '{localisation,shape_area}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'shape_area'::text
            ELSE NULL::text
        END AS shape_area,
        CASE
            WHEN (emplacements.data #> '{localisation,rounded_shape_area}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'rounded_shape_area'::text
            ELSE NULL::text
        END AS rounded_shape_area,
        CASE
            WHEN (emplacements.data #> '{observation}'::text[]) IS NOT NULL THEN emplacements.data -> 'observation'::text
            ELSE NULL::json
        END AS observation
   FROM emplacements;


CREATE OR REPLACE VIEW public.observations
 AS
 WITH obs AS (
         SELECT json_array_elements(http.content::json -> 'value'::text) AS data
           FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions.emplacement.observation'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
        )
 SELECT obs.data ->> '__id'::text AS id,
    obs.data ->> '__Submissions-emplacement-id'::text AS "__Submissions-emplacement-id",
        CASE
            WHEN (obs.data #> '{heure}'::text[]) IS NOT NULL THEN obs.data -> 'heure'::text
            ELSE NULL::json
        END AS "heu""re",
        CASE
            WHEN (obs.data #> '{type_obs,useregne}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'useregne'::text
            ELSE NULL::text
        END AS useregne,
        CASE
            WHEN (obs.data #> '{type_obs,calcul_regne}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'calcul_regne'::text
            ELSE NULL::text
        END AS calcul_regne,
        CASE
            WHEN (obs.data #> '{type_obs,type_observation}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'type_observation'::text
            ELSE NULL::text
        END AS type_observation,
        CASE
            WHEN (obs.data #> '{obs,pression}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) ->> 'pression'::text
            ELSE NULL::text
        END AS pression,
        CASE
            WHEN (obs.data #> '{obs,pression,nom_pression}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'nom_pression'::text
            ELSE NULL::text
        END AS nom_pression,
        CASE
            WHEN (obs.data #> '{obs,pression,intensite}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'intensite'::text
            ELSE NULL::text
        END AS intensite,
        CASE
            WHEN (obs.data #> '{obs,pression,atteinte}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'atteinte'::text
            ELSE NULL::text
        END AS atteinte,
        CASE
            WHEN (obs.data #> '{obs,pression,menace}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'menace'::text
            ELSE NULL::text
        END AS menace,
        CASE
            WHEN (obs.data #> '{obs,observation_generale}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) -> 'observation_generale'::text
            ELSE NULL::json
        END AS observation_generale,
        CASE
            WHEN (obs.data #> '{obs,observation_generale,nom_observation_generale}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'observation_generale'::text) ->> 'nom_observation_generale'::text
            ELSE NULL::text
        END AS nom_observation_generale,
        CASE
            WHEN (obs.data #> '{obs,habitat,searchtext_habitat}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'habitat'::text) ->> 'searchtext_habitat'::text
            ELSE NULL::text
        END AS searchtext_habitat,
        CASE
            WHEN (obs.data #> '{obs,habitat,search_nom_habitat}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'habitat'::text) ->> 'search_nom_habitat'::text
            ELSE NULL::text
        END AS search_nom_habitat,
        CASE
            WHEN (obs.data #> '{obs,animalia,recherche_espece_animale}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'recherche_espece_animale'::text
            ELSE NULL::text
        END AS recherche_espece_animale,
        CASE
            WHEN (obs.data #> '{obs,animalia,lb_nom_animalia}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'lb_nom_animalia'::text
            ELSE NULL::text
        END AS lb_nom_animalia,
        CASE
            WHEN (obs.data #> '{obs,animalia,cd_nom_animalia}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'cd_nom_animalia'::text
            ELSE NULL::text
        END AS cd_nom_animalia,
        CASE
            WHEN (obs.data #> '{obs,plantae,recherche_espece_plante}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'recherche_espece_plante'::text
            ELSE NULL::text
        END AS recherche_espece_plante,
        CASE
            WHEN (obs.data #> '{obs,plantae,lb_nom_plantae}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'lb_nom_plantae'::text
            ELSE NULL::text
        END AS lb_nom_plantae,
        CASE
            WHEN (obs.data #> '{obs,plantae,cd_nom_plantae}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'cd_nom_plantae'::text
            ELSE NULL::text
        END AS cd_nom_plantae,
        CASE
            WHEN (obs.data #> '{obs,fungi,recherche_espece_champi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'recherche_espece_champi'::text
            ELSE NULL::text
        END AS recherche_espece_champi,
        CASE
            WHEN (obs.data #> '{obs,fungi,lb_nom_fungi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'lb_nom_fungi'::text
            ELSE NULL::text
        END AS lb_nom_fungi,
        CASE
            WHEN (obs.data #> '{obs,fungi,cd_nom_fungi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'cd_nom_fungi'::text
            ELSE NULL::text
        END AS cd_nom_fungi,
        CASE
            WHEN (obs.data #> '{obs,groupe}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) ->> 'groupe'::text
            ELSE NULL::text
        END AS groupe,
        CASE
            WHEN (obs.data #> '{detail,adulte_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_sexe'::text
            ELSE NULL::text
        END AS adulte_sexe,
        CASE
            WHEN (obs.data #> '{detail,adulte_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_male'::text
            ELSE NULL::text
        END AS adulte_male,
        CASE
            WHEN (obs.data #> '{detail,adulte_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_femelle'::text
            ELSE NULL::text
        END AS adulte_femelle,
        CASE
            WHEN (obs.data #> '{detail,adulte_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_sexe_indet'::text
            ELSE NULL::text
        END AS adulte_sexe_indet,
        CASE
            WHEN (obs.data #> '{detail,juvenile_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_sexe'::text
            ELSE NULL::text
        END AS juvenile_sexe,
        CASE
            WHEN (obs.data #> '{detail,juvenile_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_male'::text
            ELSE NULL::text
        END AS juvenile_male,
        CASE
            WHEN (obs.data #> '{detail,juvenile_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_femelle'::text
            ELSE NULL::text
        END AS juvenile_femelle,
        CASE
            WHEN (obs.data #> '{detail,juvenile_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_sexe_indet'::text
            ELSE NULL::text
        END AS juvenile_sexe_indet,
        CASE
            WHEN (obs.data #> '{detail,effectif_textuel}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'effectif_textuel'::text
            ELSE NULL::text
        END AS effectif_textuel,
        CASE
            WHEN (obs.data #> '{detail,adulte}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte'::text
            ELSE NULL::text
        END AS adulte,
        CASE
            WHEN (obs.data #> '{detail,juvenile}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile'::text
            ELSE NULL::text
        END AS juvenile,
        CASE
            WHEN (obs.data #> '{detail,plantule}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'plantule'::text
            ELSE NULL::text
        END AS plantule,
        CASE
            WHEN (obs.data #> '{detail,graine}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'graine'::text
            ELSE NULL::text
        END AS graine,
        CASE
            WHEN (obs.data #> '{detail,larve}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'larve'::text
            ELSE NULL::text
        END AS larve,
        CASE
            WHEN (obs.data #> '{detail,Oeuf}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'Oeuf'::text
            ELSE NULL::text
        END AS "Oeuf",
        CASE
            WHEN (obs.data #> '{detail,age_indet_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_sexe'::text
            ELSE NULL::text
        END AS age_indet_sexe,
        CASE
            WHEN (obs.data #> '{detail,age_indet_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_male'::text
            ELSE NULL::text
        END AS age_indet_male,
        CASE
            WHEN (obs.data #> '{detail,age_indet_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_femelle'::text
            ELSE NULL::text
        END AS age_indet_femelle,
        CASE
            WHEN (obs.data #> '{detail,age_indet_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_sexe_indet'::text
            ELSE NULL::text
        END AS age_indet_sexe_indet,
        CASE
            WHEN (obs.data #> '{detail,no_count}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'no_count'::text
            ELSE NULL::text
        END AS no_count,
        CASE
            WHEN (obs.data #> '{detail,total_individu}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'total_individu'::text
            ELSE NULL::text
        END AS total_individu,
        CASE
            WHEN (obs.data #> '{detail,note_indiv}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'note_indiv'::text
            ELSE NULL::text
        END AS note_indiv,
        CASE
            WHEN (obs.data #> '{detail_optionnel,determination}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'determination'::text
            ELSE NULL::text
        END AS determination,
        CASE
            WHEN (obs.data #> '{detail_optionnel,comportement}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'comportement'::text
            ELSE NULL::text
        END AS comportement,
        CASE
            WHEN (obs.data #> '{detail_optionnel,eff_habitat}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'eff_habitat'::text
            ELSE NULL::text
        END AS eff_habitat,
        CASE
            WHEN (obs.data #> '{detail_optionnel,etat_conservation}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'etat_conservation'::text
            ELSE NULL::text
        END AS etat_conservation,
        CASE
            WHEN (obs.data #> '{detail_optionnel,gestion}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'gestion'::text
            ELSE NULL::text
        END AS gestion,
        CASE
            WHEN (obs.data #> '{detail_optionnel,code_phyto}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'code_phyto'::text
            ELSE NULL::text
        END AS code_phyto,
        CASE
            WHEN (obs.data #> '{detail_optionnel,remarque}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'remarque'::text
            ELSE NULL::text
        END AS remarque,
        CASE
            WHEN (obs.data #> '{detail_optionnel,surface_estimee}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'surface_estimee'::text
            ELSE NULL::text
        END AS surface_estimee,
        CASE
            WHEN (obs.data #> '{detail_optionnel,lineaire_estime}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'lineaire_estime'::text
            ELSE NULL::text
        END AS lineaire_estime,
        CASE
            WHEN (obs.data #> '{detail_optionnel,prendre_image}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'prendre_image'::text
            ELSE NULL::text
        END AS prendre_image,
        CASE
            WHEN (obs.data #> '{detail_optionnel,prise_image}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'prise_image'::text
            ELSE NULL::text
        END AS prise_image
   FROM obs;

I can now JOIN those 3 tables to create my complete data (it is an exemple, really not optimized) :

SELECT *
FROM sicen2020_core AS core
JOIN localisations AS loc ON loc."__Submissions-id" = core.id
JOIN observations AS obs ON obs."__Submissions-emplacement-id" = loc.id

Accessing the API from our PostgreSQL database is a great step forward.
It has some temporary limitations but real big advantages.
About the advantages :

• as it was pointed by @seadowg here, Odata API is THE interface to query Central, with the stability that could not be guarantee on the database schema.
• Geometries are exposed in WKT or geojson

About the limitation, I understood that for the moment we can only query all the data from Central. But maybe I missed some explanations in the documentation I read too quickly. It would be great to query about submissions with, for example, an id higher than the last we get into the database.
We have now the efficiency we had with aggregate SQL direct access, through a stable public interface, and the new features from Central.

[mathieubossaert]

@mathieubossaert

I understood that for the moment we can only query all the data from Central. But maybe I missed some explanations in the documentation I read too quickly. It would be great to query about submissions with, for example, an id higher than the last we get into the database.

This limitation seems to be very temporary

@Matthew_White

As of the current version (v1.0), Central does not have the ability to filter data. In v1.1, we plan to add the ability to filter by submission date and submitter.

[Morck]

@mathieubossaert thank you for sharing this. Maybe you or others can give a hint why I´m having this issue. My build is done following the official docker guide . I want to connect to the database by mapping the container port (5432 --> 5432) and succeded. But backside is that logging in to central fails ( an error pop-up comes up). Note, that I made an new instance(container) of the originally container and mapped it for remote connection.

So there must be a part that I´m missing?

[mathieubossaert]

Hi @Morck,

@Morck

Note, that I made an new instance(container) of the originally container and mapped it for remote connection.

I do not really understand what you did :

After those tries, I will continue to use API calls from my internal database instead of parsing xml over FDW_Postgres

[Morck]

I was unclear there. With new instance I meant a new clone of the postgresql container - but mapped to host. I´m testing another approach now with a nodered setup. I will share the setup when succeded.

[mathieubossaert]

Some work in progress about retrieving central submission data into PostgreSQL without any external tool (except the http_fdw).
Would be nice to also test the "COPY FROM program" PostgreSQL capabilities (COPY FROM curl...)
But for the moment this quite simple SQL query works fine, even if I have to find a way to manage potential connection problem to central (ex. timeout).
Also have to improve it with the filtering possibility of central submissions
And why not dynamically build this query using the "Form schema field" from central's API

This version is simplest than the first one because it is not necessary to test if a path exists in the json, if it does not, it returns a null value.

SELECT http_set_curlopt('CURLOPT_SSL_VERIFYHOST', '0'), 
	http_set_curlopt('CURLOPT_SSL_VERIFYPEER', '0');
	
with submissions as(
SELECT json_array_elements(http.content::json -> 'value'::text) AS form_data
FROM http((
	'GET', 
	'https://my_central_instance.org/v1/projects/4/forms/Sicen.svc/Submissions',
	ARRAY[http_header('Authorization',concat('Basic ',encode('myemail@adress.org:PyPassw0rd','base64'),''''))], 
	NULL, 
	NULL
	)::http_request))
, 
emplacements as(
	SELECT json_array_elements(http.content::json -> 'value'::text) AS form_data
	FROM http((
	'GET', 
	'https://my_central_instance.org/v1/projects/4/forms/Sicen.svc/Submissions.emplacements',
	ARRAY[http_header('Authorization',concat('Basic ',encode('myemail@adress.org:PyPassw0rd','base64'),''''))], 
	NULL, 
	NULL
	)::http_request)) , 
observations as(
	SELECT json_array_elements(http.content::json -> 'value'::text) AS form_data
	FROM http((
	'GET',
	'https://my_central_instance.org/v1/projects/4/forms/Sicen.svc/Submissions.emplacements.localites.observations',
	ARRAY[http_header('Authorization',concat('Basic ',encode('myemail@adress.org:PyPassw0rd','base64'),''''))], 
	NULL, 
	NULL
	)::http_request)),
contexte AS (
	SELECT form_data, form_data ->> '__id'::text AS submission_id,
	((form_data -> '__system'::text) -> 'submissionDate'::text)::text
	::date AS submissionDate,
	((form_data -> '__system'::text) -> 'submitterName'::text)::text AS submitterName,
	((form_data -> 'utilisateur'::text) -> 'email_utilisateur'::text)::text AS user_mail,
	((form_data -> 'utilisateur'::text) -> 'user_name'::text)::text AS user_name,
	((form_data -> 'utilisateur'::text) -> 'date_time'::text)::text	::timestamp AS date_time
	FROM submissions
),
localite AS (
	SELECT form_data ->> '__id'::text AS id_localite,
	 form_data ->> '__Submissions-id'::text AS submission_id,
	split_part(((form_data -> 'localites'::text) -> 'loc'::text) ->> 'heure_localite'::text,' ',2) as heure_localite,
	((form_data -> 'localites'::text) -> 'loc'::text) ->> 'methode_geo'::text AS methode_geo,
	COALESCE(
		((form_data -> 'localites'::text) -> 'loc'::text) ->> 'point'::text,
		((form_data -> 'localites'::text) -> 'loc'::text) ->> 'trace'::text,
		((form_data -> 'localites'::text) -> 'loc'::text) ->> 'shape'::text,
		CONCAT(
			'POINT (',
			((form_data -> 'localites'::text) -> 'loc'::text) ->> 'longitude'::text,' ',
			((form_data -> 'localites'::text) -> 'loc'::text) ->> 'latitude'::text,')'
 		)
	) AS wkt
	FROM emplacements
),
obs AS (
	--SELECT * FROM  datas
	SELECT form_data ->> '__id'::text AS id_observation,
	form_data ->> '__Submissions-emplacements-id'::text AS id_localite,
	((form_data -> '__system'::text) -> 'submissionDate'::text)::text AS submissionDate,
	--
	((form_data -> 'obs'::text) -> 'animalia_selection'::text) ->> 'cd_nom_animalia'::text,
	((form_data -> 'obs'::text) -> 'heure_obs'::text) AS heure_obs,
	(form_data -> 'obs'::text) ->> 'type_observation'::text AS type_observation,
	(form_data -> 'obs'::text) ->> 'pression'::text AS pression,
	((form_data -> 'obs'::text) -> 'pression'::text) ->> 'nom_pression'::text AS nom_pression,
	((form_data -> 'obs'::text) -> 'pression'::text) ->> 'intensite'::text AS intensite,
	((form_data -> 'obs'::text) -> 'pression'::text) ->> 'atteinte'::text AS atteinte,
	((form_data -> 'obs'::text) -> 'pression'::text) ->> 'menace'::text AS menace,
	(form_data -> 'obs'::text) -> 'observation_generale'::text AS observation_generale,
	((form_data -> 'obs'::text) -> 'observation_generale'::text) ->> 'nom_observation_generale'::text AS nom_observation_generale,
	((form_data -> 'obs'::text) -> 'habitat'::text) ->> 'searchtext_habitat'::text AS searchtext_habitat,
	((form_data -> 'obs'::text) -> 'habitat'::text) ->> 'search_nom_habitat'::text AS search_nom_habitat,
	COALESCE(
		((form_data -> 'obs'::text) -> 'animalia_selection'::text) ->> 'cd_nom_animalia'::text,
	((form_data -> 'obs'::text) -> 'plantae_selection'::text) ->> 'cd_nom_plantae'::text,
	((form_data -> 'obs'::text) -> 'fungi_selection'::text) ->> 'cd_nom_fungi'::text
		)AS cd_nom,
		COALESCE(
	((form_data -> 'obs'::text) -> 'animalia_selection'::text) ->> 'lb_nom_animalia'::text,
	((form_data -> 'obs'::text) -> 'plantae_selection'::text) ->> 'lb_nom_plantae'::text,
	((form_data -> 'obs'::text) -> 'fungi_selection'::text) ->> 'lb_nom_fungi'::text 
			)AS lb_nom,
		COALESCE(
	((form_data -> 'obs'::text) -> 'animalia_selection'::text) ->> 'recherche_animalia'::text,
	((form_data -> 'obs'::text) -> 'plantae_selection'::text) ->> 'recherche_plantae'::text,
	((form_data -> 'obs'::text) -> 'fungi_selection'::text) ->> 'recherche_fungi'::text
			)AS recherche,
	(form_data -> 'obs'::text) ->> 'groupe'::text AS groupe,
	(form_data -> 'obs'::text) ->> 'version_taxref'::text AS version_taxref,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'adulte_sexe'::text AS adulte_sexe,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'adulte_male'::text AS adulte_male,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'adulte_femelle'::text AS adulte_femelle,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'adulte_sexe_indet'::text AS adulte_sexe_indet,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'juvenile_sexe'::text AS juvenile_sexe,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'juvenile_male'::text AS juvenile_male,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'juvenile_femelle'::text AS juvenile_femelle,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'juvenile_sexe_indet'::text AS juvenile_sexe_indet,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'effectif_textuel'::text AS effectif_textuel,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'adulte'::text AS adulte,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'juvenile'::text AS juvenile,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'plantule'::text AS plantule,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'graine'::text AS graine,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'larve'::text AS larve,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'Oeuf'::text AS "Oeuf",
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'age_indet_sexe'::text AS age_indet_sexe,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'age_indet_male'::text AS age_indet_male,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'age_indet_femelle'::text AS age_indet_femelle,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'age_indet_sexe_indet'::text AS age_indet_sexe_indet,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'no_count'::text AS no_count,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'total_individu'::text AS total_individu,
	((form_data -> 'obs'::text) -> 'detail'::text) ->> 'note_indiv'::text AS note_indiv,
	((form_data -> 'obs'::text) -> 'qualite'::text) ->> 'determination'::text AS determination,
	((form_data -> 'obs'::text) -> 'qualite'::text) ->> 'sensible'::text AS sensible,
	((form_data -> 'obs'::text) -> 'qualite'::text) ->> 'fiabilite'::text AS fiabilite,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'comportement'::text AS comportement,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'eff_habitat'::text AS eff_habitat,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'etat_conservation'::text AS etat_conservation,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'gestion'::text AS gestion,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'code_phyto'::text AS code_phyto,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'remarque'::text AS remarque,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'surface_estimee'::text AS surface_estimee,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'lineaire_estime'::text AS lineaire_estime,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'prendre_image'::text AS prendre_image,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'prise_image'::text AS prise_image,
	((form_data -> 'obs'::text) -> 'detail_optionnel'::text) ->> 'remarque'::text AS remarque
	FROM observations
)
SELECT contexte.*, obs.*, localite.wkt
FROM contexte 
JOIN localite USING(submission_id)
JOIN obs USING (id_localite)

[mathieubossaert]

Central2pg : PostgreSQL set of functions to get data from Central

Hi to all, here is a first shared version of a recent hard work, which allow us to automate data retrieval from Central into PostgreSQL/PostGIS database. Functions dynamically create PostgreSQL tables from Central's json and keep geojson fields as geojson. Functions are here : https://framagit.org/mathieubossaert/central2pg Feel free to comment, criticize and improve the work. Coming features into central will facilitate its improvement. I will have some questions about some details ; I ju…