Versão do conjunto de dados em análise: 01de Maio 2025
- Número total de features / polígonos [3]: 2615362
- Área total dos polígonos, em hectares [4]: 1255936.8
- Número de features / polígonos com geometrias inválidas [5]: 112282
- Número de features / polígonos repetidos [6]: 0
- Número de sobreposições [10] : 964584 (36,88 %)
- Área ocupada pelas sobreposições, em hectares: 30426.8 (2,42 %)
#### Operações Realizadas ####
[1]
ogrinfo -so opendata-rgg-01072024.gpkg opendata-rgg-01072024
INFO: Open of `opendata-rgg-01072024.gpkg'
using driver `GPKG' successful.
Layer name: opendata-rgg-01072024
Geometry: Multi Polygon
Feature Count: 2255870
Extent: (-94062.316000, -41320.713000) - (162060.682800, 276013.258500)
Layer SRS WKT:
PROJCRS["ETRS89 / Portugal TM06",
BASEGEOGCRS["ETRS89",
ENSEMBLE["European Terrestrial Reference System 1989 ensemble",
MEMBER["European Terrestrial Reference Frame 1989"],
MEMBER["European Terrestrial Reference Frame 1990"],
MEMBER["European Terrestrial Reference Frame 1991"],
MEMBER["European Terrestrial Reference Frame 1992"],
MEMBER["European Terrestrial Reference Frame 1993"],
MEMBER["European Terrestrial Reference Frame 1994"],
MEMBER["European Terrestrial Reference Frame 1996"],
MEMBER["European Terrestrial Reference Frame 1997"],
MEMBER["European Terrestrial Reference Frame 2000"],
MEMBER["European Terrestrial Reference Frame 2005"],
MEMBER["European Terrestrial Reference Frame 2014"],
ELLIPSOID["GRS 1980",6378137,298.257222101,
LENGTHUNIT["metre",1]],
ENSEMBLEACCURACY[0.1]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",4258]],
CONVERSION["Portugual TM06",
METHOD["Transverse Mercator",
ID["EPSG",9807]],
PARAMETER["Latitude of natural origin",39.6682583333333,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8801]],
PARAMETER["Longitude of natural origin",-8.13310833333333,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8802]],
PARAMETER["Scale factor at natural origin",1,
SCALEUNIT["unity",1],
ID["EPSG",8805]],
PARAMETER["False easting",0,
LENGTHUNIT["metre",1],
ID["EPSG",8806]],
PARAMETER["False northing",0,
LENGTHUNIT["metre",1],
ID["EPSG",8807]]],
CS[Cartesian,2],
AXIS["easting (X)",east,
ORDER[1],
LENGTHUNIT["metre",1]],
AXIS["northing (Y)",north,
ORDER[2],
LENGTHUNIT["metre",1]],
USAGE[
SCOPE["Topographic mapping (medium scale)."],
AREA["Portugal - mainland - onshore."],
BBOX[36.95,-9.56,42.16,-6.19]],
ID["EPSG",3763]]
Data axis to CRS axis mapping: 1,2
FID Column = fid
Geometry Column = geom
area: Real (0.0)[2]
ogr2ogr -f PostgreSQL PG:"host=localhost dbname=geocatalogo user=*** password=*** active_schema=catalogo" opendata-rgg-01072024.gpkg opendata-rgg-01072024 -nln catalogo.ebupi -lco FID=gid -overwrite[3]
geocatalogo=# SELECT count(*) FROM catalogo.ebupi;
count
---------
2255870
(1 row)[4]
geocatalogo=# SELECT sum(ST_Area(geom))/10000 AS area FROM catalogo.ebupi;
area
--------------------
1419771.2919224324
(1 row)[5]
geocatalogo=# SELECT count(*) FROM catalogo.ebupi WHERE ST_IsValid(geom) IS FALSE;
count
-------
0
(1 row)[6]
WITH repeated_polygons AS (
SELECT
ARRAY_AGG(gid) AS id_list,
COUNT(*) AS count,
geom
FROM
catalogo.ebupi
GROUP BY
geom
HAVING
COUNT(*) > 1
)
SELECT
ROW_NUMBER() OVER () AS id,
count,
ARRAY_TO_STRING(id_list, '|') AS id_list,
geom AS geom
FROM
repeated_polygons
ORDER BY count DESC;[7]
WITH polygon_repetitions AS (
SELECT
COUNT(*) AS repetition_count
FROM
catalogo.ebupi
GROUP BY
geom
HAVING
COUNT(*) > 1
)
SELECT
repetition_count,
COUNT(*) AS num_polygons
FROM
polygon_repetitions
GROUP BY
repetition_count
ORDER BY
repetition_count;[8]
WITH repeated_polygons AS (
SELECT
ARRAY_AGG(gid) AS id_list,
COUNT(*) AS count,
ST_Area(geom) AS area,
geom
FROM
catalogo.ebupi
GROUP BY
geom
HAVING
COUNT(*) > 1
)
SELECT
SUM(count - 1) AS total_extra_polygons,
SUM((count - 1) * area)/10000 AS total_extra_area
FROM
repeated_polygons;[9]
CREATE TABLE catalogo.ebupi_no_duplicates AS
WITH ranked_polygons AS (
SELECT
gid,
geom,
ROW_NUMBER() OVER (
PARTITION BY geom
ORDER BY gid ASC
) AS rank
FROM
catalogo.ebupi
)
SELECT
gid,
geom
FROM
ranked_polygons
WHERE
rank = 1;
ALTER TABLE catalogo.ebupi_no_duplicates
ADD CONSTRAINT ebupi_no_duplicates_pkey PRIMARY KEY (gid);
CREATE INDEX ebupi_no_duplicates_geom_idx
ON catalogo.ebupi_no_duplicates
USING GIST (geom);[10]
CREATE TABLE catalogo.ebupi_partial_overlaps AS
WITH bbox_filtered AS (
SELECT
a.gid AS gid_polygon1,
b.gid AS gid_polygon2,
a.geom AS geom1,
b.geom AS geom2
FROM
catalogo.ebupi_no_duplicates a,
catalogo.ebupi_no_duplicates b
WHERE
a.gid < b.gid
AND a.geom && b.geom
)
SELECT
row_number() over() AS gid,
gid_polygon1,
gid_polygon2,
ST_Area(ST_Intersection(geom1, geom2))/10000 AS area_hectares,
ST_Multi(ST_Intersection(geom1, geom2)) AS geom
FROM
bbox_filtered
WHERE
ST_Intersects(geom1, geom2)
AND (ST_GeometryType(ST_Intersection(geom1, geom2)) = 'ST_Polygon'
OR ST_GeometryType(ST_Intersection(geom1, geom2)) = 'ST_MultiPolygon');
ALTER TABLE catalogo.ebupi_partial_overlaps
ADD CONSTRAINT ebupi_partial_overlaps_pkey PRIMARY KEY (gid);
CREATE INDEX ebupi_partial_overlaps_geom_idx
ON catalogo.ebupi_partial_overlaps
USING GIST (geom);