Reset guided tours

The commands to reset the tours for users are either:

Reset tour for a specific user

UPDATE public.guided_tours SET editor_tour_completed = false WHERE user_id = '<UUID>';

Reset tour for all users

UPDATE public.guided_tours SET editor_tour_completed = false WHERE editor_tour_completed = true;

Statistics for maps

Get the number of plantings per map

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get the number of plantings per map for current day

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE pl.add_date <= CURRENT_DATE AND pl.remove_date >= CURRENT_DATE GROUP BY m.id;

Get the number of plantings per map for current year

SELECT m.id, count(*) AS "number of platings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE date_part('year', pl.add_date) <= date_part('year', CURRENT_DATE) AND date_part('year', pl.remove_date) >= date_part('year', CURRENT_DATE) GROUP BY m.id;

Get the number of plantings per map that where modified in the last 30 days

SELECT m.id, count(*) AS "number of platings modified in the last 30 days" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id WHERE pl.modified_at >= now() - interval '30 day' GROUP BY m.id;

Get number of different kinds of plantings per map

SELECT m.id, count(DISTINCT pl.seed_id) AS "number of different plantings" FROM plantings pl JOIN layers l ON pl.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of drawings per map

SELECT m.id, count(*) AS "number of drawings" FROM drawings d JOIN layers l ON d.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of shadings per map

SELECT m.id, count(*) AS "number of shadings" FROM shadings s JOIN layers l ON s.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of soil textures per map

SELECT m.id, count(*) AS "number of soil_textures" FROM soil_textures st JOIN layers l ON st.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;

Get number of hydrologies per map

SELECT m.id, count(*) AS "number of hydrologies" FROM hydrologies h JOIN layers l ON h.layer_id = l.id JOIN maps m ON l.map_id = m.id GROUP BY m.id;