# ODK to PostgreSQL to nearly live Webmap

**URL:** <https://forum.getodk.org/t/odk-to-postgresql-to-nearly-live-webmap/36973>\
**Category:** Showcase\
**Tags:** odk-central\
**Created:** [March 10, 2022, 7:06am UTC](https://forum.getodk.org/t/odk-to-postgresql-to-nearly-live-webmap/36973 "2022-03-10T07:06:58Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Theron\_Morgan-Brown1](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/theron_morgan-brown1/32/15693_2.png) [@Theron\_Morgan-Brown1](https://forum.getodk.org/u/Theron_Morgan-Brown1)\
**Post date:** [March 10, 2022, 7:06am UTC](https://forum.getodk.org/t/odk-to-postgresql-to-nearly-live-webmap/36973/1 "2022-03-10T07:06:58Z")

</div>

Hi all,

I thought I would share how I have used [mathieubossaert](https://forum.getodk.org/u/mathieubossaert)'s [showcase](https://forum.getodk.org/t/postgresql-set-of-functions-to-get-data-from-central/33350) 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](https://github.com/mathieubossaert/central2pg/tree/master) 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](https://www.flother.is/til/postgis-geojson/) 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:

 ![Screen Shot 2022-03-10 at 10.06.12 AM](https://getodk.b-cdn.net/uploads/default/original/3X/0/1/0136a9b78054b10e11e7b3aa6159ddd0d6eb8142.jpeg)