Statistics

gistfile1.sql

-- Global

CREATE index pa_geom_idx ON protected_areas USING gist(the_geom);

ALTER Table protected_areas add column the_geog geography;
Update protected_areas set the_geog = the_geom::geography;
CREATE index pa_geog_idx ON protected_areas USING gist(the_geog);


-- Total PA's

SELECT count(*) from protected_areas;

--Number of PA's by IUCN Category

SELECT ic.name, count(*) FROM protected_areas pa 
LEFT JOIN iucn_categories ic ON pa.iucn_category_id = ic.id 
GROUP BY ic.id;

-- Number of designations

SELECT count(*) from designations;


-- PA's per designation

SELECT dg.name, count(*) FROM protected_areas pa 
JOIN designations dg ON pa.designation_id = dg.id
GROUP BY dg.id
order by count desc;

-- Countries providing Data

SELECT COUNT(*) FROM (
SELECT COUNT(*) pa_count FROM protected_areas pa 
RIGHT JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
INNER JOIN countries C ON C.id = cpa.COUNTRY_id 
GROUP BY c.id) a;

--Country

-- Number of PA's by country

SELECT count(*) FROM protected_areas pa 
LEFT JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
LEFT JOIN COUNTRIES C ON C.id = cpa.COUNTRY_id 
WHERE c.id = '1';

-- Number of PA's with IUCN category

SELECT count(*) FROM protected_areas pa 
LEFT JOIN iucn_categories ic ON pa.iucn_category_id = ic.id 
LEFT JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
LEFT JOIN countries c ON C.id = cpa.COUNTRY_id
WHERE country_id = 1 AND NOT ic.name IN ('Not Reported','Not Applicable');

-- Number of designations

SELECT Count(*) FROM
(SELECT dg.id, dg.name, count(*) FROM protected_areas pa 
JOIN designations dg ON pa.designation_id = dg.id 
JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
JOIN countries c ON C.id = cpa.COUNTRY_id
WHERE country_id = 1
group by dg.id) a

SELECT * FROM protected_areas pa
JOIN designations dg ON pa.designation_id = dg.id 
JOIN iucn_categories ic ON pa.iucn_category_id = ic.id 
JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
JOIN countries c ON C.id = cpa.COUNTRY_id
WHERE country_id = 1;


-- Total PAs by designation

SELECT dg.id, dg.name, count(*) FROM protected_areas pa 
LEFT JOIN designations dg ON pa.designation_id = dg.id 
LEFT JOIN countries_protected_areas cpa ON pa.id = cpa.protected_area_id 
LEFT JOIN countries c ON C.id = cpa.COUNTRY_id
WHERE country_id = 1
group by dg.id



-- Other Global

-- PA's by governance

SELECT gov.name, count(*) FROM protected_areas pa 
LEFT JOIN governances gov ON governance_id = gov.id 
GROUP BY gov.id;


--PA's per designation and juristiction

SELECT dg.name, jd.name, count(*) FROM protected_areas pa 
JOIN designations dg ON pa.designation_id = dg.id
JOIN jurisdictions jd ON dg.jurisdiction_id = jd.id
GROUP BY dg.id, jd.id
order by count desc;

--PA's per jurisdicion

SELECT jd.name, count(*) FROM protected_areas pa 
JOIN designations dg ON pa.designation_id = dg.id
JOIN jurisdictions jd ON dg.jurisdiction_id = jd.id
GROUP BY  jd.id
order by count desc;