Some useful SQL Scripts

Create new Representation

Create a new representation and associate it with a resource.

WITH rep as 
(INSERT INTO "public".representation 
("type", spatialresolution, "name", description, applicationprofile, tags, 
"function", contentlocation, temporalresolution, protocol, content, 
spatialscale, contenttype, uuid, uploadmessage, uploadstatus) 
VALUES (213, NULL, 'corine:clc00_c111 Tileserver', 
'CLC:clc00_c211 Tileserver', 1359, 11950, 72, 
'http://tl-243.xtr.deltares.nl/tileserver/corine:clc00_c111/{z}/{x}/{y}.png', 
NULL, 205, NULL, NULL, 59, 'corine:clc00_c111', NULL, NULL) 
RETURNING id) INSERT INTO "public".jt_resource_representation (representationid, resource_reference) 
SELECT id, 11867 from rep;

Copy CSW View to static data table

csw_copy_data_table_from_view_for_pycsw.sql

Find resources by bbox without search geometry

SELECT count(distinct r.id) FROM resource r 
INNER JOIN geom g ON r.spatialcoverage = g.id 
AND g.geo_field && st_GeomFromEWKT('SRID=4326;POLYGON ((-9.194288369255645 54.72093488558994, -9.194288369255645 35.72093488558994, 13.805711630744355 35.72093488558994, 13.805711630744355 54.72093488558994, -9.194288369255645 54.72093488558994))') 
AND st_intersects(st_GeomFromEWKT('SRID=4326;POLYGON ((-9.194288369255645 54.72093488558994, -9.194288369255645 35.72093488558994, 13.805711630744355 35.72093488558994, 13.805711630744355 54.72093488558994, -9.194288369255645 54.72093488558994))'), geo_field) 
WHERE r.id IS NOT NUL

Find resources by bbox with search geometry

Spatial search using the geom_search table

SELECT  count  (distinct resource.id) FROM resource
INNER JOIN geom_search ON resource.id = geom_search.resource
AND geom_search.geo_field && st_GeomFromEWKT('SRID=4326;POLYGON ((-9.194288369255645 54.72093488558994, -9.194288369255645 35.72093488558994, 13.805711630744355 35.72093488558994, 13.805711630744355 54.72093488558994, -9.194288369255645 54.72093488558994))') 
AND st_intersects(st_GeomFromEWKT('SRID=4326;POLYGON ((-9.194288369255645 54.72093488558994, -9.194288369255645 35.72093488558994, 13.805711630744355 35.72093488558994, 13.805711630744355 54.72093488558994, -9.194288369255645 54.72093488558994))'), geom_search.geo_field) 
WHERE resource.id IS NOT NULL

Find unrelated geometries in the geom table

Find geometries, that are not directly connected to resources.

SELECT count(geom.id)
FROM geom
WHERE geom.id NOT IN
    (SELECT spatialcoverage
     FROM resource WHERE spatialcoverage IS NOT NULL);

Find unrelated geometries in the geom_search table

Find search geometries, that are not directly connected to resources.

SELECT count(geom_search.id)
FROM geom_search
WHERE resource NOT IN (
  SELECT id
  FROM resource);

Remove unrelated geometries in the geom_search table

Remove search geometries, that are not directly connected to resources.

DELETE
FROM geom_search
WHERE geom_search.resource NOT IN
    (SELECT id
     FROM resource);

Insert imported geometries

Insert geometries imported from a .shp file from a temporary import table into the common geom_search table and simplify them with ST_Envelope. Replace '4711' with the respective resource id.

INSERT INTO public.geom_search(resource, geo_field)
SELECT 4711,
       ST_Envelope(geom)
FROM import_tables.geosearch_import;

Fill geom_search with valid resources geometries

Find Multipoint / Multipolygons and small bboxes and add them to the geometry search table. Simplify geometries to speed up geo-search.

TRUNCATE "public".geom_search;

INSERT INTO public.geom_search(resource, geo_field, geom)
SELECT DISTINCT resource.id,
                geom.geo_field,
                geom.id
FROM resource
JOIN geom ON resource.spatialcoverage = geom.id
WHERE
    (SELECT ST_GeometryType(geo_field) IN ('ST_MultiPoint',
                                           'ST_MultiPolygon',
                                           'ST_Point'))
  OR (
        (SELECT ST_GeometryType(geo_field) = 'ST_Polygon')
      AND
        (SELECT ST_Area(geo_field) < 100));


UPDATE geom_search
SET geo_field = ST_Simplify(geo_field,0.1)
WHERE ST_GeometryType(geo_field) = 'ST_MultiPolygon';

Find MultiPoint Resources

SELECT name, description,
  (SELECT ST_GeometryType(geo_field)) AS mp
FROM resource
JOIN geom ON resource.spatialcoverage = geom.id
WHERE
    (SELECT ST_GeometryType(geo_field) = 'ST_MultiPoint')
ORDER BY resource.name;

Find used tags of a specific tag group

SELECT DISTINCT tag.name
FROM tag
INNER JOIN jt_resource_tag ON jt_resource_tag.tagid = tag.id
WHERE taggroup IN
    (SELECT id
     FROM taggroup
     WHERE name ILIKE '%cuahsi%')
ORDER BY tag.name

Find duplicate tags of a specific tag group

SELECT tag.name,
    COUNT(tag.name) AS NumOccurrences
FROM tag
LEFT JOIN taggroup ON tag.taggroup = taggroup.id
WHERE taggroup.name = 'keywords - open'
GROUP BY 
    tag.name
HAVING ( COUNT(tag.name) > 1 )
ORDER BY tag.name;

Rename Tags

UPDATE jt_resource_tag
SET tagid =
  (SELECT id
   FROM tag
   WHERE taggroup IN
       (SELECT id
        FROM taggroup
        WHERE name ILIKE '%cuahsi%')
     AND tag.name = 'Land cover' LIMIT 1)
WHERE tagid =
    (SELECT id
     FROM tag
     WHERE taggroup IN
         (SELECT id
          FROM taggroup
          WHERE name ILIKE '%cuahsi%')
       AND tag.name = 'Land surface classification' LIMIT 1);

DELETE
FROM tag
WHERE taggroup =
    (SELECT id
     FROM taggroup
     WHERE name ILIKE '%cuahsi%')
  AND tag.name = 'Land surface';

Add Keywords to Resources

--INSERT INTO jt_resource_tag (resource_reference, tagid)
SELECT resource.id,
       tag.id
FROM resource,
     tag
WHERE resource.name ILIKE 'AMSRE_36V_AM_FT_%'
  AND tag.name ILIKE 'Temperature, soil'
  AND tag.taggroup = 169;

Find duplicate organisations

SELECT DISTINCT n1.* FROM contact n1
inner join contact n2 on n2.organisation=n1.organisation
where n1.id <> n2.id
ORDER BY n1.organisation

Import CUAHSI Tabluar Ontology

Import the CUAHSI Tabluar Ontology into new CUAHSI keyword list and skip duplicate keywords:

See https://github.com/switchonproject/cids-custom-switchon-server/blob/dev/src/model/scripts/import_import_cuashi_ontology.sql

List all keywords

Get a list of all keywords in the Meta-Data Repository

select name from tag 
where taggroup in 
(select id from taggroup where name ilike '%keyword%') 
group by tag.name order by tag.name