Risk analysis fixes required enhancements: add support for intersection between risk analysis shapes and for exposure covariates

[peterhambly]

Add support for intersection between risk analysis shapes and for exposure covariates:

• Add the columns to rif40_study_areas and to the extract table for risk analysis studies:
  • intersect_count: Number of intersects with shapes
  • distance_from_nearest_source: Distance from nearest source (Km)
  • nearest_rifshapepolyid: Nearest rifshapepolyid (shape reference)
  • exposure_value: Exposure value (when bands selected by exposure values)

The rifshapepolyid is a shape reference. With point shapefiles there is one rifshapepolyid per point, with shapes there is one rifshapepolyid per shape in a shapefile. Shape shapefiles are limited to one set of shapes where the centroids must all lie within the largest shape to enforce this as it is impossible to reliably determine which shape belongs with which rifshapepolyid otherwise.

A new view rif40.rif40_exposure_values has been created to support exposure covariates:

Column	Type	Modifiers	Storage	Description
username	character varying(90)		extended	Username
study_id	integer		plain	Unique study index: study_id. Created by SEQUENCE rif40_study_id_seq
band_id	integer		plain	A band allocated to the area
total_areas	bigint		plain	Total area id
total_rifshapepolyid	bigint		plain	Total rifshapepolyid (shape reference)
min_intersect_count	integer		plain	Minimum number of intersects with shapes
max_intersect_count	integer		plain	Maximum number of intersects with shapes
min_distance_from_nearest_source	numeric		main	Minimum distance from nearest source (Km)
max_distance_from_nearest_source	numeric		main	Maximum distance from nearest source (Km)
min_exposure_value	numeric		main	Minimum exposure value
max_exposure_value	numeric		main	Maximum exposure value

View definition:

 SELECT rif40_study_areas.username,
    rif40_study_areas.study_id,
    rif40_study_areas.band_id,
    count(rif40_study_areas.area_id) AS total_areas,
    count(DISTINCT rif40_study_areas.nearest_rifshapepolyid) AS total_rifshapepolyid,
    min(rif40_study_areas.intersect_count) AS min_intersect_count,
    max(rif40_study_areas.intersect_count) AS max_intersect_count,
    min(rif40_study_areas.distance_from_nearest_source) AS min_distance_from_nearest_source,
    max(rif40_study_areas.distance_from_nearest_source) AS max_distance_from_nearest_source,
    min(rif40_study_areas.exposure_value) AS min_exposure_value,
    max(rif40_study_areas.exposure_value) AS max_exposure_value
   FROM rif40_study_areas
  WHERE rif40_study_areas.exposure_value IS NOT NULL
  GROUP BY rif40_study_areas.username, rif40_study_areas.study_id, rif40_study_areas.band_id
  ORDER BY rif40_study_areas.username, rif40_study_areas.study_id, rif40_study_areas.band_id;

Example data:

1> select * from rif40.rif40_exposure_values where study_id = 166;
2> go
username study_id    band_id    total_areas total_rifshapepolyid min_intersect_count max_intersect_count min_distance_from_nearest_source max_distance_from_nearest_source min_exposure_value   max_exposure_value
-------- ----------- ---------- ----------- -------------------- ------------------- ------------------- -------------------------------- -------------------------------- -------------------- --------------------
peter            166          1          12                    5                   1                   1                              974                             3466                   69                   72
peter            166          2          19                    4                   1                   1                              419                             7393                   63                   66
peter            166          3          51                    2                   1                   1                             1198                            11123                   57                   60

(3 rows affected)

The extract table is untouched for disease mapping and the extract has been fixed for risk analysis.

Also fixed:

• Add selector arrow to ICD selector;
• Fix shape layer order (not ordered as createAreaNameList() not working);
• Fix clear selection (not clearing selected shapes);

Requires that alter 11 be run. This provides:

1. Support for Postgres 10 partitioning;
2. Intersection counting (study areas only);
3. Exposure value support;
4. Add intersection counting and exposure value support to extracts;
5. View rif40_exposure_values;

• SQL Server:

  • Working directory: ...GitHub/rapidInquiryFacility/rifDatabase/SQLserver/alter scripts
  • Usage: `sqlcmd -U rif40 -d <database name> -b -m-1 -e -r1 -i v4_0_alter_11.sql -v pwd="%cd%"
  • Connect flags if required: -P -S

• Postgres:

  • Working directory: .../GitHub/rapidInquiryFacility/rifDatabase/Postgres/psql_scripts
  • Usage: psql -U rif40 -d sahsuland -w -e -P pager=off -f alter_scripts/v4_0_alter_11.sql

[peterhambly]

Have now made requested changes

[bparkes]

ok