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

▶

Feb 24, 2024 464 likes •760 views

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

SLIDE 1

Managing WCMC Information

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

SLIDE 2

Managing WCMC Information

Protected Planet

All Protected Areas of the World in one Website

http://www.protectedplanet.net/

SLIDE 3

Managing WCMC Information

Protected Planet

All Protected Areas of the World in one Website

SLIDE 4

Managing WCMC Information

Protected Planet

Protected Area Page

SLIDE 5

Managing WCMC Information

Protected Planet

Github Repository

SLIDE 6

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

SLIDE 7

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

SLIDE 8

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

SLIDE 9

Managing WCMC Information

Statistics

National Statistics

SLIDE 10

Managing WCMC Information

Statistics

Overlapping Protected Areas

SLIDE 11

Managing WCMC Information

Statistics

Overlapping Protected Areas

SLIDE 12

Managing WCMC Information

Statistics

Overlapping Protected Areas

SLIDE 13

Managing WCMC Information

Statistics

Overlapping Protected Areas

SLIDE 14

Managing WCMC Information

Creating a Flat Dataset

Dissolving All Geometries

SELECT iso3, ST_Union(the_geom) FROM standard_polygons GROUP BY iso3 Medebach, Germany

SLIDE 15

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

SLIDE 16

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

SLIDE 17

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

SLIDE 18

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

SLIDE 19

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

SLIDE 20

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

SLIDE 21

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') ) a Nilgiri, India

SLIDE 22

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) 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 Rio de Janeiro, Brazil

SLIDE 23

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) 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 Quiçama, Angola

SLIDE 24

Managing WCMC Information

Calculating Statistics

Values to Calculate

Land Area (territory)
EEZ Area + Territorial Seas (territory)
Protected Areas Land Area
Protected Areas Marine Area

SLIDE 25

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

SLIDE 26

Managing WCMC Information

Calculating Statistics

Handling Null Values

SELECT land_area, eez_area, ts_area, COALESCE(pa_land_area,0) + COALESCE(pa_marine_area,0), pa_land_area, pa_marine_area, (COALESCE(pa_land_area,0) + COALESCE(pa_marine_area,0)) / (land_area + COALESCE(eez_area, 0) + COALESCE(ts_area,0))*100, COALESCE(pa_land_area,0) / land_area * 100, CASE WHEN eez_area = 0 THEN ELSE COALESCE(pa_eez_area,0) / eez_area * 100 END, CASE WHEN ts_area = 0 THEN ELSE COALESCE(pa_ts_area,0) / ts_area * 100 END 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 China

SLIDE 27

Managing WCMC Information

Calculating Statistics

Insert into Stats Table

INSERT INTO country_statistics ( country_id, land_area, eez_area, ts_area, pa_area, pa_land_area, pa_marine_area, pa_eez_area, pa_ts_area, percentage_pa_cover, percentage_pa_land_cover, percentage_pa_eez_cover, percentage_pa_ts_cover, created_at, updated_at ) SELECT land_area, eez_area, ts_area, COALESCE(pa_land_area,0) + COALESCE(pa_marine_area,0), pa_land_area, pa_marine_area, (COALESCE(pa_land_area,0) + COALESCE(pa_marine_area,0)) / (land_area + COALESCE(eez_area, 0) + COALESCE(ts_area,0))*100, COALESCE(pa_land_area,0) / land_area * 100, CASE WHEN eez_area = 0 THEN ELSE COALESCE(pa_eez_area,0) / eez_area * 100 END, CASE WHEN ts_area = 0 THEN ELSE COALESCE(pa_ts_area,0) / ts_area * 100 END 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 Republic of Korea

SLIDE 28

Managing WCMC Information

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