mapx
mapx copied to clipboard
unep-grid
Addition of a tool in MapX to have general statistics on a project
Creation of an interface in MapX so that administrators (and publishers ?) can have statistics on their project with an export tool to generate a PDF report.
Here is a list of suggestions of what could be displayed:
- Project overview:
- number of views
- number of sources
- number of sources without views (with an option to display the complete list)
- Project popularity:
- number of unique users (last month, in total)
- map showing the location of users (approximation)
- top 5 views
- time spent by users consulting views (last month, in total)
- Project quality:
- project score
- completeness of metadata (with an option to display detailed information)
- ~publishers evaluation / ranking (?)~
Very well summarized. I would however not provide the possibility to evaluate or rank the publishers
Unfortunately, GitHub does not support SQL files. The 1st draft of the query is available in the following .txt file: sql_query.txt
Thanks !
There are some tricks for SQL (or any code) : <details> tag + markdown code. A "copy to clipboard" button is then shown.
Text files are fine, too.
sql_query.sql
WITH views_project_ids AS (
SELECT
id AS id_view
FROM
mx_views_latest
WHERE
project = 'MX-IY9-QCF-ILZ-UVO-07Y'
OR data #> '{"projects"}' @> to_jsonb('MX-IY9-QCF-ILZ-UVO-07Y'::text)
UNION
SELECT
jsonb_array_elements_text(views_external) AS id_view
FROM
mx_projects
WHERE
id = 'MX-IY9-QCF-ILZ-UVO-07Y'
),
views_project AS (
SELECT
id,
data #> '{"title"}' AS view_title,
data #>> '{"source", "layerInfo", "name"}' AS vt_source,
type,
readers @> '["public"]' AS public,
project != 'MX-IY9-QCF-ILZ-UVO-07Y' AS external
FROM
mx_views_latest
WHERE
id IN (
SELECT
*
FROM
views_project_ids)),
views_count AS (
SELECT
COUNT(*)
FROM
views_project
),
public_views_count AS (
SELECT
COUNT(*)
FROM
views_project
WHERE
public
),
views_published_count AS (
SELECT
COUNT(*)
FROM
views_project
WHERE
NOT external
),
public_views_published_count AS (
SELECT
COUNT(*)
FROM
views_project
WHERE
NOT external
AND public
),
sources AS (
SELECT
COUNT(*)
FROM
mx_sources
WHERE
project = 'MX-IY9-QCF-ILZ-UVO-07Y'
),
sources_with_view AS (
SELECT
COUNT(DISTINCT vt_source)
FROM
views_project
WHERE
NOT external
),
admins_list AS (
SELECT
jsonb_array_elements_text(admins) AS admins
FROM
mx_projects
WHERE
id = 'MX-IY9-QCF-ILZ-UVO-07Y'
),
publishers_list AS (
SELECT
jsonb_array_elements_text(publishers) AS publishers
FROM
mx_projects
WHERE
id = 'MX-IY9-QCF-ILZ-UVO-07Y'
),
members_list AS (
SELECT
jsonb_array_elements_text(members) AS members
FROM
mx_projects
WHERE
id = 'MX-IY9-QCF-ILZ-UVO-07Y'
),
admins AS (
SELECT
COUNT(*)
FROM
admins_list
),
publishers AS (
SELECT
COUNT(*)
FROM
publishers_list
WHERE
publishers NOT IN (
SELECT
admins
FROM
admins_list)
),
members AS (
SELECT
COUNT(*)
FROM
members_list
WHERE
members NOT IN (
SELECT
admins
FROM
admins_list)
AND members NOT IN (
SELECT
publishers
FROM
publishers_list)),
logs AS (
SELECT
id_log,
ip_user,
id_user,
is_guest,
data
FROM
mx_logs
WHERE
id_project = 'MX-IY9-QCF-ILZ-UVO-07Y'
AND date_modified > (CURRENT_DATE - 365)),
connections AS (
SELECT
ip_user,
id_user,
is_guest,
count(*)
FROM
logs
WHERE
id_log = 'session_start'
OR id_log = 'project_change'
GROUP BY
ip_user,
id_user,
is_guest
),
connections_ip_country AS (
SELECT
c.id_user,
c.is_guest,
coalesce(m.country_name, 'unknown') AS country_name,
m.country_iso_code AS country,
c.count
FROM
connections c
LEFT JOIN
mx_ip m ON c.ip_user <<= m.network
),
conn_by_country AS (
SELECT
country,
country_name,
SUM(count)
FROM
connections_ip_country
GROUP BY
country,
country_name
ORDER BY
sum DESC
),
conn_by_country_json AS (
SELECT
json_agg(row_to_json(cc)) tbl
FROM
conn_by_country cc
),
conn_by_users AS (
SELECT
SUM(count)
FROM
connections
WHERE
NOT is_guest
),
conn_by_guests AS (
SELECT
SUM(count)
FROM
connections
WHERE
is_guest
),
conn_by_distinct_users AS (
SELECT
COUNT(DISTINCT id_user)
FROM
connections
WHERE
NOT is_guest
),
views_duration AS (
SELECT
data #>> '{"id_view"}' AS view_id,
SUM(CAST(data #> '{"view_duration_seconds"}' AS numeric)) AS duration
FROM
logs
WHERE
id_log = 'view_remove'
GROUP BY
data #>> '{"id_view"}'),
views_duration_join AS (
SELECT
d.view_id,
v.view_title,
v.type,
v.public,
v.external,
d.duration
FROM
views_duration d,
views_project v
WHERE
d.view_id = v.id
ORDER BY
duration DESC),
views_duration_json AS (
SELECT
json_agg(row_to_json(v)) tbl
FROM
views_duration_join v
),
views_duration_total AS (
SELECT
SUM(duration)
FROM
views_duration),
views_activation AS (
SELECT
data #>> '{"id_view"}' AS view_id,
COUNT(*) AS activation
FROM
logs
WHERE
id_log = 'view_add'
GROUP BY
data #>> '{"id_view"}'),
views_activation_join AS (
SELECT
a.view_id,
v.view_title,
v.type,
v.public,
v.external,
a.activation
FROM
views_activation a,
views_project v
WHERE
a.view_id = v.id
ORDER BY
activation DESC),
views_activation_json AS (
SELECT
json_agg(row_to_json(v)) tbl
FROM
views_activation_join v
),
views_activation_total AS (
SELECT
SUM(activation)
FROM
views_activation)
SELECT
json_build_object('id','MX-IY9-QCF-ILZ-UVO-07Y',
'nb_views', vc.count,
'nb_public_views', pvc.count,
'nb_views_published', vpc.count,
'nb_public_views_published', pvpc.count,
'nb_sources', s.count,
'nb_sources_without_views', s.count - sv.count,
'nb_admins', a.count,
'nb_publishers', p.count,
'nb_members', m.count,
'conn', cu.sum + cg.sum,
'conn_by_guests', cg.sum,
'conn_by_users', cu.sum,
'conn_by_distinct_users', cdu.count,
'conn_by_country', cc.tbl,
'views_duration', vdj.tbl,
'views_duration_total', vdt.sum,
'views_activation', vaj.tbl,
'views_activation_total', vat.sum) AS project_stats
FROM
views_count vc,
public_views_count pvc,
views_published_count vpc,
public_views_published_count pvpc,
sources s,
sources_with_view sv,
admins a,
publishers p,
members m,
conn_by_guests cg,
conn_by_users cu,
conn_by_distinct_users cdu,
conn_by_country_json cc,
views_duration_json vdj,
views_duration_total vdt,
views_activation_json vaj,
views_activation_total vat
It would also be relevant to add a routine to warn admins about views that are not longer maintained in their project.
- SQL query to list all views that were not updated from 6 months (or a year)
- automatically set these views to private (readers >= members)
- send an email to the admins each week with the results of the routine