Postgres/PostGIS Spatial Query for Percentage Overlap (and display results in Excel dynamically!)
Channel: GIS Coordinated
Date: 2022-06-17

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/pan-european/corine-land-cover/

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

How to load data into Postgres/PostGIS: 
https://www.youtube.com/watch?v=pWWAY9yzeI0