Hi all,
I thought I would share how I have used mathieubossaert's showcase on getting data from odk-central to a PostgreSQL database to create a nearly live webmap of data collected with ODK.
I'm using his postgres functions from github to connect to ODK central and pull the form I want into a table in my postgres database. In this case, I'm using a form that tracks tree planting in a reforestation project by blocks. The postgres database has a postgis table that holds all the information for the blocks. There is a column for whether the block was planted or not and a column for the planting date. I have a postgres function to update those columns based on matching block names in the table pulled from ODK central.
To create the layout for the webmap, I'm using the qgis2web plugin available in QGIS. It does an amazing job of replicating QGIS styles in Leaflet and generates a complete directory structure for a custom webmap. Within the directory structure is a folder called data. In that folder, there is a script for each of the layers from QGIS. When I opened those scripts, I realized that they are essentially geojson objects with a variable declaration at the beginning.
I found a blog post here that describes how to build geojson objects from postgis objects in postgres. Then I use the postgres CONCAT and COPY functions to add the correct variable declaration and output the result to replace the original script for the QGIS layer.
The complete script is something like this
COPY(SELECT concat('var the_qgis_layer_name_# = ',
json_build_object('type', 'FeatureCollection', 'features',
json_agg(st_asgeojson(t.*)::json)))
FROM the_updated_postgis_table t)
TO 'the_qgis2web_directory/data/the_qgis_layer_name_#.js';
Then I automated the whole process using pg_cron so the data is updated each night.
Here is a screenshot of the result:
