NoPublic, bureaucrat, administrator
1,068
edits
Changes
Created page with "{{TOC right}} When trying to determine the contaminated area (areasize) by type of contamination, you noticed that simply grouping by device category and summing up the land a..."
{{TOC right}}
When trying to determine the contaminated area (areasize) by type of contamination, you noticed that simply grouping by device category and summing up the land areasizes leads to wrong numbers because of double-counting rows. For example, if a land is contaminated with two different models of anti-personnel mines (and this is recorded in IMSMA), then this particular hazard appears twice in a query/domain joining the data from HAZARD and HAZDEVICEINFO. While this is normal for that data design - and great for analysing which models are expected to be found on which land, it is not convenient for the particular question of the extent of contamination by device type. In order to tackle this, a derived table could be created, based on the following query:
<pre>
select a.hazard_guid,
a.province,
round(a.areasize) as areasize,
case when ord_landmines > 0 then 'Landmines'
when ord_landmines = 0 and ord_clusters > 0 then 'Cluster Munition'
when ord_landmines = 0 and ord_clusters = 0 and ord_other > 0 then 'UXO/Other'
else 'Unknown' end as contamination_type
from
(select hazard.hazard_guid, hazard.gazetteer_level2_name as province, hazard.areasize,
sum(case when hazdeviceinfo.ordcategoryenum_enum = 'Landmines' then 1 else 0 end) as ord_landmines,
sum(case when hazdeviceinfo.ordcategoryenum_enum = 'Clusters and dispensers' then 1 else 0 end) as ord_clusters,
sum(case when hazdeviceinfo.ordcategoryenum_enum is not null and hazdeviceinfo.ordcategoryenum_enum not in ('Landmines','Clusters and dispensers') then 1 else 0 end) as ord_other,
sum(case when hazdeviceinfo.ordcategoryenum_enum is null then 1 else 0 end) as ord_none
from hazard left join hazdeviceinfo on (hazard.hazard_guid = hazdeviceinfo.hazard_guid)
group by hazard.hazard_guid) a
order by province, contamination_type desc
</pre>
This query does the following:
* For each hazard, it counts how often devices of the category ''Landmines'' appear (0 to x times)
* Idem for the categories ''Clusters and dispensers'' and all other categories (summarized as ''UXO/Other'')
* If no device category is recorded for a land, then the contamination is categorize as ''Unknown''
* Finally, for each hazard, if there is any landmine contamination (regardless if there are other types as well), then the contamination type is set to ''Landmines''. If there is no landmine contamination, but there are cluster munitions, then the contamination type is set to ''Cluster munition'''. If there is no landmine and no cluster munition contamination, but any other device, then the contamination type is set to ''UXO/Other''. If there is no device information at all, then the contamination type is set to ''Unknown''.
In MINT:
# In the Land domain, create a derived table named ''Contamination_type'' with the above query
# In the Joins tab, create a join between this derived table and the hazard table (use the hazard_guid) - which join type would you choose?
# Make sure to include the attribute contamination_type in the list of displayed attributes
# Create an appropriate chart for the indicator '''Sqkm by province and device'''
# Describe the difference this derived table made
{{NavBox Business Intelligence}}
[[Category:VIE]]
[[Category:NoPublic]]
When trying to determine the contaminated area (areasize) by type of contamination, you noticed that simply grouping by device category and summing up the land areasizes leads to wrong numbers because of double-counting rows. For example, if a land is contaminated with two different models of anti-personnel mines (and this is recorded in IMSMA), then this particular hazard appears twice in a query/domain joining the data from HAZARD and HAZDEVICEINFO. While this is normal for that data design - and great for analysing which models are expected to be found on which land, it is not convenient for the particular question of the extent of contamination by device type. In order to tackle this, a derived table could be created, based on the following query:
<pre>
select a.hazard_guid,
a.province,
round(a.areasize) as areasize,
case when ord_landmines > 0 then 'Landmines'
when ord_landmines = 0 and ord_clusters > 0 then 'Cluster Munition'
when ord_landmines = 0 and ord_clusters = 0 and ord_other > 0 then 'UXO/Other'
else 'Unknown' end as contamination_type
from
(select hazard.hazard_guid, hazard.gazetteer_level2_name as province, hazard.areasize,
sum(case when hazdeviceinfo.ordcategoryenum_enum = 'Landmines' then 1 else 0 end) as ord_landmines,
sum(case when hazdeviceinfo.ordcategoryenum_enum = 'Clusters and dispensers' then 1 else 0 end) as ord_clusters,
sum(case when hazdeviceinfo.ordcategoryenum_enum is not null and hazdeviceinfo.ordcategoryenum_enum not in ('Landmines','Clusters and dispensers') then 1 else 0 end) as ord_other,
sum(case when hazdeviceinfo.ordcategoryenum_enum is null then 1 else 0 end) as ord_none
from hazard left join hazdeviceinfo on (hazard.hazard_guid = hazdeviceinfo.hazard_guid)
group by hazard.hazard_guid) a
order by province, contamination_type desc
</pre>
This query does the following:
* For each hazard, it counts how often devices of the category ''Landmines'' appear (0 to x times)
* Idem for the categories ''Clusters and dispensers'' and all other categories (summarized as ''UXO/Other'')
* If no device category is recorded for a land, then the contamination is categorize as ''Unknown''
* Finally, for each hazard, if there is any landmine contamination (regardless if there are other types as well), then the contamination type is set to ''Landmines''. If there is no landmine contamination, but there are cluster munitions, then the contamination type is set to ''Cluster munition'''. If there is no landmine and no cluster munition contamination, but any other device, then the contamination type is set to ''UXO/Other''. If there is no device information at all, then the contamination type is set to ''Unknown''.
In MINT:
# In the Land domain, create a derived table named ''Contamination_type'' with the above query
# In the Joins tab, create a join between this derived table and the hazard table (use the hazard_guid) - which join type would you choose?
# Make sure to include the attribute contamination_type in the list of displayed attributes
# Create an appropriate chart for the indicator '''Sqkm by province and device'''
# Describe the difference this derived table made
{{NavBox Business Intelligence}}
[[Category:VIE]]
[[Category:NoPublic]]
