I have been spatially joining land use zones to parcels successfully with the following query:
CREATE TABLE new_parcel AS
SELECT DISTINCT ON(t.geom) t.*, m.basezone, m.overlay
FROM parcel AS t
LEFT JOIN zoning AS m
ON st_intersects(t.geom, m.geom)
AND st_area(st_intersection(t.geom, m.geom)) / st_area(t.geom) > 0.5
Currently, more than 50% of the parcel's area must be within a single zone for a join to occur.
However, it turns out that some of the parcels overlap 3 or more zones and are not getting joined to the zoning
table because less than 50% of their area overlaps. For example, the parcels of dark purple color overlap more than two zones:
How would one modify the query to add extra columns in the resulting table which would include data from all zones overlapped?
The existing code produces a table that has
basezone | overlay
How could one produce
basezone_2|overlay_2|basezone_3|overlay_3|...
and so on for all zones overlapped?