Video 140
In this longer (!) video, I explain in detail the creation of a single SQL statement that generates a report for postcodes in Sicily and the amount of overlap by a specific Land Cover code (in this case 311 Broad Leaved Forest). The question is:

"What percentage of each polygon from table A is overlapped by polygons from table B?"

This video is for those wishing to start their journey into making the database do the real work...

I show the data in QGIS first, clarifying what it is and what it looks like and what I want to end up with (in terms of a little report)

I then use pgAdmin ver 6.10 to run the SQL Statements

After checking that the results look right, I then turn that SQL Statement into a VIEW

In Excel, I then access that VIEW directly from the database but slightly transform the data to meet my specific requirement that being I want just the Top 10 records in terms of percentage.


The actual query:

select b."CAP" as "Postcode",
to_char(Sum(ST_Area(ST_INTERSECTION(a.geom, b.geom))) / ST_Area(b.geom) * 100,'99.99%') as "Percentage Overlap"

from public."LandUseSicily" a, public."SicilyCodes" b

WHERE ST_Overlaps(b.geom, a.geom) group by b."CAP", b.geom order by "Percentage Overlap" ;

(amend "a" and "b" to your tables)

The data comes from CORINE:
https://land.copernicus.eu/paneurope...

Postgres ODBC Driver (MSI, if in doubt pick the last one in this list!):
https://www.postgresql.org/ftp/odbc/v...

How to load data into Postgres/PostGIS:
   • 3 Ways to load ANY geodata into Post...