Need to create spreadsheet download function in SQL boot up scripts

[timlinux]

@meomancer already added it to the DB, please make sure it is also in our initialisation SQL.

create OR REPLACE function flood_event_spreadsheet(flood_event_id integer) returns TABLE(spreadsheet_content text)
  language plpgsql
as
$$
begin return query
        select encode(spreadsheet, 'base64') as spreadsheet_content from flood_event where id=flood_event_id;
    end;
$$;

[timlinux]

Also please add this one to create the flooded layer view.

create or replace view vw_flood_event_buildings_map as
select b.geometry, b.building_type, b.district_id, b.sub_district_id, b.village_id, feb.depth_class_id, feb.flood_event_id
    from osm_buildings as b, flood_event_buildings as feb;
comment on view vw_flood_event_buildings_map is 'Flooded event buildings map view. Added by Tim to show when we select a flood.';

[meomancer]

please also this one

create OR REPLACE function flood_event_newest_forecast_f(forecast_date_start timestamp without time zone, forecast_date_end timestamp without time zone) returns TABLE(forecast_date_str text, acquisition_date_str text, trigger_status_id int)
  language plpgsql
as
$$
begin return query
        select distinct on (forecast_date_str) a.forecast_date_str, a.acquisition_date_str, a.trigger_status
        from (
            select id, to_char(forecast_date, 'YYYY-MM-DD') as forecast_date_str, to_char(acquisition_date, 'YYYY-MM-DD') as acquisition_date_str, trigger_status from flood_event
            where forecast_date >= forecast_date_start and forecast_date < forecast_date_end AND forecast_date IS NOT NULL
    ) as a ORDER BY a.forecast_date_str DESC, a.acquisition_date_str DESC;
    end;
$$;

it's needed to fetch forecast for calendar

[NyakudyaA]

I have added this in the script and pushed to my branch for now.

[NyakudyaA]

Also please add this one to create the flooded layer view.

create or replace view vw_flood_event_buildings_map as
select b.geometry, b.building_type, b.district_id, b.sub_district_id, b.village_id, feb.depth_class_id, feb.flood_event_id
  from osm_buildings as b, flood_event_buildings as feb;
comment on view vw_flood_event_buildings_map is 'Flooded event buildings map view. Added by Tim to show when we select a flood.';

@timlinux Using this view as it is will not work in geoserver because we have to have a unique column for pk. we can't use the district_id etc because if we expect to use them in the front end.

Should we not be returning unique values ?

[timlinux]

@NyakudyaA there was also an issue with my query as it was not doing the join between the buildings and the flood_event_buildints table. The query below adds a unique row number:

create or replace view vw_flood_event_buildings_map as
select 
    row_number() OVER () as id, 
    b.geometry, 
    b.building_type, 
    b.district_id, 
    b.sub_district_id, 
    b.village_id, 
    feb.depth_class_id, 
    feb.flood_event_id
from osm_buildings as b, flood_event_buildings as feb
    where feb.building_id=b.id;
comment on view vw_flood_event_buildings_map is 'Flooded event buildings 
map view. Added by Tim to show when we select a flood.';

However I have an issue when trying to do a simple selection from this:

gis=# select count (*) from vw_flood_event_buildings_map ;
ERROR:  could not resize shared memory segment "/PostgreSQL.1457254016" to 8388608 bytes: No space left on device
CONTEXT:  parallel worker

There is however plenty of disk space. I googled around a bit and there were some comments about pg settings e.g. https://stackoverflow.com/questions/55803015/google-cloud-sql-pg11-could-not-resize-shared-memory-segment though I am not sure if these are relevant...

[NyakudyaA]

@NyakudyaA there was also an issue with my query as it was not doing the join between the buildings and the flood_event_buildints table. The query below adds a unique row number:

create or replace view vw_flood_event_buildings_map as
select 
    row_number() OVER () as id, 
    b.geometry, 
    b.building_type, 
    b.district_id, 
    b.sub_district_id, 
    b.village_id, 
    feb.depth_class_id, 
    feb.flood_event_id
from osm_buildings as b, flood_event_buildings as feb
  where feb.building_id=b.id;
comment on view vw_flood_event_buildings_map is 'Flooded event buildings 
map view. Added by Tim to show when we select a flood.';

However I have an issue when trying to do a simple selection from this:

gis=# select count (*) from vw_flood_event_buildings_map ;
ERROR:  could not resize shared memory segment "/PostgreSQL.1457254016" to 8388608 bytes: No space left on device
CONTEXT:  parallel worker

There is however plenty of disk space. I googled around a bit and there were some comments about pg settings e.g. https://stackoverflow.com/questions/55803015/google-cloud-sql-pg11-could-not-resize-shared-memory-segment though I am not sure if these are relevant...

Hi @timlinux I think I understand what the issue is all about. This is more a docker issue than the db It seems even if you set your memory threshold in postgresql.conf docker still limits it to around 64mb.

https://stackoverflow.com/questions/56751565/pq-could-not-resize-shared-memory-segment-no-space-left-on-device

[timlinux]

Hi @NyakudyaA

Yeah I saw the same article. Googling around it look slik eyou can add it to docker-compose and that rancher should now support it. See also:

https://github.com/rancher/rancher/issues/4708

Maybe we can set it to something generous like 2gb?

See also https://www.cyberciti.biz/tips/what-is-devshm-and-its-practical-usage.html for more background on /dev/shm

[NyakudyaA]

Added this. We using mviews and they have no issue with memory resize