Some useful SQL Scripts

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);

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.

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));

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;

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