query URLs

gistfile1.txt

SELECT                                                                               
    count (distinct mv.value), mv.name, (substring(mv.value from '(https?://([^/]*){1})')) as url
FROM
    annotation_mapvalue as mv
WHERE 
    (substring(mv.value from '(https?://([^/]*){1})')) is not null
GROUP BY mv.name, url
ORDER BY 2;

gistfile2.txt

idr=> SELECT
     count(distinct mv.value),
     sqname,
     sqmv.url
FROM
     annotation_mapvalue as mv
JOIN
     (
     SELECT
          
          distinct ( substring(name from '(.*) URL') )as sqname,
          (substring(sqmv.value from '(https?://([^/]*/){1})')) as url,
          sqmv.annotation_id
     FROM
          annotation_mapvalue as sqmv
     WHERE
          sqmv.name like '%URL'

     ) AS sqmv ON sqmv.annotation_id = mv.annotation_id
WHERE
        mv.name = sqname
group by 
     sqname,
     sqmv.url
order by 2 ASC;

gistfile3.txt

select count(ial.id) from annotation_mapvalue as mv
join annotation as a on (a.id=mv.annotation_id)
join imageannotationlink as ial on (ial.child=a.id)
where mv.value like 'CMPO_%’