Protecting the Planet with PostGIS How we are calculating complex - PowerPoint PPT Presentation

Protecting the Planet with PostGIS How we are calculating complex protected area coverage statistics for all countries in the world. Miguel Torres, Data Manager, UNEP-WCMC Managing WCMC Information

Protected Planet All Protected Areas of the World in one Website http://www.protectedplanet.net/ Managing WCMC Information

Protected Planet All Protected Areas of the World in one Website • Managing WCMC Information

Protected Planet Protected Area Page • Managing WCMC Information

Protected Planet Github Repository • Managing WCMC Information

Protected Areas Dataset 210,000 Protected Areas • • 91% with Boundaries Defined • Remaining Points With Areas as Attributes • Monthly release of the World Database on Protected Areas Managing WCMC Information

Statistics Calculate every month, automatically: Territory Covered by Protected Areas • • Land Covered by Protected Areas Exclusive Economic Zones and Territorial Seas Covered by • Marine Protected Areas Managing WCMC Information

Statistics Methodology Ruby on Rails + PostGIS • • Ruby iterates through the list of countries PostGIS does all the geospatial calculation • • Geometries results stored on a Postgres table • Data shown in the website using Ruby on Rails and Javascript Managing WCMC Information

Statistics National Statistics Managing WCMC Information

Statistics Overlapping Protected Areas Managing WCMC Information

Statistics Overlapping Protected Areas Managing WCMC Information

Statistics Overlapping Protected Areas Managing WCMC Information

Statistics Overlapping Protected Areas Managing WCMC Information

Creating a Flat Dataset Dissolving All Geometries SELECT iso3, ST_Union(the_geom) FROM standard_polygons GROUP BY iso3 Medebach, Germany Managing WCMC Information

Creating a Flat Dataset Splitting by Countries SELECT iso3, ST_Union(the_geom) FROM standard_polygons WHERE iso3 = #{iso3} GROUP BY iso3 Gerês / Xurés, Portugal / Spain Managing WCMC Information

Creating a Flat Dataset Splitting by Type SELECT iso3, ST_Union(the_geom) FROM standard_polygons WHERE iso3 = #{iso3} AND is_marine = #{type} GROUP BY iso3 Bijagós, Guinea Bissau Managing WCMC Information

Creating a Flat Dataset Adding Point Geometries SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, the_geom the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND is_marine = #{type} GROUP BY iso3 UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} ) a Mahajanga, Madagascar Managing WCMC Information

Creating a Flat Dataset Simplifying Geometries in Certain Countries COMPLEX_COUNTRIES = { 'marine' => ['GBR','USA','CAN','MYT','CIV','AUS'], 'land' => ['DEU','USA','FRA','GBR','AUS','FIN','BGR','CAN', 'ESP','SWE','BEL','EST','IRL','ITA','LTU', 'NZL','POL','CHE'] } def geometry_attribute country, area_type if COMPLEX_COUNTRIES[area_type].include? country.iso_3 'ST_Makevalid(ST_Buffer(ST_Simplify(the_geom,0.005),0.0))' else 'the_geom' end end United States Managing WCMC Information

Creating a Flat Dataset Simplifying Geometries in Certain Countries SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, ST_Union(#{geometry_attribute(country, area_type)}) the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND is_marine = #{type} UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} ) a United States Managing WCMC Information

Creating a Flat Dataset Dealing with Transnational Protected Areas SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, #{geometry_attribute(country, area_type)} the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND is_marine = #{type} UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} UNION SELECT country.iso_3, ST_Intersection(country.land_geom, polygon.the_geom) the_geom FROM standard_polygons polygon INNER JOIN countries country ON ST_Intersects(country.land_geom, polygon.the_geom) WHERE polygon.iso3 LIKE '%,%' AND iso3 = #{iso3} AND is_marine = #{type} ) a Waterton Glacier International Peace Park, United States / Canada Managing WCMC Information

Creating a Flat Dataset Excluding Not Relevant for Statistics’ Protected Areas SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, #{geometry_attribute(country, area_type)} the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT country.iso_3, ST_Intersection(country.land_geom, polygon.the_geom) the_geom FROM standard_polygons polygon INNER JOIN countries country ON ST_Intersects(country.land_geom, polygon.the_geom) WHERE polygon.iso3 LIKE '%,%' AND iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') Nilgiri, India ) a Managing WCMC Information

Creating a Flat Dataset Making Geometries Valid SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, #{geometry_attribute(country, area_type)} the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND ST_IsValid(polygon.wkb_geometry) AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT country.iso_3, ST_Makevalid(ST_Intersection(ST_Buffer(country.land_geom,0.0), polygon. the_geom)) the_geom FROM standard_polygons polygon INNER JOIN countries country ON ST_Intersects(ST_Buffer(country.land_geom,0.0), polygon. the_geom) Rio de Janeiro, Brazil WHERE polygon.iso3 LIKE '%,%' AND iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') ) a Managing WCMC Information

Creating a Flat Dataset Updating the Table with the flat geometries UPDATE countries SET #{type}_pas_geom = a.the_geom FROM( SELECT ST_UNION(the_geom) as the_geom FROM ( SELECT iso3, #{geometry_attribute(country, area_type)} the_geom FROM standard_polygons WHERE iso3 = #{iso3} AND ST_IsValid(polygon.wkb_geometry) AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT iso3, ST_Buffer(the_geom::geography, |/( rep_area*1000000 / pi() ))::geometry the_geom FROM standard_points WHERE iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') UNION SELECT country.iso_3, ST_Makevalid(ST_Intersection(ST_Buffer(country.land_geom,0.0), polygon.the_geom)) the_geom FROM standard_polygons polygon INNER JOIN countries country ON ST_Intersects(ST_Buffer(country.land_geom,0.0), polygon.the_geom) Quiçama, Angola WHERE polygon.iso3 LIKE '%,%' AND iso3 = #{iso3} AND is_marine = #{type} AND status NOT IN ('Proposed', 'Not Reported') AND desig NOT IN ('UNESCO-MAB Biosphere Reserve', 'UNESCO-MAB Réserve de Biosphère') ) b ) a Managing WCMC Information

Calculating Statistics Values to Calculate • Land Area (territory) • EEZ Area + Territorial Seas (territory) • Protected Areas Land Area • Protected Areas Marine Area Managing WCMC Information

Calculating Statistics Updating the Table with the flat geometries SELECT land_area, eez_area, ts_area, pa_land_area + pa_marine_area, pa_land_area, pa_marine_area, (pa_land_area + pa_marine_area) / (land_area + eez_area + ts_area)*100, pa_land_area / land_area * 100, FROM ( SELECT id, ST_Area(ST_Transform(land_pas_geom,954009)) pa_land_area, ST_Area(ST_Transform(marine_pas_geom,954009)) pa_marine_area, ST_Area(ST_Transform(land_geom,954009)) land_area, ST_Area(ST_Transform(eez_geom,954009)) eez_area, ST_Area(ST_Transform(ts_geom,954009)) ts_Area FROM countries ) areas United States Managing WCMC Information