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 used topic categories

SELECT DISTINCT tag.name
FROM tag
INNER JOIN resource ON resource.topiccategory = tag.id
WHERE taggroup IN
    (SELECT id
     FROM taggroup
     WHERE name ILIKE 'topic category')
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

geom_search entries

Show the last 10 entries in the geom search table including the type of the geometry

SELECT id,
       resource,
       geom,
       ST_GeometryType(geo_field)
FROM geom_search
ORDER BY resource DESC LIMIT 10

conflicting geom_search entries

Show the last 10 conflicting GeometryCollection entries in the geom search table that cannot be used with spatial search

SELECT id,
       resource,
       geom,
       ST_AsText(geo_field)
FROM geom_search
WHERE ST_GeometryType(geo_field) = 'ST_GeometryCollection'
ORDER BY resource DESC LIMIT 10

geosearch_import entries

Show the last 10 entries in the geosearch_import table including the WKT geometries

SELECT ogc_fid,
       fid,
       ST_AsText(geom)
FROM import_tables.geosearch_import
ORDER BY ogc_fid LIMIT 10;

geom_search duplicates

Find real duplicate entries in the geom_search table

SELECT id,
       resource,
       geom,
       ST_ASTEXT(geo_field)
FROM geom_search
WHERE resource IN
    (SELECT DISTINCT resource
     FROM geom_search
     GROUP BY resource HAVING COUNT(resource) = 2)
ORDER BY resource;

geom_search to geom

Update the spatial coverage of a specific resource with the minimum bounding box of their geometries from the geom_search table.

WITH geom_coverage AS
  (INSERT INTO "public".geom (geo_field) SELECT ST_Envelope(ST_ConvexHull(ST_Collect(geo_field))) AS geo_field
   FROM public.geom_search
   WHERE resource = 8710 RETURNING id)
UPDATE "public".resource
SET spatialcoverage =
  (SELECT id
   FROM geom_coverage)
WHERE id = 8710