Substrat values from two polygon tables according to maximum polygon overlap using Postgis

Question

I have two polygon layers/tables in QGIS. The two layers represent (almost) the same spatial division (census boundaries). Basically, the polygons don't perfectly overlap because of differences in precisions between the two layers, but they should represent about the same census boundaries. Each layer has a "var" integer field.

My goal is to find the difference between the two layers' var values, polygon per polygon. So if two polygons' positions almost match, I want to substrat the value of one to the other, and attribute the result to one of the polygons.

I have done this thus far:

SELECT a.var, b.var, b.var - a.var as diff, b.geometry
FROM layer1 as a, layer2 as b
WHERE ST_Intersects(a.geometry, b.geometry);

But it results in way two many polygons. What is the proper way to achieve this?

Answer 1

You can intersect, order the result by area and select distinct on the id. Select distinct will select the id with the first=largest area.

select distinct on (a.ogc_fid) 
                    a.ogc_fid aid, 
                    b.ogc_fid bid, 
                    a.geom ageom, 
                    b.geom bgeom, 
                    st_area(st_intersection(a.geom, b.geom)) area
from test.districtsbuff a
join test.districts b
on st_intersects(a.geom, b.geom)
order by aid, area desc

Answer 2

SELECT a.var, b.var, b.var - a.var as diff, b.geometry
FROM layer1 as a, layer2 as b
WHERE ST_Area(ST_Intersection(a.geometry,b.geometry)) > 0
 and ST_Area(ST_SymDifference(a.geometry,b.geometry))/ST_Area(ST_Intersection(a.geometry,b.geometry)) < 0.01;

Difference between common polygon areas less then 1% here. Select limit by experience.