Spatial join if percentage of area is within

Question

I have been performing spatial joins but there is a problem. The geometries of both layers are slightly misaligned. I would like to join by location where parcels are within historic_subdistricts and to keep both matching and non matching records. The query below works but the problem is that ST_Within only works if geometry of one layer is completely contained by the other.

CREATE TABLE test_join AS
SELECT t.*, m.*
FROM parcels AS t
LEFT JOIN historic_subdistricts= AS m
ON ST_Within(t.geom, m.geom)

How would one write a query to perform a spatial join if 90% of the area of a parcel in parcels is within a subdistrict in historic_subdistricts ?

Here is a visualization of the problem:

Answer 1

You can try below query by getting the intersected area of both geometries and the total area of Parcel geometry, then get the percentage and compare it 90%:

CREATE TABLE test_join AS
SELECT t.*, m.*
FROM parcels AS t , historic_subdistricts AS m
where st_intersects(t.geom, m.geom) and    
(st_area(st_intersection(t.geom, m.geom))/st_area(t.geom)) > 0.9

Make sure both fields are indexed.

Answer 2

My favourite trick for cases like this is to join on the centroids:

 SELECT parcel.id, historic_subdistricts.id
 FROM parcel JOIN historic_subdistricts
 ON ST_Contains(historic_subdistricts.geom, ST_Centroid(parcel.geom))

This neatly avoids double-counting, and unless your overlaps are really really large should correctly assign all your boundary shapes.