[geo coverage] Find all signals for a location & vice-versa

[melange396]

We may want to be able to answer the question: "What [other] signals are available for this geographic location?"

That is, when someone is looking at data for a particular location, they may be interested in finding other signals available for the same place, (for example) to do comparison/contrasting. Another use case is for a public health official, responsible for some region, who wants to see all the data available and relevant to them. The answer to the question should not just be "other signals that also use the same geographic type/level/resolution (like 'county')", but more specifically, "other signals that have actual data points for the exact same geographic entity (like 'Allegheny County')". Many signals have coverage across most or all locations of their representative geo_types, but some signals are specific to certain regional subsets, and some signals do not have data for some geo_values due to thresholding cutoffs or reporting practices.

The opposite question ("What [other] locations are available for this signal?") should already be easy[-ish] to answer by querying the database in a way that uses the (signal, geo, ...) prefix of an existing index:

delphi-epidata/src/ddl/v4_schema.sql

Line 57 in c1551af

UNIQUE INDEX `value_key_gt` (`signal_key_id`, `geo_key_id`, `time_type`, `time_value`)

Answering the question at hand may be as simple as adding a new (geo, signal) index, or we might consider maintaining another (new) table that stores these relations and which is kept up to date during acquisition. In either case, the information should be made available through a new API endpoint, or possibly by adding a special argument to an existing endpoint.

I believe this can be done across "all time" fairly easily, but another layer of usefulness (and complexity) would be to be able to answer for a time range or perhaps just a particular point in time.

[melange396]

related to:

• issue: Create an Endpoint for Signal Coverage #1583
• PR: Signal Documentation Coverage Endpoint #1584

[melange396]

The core of this issue was accomplished in #1584.

Potential still TODOs:

• The precomputed coverage_crossref DB table added by Signal Documentation Coverage Endpoint #1584 will also let us easily answer the inverse question: "What geographic locations are covered by this signal?". Add another endpoint for that.
• Add the ability to find coverage information for a particular time range. This will require a query over the actual data table, since i dont think we can precompute that efficiently.
• Add support for "source aliases", which can be seen in other endpoints' use of a mapper (this is very low priority).