data curation of geohub data is required

[JinIgarashi]

a lot of existing geohub datasets are not actually useful, we need to do data curation to remove useless datasets.

The below CSV contains all datasets with all metadata including tags as of 22 August 2024.

geohub-data-20240822.csv

SQL to fetch metadata

WITH key_value_pairs AS (
	SELECT
		x.id,
		z.key,
		array_agg(z.value) AS values_array
	FROM geohub.dataset x
	LEFT JOIN geohub.dataset_tag y
	ON x.id = y.dataset_id
	INNER JOIN geohub.tag z
	ON y.tag_id = z.id
	GROUP BY x.id, z.key
),
datasetTags as (
	SELECT
		id,
		MAX(CASE WHEN key = 'algorithm' THEN values_array END) AS algorithm,
		MAX(CASE WHEN key = 'collection' THEN values_array END) AS collection,
		MAX(CASE WHEN key = 'container' THEN values_array END) AS container,
		MAX(CASE WHEN key = 'continent' THEN values_array END) AS continent,
		MAX(CASE WHEN key = 'country' THEN values_array END) AS country,
		MAX(CASE WHEN key = 'extent' THEN values_array END) AS extent,
		MAX(CASE WHEN key = 'geometrytype' THEN values_array END) AS geometrytype,
		MAX(CASE WHEN key = 'granularity' THEN values_array END) AS granularity,
		MAX(CASE WHEN key = 'layertype' THEN values_array END) AS layertype,
		MAX(CASE WHEN key = 'multi_year_format' THEN values_array END) AS multi_year_format,
		MAX(CASE WHEN key = 'multi_year_from' THEN values_array END) AS multi_year_from,
		MAX(CASE WHEN key = 'multi_year_to' THEN values_array END) AS multi_year_to,
		MAX(CASE WHEN key = 'provider' THEN values_array END) AS provider,
		MAX(CASE WHEN key = 'region' THEN values_array END) AS region,
		MAX(CASE WHEN key = 'resolution' THEN values_array END) AS resolution,
		MAX(CASE WHEN key = 'schema' THEN values_array END) AS schema,
		MAX(CASE WHEN key = 'sdg_goal' THEN values_array END) AS sdg_goal,
		MAX(CASE WHEN key = 'sdg_target' THEN values_array END) AS sdg_target,
		MAX(CASE WHEN key = 'stac' THEN values_array END) AS stac,
		MAX(CASE WHEN key = 'stacApiType' THEN values_array END) AS stacApiType,
		MAX(CASE WHEN key = 'stacType' THEN values_array END) AS stacType,
		MAX(CASE WHEN key = 'table' THEN values_array END) AS table,
		MAX(CASE WHEN key = 'theme' THEN values_array END) AS theme,
		MAX(CASE WHEN key = 'type' THEN values_array END) AS type,
		MAX(CASE WHEN key = 'unit' THEN values_array END) AS unit,
		MAX(CASE WHEN key = 'year' THEN values_array END) AS year
	FROM key_value_pairs
	GROUP BY id
)
SELECT 
	a.id, 
	a.name,
	a.url,
	a.is_raster, 
	a.description, 
	a.access_level, 
	a.license, 
	a.createdat,
	a.created_user, 
	a.updatedat, 
	a.updated_user, 
	array_to_string(b.algorithm, ',') as algorithm,
	array_to_string(b.collection, ',') as collection,
	array_to_string(b.container, ',') as container,
	array_to_string(b.continent, ',') as continent,
	array_to_string(b.country, ',') as country,
	array_to_string(b.extent, ',') as extent,
	array_to_string(b.geometrytype, ',') as geometrytype,
	array_to_string(b.granularity, ',') as granularity,
	array_to_string(b.layertype, ',') as layertype,
	array_to_string(b.multi_year_format, ',') as multi_year_format,
	array_to_string(b.multi_year_from, ',') as multi_year_from,
	array_to_string(b.multi_year_to, ',') as multi_year_to,
	array_to_string(b.provider, ',') as provider,
	array_to_string(b.region, ',') as region,
	array_to_string(b.resolution, ',') as resolution,
	array_to_string(b.schema, ',') as schema,
	array_to_string(b.sdg_goal, ',') as sdg_goal,
	array_to_string(b.sdg_target, ',') as sdg_target,
	array_to_string(b.stac, ',') as stac,
	array_to_string(b.stacApiType, ',') as stacApiType,
	array_to_string(b.stacType, ',') as stacType,
	array_to_string(b.table, ',') as table,
	array_to_string(b.theme, ',') as theme,
	array_to_string(b.type, ',') as type,
	array_to_string(b.unit, ',') as unit,
	array_to_string(b.year, ',') as year,
	ST_ASTEXT(a.bounds) as bounds
FROM geohub.dataset a
LEFT JOIN datasetTags b
ON a.id = b.id;