Stories of sample queries possible from front-end

[asdofindia]

We need to build a set of example queries that will be made from the frontend. This should guide our database design to a certain extent. Some queries are hard to make with some database designs. Some other are harder to query with other database designs. Let us figure out which are the important queries.

[asdofindia]

We will start from the simple and proceed towards complicated. I'll document views rather than queries because we could decide what query works best for views based on our design.

At first level there could be many kinds of views: 1) A map of India which displays all the districts coloured by some calculated variable (say SDG3 attainment status) with all the major indicator group names as text on the top bar (to navigate to other SDGs on the same map). 2) A dashboard page of a particular district which shows values of all groups/subgroups/indicators with colors that signify whether some is worse than 75 percentile, or better than 75 percentile. Including a map of the district in the middle. 3) A page that has a box corresponding to each indicator (or group). This box contains a list of all districts ordered from best to worst for that indicator (or group). A select box on the top right allows us to choose one district and when that's done all the boxes scroll to reveal the position of that district in all the boxes. 4) A google search landing page for when someone searches "What is the maternal mortality rate in Alipurduar?" that has prominent display of the answer and then links to various other indicators available for Alipurduar in our website and also links to various other districts' MMR. 5) A page that allows one to view a particular dataset (say this one) in its entirety laid out in a table. Can have a link to download this in some format. 6) A page dedicated to an indicator (like Maternal Mortality Rate) that (similar to homepage) shows a choropleth map and also shows the various related indicators (from the same group/subgroup).

[asdofindia]

7. Ability to generate a table of results (with chosen columns and rows) sorted by multiple columns.
8. Ability to plot a map from a spreadsheet which has a column that has place names.
9. Ability to do a timeseries or comparison with a slider.

[asdofindia]

10. Aggregation where several geographies are compared to each other using values from different datasets and indicators.

Description: imagine district Kannur; 5 Indicators that fall under child health like "U5MR", "NNMR", etc. and two datasets - NFHS and AHS. How do we query something like, what position is Kannur at in terms of those indicators across these datasets.

[asdofindia]

11. Someone searches "India literacy rate" in google. Our healthheatmap pops up in the results. User opens the link. The choropleth map comes up (perhaps district wise). They can drill down to the district, if they want. We should suggest related indicators like "schooling", and similar indicators. The user should be able to compare literacy rate with these.

12. URLs that store the state of the application. SEO friendly, shareable.

[asdofindia]

13. Use research from existing literature and show case it using the visualization tools available.

14. Create scoring mechanism for categories. Create scores for each district. Correlate the score for each district with some other related indicator. Essentially a derived indicator vs a regular indicator.

15. Take one category - take 3 or 4 indicators - correlate the values of these indicators with values of other indicators and overlay one on top of the other.

[asdofindia]

16. National average, state average, etc for each value being displayed. Or, if it is a count, show the national total and state total for the particular indicator/source combination. Then, return the ratio of each district to this aggregate.

[rsprabha]

We will start from the simple and proceed towards complicated. I'll document views rather than queries because we could decide what query works best for views based on our design.

At first level there could be many kinds of views:

1. A map of India which displays all the districts coloured by some calculated variable (say SDG3 attainment status) with all the major indicator group names as text on the top bar (to navigate to other SDGs on the same map).

2. A dashboard page of a particular district which shows values of all groups/subgroups/indicators with colors that signify whether some is worse than 75 percentile, or better than 75 percentile. Including a map of the district in the middle.

3. A page that has a box corresponding to each indicator (or group). This box contains a list of all districts ordered from best to worst for that indicator (or group). A select box on the top right allows us to choose one district and when that's done all the boxes scroll to reveal the position of that district in all the boxes.

4. A google search landing page for when someone searches "What is the maternal mortality rate in Alipurduar?" that has prominent display of the answer and then links to various other indicators available for Alipurduar in our website and also links to various other districts' MMR.

5. A page that allows one to view a particular dataset (say [this one](http://rchiips.org/NFHS/about.shtml)) in its entirety laid out in a table. Can have a link to download this in some format.

6. A page dedicated to an indicator (like Maternal Mortality Rate) that (similar to homepage) shows a choropleth map and also shows the various related indicators (from the same group/subgroup).

Some variation of these six points is good as a starting point. We should see how best we can do this at the earliest. We can prioritize this and start working. Others will fall in place and evolve as we go along.

[rsprabha]

One more point I may add. Any data must be downloadable by a registered user and the download must be recorded on the user's profile page. This will be the most valuable contribution to the public data and health and make the portal moist popular and used.