Calculated data sources allow you to process complicated data in advance in order to build consoles that load extremely fast. You can also use them to build advanced datasets that would time out in normal use.
In this guide, we'll show how to use this feature through examples.
Examples covered in this guide:
- Population served by water point (school, HCF, etc)
- % of functional vs non-functional water points by district
- Water points, schools, and health facilities by country
- Population reached with service within 500m of each water point (school, health facilit, etc) of a district
Use Calculated Data Sources when:
1) You want your dashboard to be very quick
2) The data doesn't have to be real-time but can be updated daily, weekly, or monthly
3) You want to combine multiple data sources into one convenient one
Or
4) You have a complex data need where calculating it live times out
Calculating population coverages within distances of locations is a computationally heavy process, and can time out for larger areas. That's why creating a calculated data source for such computations can be very effective. In this example, we'll count the number of people living within 500 metres of each water point mapped in Kenya, using the population data layers available on the platform.
2) If the appropriate data source doesn't exist yet, click + New Calculated Data Source
3) Name the data source appropriately, e.g. Population within 500m of water points in Kenya
4) Select the initial data source, in this case, water points
5) Filter it down appropriately. In this example, we'll filter down to sites in Kenya.
6) Carefully select the right columns with the aggregation logic:
- First column: Name
- Second column: Unique ID
- Third column: Population within 500m
7) Set permissions and schedule
8) Save and calculate
Caution: This approach of course can double count the same person to belong within the coverage area of multiple water points. Therefore a sum of population served may show a larger number than the total population of an area. For an aggregate approach, see example 4.
The system can calculate population densities using available satellite data
Column defintitions
Results
Pro-tip:
If you want to show users when the data was most recently calculated, add a column with the Formula for Now. This will record the time at which the calculation was done.
Let's calculate the % of functional vs non-functional water points by district for a country, updated monthly. Then, this data source will always be ready for rapid use in your dashboards.
2) If the appropriate data source doesn't exist yet, click + New Calculated Data Source
3) Name the data source appropriately, e.g. % Functional Water Points for Rwanda
4) Select the initial data source, in this case Water Points
5) Filter it down appropriately. In this example down to sites located in Rwanda
6) Carefully select the right columns with the aggregation logic:
- First column: Administrative region > District of Rwanda
(From Water Point Location - Administrative region - Regions by country - Rwanda - District of Rwanda
- Second column: Percent where water point functional status Functional status is Functional out of all water points that have any functional status at all
(This depends on where you want to get the functionality
information from. You may have your own survey. But the standard option
is the Water Point Functional Status indicator.)
7) Set permissions and schedule
8) Save and calculate
You now have this data source available for any dashboard, map, and datagrid under the heading Calculated.
Here we will use a subquery-expression to link two data sources. You can use the same expression to link up many data sources together as long as you have a shared dimension of data. This way it's possible to build a global summary table of your interventions by water points, schools, and health facilities grouped by country. You can then filter this down by organization, date, etc.
2) If the appropriate data source doesn't exist yet, click + New Calculated Data Source
3) Name the data source appropriately, e.g. Water points, schools, and health facilities by country
4) Select the initial data source, in this case, Administrative regions (under Advanced)
5) Filter it down appropriately. Because we want this data summarized by Country, let's add a filter for: Level is Country
6) Carefully select the right columns with the aggregation logic:
- First column: Full Name. This will show the name of the country
- Second column: Subquery Formula
Subquery data source: Water point
References to Outer Query: Administrative Region (For each water point in the system, we want to check which country it belongs to, so we pass in the country row from our original data source)
Value to calculate: Number of water point
Filter Subquery Data Source:
Administrative region (of the water point) is within Adminstrative Regions (from outer query)
This subquery now goes through every water point in the system and checks the administrative region it belongs to. Then it checks whether the admin region belongs within the admin region (country) passed from the original data source (the country table we are building) and counts a water point into it if it matches.
Note: If you want to limit the water points by year or by your organization, etc., you can do it by adding a filter to the subquery.
You can repeat this with a new subquery column for schools and health facilities with the same logic.
If you want to demonstrate that you've helped a certain proportion of the population within a region with your interventions, how would you do it? One way is to calculate the population that lives within a certain distance of the interventions you've made and compare that against the overall population of the region. Here, we'll do that for a district using 500m buffers for water points in that district, comparing it against the population density data that's already in the system.
This is a complex example that shows how you can accomplish calculations that would otherwise be impossible to do without timing out using expressions in a dashboard.
2) If the appropriate data source doesn't exist yet, click + New Calculated Data Source
3) Name the data source appropriately, e.g. Population reached for my district
4) Select the initial data source, in this case, Administrative regions (under Advanced)
5) Filter it down appropriately. Because we want this data filtered for a district, we can just pick the district we're interested in. The same logic would work for a list of districts.
6) Carefully select the right columns with the aggregation logic:
- First column: Full Name. This will show the name of the district(s)
- Second column: Population within Formula set to 0 metres within the Shape. This calculates the entire population within the district
- Third column: Population within 500m of water points. Create a Population within formula and for its location choose the formula Overlap between shapes. This finds the water points within the district. Create the overlap between the Shape of the district and a Spatial join formula to water points for a distance of 0 meters. In the To table to water points, merge all the shapes together and apply one more formula, Expand Shape. With this you can expand the shape of the total shape of all water points by your desired distance, here 500m.
7) Set permissions and schedule
8) Save and calculate
Pro-tip:
If you want to show users when the data was most recently calculated, add a column with the Formula for Now. This will record the time at which the calculation was done.
Questions, suggestions, or working on a complex import? Please get in touch via info@mwater.co
