fjcerignoni · December 13, 2022 18:59
diff --git a/pa_br_munbioma_process.sql b/pa_br_munbioma_process.sql
 INSERT INTO spatial_ref_sys (srid,auth_name,auth_srid,srtext,proj4text) VALUES
 	 (97823,'sr-org',7823,'PROJCS["Conica_Equivalente_de_Albers_Brasil",GEOGCS["GCS_SIRGAS2000",DATUM["D_SIRGAS2000",SPHEROID["Geodetic_Reference_System_of_1980",6378137,298.2572221009113]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Albers"],PARAMETER["standard_parallel_1",-2],PARAMETER["standard_parallel_2",-22],PARAMETER["latitude_of_origin",-12],PARAMETER["central_meridian",-54],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1]]','+proj=aea +lat_1=-2 +lat_2=-22 +lat_0=-12 +lon_0=-54 +x_0=0 +y_0=0 +ellps=GRS80 +units=m +no_defs');

 --DROP MATERIALIZED VIEW limites.pa_br_limite_line;
 CREATE MATERIALIZED VIEW limites.pa_br_limite_line AS (
 	WITH dump AS (
 		SELECT (st_dump(geom)).geom FROM limites.pa_br_limite 
 	)
 	SELECT row_number() OVER () as id, st_exteriorring(geom) as geom FROM dump 
 );
 CREATE INDEX pa_br_limite_line_geom_idx ON limites.pa_br_limite_line USING gist(geom);

 --DROP MATERIALIZED VIEW limites.pa_br_limite_line_buffer;
 CREATE MATERIALIZED VIEW limites.pa_br_limite_line_buffer AS (
 	WITH buffer AS (
 		SELECT id, st_transform(st_buffer(st_transform(geom,97823),10000),4674) as geom FROM limites.pa_br_limite_line 
 	), uni AS (
 		SELECT st_union(geom) as geom FROM buffer
 	), subdivide AS (	
 		SELECT st_subdivide(geom) as geom FROM uni
 	)
 	SELECT row_number() OVER () as id, geom FROM subdivide
 );

 --DROP MATERIALIZED VIEW limites.pa_br_municipios_border;
 CREATE MATERIALIZED VIEW limites.pa_br_municipios_border AS (
 	SELECT a.cd_mun, st_union(st_intersection(a.geom, b.geom)) as geom
 	FROM limites.pa_br_municipios a
 	JOIN limites.pa_br_limite_line_buffer b ON st_intersects(a.geom,b.geom)
 	GROUP BY a.cd_mun
 );
 CREATE INDEX pa_br_municipios_border_geom_idx ON limites.pa_br_municipios_border USING gist(geom);

 --DROP MATERIALIZED VIEW limites.pa_br_bioma_border;
 CREATE MATERIALIZED VIEW limites.pa_br_bioma_border AS (
 	 SELECT row_number() OVER () as id, a.id_bioma, a.bioma, st_union(st_intersection(a.geom,b.geom)) as geom
 	 FROM limites.pa_br_biomas a
 	 JOIN limites.pa_br_limite_line_buffer b ON st_intersects(a.geom,b.geom)
 	 GROUP BY a.id_bioma, a.bioma
 );
 CREATE INDEX pa_br_bioma_border_geom_idx ON limites.pa_br_bioma_border USING gist(geom);

 --DROP TABLE limites.pa_br_municipios_bioma_diff;
 CREATE TABLE limites.pa_br_municipios_bioma_diff AS (
 	SELECT row_number() OVER () as id, a.cd_mun, st_difference(a.geom,b.geom) geom
 	FROM limites.pa_br_municipios_border a
 	JOIN limites.pa_br_bioma_border b ON st_overlaps(a.geom,b.geom)
 );
 CREATE INDEX pa_br_municipios_bioma_diff_geom_idx ON limites.pa_br_municipios_bioma_diff USING gist(geom);

 --DROP TABLE limites.pa_br_municipios_bioma_diff_dump;
 CREATE TABLE limites.pa_br_municipios_bioma_diff_dump AS (
 	SELECT cd_mun, (st_dump(geom)).geom FROM limites.pa_br_municipios_bioma_diff
 );

 ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD COLUMN fid serial;
 ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD COLUMN id_bioma int;

 ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD CONSTRAINT pa_br_municipios_bioma_diff_dump_pk PRIMARY KEY (fid);
 CREATE INDEX pa_br_municipios_bioma_diff_dump_geom_idx ON limites.pa_br_municipios_bioma_diff_dump USING gist(geom);

 ------------
 -- OPEN IN QGIS AND MANUALLY ASSIGN BIOME ID TO THE TABLE limites.pa_br_municipios_bioma_diff_dump
 -----------

 --DROP TABLE limites.pa_br_munbioma;
 CREATE TABLE limites.pa_br_munbioma AS (
 	WITH uni AS (
 		SELECT cd_mun, id_bioma, st_union(geom) as geom 
 		FROM limites.pa_br_municipios_bioma_diff_dump
 		GROUP BY cd_mun, id_bioma
 	), process AS (
 		SELECT *, st_area(st_transform(geom,97823))/10000 as area_ha FROM
 		(SELECT a.cd_mun, b.id_bioma, 
 			   CASE WHEN st_within(a.geom,b.geom) THEN st_multi(a.geom)::geometry(MULTIPOLYGON,4674)
 			   	    ELSE st_multi(st_collectionextract(st_intersection(a.geom,b.geom),3))::geometry(MULTIPOLYGON,4674) END AS geom
 		FROM limites.pa_br_municipios a
 		JOIN limites.pa_br_biomas b ON st_intersects(a.geom,b.geom) AND NOT st_touches(a.geom,b.geom)
 		UNION
 		SELECT cd_mun, id_bioma, geom FROM uni) foo
 	)
 	SELECT row_number() OVER () as id_munbioma, cd_mun, id_bioma, 
 		   CASE WHEN NOT st_isvalid(st_union(geom)) THEN st_multi(st_collectionextract(st_makevalid(st_union(geom)),3)) ELSE st_multi(st_union(geom)) END as geom
 	FROM process
 	GROUP BY cd_mun, id_bioma
 	ORDER BY cd_mun, id_bioma
 );	

 CREATE INDEX pa_br_munbioma_geom_idx ON limites.pa_br_munbioma USING gist(geom);
 ALTER TABLE limites.pa_br_munbioma ADD CONSTRAINT pa_br_munbioma_pk PRIMARY KEY (id_munbioma);
	INSERT INTO spatial_ref_sys (srid,auth_name,auth_srid,srtext,proj4text) VALUES
	(97823,'sr-org',7823,'PROJCS["Conica_Equivalente_de_Albers_Brasil",GEOGCS["GCS_SIRGAS2000",DATUM["D_SIRGAS2000",SPHEROID["Geodetic_Reference_System_of_1980",6378137,298.2572221009113]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Albers"],PARAMETER["standard_parallel_1",-2],PARAMETER["standard_parallel_2",-22],PARAMETER["latitude_of_origin",-12],PARAMETER["central_meridian",-54],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1]]','+proj=aea +lat_1=-2 +lat_2=-22 +lat_0=-12 +lon_0=-54 +x_0=0 +y_0=0 +ellps=GRS80 +units=m +no_defs');

	--DROP MATERIALIZED VIEW limites.pa_br_limite_line;
	CREATE MATERIALIZED VIEW limites.pa_br_limite_line AS (
	WITH dump AS (
	SELECT (st_dump(geom)).geom FROM limites.pa_br_limite
	)
	SELECT row_number() OVER () as id, st_exteriorring(geom) as geom FROM dump
	);
	CREATE INDEX pa_br_limite_line_geom_idx ON limites.pa_br_limite_line USING gist(geom);

	--DROP MATERIALIZED VIEW limites.pa_br_limite_line_buffer;
	CREATE MATERIALIZED VIEW limites.pa_br_limite_line_buffer AS (
	WITH buffer AS (
	SELECT id, st_transform(st_buffer(st_transform(geom,97823),10000),4674) as geom FROM limites.pa_br_limite_line
	), uni AS (
	SELECT st_union(geom) as geom FROM buffer
	), subdivide AS (
	SELECT st_subdivide(geom) as geom FROM uni
	)
	SELECT row_number() OVER () as id, geom FROM subdivide
	);

	--DROP MATERIALIZED VIEW limites.pa_br_municipios_border;
	CREATE MATERIALIZED VIEW limites.pa_br_municipios_border AS (
	SELECT a.cd_mun, st_union(st_intersection(a.geom, b.geom)) as geom
	FROM limites.pa_br_municipios a
	JOIN limites.pa_br_limite_line_buffer b ON st_intersects(a.geom,b.geom)
	GROUP BY a.cd_mun
	);
	CREATE INDEX pa_br_municipios_border_geom_idx ON limites.pa_br_municipios_border USING gist(geom);

	--DROP MATERIALIZED VIEW limites.pa_br_bioma_border;
	CREATE MATERIALIZED VIEW limites.pa_br_bioma_border AS (
	SELECT row_number() OVER () as id, a.id_bioma, a.bioma, st_union(st_intersection(a.geom,b.geom)) as geom
	FROM limites.pa_br_biomas a
	JOIN limites.pa_br_limite_line_buffer b ON st_intersects(a.geom,b.geom)
	GROUP BY a.id_bioma, a.bioma
	);
	CREATE INDEX pa_br_bioma_border_geom_idx ON limites.pa_br_bioma_border USING gist(geom);

	--DROP TABLE limites.pa_br_municipios_bioma_diff;
	CREATE TABLE limites.pa_br_municipios_bioma_diff AS (
	SELECT row_number() OVER () as id, a.cd_mun, st_difference(a.geom,b.geom) geom
	FROM limites.pa_br_municipios_border a
	JOIN limites.pa_br_bioma_border b ON st_overlaps(a.geom,b.geom)
	);
	CREATE INDEX pa_br_municipios_bioma_diff_geom_idx ON limites.pa_br_municipios_bioma_diff USING gist(geom);

	--DROP TABLE limites.pa_br_municipios_bioma_diff_dump;
	CREATE TABLE limites.pa_br_municipios_bioma_diff_dump AS (
	SELECT cd_mun, (st_dump(geom)).geom FROM limites.pa_br_municipios_bioma_diff
	);

	ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD COLUMN fid serial;
	ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD COLUMN id_bioma int;

	ALTER TABLE limites.pa_br_municipios_bioma_diff_dump ADD CONSTRAINT pa_br_municipios_bioma_diff_dump_pk PRIMARY KEY (fid);
	CREATE INDEX pa_br_municipios_bioma_diff_dump_geom_idx ON limites.pa_br_municipios_bioma_diff_dump USING gist(geom);

	------------
	-- OPEN IN QGIS AND MANUALLY ASSIGN BIOME ID TO THE TABLE limites.pa_br_municipios_bioma_diff_dump
	-----------

	--DROP TABLE limites.pa_br_munbioma;
	CREATE TABLE limites.pa_br_munbioma AS (
	WITH uni AS (
	SELECT cd_mun, id_bioma, st_union(geom) as geom
	FROM limites.pa_br_municipios_bioma_diff_dump
	GROUP BY cd_mun, id_bioma
	), process AS (
	SELECT *, st_area(st_transform(geom,97823))/10000 as area_ha FROM
	(SELECT a.cd_mun, b.id_bioma,
	CASE WHEN st_within(a.geom,b.geom) THEN st_multi(a.geom)::geometry(MULTIPOLYGON,4674)
	ELSE st_multi(st_collectionextract(st_intersection(a.geom,b.geom),3))::geometry(MULTIPOLYGON,4674) END AS geom
	FROM limites.pa_br_municipios a
	JOIN limites.pa_br_biomas b ON st_intersects(a.geom,b.geom) AND NOT st_touches(a.geom,b.geom)
	UNION
	SELECT cd_mun, id_bioma, geom FROM uni) foo
	)
	SELECT row_number() OVER () as id_munbioma, cd_mun, id_bioma,
	CASE WHEN NOT st_isvalid(st_union(geom)) THEN st_multi(st_collectionextract(st_makevalid(st_union(geom)),3)) ELSE st_multi(st_union(geom)) END as geom
	FROM process
	GROUP BY cd_mun, id_bioma
	ORDER BY cd_mun, id_bioma
	);

	CREATE INDEX pa_br_munbioma_geom_idx ON limites.pa_br_munbioma USING gist(geom);
	ALTER TABLE limites.pa_br_munbioma ADD CONSTRAINT pa_br_munbioma_pk PRIMARY KEY (id_munbioma);